How to make Excel automatically copy formulas and calculate totals
How to make Excel automatically copy formulas and calculate totals: Using functions/formulas to calculate data is almost a daily task for Excel users. However, when adding new rows/columns of data using the same approach, the default is that those formulas are not applied to the new rows/columns. At this point, you have to re-enter the formula and function to calculate for the new data position, which is very time-consuming. There is a more efficient way to customize Excel to automatically copy formulas and calculate totals when changing the data range. Please follow the article below for instructions.
Step 1
For example, we have a data table as shown below, first we need to set the format of the data table to Table. You select the entire data table and then select the Home tab on the toolbar. Then select Format as Table. The scroll bar will display the available Table templates in Excel. You select a template that you like.
How to make Excel automatically copy formulas and calculate totals
Step 2
At this point, the Format As Table dialog box appears. You select the entire table and then click OK.
How to make Excel automatically copy formulas and calculate totals
Step 3
Next, you select the Design tab on the toolbar. Here you can adjust the format of the table in the Table Styles section. In addition, there are sections for you to customize the table as follows:
- Total row: Add a total row to the end of the worksheet, with a list of functions for each cell in the total row.
- Banded row and Banded columns: Change the darkness/lightness of alternating rows/columns.
- First column and last column: Change the format of the first and last column of the table.
- Filter button: Hide or show the arrow on the header row.
How to make Excel automatically copy formulas and calculate totals
Step 4
To automatically calculate totals, you just need to tick the Total Row box. That’s all the total cell will be automatically inserted at the end of the worksheet.
How to make Excel automatically copy formulas and calculate totals
Step 5
Next, to add a row, you right-click on the position where you want to add the row on the data table.
The scroll bar appears, you select Insert => Table Rows Above.
How to make Excel automatically copy formulas and calculate totals
That’s it, a new row has been added to the data table. Along with that, the cell in the Total column will also be automatically copied with the formula like the remaining cells of this column. Now you just need to enter data into the remaining cells in the newly added row.
How to make Excel automatically copy formulas and calculate totals
In this way, the article has instructed you on the trick to automatically copy formulas, calculate totals in Excel data tables. Hopefully, the article will be helpful to you in your work. Wish you success!
