Every formula in Microsoft Excel relies on cell references to pinpoint where data lives. Understanding how cell references change—or stay fixed—when copied across a worksheet is fundamental to building dynamic spreadsheets.
Cell references are split into two primary types: relative and absolute. Relative references adjust dynamically based on where you move or copy a formula. Absolute references, denoted by dollar signs ($), remain locked to a specific row, column, or exact cell.
Watch this video walk-through to see relative and absolute references in action:
What are Relative References?
By default, all cell references in Excel are relative references. Relative references tell Excel to look at cells relative to the position of the formula cell. For instance, if you write =A1+B1 in cell C1 and copy it down to cell C2, Excel shifts the row numbers automatically, resulting in =A2+B2.
How to Create and Fill Formulas with Relative References
In this exercise, we'll calculate the total cost for multiple items by multiplying each item's price by its quantity. By writing a relative formula once, we can fill the remaining rows instantly.
-
Select cell D4 where the first line-item total will go.
-
Type the relative formula:
=B4*C4.
- Press Enter to run the calculation.
-
Click back into cell D4 and hover over the small green box at the bottom-right corner to locate the Fill Handle.
-
Click and drag the fill handle down across cells D5:D13.
-
Release the mouse button. Excel automatically fills the formulas using relative addresses (e.g.,
=B5*C5,=B6*C6).
Double-click any filled cell to verify the formula. Notice how the row numbers changed automatically relative to each row.
What are Absolute References?
Unlike relative references, absolute references lock specific rows or columns in place. When copying an absolute reference, Excel keeps that target reference unchanged regardless of destination.
You lock references in Excel by inserting a dollar sign ($) before the column letter, row number, or both:
| Reference Style | Syntax | Behavior When Copied |
|---|---|---|
| Absolute | $A$1 |
Locks both Column A and Row 1. Never changes. |
| Mixed (Row Lock) | A$1 |
Column updates when dragged across; Row 1 remains locked. |
| Mixed (Column Lock) | $A1 |
Column A remains locked; Row updates when dragged down. |
A1 → $A$1 → A$1 → $A1.
How to Create and Use Absolute References
In this example, cell E2 holds a fixed sales tax rate (7.5%). We need to multiply every item total in Column D by this fixed cell address.
-
Select cell D4.
-
Enter the formula:
=(B4*C4)*$E$2. Adding dollar signs to$E$2ensures it remains fixed.
- Press Enter to display the result.
-
Locate the Fill Handle in cell D4.
-
Drag the fill handle down through D5:D13.
-
Release the mouse. Notice how every row correctly calculates using cell
$E$2.
Double-checking the formulas shows that while relative cells updated per row, $E$2 stayed pinned to the exact cell.
Warning: Omitting the dollar signs causes Excel to treat the cell as relative. Dragging the formula down will offset the tax cell reference to E3, E4, etc., generating zero values or incorrect math errors.
Referencing Cells Across Multiple Worksheets
Excel allows you to incorporate cells located on separate worksheets inside the same workbook. To pull data from a different sheet, format the reference as: SheetName!CellAddress.
'July Budget'!A1.
Step-by-Step Cross-Sheet Reference Guide
-
Locate the cell on your source sheet. Here, we want to reference cell E14 on the Menu Order tab.
-
Switch to the destination sheet tab (e.g., Catering Invoice).
- Select target cell C4.
-
Type an equal sign, followed by the sheet name, an exclamation mark, and the cell reference:
='Menu Order'!E14.
-
Press Enter. The destination cell dynamically reflects the value of cell E14 on the primary sheet.
If you misspell a worksheet name or point to a deleted cell, Excel outputs a #REF! error.
Practical Hands-On Challenge
- Open the downloaded practice workbook.
- Select the Paper Goods sheet tab.
- In cell D4, write a formula multiplying unit price (B4), quantity (C4), and tax rate (E2). Lock cell E2 with absolute references (
$E$2). - Use the Fill Handle to copy the formula down through D5:D12.
- Change cell E2 to 6.5% to verify all line totals auto-update correctly.
- Switch to the Catering Invoice sheet tab.
- Replace cell C5's hardcoded number with a reference to cell
'Paper Goods'!E13. - Your sheet should match this output:
Frequently Asked Questions
What is the difference between relative and absolute references?
Relative references adjust based on relative row and column shifts when copied (e.g., A1 turns to A2). Absolute references stay fixed on a specified cell address regardless of copying destination (e.g., $A$1 stays $A$1).
How do I quickly make a reference absolute in Excel?
Press the F4 key on your keyboard while editing a formula in the formula bar. It cycles through absolute ($A$1), mixed (A$1, $A1), and relative (A1) modes.
What does the dollar sign ($) symbol mean in an Excel formula?
The dollar sign serves as a lock symbol in Excel. Placing $ before the column letter locks the column, while placing it before the row number locks the row.
How do I reference a cell on a separate sheet tab?
Use the format 'Sheet Name'!CellAddress. For example, referencing cell B2 from a worksheet named Budget requires typing ='Budget'!B2.
What causes the #REF! error in Excel?
The #REF! error indicates an invalid cell address. This occurs if you delete cells, rows, or entire sheet tabs referenced in an active formula.
