Cells, references and formulas

In depth

Relative, absolute and mixed references

When a formula is filled or copied to another cell, Excel adjusts its references. By default a reference is a relative reference: it means “the cell so many rows and columns away from this one”. The formula =G2*H2-I2 in K2 really says “this row’s quantity times this row’s price, less this row’s discount”, so filled down to K3 it becomes =G3*H3-I3, and so on to row 232. That is exactly right for a calculation repeated on every row.

It is exactly wrong for an input that every row shares. Suppose P1 holds a planned price rise of 6% and L2 holds =H2*(1+P1). Filled down, L3 becomes =H3*(1+P2); P2 is empty, an empty cell counts as 0 in arithmetic, and every row after the first quietly shows today’s price.

A dollar sign fixes the part of a reference it stands in front of. An absolute reference fixes both, $P$1, and =H2*(1+$P$1) fills down correctly. A mixed reference fixes one part: $P1 keeps the column, P$1 keeps the row.

You don’t have to type the dollar signs. In Windows Excel, put the cursor in a reference in the formula bar and press F4 to cycle through the combinations of absolute and relative; on a Mac, press Command+T (Microsoft’s Mac page). The question to ask of every reference is: when this formula is copied, which part of this address should move?

Mixed references earn their keep in two-way tables. To show four products’ prices after rises of 4%, 6% and 10%, put the prices down column B (B3:B6) and the rises across row 2 (C2:E2). Every cell needs “this row’s price, in column B” and “this column’s rise, in row 2”, so the formula in C3 is =$B3*(1+C$2). Filled across and down it fills all twelve cells correctly; the challenging example works it through.

Filling, copying and moving

The fill handle is the small square at the bottom-right corner of the selected cell: drag it down or across and Excel copies the formula into each cell it passes, adjusting relative references as it goes. Ctrl+D fills the top cell of a selection down, and Ctrl+R fills the left-hand cell right; on a Mac, Control+D and Control+R do the same (Microsoft’s Mac page). Copying and pasting adjusts references in the same way. Moving a formula, by cutting and pasting it or dragging its cell, does not: Microsoft notes that the references stay the same whatever their type.

A column of calculations should hold one formula, filled. Show Formulas (below) makes this easy to check, because every cell in the column should then read the same apart from its row numbers. A cell that differs, such as a typed-in number among formulas or a formula pointing at the wrong row, is a common sign of a spreadsheet error, and the applied example finds one.

Ranges and whole-column references

Functions work on ranges: =SUM(J2:J232) adds the 231 order totals. If Ana pastes July’s orders below row 232, that range doesn’t grow; a whole-column reference such as =SUM(J:J) does, and SUM skips the text heading in J1.

But a whole column includes everything in it. Put that total in J234 and the formula refers to its own cell, a circular reference. Microsoft also advises against whole columns in heavy functions such as SUMPRODUCT, which then multiply every cell in the column, empty ones included. Excel tables, whose references grow with the data, are the better answer; they come in the tables-and-cleaning lesson.

What a cell shows and what it stores

A number format changes how a value looks, not the value. Format 2.345 to two decimal places and the cell shows 2.35, but every formula that uses it still gets 2.345. Usually that is what you want: carry full precision and round only what you report.

It goes wrong when the rounded figure is the real one. Split a $100 platter fee into three invoice lines with =100/3: each line shows $33.33, the total =SUM(B2:B4) shows $100.00, and a client who adds the lines gets $99.99. If each line is what is actually charged, round it. =ROUND(100/3,2) stores 33.33, and then the lines and the total agree, at $99.99; whether the missing cent goes on one line is a business decision, not a formatting one.

Underneath, Excel stores numbers in binary to 15 significant digits of precision. Most decimal fractions, 0.1 among them, have no exact binary form, so tiny errors appear far to the right of the decimal point. Microsoft’s example: =(43.1-43.2)+1, formatted to 15 decimal places, shows 0.899999999999999 rather than 0.9. Since Excel 97, when an addition or subtraction gives a result at or very close to zero, Excel compensates for the conversion error, so Microsoft’s =1.333+1.225-1.333-1.225 shows 0 where Excel 95 showed −2.22044604925031E−16.

