Excel formulas: relative vs absolute cell references

#Why it matters

The single most common bug in an analyst's workbook is a formula that was right in row 2 and wrong in row 3, because a reference moved when it should have stayed put. An Excel absolute reference ($A$1) is how you pin a cell, and knowing exactly what moves when you copy a formula is the foundation every other Excel lesson builds on. In the 50 Data Analyst job descriptions we read, Excel appeared in 57% of junior postings, and the Excel tests that services firms and operations teams send in the first round start with exactly this.

#The concept

#Formula anatomy and operator precedence

Every formula starts with =. After that come values, cell references, operators and functions. Excel evaluates operators in a fixed order, and brackets override it:

So =2+3*4^2 is 2 + 3*16 = 50, and =(2+3)*4^2 is 80. One surprise trips people up: negation ranks above power, so =-2^2 returns 4, not -4. Write =-(2^2) or =0-2^2 when you mean minus four. If a formula mixes more than two operators, add brackets even when they are not strictly needed; the next reader will not have this table open.

#Relative, absolute and mixed references

A reference like F2 is relative. Excel stores it as "the cell four columns to the left in the same row", not as the address F2. When you copy the formula down one row, it becomes F3; copy it one column right and it becomes G2.

A dollar sign freezes the part it sits in front of:

Press F4 while the cursor is on a reference to cycle A1 to $A$1 to A$1` to `$A1 and back. On many laptops the F-keys control volume or brightness, so you need Fn+F4. On a Mac, use Cmd+T (or Fn+F4). In Google Sheets, F4 works the same way.

Two copy behaviours are worth knowing:

• Copy and paste (or fill) adjusts relative references.

• Cut and paste (or drag to move) never adjusts them. The formula keeps pointing at the same cells, and any formula that pointed at the moved cell follows it to the new place.

#Named ranges

A name is a label for a cell or range: VAT_rate instead of $O$1. Names are absolute by default, so =F2*(1+VAT_rate) copies down safely and reads like a sentence. Create one by selecting the cell and typing the name into the Name Box (left of the formula bar), or with Formulas, Define Name. Formulas, Name Manager (Ctrl+F3) lists, edits and deletes them. Names cannot contain spaces and cannot look like a cell address. Modern Excel has columns up to XFD, so GST18 and even VAT1 are real cells (column GST, row 18) and are refused; GST_rate and VAT_rate are fine. Each name has a scope: the whole workbook (the default) or one sheet.

#Error values and what causes each

#NAME? is the one to remember for version checks: a workbook built in Microsoft 365 that uses XLOOKUP shows #NAME? when a colleague opens it in Excel 2016.

#Debugging tools

