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!

4 ★ | 1 Vote

May be interested

  • How to create an Excel formula in Notepad ++How to create an Excel formula in Notepad ++
    when entering excel formulas in notepad ++, there will be additional features highlighting, highlighting, and suggestion. the following article will guide you how to set up an excel input formula in notepad ++.
  • How to copy formulas that contain references in ExcelHow to copy formulas that contain references in Excel
    the following article details how to copy formulas that contain references in excel.
  • How to enter formulas in ExcelHow to enter formulas in Excel
    how to enter formulas in excel. to enter a formula in excel, you must start with the equal sign or excel will interpret it as a string. to enter the formula do the following: step 1: type the equal sign at the position of the cell to calculate according to the formula. step 2: enter the clutch
  • Copy, copy data in ExcelCopy, copy data in Excel
    with similar data or formats, instead of having to enter them all in turn, copying (copying) data in excel is very necessary. using the methods of copying data, you can quickly copy data, formulas, formatting ...
  • Instructions for copying formulas and data between Excel tablesInstructions for copying formulas and data between Excel tables
    often you still copy and paste normally with the key combination ctrl + c and ctrl + v for each data. but if you need to copy data or formulas to different cells and not adjacent to each other, this will take a lot of time ...
  • How to copy formulas and data between Excel tables quicklyHow to copy formulas and data between Excel tables quickly
    copying formulas and data between excel tables helps you work more efficiently without having to re-enter them manually. depending on your needs, you can use keyboard shortcuts or advanced features to ensure accurate copying. let's explore effective ways to do it in this article.
  • How to calculate time in Excel: Add and subtract timeHow to calculate time in Excel: Add and subtract time
    excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. the following software tips will guide you the most basic formulas. the problem is that you have time to complete two steps of work, and you want to know how many hours and minutes it will take to complete the job.
  • How to delete quick recipe on ExcelHow to delete quick recipe on Excel
    in addition to locking excel formulas, we can delete formulas and retain results, or delete all cells with formulas and results.
  • Excel tricks that let you write formulas more easilyExcel tricks that let you write formulas more easily
    if you've ever stared at a long excel formula and thought, 'is there a better way to write it?', the good news is that there is. use lambda and let.
  • How to lock formulas in Excel - Protect excel formulasHow to lock formulas in Excel - Protect excel formulas
    when sharing excel files, if you don't want others to edit the formulas or view the formulas, you can lock the formulas.