Relative and Absolute Reference Excel
Cell references in spreadsheets come in two types: relative and absolute Reference Excel. The behavior of these references differs when formulas are copied and filled into other cells. Relative references adjust based on the relative position of rows and columns, changing when copied to a new location. In contrast, absolute reference in Excel remain constant regardless of their placement.
Relative References Excel:
By default, all cell references are considered relative. When a formula is copied across multiple cells, these references adapt to the new location based on the relative positions of rows and columns. For instance, if the formula =A1+B1 is copied from row 1 to row 2, it transforms into =A2+B2. Relative references are beneficial when repeating calculations across various rows or columns.
To create and copy a formula using relative references:
Select the cell where the formula will reside; in our example, it’s cell D2.
Input the formula to calculate the desired value; in our example, type =E2*F2.
Press Enter on your keyboard to calculate the formula, and the result will be displayed in the cell.
Identify the fill handle in the selected cell’s lower-right corner (G2 in our example).
Click, hold, and drag the fill handle over the cells you intend to fill; for our example, it’s cells G3:G12.
Release the mouse, and the formula will be copied to the selected cells with relative references, calculating values for each cell.
You can double-click the filled cells to verify their formulas for accuracy, ensuring that each cell’s relative cell references differ depending on its row.
Absolute Reference Excel:
In certain scenarios, it’s essential to maintain the consistency of a cell reference when filling cells. Unlike relative references, absolute reference Excel do not alter when copied or filled. To keep a row and/or column constant, you can denote an absolute reference by placing a dollar sign ($) before the column and row. If the dollar sign precedes either the column or row (but not both), it’s termed a mixed reference.
The formats you’ll commonly use in formulas are the relative (A2) and absolute ($A$2) formats. Mixed references, identified by a dollar sign before either the column or row, are less frequently employed.
When formulating a formula in Microsoft Excel, pressing the F4 key on your keyboard allows you to toggle between relative, absolute, and mixed cell references, as demonstrated in the accompanying video. This provides a convenient way to swiftly incorporate an absolute cell reference cell.
To create and copy a formula using absolute cell reference excel:
Select the cell where the formula will reside; in our example, it’s cell D3.
Input the formula to calculate the desired value; in our example, type =(B3C3)$E$1.
Press Enter on your keyboard to calculate the formula, and the result will be displayed in the cell.
Identify the fill handle in the lower-right corner of the selected cell (D3 in our example).
Click, hold, and drag the fill handle over the cells you intend to fill; for our example, it’s cells D4:D10.
Release the mouse, and the formula will be copied to the selected cells with an absolute reference, calculating values for each cell.
You can double-click the filled cells to verify their formulas for accuracy. The absolute reference in Excel should remain consistent for each cell, while the other references adjust relative to the cell’s row.
Always include the dollar sign ($) when creating an absolute reference across multiple cells. Omitting dollar signs, as shown in the example below, may cause the spreadsheet to interpret it as a relative reference, leading to incorrect results when copied to other cells.
