AutoFill Formulas: Relative vs Absolute References

Part of the free Module 2: Fill, Auto Fill and Flash Fill · Lesson 5 of 6 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

When you AutoFill a formula in Excel, every relative reference shifts by the number of rows and columns the formula moved, while an absolute reference marked with dollar signs stays fixed. Understanding relative, absolute and mixed references, and the F4 key that toggles them, is what makes a formula fill correctly down a column, across a row or through a whole grid.

Table of Contents

What happens when a formula is filled

Excel does not store a formula as “B2 times C2”. It stores “the cell two columns to the left times the cell one column to the left”. When the formula in D2 is filled to D3, those offsets are applied from the new position, so D3 reads =B3*C3. This is a relative reference and it is the default. It is exactly what you want for row-by-row calculations and exactly what you do not want when every row must refer to one tax rate, one exchange rate or one header cell. For those you lock the reference with dollar signs.

Relative, absolute and mixed references

Reference	Example	Filled down one row	Filled right one column	Use it for
Relative	`A1`	`A2`	`B1`	Row-by-row and column-by-column calculations
Absolute	`$A$1`	`$A$1`	`$A$1`	One fixed input cell: tax rate, exchange rate, target
Mixed, column locked	`$A1`	`$A2`	`$A1`	Row labels or values that must stay in column A while filling right
Mixed, row locked	`A$1`	`A$1`	`B$1`	Header values in row 1 that must hold while filling down

Press F4 while the cursor is inside or next to a reference in the formula bar to cycle through the four forms: A1, $A$1, A$1, $A1, then back to A1. On many laptops you need Fn+F4. On a Mac use Cmd+T.

How to fill a formula with a locked reference

Type the formula in the first cell, for example =C2*$G$1 where G1 holds the tax rate.
While typing, place the cursor on G1 and press F4 once to make it $G$1.
Press Enter, then select the cell again.
Double-click the fill handle, or select the range and press Ctrl+D. Every row multiplies its own value in column C by the single rate in G1.
Click any filled cell and read the formula bar: the relative part changed, the absolute part did not.

Fill a formula across a grid with mixed references

A multiplication table or a price-times-quantity grid needs one formula that works in both directions. In a table with quantities across row 1 (B1:F1) and unit prices down column A (A2:A6), enter =$A2*B$1 in B2. The $A keeps the price in column A when you fill right; the $1 keeps the quantity in row 1 when you fill down. Select B2:F6 and press Ctrl+R then Ctrl+D, and the grid is complete from one formula.

Worked example: commission sheet with one rate cell

A: Rep	B: Sales	C: Commission	D: Share of total
Asha	42,000	`=B2*$F$1`	`=B2/SUM($B$2:$B$5)`
Ben	31,500	filled	filled
Chen	58,200	filled	filled
Dana	27,900	filled	filled

Cell F1 holds the commission rate, 5 percent. With =B2*$F$1 in C2 and =B2/SUM($B$2:$B$5) in D2, select C2:D5 and press Ctrl+D. Results: Asha 2,100 and 26.3 percent, Ben 1,575 and 19.7 percent, Chen 2,910 and 36.5 percent, Dana 1,395 and 17.5 percent. Change F1 to 6 percent and every commission updates. Without the dollar signs, C3 would read =B3*F2, an empty cell, and return zero.

In Excel 365 you can write the whole column at once with a dynamic array: =B2:B5*F1 in C2 spills four results and needs no filling and no dollar signs, because nothing moves.

Fill formulas across worksheets

A reference to another sheet such as Jan!B2 fills like any other: the cell part moves, the sheet name does not. To sum the same cell across a run of sheets, use a 3D reference: =SUM(Jan:Dec!B2), which fills down to B3, B4 and so on. To copy a finished formula block to several sheets, group the sheets and use Home > Fill > Across Worksheets as shown in chapter 1. The formulas are copied exactly as written, so lock any reference that must point at the source sheet with the sheet name included, for example Rates!$B$1.

Named ranges and Table references