You may also meet =0.1+0.2=0.3. In binary, 0.1 + 0.2 is not exactly 0.3, yet Excel shows TRUE, and so do the worksheets on this site, which compare numbers to 15 significant digits. Microsoft doesn’t document the exact rule its comparisons follow, so don’t build a model on the exact equality of calculated decimals: compare rounded values, =ROUND(A2,2)=ROUND(B2,2), or test the size of the difference, =ABS(A2-B2)<0.005. Microsoft also offers a workbook option, Set precision as displayed, which permanently cuts every stored value down to its displayed format. It can’t be undone; leave it alone.

Error values

When a formula can’t produce a result it returns an error value, which names what went wrong. Learn to read them rather than hide them.

One more, #SPILL!, belongs to the dynamic-array functions in the tables-and-cleaning lesson. Error values are useful because they are loud. A formula that quietly returns a wrong number is far more dangerous than one that returns #REF!.

Tracing a formula

Three tools on the Formulas tab show how a sheet is wired. Show Formulas switches every cell between its result and its formula (Ctrl+`, the grave-accent key, in Windows; Control+` on a Mac, as Microsoft’s keyboard shortcuts page lists), which is the fastest way to spot the odd formula out in a column. Trace Precedents and Trace Dependents, in the Formula Auditing group, draw arrows from the cells a formula uses (its precedents) and to the formulas that use a cell (its dependents). The arrows are blue for cells without errors and red for cells that cause errors, and Remove Arrows clears them.

Evaluate Formula, in the same group, works through one cell’s formula a step at a time. Microsoft’s example is =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) with 55, 35, 45 and 25 in F2:F5. The first step replaces the average with 40; the next turns 40>50 into FALSE; and the formula returns 0 without ever calculating the SUM. Stepping through like this shows exactly where a long formula stops doing what you meant.

A circular reference is a formula that refers to its own cell, directly or through other formulas. Excel warns the first time one appears, and the cell may then show 0 or its last calculated value; Formulas > Error Checking > Circular References, and the status bar, show where it is. It is almost always a mistake, such as a total inside the range it adds. Excel can be told to allow it (File > Options > Formulas > Enable iterative calculation, after which it recalculates up to 100 times by default, or until values change by less than 0.001), but a model that needs that should say so in large letters.

Designing a worksheet someone else can check

• Keep inputs apart and labelled. Every assumption (the price rise, the subscription discount, a fee rate) goes in its own cell, with a label, a unit and a source, such as “Price rise from 1 July, % of current price (Ana’s plan)”.
• Don’t hard-code numbers in formulas. Refer to the input cell with an absolute reference or a name. Constants that can never change, such as 12 months in a year, are the exception.
• One formula per column, filled. Consistent formulas can be checked by checking one of them.
• Separate data, calculations and results. The raw data stays untouched, as B02 insisted; calculations come next and results last, so a reader can follow the flow.
• Build in checks. A cell that should show zero when the sheet is right, such as the rebuilt line totals minus the recorded order totals, catches errors you weren’t looking for.
• Name the key inputs, so that formulas read like the business rule.

A defined name labels a cell or range so that formulas can use the label. Select P1, type Rise in the Name Box to the left of the formula bar and press Enter, and =H2*(1+Rise) reads like the rule it implements. Formulas > Name Manager lists, edits and deletes names, and Formulas > Create from Selection names cells from the labels beside them. According to Microsoft’s Name Manager page, a name can be up to 255 characters long and applies to the whole workbook unless you choose otherwise. When you define a name by selecting cells, Excel stores an absolute reference such as Sheet1!$P$1, so the name doesn’t move when a formula is filled.

Why it matters: spreadsheet errors are normal

Spreadsheet errors are not rare accidents. Raymond Panko’s review of the research reported that the field audits from 1997 onward found errors in at least 86% of the spreadsheets audited, and that across development experiments 51% of the spreadsheets participants built contained errors. People don’t see their own: in one experiment the developers’ median estimate of the chance that their spreadsheet held an error was 10%, yet 86% of them had made one. The only technique Panko found demonstrated to work was code inspection, a group checking a spreadsheet cell by cell, and he notes that even this catches only about 80% of errors.

The best-known case is economic. In 2010 Carmen Reinhart and Kenneth Rogoff reported that countries with public debt above 90% of GDP grew more slowly. Replicating their work in 2013, Thomas Herndon, Michael Ash and Robert Pollin found that the working spreadsheet averaged rows 30 to 44 instead of rows 30 to 49, which left Australia, Austria, Belgium, Canada and Denmark out of the calculation. With that error, and with data the paper had excluded and an unusual weighting corrected as well, average growth for the high-debt countries went from the published −0.1% to 2.2%. A range that stopped five rows short became part of a policy debate.