When you copy and paste a formula with absolute cell references Excel substitute new references to reflect the new formula location?

Excel Cell Reference [Table of Contents]

Cell Reference in Excel
Types of Cell Reference in Excel
- #1 – Relative Cell Reference in Excel
- #2- Absolute Cell Reference in Excel
- #3- Mixed Cell Reference in Excel

Cell Reference in excel is the way to represent the identity and the location of any cell with the help of combining Column Name and Row Number on a worksheet. For example, if we say cell B10, then it expands as Column B and 10th Row. Similarly, we can define or declare cell references to any position in the worksheet. We can also activate R1C1 from Excel Options, another way for cell reference, where R1 is Row1 and C1 is Column1.

Types of Cell Reference in Excel

We have three different types of Cell References in Excel –

Relative Cell Reference in Excel
Absolute Cell Reference in Excel
Mixed Cell Reference in Excel

Using the correct type of Cell Reference in a particular scenario will save a lot of time and effort and make the work much easier.

#1 – Relative Cell Reference in Excel

Relative cell references in excel refer to a cell or a range of cells in excel. Every time a value is entered into a formula, such as SUMIFS, it is possible to input into Excel a “cell reference” as a substitute for a hard-coded number. A cell reference may come in the form B2, where B corresponds to the cell column letter in question and 2 represents the row number. Whenever Excel comes across a cell reference, it visits the particular cell, extracts out its value, and uses that value in whichever formula that you’re writing. When this cell reference in excel is duplicated to a different location, the relative cell references in excel correspondingly also change automatically.

When we refer to cells like this, we can achieve it with any of the two cell reference types in excel: absolute and relative. The demarcation between these two distinct reference types is the different inherent behavior when you drag or copy and paste them to different cells. Relative Cell references can alter themselves and adjust as you copy and paste them; absolute references contrarily do not. Therefore, in order to successfully achieve results in Excel, it is critical to be able to use relative and absolute cell references in the right way.

How to effectively use Relative cell reference in Excel?

To comprehensively understand the versatility and usability of this amazing feature of Excel, we will need to look at a few practical examples to grasp its true value.

Example #1

Let us consider a simple example to explain the mechanics of Relative Cell Reference in Excel. If we wish to have the sum of two numbers in two different cells – A1 and A2, and have the result in a third cell A3.

So we apply the formula =A1+A2

Which would yield the result as 100 in A3.

Now suppose, we have a similar scenario in the next column [B]. Cell B1 and B2 have two numbers, and we wish to have the sum in B3.

We can achieve this in two different ways:

Here we physically write the formula to add the two cells B1 and B2 in B3.

The result is 30.

Or we could simply copy the formula from cell A3 and paste it into cell B3 [it would work if we drag the formula from A3 to B3 also].

So, when we copy the contents of cell A3 and paste in B3 or drag the contents of cell A3 and paste in B3, the formula gets copied, not the result. We could achieve the same result by right-clicking on cell A3 and use the Copy option.

And after that, we move to the next cell, B3, and right-click and select “Formulas [f]”.

What this means is that cell A3=A1+A2. When we copy A3 and move one cell to the right and paste it onto cell B3, the formula automatically adapts itself and changes to become B3=B1+B2. It applies the summation formula for B1 and B2 cells instead.

Example #2

Now, let’s look at yet another practical scenario that would make the concept quite clear. Let us assume that we have a data set consisting of the Unit Price of a product and the quantity sold for each of them. Now our objective is to calculate the Sale Price, which the following formula can describe:

Sale Price = Unit Price x Units Sold.

To be able to find the Sale Price, we need to now multiply Unit Price with Units Sold for each product. So, we shall now proceed to apply this formula for the first cell in Sale Price, i.e. for Product 1.

When we apply the formula, we get the following result for Product 1:

It successfully multiplied the Unit Cost by the Units Sold for Product 1, i.e. cell G2 * cell H2, i.e. 1826.00 * 20, which gives us the result 36520.00.

So now we see that we have 9 other products to go. In real case scenarios, this could go up to hundreds or thousands of rows. It becomes difficult and nearly impossible to simply go about writing the formula for each row.

Hence, we will use the Relative Reference feature of Excel and simply copy the contents of cell I2 and paste in all of the remaining cells in the table for the column Sale Price or simply drag the formula from cell I2 to the rest of the rows in that column and get the results for the whole table in less than 5 seconds.

