What happens when a formula with relative cell references is copied from one cell to another?

What are Relative Cell References in Excel?

Relative reference is a type of cell reference in Excel. This reference changes when the formula is copied to any other cell or any other worksheet. Relative cell references are used whenever calculations need to be repeated.

Nội dung chính Show

• What are Relative Cell References in Excel?
• How to use Relative References in Excel? (With Examples)
• How Relative References in Excel Change?
• Absolute References in Excel
• Frequently Asked Questions
• Recommended Articles
• When a formula that contains a relative reference is copied from one cell to another cell?
• Does a relative cell reference change when copied to another cell?
• When you copy the formula and paste it to another cell the reference of cells do not gets changed?
• What happens when you copy a formula that contains an absolute reference to a new location?

For instance, in cell A1, we have “=B1+C1.” On copying this formula to cell B2, the formula becomes “=C2+D2.” This is because the first formula refers to two cells on the right of cell A1 while the second formula refers to two cells on the right of cell B2. Cells B1 and C1 are to the right of cell A1 and cells C2 and D2 are to the right of cell B2.

How to use Relative References in Excel? (With Examples)

Example #1

Let us understand the concept of relative cell references in Excel with an example. We want a summation of two numbers located in cells A1 & A2. Say we want the sum in cell A3.

So, in cell A3, we applied “=A1+A2.”

The sum of cell A1 and A2 is equal to 100.

Now, with different values of cells B1 and B2, we want a summation in cell B3.

The summation can be done in two ways. We can either apply the Excel formula of addition to cell B3 or copy and paste the formula of cell A3 to cell B3.

On copying cell A3 and pasting in cell B3, the answer is not 100. This is because cell A3, which is copied, contains a formula, not a value.

The output of cell A3 depends on cells A1 and A2. After copying cell A3, as we move one cell to the right, A1 becomes B1 and A2 becomes B2. Thus, cell B3 applies summation to the values of cells B1 and B2.

Example #2

Let us consider another example of relative references in Excel. We want to calculate the sales revenue by using the equation Units Sold*Unit Price=Sales Revenue.

To calculate the sales revenue, we multiply the number of units sold by the unit price.

The formula B2*C2 gives the sales revenue for product-1. Applying this formula to all products would become tedious. So, we copy and paste the formula to the other cells.

As we copy the formula from cell D2 to cell D3, the formula reference changes from B2*C2 to B3*C3. To determine the sales revenueSales revenue refers to the income generated by any business entity by selling its goods or providing its services during the normal course of its operations. It is reported annually, quarterly or monthly as the case may be in the business entity's income statement/profit & loss account.read more for all products, either press Ctrl+D or copy and paste cell D2 in the selected cells.

Writing the formula for each product would have taken a minute, but copying or dragging the fill handleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications.read more takes only a few seconds.

How Relative References in Excel Change?

In relative references, each referred cell changes as we move left, right, down or up. By default, every cell in Excel has a relative reference.

For example, give reference to cell C10 and move one cell in the following way:

• Downward–The reference changes to C11.
• Upward–The reference changes to C9.
• Leftward–The reference changes to B10.
• Rightward–The reference changes to D10.

Absolute References in Excel

In absolute referencesAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more, the cell address or the cell reference does not change when the formula is copied. With the help of an absolute reference, the row and the column both can be kept constant. This can be done by using a dollar sign ($) in the formula. The dollar sign precedes the row and the column.

Frequently Asked Questions

#1 – What do using relative references mean in Excel?

Using the relative reference means copying the reference from one cell to the other in order to avoid repetitive calculations. This can be done either by directly copying the formula of a cell or by dragging the fill handle to the desired cells.

#2 – What are relative, absolute, and mixed references in Excel?

The relative, absolute, and mixed references are explained as follows:

– A relative reference is the relative location of a cell. The row and column both are not fixed like A1, B2, C3, and so on.
– An absolute reference is the fixed or absolute address of the cell like $A$1, $B$2, and so on. The row and column both are held constant in an absolute reference.
– A mixed referenceA mixed reference is a type of cell reference that differs from absolute and relative cell reference. In the mixed cell reference, we only refer to the column of the cell or the row of the cell.read more is one in which either the row or the column is held constant like A$1, $B2, and so on.

#3 – How to switch between relative, absolute, and mixed references in Excel?

The reference of a cell can be changed in either of the two ways:

1) Place the dollar sign ($) preceding the row and/or the column manually.
2) Press the F4 key after double-clicking the cell containing the formula. Alternatively, select the cell and press the F2 key, followed by the F4 key.

The reference type changes from relative (A1), to absolute ($A$1), to mixed (A$1), to mixed again ($A1), and back to relative (A1). This happens when the F4 key is pressed repeatedly.

• Relative references in Excel are cell references that change when the formula is copied to any other cell or any other worksheet.
• By default, every cell in Excel has a relative reference.
• In relative references, type “=A1+A2” in cell A3, copy and paste the formula in cell B3, and the formula automatically changes to “=B1+B2.”
• In absolute references, the cell address does not change when the formula is copied.
• In absolute references, the dollar sign ($) holds the row and the column reference constant.

Recommended Articles

This has been a guide to Relative References in Excel. Here we discuss how to use it along with the example and downloadable Excel templates. You may also look at these useful functions in Excel –

• Use 3D Reference in ExcelThe term "3D reference" refers to a three-dimensional reference. It implies anything distinct from the standard view.  When we have multiple data in several spreadsheets with shared data across all sheets, we can use robust referencing.read more
• ISNUMBER in Excel
• TRANSPOSE Excel Function