Buzz
In addition to the common mathematical functions in Excel, duplicating formulas with references is one of the most crucial aspects. Understanding references correctly allows you to create a unique formula that can be copied to any Excel spreadsheet and any desired location. In this article, Mytour will guide you on how to duplicate formulas with references in Excel.
How to Duplicate Formulas with References in Excel
1. How to Duplicate Formulas with Relative References:
Relative references are references where the row and column in the formula will change when copying the formula to a different location on the spreadsheet. The relative address reference is in the form: A3; B1:C7;…
Consider the example: For the data table shown, calculate the column “TOTAL” = “QUANTITY” * “UNIT PRICE”
In cell I2, enter the formula =G2*H2, press enter. To copy the formula, follow these steps:
Method 1: In cell I2, press Ctrl + C, select the data range I3:I12, press Ctrl + V
Method 2: Select the data range I2: I12, press Ctrl + D
Method 3: Hover over the bottom right corner of cell I2, when the black plus sign appears, left-click and drag to cell I12
Method 4: Hover over the bottom right corner of cell I2, when the black plus sign appears, double-click to copy the formula.
All 04 methods above yield results as shown in the image.
2. How to Duplicate Formulas with Absolute References:
Absolute references are references where the row and column do not change when copying the formula to a different location on the spreadsheet. Absolute references are in the form: $A$1 ; $B$2:$E$5 ; …
Consider the example: For the spreadsheet shown, use the formula to represent warehouse data exported to the range I4: I14, where the export warehouse is cell E1.
In cell I4, enter the formula = $E$1, press enter. Then, copy the formula using the following methods:
Method 1: In cell I4, press Ctrl + C, select the data range I5:I14, press Ctrl + V
Method 2: Select the data range I4: I14, press Ctrl + D
Method 3: Hover over the bottom right corner of cell I4, when the black plus sign appears, left-click and drag to cell I14
Method 4: Hover over the bottom right corner of cell I4, when the black plus sign appears, double-click to copy the formula.
All four methods above yield results as shown in the image.
3. How to Duplicate Formulas with Mixed References:
Mixed references are a combination of relative and absolute references. Mixed references can fix the row or column or both.
Example: Consider the data table below and calculate the total quantity by customer.
In cell E15, enter the formula = SUMIF($E$2:$E$12,B15,$G$2:$G$12), then press enter.
Method 1: In cell E15, press Ctrl + C, select the data range E16:E17, press Ctrl + V
Method 2: Select the data range E15: E17, press Ctrl + D
Method 3: Hover over the bottom right corner of cell E15 until the black plus sign appears, left-click, and drag to cell E17
Method 4: Hover over the bottom right corner of cell E15 until the black plus sign appears, double-click to copy the formula.
After applying one of the 4 methods above, the result is as shown in the image.
Thus, this article by Mytour guides you on how to copy formulas with references in Excel. This method applies to all versions of Office such as: Office 2016, Office 2013, Office 2010, Office 2007,... . Additionally, you can explore other Excel applications on the Mytour website.