Here we press Ctrl + D. So the output will look like below:

#2 – Absolute Cell Reference in Excel

Most of our daily work in Excel involves handling formulae. Therefore, having a working knowledge of Relative, Absolute, or Mixed cell References in excel becomes quite important.

Let us see the following:

=A1 is a relative reference, where both the row and column change when we copy the formula cell.

=$A$1 is an absolute cell reference; both the column and row are locked and do not change when we copy the formula cell. Thus, the cell value remains constant.

In =$A1, the column is locked, and the row can keep changing for that specific column.

In =A$1, the row is locked, and the column can keep changing for that specific row.

Unlike Relative Reference, which can change as it moves to different cells, the absolute reference doesn’t change. The only thing required here is to lock the specific cell completely.

Using a dollar sign in the formula, w.r.t. a cell reference makes it an absolute cell reference as the dollar sign locks the cell. We can lock either the row or the column using the dollar sign. If the “$” is before an alphabet, then it locks a column, and if the “$” is before a number, then a row is locked.

#3- Mixed Cell Reference in Excel

How to effectively use Absolute cell reference in Excel also how to use Mixed cell Reference in excel?

To get a comprehensive understanding of Absolute and Mixed cell Reference in excel, let us look at the following example.

We have the sales data for 4 sales managers across different months, where sales have occurred multiple times in a month.

Our objective is to calculate the consolidated sales summary of all 4 sales managers. We shall apply the SUMIFS formula to get the desired result.

The result will be as:

Let us observe the formula to see what happened.

In the “sum_range”, we have $C$2:$C$17. There is a dollar sign in front of both the alphabet and the numbers. Thus, both the rows and columns for the cell range are locked. This is an absolute cell reference.
Next, we have “criteria_range1”. Here too, we have absolute cell reference.
After this, we have “criteria1” – $F2. Here we see that only the column will be locked while copying the formula cell, meaning only the row will change when we copy the formula to a different cell [moving down]. This is a mixed cell reference.
Next, we have “criteria_range2”, which is also an absolute cell reference.
The final segment of the formula is “criteria2” – G$1. Here we observe that the dollar sign is present in front of the number and not the alphabet. Thus, only the row is locked when we copy the formula cell. The column can change when we copy the formula cell to a different cell [moving right]. This is a mixed cell reference.

Dragging the formula across the summary table by pressing the Ctrl+D key first and later Ctrl+R. We get the following result:

Mixed cell reference refers to a particular row or column only, such as =$A2 or =A$2. If we want to create a mixed cell reference, we can press the F4 key on the formula two to three times, as per your requirement, i.e. to refer to a row or a column. Pressing F4 again will cause the cell reference to change to relative reference.

Things to Remember

While copying the Excel formula, relative referencing is generally what is desired. This is the reason why this is the default behavior of Excel. But sometimes, the objective might be to apply absolute reference rather than relative Cell reference in excel. Absolute Reference is making a cell reference fixed to an absolute cell address, due to which, when the formula is copied, it remains unaltered.
Absolutely no dollar signs are required with Relative referencing. When we copy the formula from one place to others, the formula will adapt accordingly. So, if we type =B1+B2 into cell B3 and then drag or copy-paste the same formula into the cell C3, the Relative Cell reference would automatically adjust the formula to =C1+C2.
With Relative referencing, the referred cells automatically adjust themselves in the formula as per your movement, either to the right, left, upward or downwards.
With Relative referencing, if we were to give a reference to cell D10 and then shift one cell downwards, it would change to D11; if instead, we shift one cell upwards, it would change to D9. If we shift one cell to the right, the reference will change to E10, and instead, if we shift one cell to the left, the reference would automatically adjust itself to C10.
Pressing F4 once will change relative Cell reference to absolute Cell reference in excel.
Pressing F4 twice will change the cell reference to a mixed reference where the row is locked.
Pressing F4 thrice will change the cell reference to a mixed reference where the column is locked.
Pressing F4 for the fourth time will change the cell reference back to the relative reference in excel.

When you copy and paste a formula with absolute cell references Excel substitutes new references to reflect the new formula location?

When you copy and paste a formula with absolute cell references, Excel substitutes new references to reflect the new formula location. In formulas, calculations in square brackets are calculated first. To enter data in a cell using examples already in your workbook, you can use the flash fill feature.

When you copy and paste a formula to a new location the formulas relative references do not change?

Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.