How to automatically calculate and copy formulas in Excel

The use of Excel functions or Excel formulas is essential for faster and more accurate data calculations. However, when adding new content with the same implementation, the default is not applied to the content, but users must re-enter the formula and function to calculate, it is relatively time consuming.

Previously, Network Administrator once instructed you to read how to copy formulas in Excel using Copy and Paste or Copy and Paste Special operations, but the steps are still relatively manual. If so, users can refer to the article below to automatically copy formulas in Excel to calculate values ​​in the form argument, available formulas.

1. 3 ways to calculate totals in Excel
2. How to fix the SUM function doesn't add up in Excel
3. 10 ways to use the Paste feature in Excel
4. Instructions for adding alternate blank lines on Microsoft Excel

How to automatically copy formulas in Excel

For example, we have a data table as shown below. In the table, the PRODUCT function is used to calculate the amount of money for each product.

How to automatically calculate and copy formulas in Excel Picture 1

Step 1:

Users black out the entire data area in the Format as Table section and click on the inverted triangle icon . Displays a list of table templates for you to use.

How to automatically calculate and copy formulas in Excel Picture 2

Step 2:

Select the correct number of rows and columns and click OK to create the data table type.

How to automatically calculate and copy formulas in Excel Picture 3

The result of the data table is displayed with the new interface. Continue to highlight the table header area, select Tabel Tools, then click the Design item in the top corner.

How to automatically calculate and copy formulas in Excel Picture 4

Step 3:

In Table Styles, click on the triangle icon and choose white alternating styles . Also in this section we also have some other custom items such as:

1. Total row: Add the total row at the end of the worksheet, with the list of functions for each cell in the total row.
2. Banded row and Banded columns: Change the density of alternating rows / columns.
3. First column and last column: Change the first and last column format of the table.
4. Filter button: Hide or show the arrow in the title row.

How to automatically calculate and copy formulas in Excel Picture 5

Step 4:

Next, select the column and then right-click and choose Table and select Totals Row to calculate the total column.

How to automatically calculate and copy formulas in Excel Picture 6

Show the total for the column you selected.

How to automatically calculate and copy formulas in Excel Picture 7

Step 5:

Next, right-click on any row that wants to add a value, then select Insert , click Table Rows Above to add rows to the spreadsheet.

How to automatically calculate and copy formulas in Excel Picture 8

Then a new row is added to the data table and the column into money will report zero because no data is available.

How to automatically calculate and copy formulas in Excel Picture 9

Now the user just needs to enter the value for the argument is finished. Immediately the cell containing the PRODUCT function is automatically executed, the calculation is also carried out as shown below.

How to automatically calculate and copy formulas in Excel Picture 10

So even if the table has more new Excel data rows, the function and formula are still applied to the new arguments. But the user has to add new data rows before the total cell, the new data is applied to the formula.

I wish you all success!