Two features make locking unnecessary. A named range (Formulas > Define Name) is always absolute: =B2*TaxRate fills down without dollar signs. Inside an Excel Table (Ctrl+T), a formula typed in one cell becomes a calculated column and fills every row itself, using structured references such as =[@Sales]*TaxRate. Both make formulas easier to read and safer to fill, and both are the professional habit.

Tips and common mistakes

Lock before you fill. Fixing references after filling means re-filling; get the first formula right and check it in row 2 before Ctrl+D.
F4 cycles, it does not just lock. Press it again if you land on the wrong form. Watch the formula bar.
Whole-column references such as SUM(B:B) shift to C:C when filled right; lock them as $B:$B if needed.
Test with a sum. After filling, put a SUM under the column and compare it with a calculator on two rows. Reference errors show up at once.
Double-click fills to the last adjacent row only. If the fill stops early, select the full range and press Ctrl+D.
Copy values before sharing. Filled formulas that reference another workbook break when the file is moved; Paste Special > Values freezes them.
Show formulas with Ctrl+` (the grave accent) to see every reference on the sheet at once and spot the one you forgot to lock.

Errors and how to fix them

Symptom after filling	Cause	Fix
Results are zero from the second row	A rate cell was relative and moved to empty cells	Make it absolute with $ and refill
#REF! in filled cells	A relative reference moved above row 1 or left of column A (usually Fill Up or Fill Left)	Anchor the reference, or fill in the other direction
Every cell shows the same number	All references were absolute, or Show Formulas is on	Unlock the row-by-row part; press Ctrl+` to toggle Show Formulas
Grid formula wrong in one direction	Mixed reference locked the wrong half	Use $ on the column for the left-hand inputs and on the row for the top inputs
Formula copied to another sheet points at the wrong sheet	Reference had no sheet name	Include the sheet name: Rates!$B$1
Table column shows #VALUE! for new rows	Calculated column was overwritten in one cell	Delete the manual entry so the column formula restores

Practice exercise

Build a 10 by 10 multiplication table from one formula using =$A2*B$1.
Create a price list in column B, put a discount percentage in E1 and fill a discounted price column that references E1 absolutely.
Compute each row’s share of the column total with =B2/SUM($B$2:$B$20), then change one value and confirm every share updates.
Name the discount cell Discount and rewrite task 2 with the name instead of $E$1.
Convert the price list to a Table and enter one formula to see the calculated column fill itself.

Key takeaways

Relative references move with the formula when filled; absolute references with $ stay fixed.
Mixed references lock only the row or only the column and make one formula fill an entire grid.
F4 cycles a reference through relative, absolute and both mixed forms.
Named ranges and Table structured references remove the need for dollar signs.
Excel 365 dynamic arrays can replace filling altogether for many calculations.

Related lessons

Frequently asked questions

How do I AutoFill a formula without changing the cell reference?

Make the reference absolute by adding dollar signs before the column letter and row number, for example $G$1, or select it in the formula bar and press F4 once. The locked reference stays on G1 in every filled cell while the other references move normally. A named range gives the same result without dollar signs.

What does F4 do in an Excel formula?

With the cursor on a cell reference in edit mode, F4 cycles the reference through four forms: relative A1, absolute $A$1, row-locked A$1 and column-locked $A1. Outside edit mode F4 repeats your last action instead. On laptops you may need Fn+F4, and on a Mac the shortcut is Cmd+T.

What is a mixed reference and when do I need one?

A mixed reference locks either the column ($A1) or the row (A$1) but not both. You need one whenever a single formula must be filled both down and across, such as a multiplication table, a price-by-quantity grid or a month-by-region matrix, where inputs sit along the top row and the left column.

Why does my filled formula return 0 or #REF!?

A reference that should have stayed fixed moved with the fill. If it moved onto empty cells the result is 0; if it moved above row 1 or left of column A, usually after Fill Up or Fill Left, Excel shows #REF!. Lock the reference with $ in the original cell and fill again.

Do I still need to fill formulas in Excel 365?

Less often. A dynamic array formula such as =B2:B100*F1 spills its results into the cells below automatically, and a Table calculated column fills itself. Filling is still the quickest option for a plain range where each row needs the same calculation, and understanding references remains essential for reading other people’s workbooks.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.