• Show Formulas: Ctrl+` (the backtick key, left of 1) toggles every cell between its value and its formula. Use it to spot the one hard-typed number in a column of formulas.

• Evaluate Formula (Formulas, Formula Auditing, Evaluate Formula) steps through a formula one operation at a time and shows each intermediate result.

• F2 puts a cell in edit mode and colour-codes each reference with a box around the cell it points at.

• F9 inside the formula bar evaluates just the highlighted part. Press Esc afterwards, or the result replaces your formula.

#Calculation mode and the stale-number trap

Formulas, Calculation Options has Automatic, Automatic except for data tables, and Manual. In Manual mode nothing recalculates until you press F9, so a report can show yesterday's numbers next to today's inputs. Two details make this dangerous:

• The mode belongs to the application, not the file. The first workbook you open in a session sets it, and every workbook you open afterwards inherits it. Open a colleague's Manual-mode model first and your own file goes stale too.

• Excel shows Calculate in the status bar when there are pending recalculations. Check it before you send numbers.

F9 recalculates all open workbooks, Shift+F9 only the active sheet, and Ctrl+Alt+F9 forces a full recalculation of everything.

#Dates are numbers

Excel stores a date as the number of days since 1 January 1900 (day 1), and a time as a fraction of a day. The first line of the retail data, 1 December 2010 at 08:26, is stored as 40513.3513888889: 40,513 days, plus 0.35139 of a day (8 hours 26 minutes is 506 of 1,440 minutes). Format the cell as General to see it.

Two quirks:

• The 1900 leap-year bug. Excel treats 1900 as a leap year and accepts 29 February 1900, a date that never existed. Microsoft kept it for compatibility with Lotus 1-2-3. It only affects dates before 1 March 1900, so you meet it only with historical data.

• The 1904 date system. Old Mac versions of Excel counted from 1 January 1904. A workbook with that setting (File, Options, Advanced, "Use 1904 date system") shows every date 1,462 days (four years and a day) off when you copy values into a 1900-system workbook.

#Keyboard navigation on big sheets

Ctrl+Down stops at the first blank cell, which is a quick way to find gaps. Ctrl+End jumps to the furthest cell Excel thinks is used; after you delete rows it can point far beyond your data until you save the file.

#Worked example: revenue, VAT and share of total on 541,909 sales lines

The data is the UCI Online Retail II dataset (UCI page, licence CC BY 4.0): every line of every invoice from a UK online gift retailer. Open online_retail_II.xlsx and go to the sheet Year 2010-2011. Prices are in pounds sterling.

The columns are A Invoice, B StockCode, C Description, D Quantity, E InvoiceDate, F Price, G Customer ID, H Country.

Step 1: a relative formula. In I1 type Revenue. In I2:

=D2*F2

Double-click the small square at the bottom-right of I2 (the fill handle). Excel fills down to the last row of the adjacent data, and each copy shifts: row 3 gets =D3*F3, and so on.

Step 2: one input cell, locked. The UK charges 20% VAT. Put the label VAT rate in N1 and 0.2 in O1. In J1 type PriceIncVAT, and in J2:

=F2*(1+$O$1)

Fill down. F2 moves, $O$1 does not. If you had written =F2*(1+O1), row 3 would read O2, which is empty, and every price below row 2 would silently equal the pre-VAT price.

Step 3: name the input. Click O1, type VAT_rate in the Name Box and press Enter. Now this gives the same answer and documents itself:

=F2*(1+VAT_rate)

Step 4: share of total. In K1 type Share and in K2:

=I2/SUM($I$2:$I$541910)

The numerator moves, the total range is locked. Format K as a percentage with four decimals. (On a real report you would put the total in one cell and reference it, so the SUM runs once instead of half a million times. The next lesson, on Excel Tables, shows a cleaner version with structured references.)

The first rows and a check sheet after recalculation:

Invoice  StockCode  Quantity  Price  Revenue  PriceIncVAT  Share
536365   85123A     6         2.55   15.3     3.06         1.56959331566564e-06
536365   71053      6         3.39   20.34    4.068        2.08663581964962e-06
536365   84406B     8         2.75   22       3.3          2.25693156500942e-06
536365   22752      2         7.65   15.3     9.18         1.56959331566564e-06
 
=SUM(I2:I541910)         9747747.934
=COUNTA(A2:A541910)      541909
=F2*(1+VAT_rate)         3.06
=E2*1                    40513.3513888889
=MOD(E2,1)               0.351388888891961

What it tells you: total revenue across every line, including cancellations, is £9,747,747.93. One line of white hanging heart T-light holders is 0.00016% of it. The time part of the first invoice is 0.3514 of a day, which is 08:26.

Step 5: a mixed-reference grid. Mixed references shine when one formula fills a whole table. Suppose you sell the same five items in India and want prices under each GST slab. Put the prices in A2:A6 and the rates 5%, 12%, 18% and 28% in B1:E1. In B2:

=$A2*(1+B$1)

Copy B2 across to E2 and down to row 6. $A` keeps every formula reading the price column, `$1 keeps every formula reading the rate row:

Price   0.05    0.12    0.18    0.28
2.55    2.6775  2.856   3.009   3.264
3.39    3.5595  3.7968  4.0002  4.3392
2.75    2.8875  3.08    3.245   3.52
7.65    8.0325  8.568   9.027   9.792
4.25    4.4625  4.76    5.015   5.44

One formula, twenty correct cells. Without the dollar signs you would need twenty hand-edited formulas.

#Worked example: break it on purpose

Make each error once, so you recognise it instantly later. Put these in a scratch sheet:

=D2/0                              -> #DIV/0!
=MATCH("NOPE",B2:B100,0)           -> #N/A
=C2*2                              -> #VALUE!   (C2 is a product description)
=SUMM(1,2)                         -> #NAME?    (misspelt SUM)
="6"*2                             -> 12        (arithmetic converts text that looks like a number)

Every result above came from the recalculated workbook. The last line is the subtle one: arithmetic operators quietly convert "6" to 6, but SUM over a range ignores numbers stored as text. So if a column has some numbers stored as text, =A2+A3 and =SUM(A2:A3) disagree, and nothing turns red.

Now make #REF!: in a copy of the sheet, write =F2*(1+$O$1) in J2 and then delete column O. The formula becomes =F2*(1+#REF!). Undo (Ctrl+Z) is the only clean fix; once you save, the original address is gone. That is one more reason to use names and keep inputs on their own sheet: deleting a column on a data sheet cannot break them.

#Common mistakes

• Forgetting the dollar on a rate or total. The first row is right, the rest are wrong, and nothing errors. Fix: after filling, click a cell in the middle of the column and press F2 to see where the references point.

• Locking too much. =$D$2*$F$2 copied down gives the first line's revenue on every row. Lock only the part that must not move.

• Hard-typed numbers inside formulas. =F2*1.2 works until VAT changes and you have to find every 1.2. Put it in an input cell, name it, reference it.

• Fixing #DIV/0! by deleting the formula. Find out why the denominator is zero. Often it is a missing row of data, which is the real problem.

• Sending a file in Manual calculation mode. The recipient sees stale numbers. Check for "Calculate" in the status bar, and press F9 before you send.

• Mixing up dd/mm and mm/dd when dates arrive as text from a CSV. A date that Excel left-aligns is text, not a date. The text and date functions lesson later in this skill fixes it properly.

#Interview angle

#Practice task

Use the Year 2010-2011 sheet of Online Retail II.

1. Add Revenue in column I with a relative formula and fill it down all rows. Check that =SUM(I:I) is about 9.75 million.

2. Put a VAT rate in an input cell, name it VAT_rate, and add PriceIncVAT in column J using the name.

3. Add Share in column K: each line's revenue as a percentage of the total. Put the total in its own cell first and reference it with an absolute reference.

4. Change the VAT rate to 17.5% (the UK rate before 2011) and confirm that every value in column J changes and nothing else does.

5. Create each of #DIV/0!, #N/A, #VALUE!, #REF! and #NAME? on a scratch sheet, and write next to each one the cause in your own words.

6. Filter Quantity to values less than 0 and find the first negative line; note its invoice number and what the C prefix means.

Done when: column J recalculates from one input cell, column K adds up to 100%, and your scratch sheet has five labelled errors.

#Further reading

• Microsoft: switch between relative, absolute and mixed references: the official F4 behaviour.

• Microsoft: calculation operators and precedence: the full operator order, including negation.

• Microsoft: Excel incorrectly assumes that 1900 is a leap year: why the bug exists and what it affects.

• Microsoft: define and use names in formulas: scope, naming rules and the Name Manager.