Find and fix formula errors in Excel

A spreadsheet will calculate a wrong formula just as happily as a right one. Some mistakes announce themselves with an error such as #DIV/0!. Others return a believable number that is simply wrong. This page shows how to audit a sheet you didn't build: read what each formula actually refers to, compare it with what it should refer to, and rebuild it so the answer is right.

Why formula errors matter in accounting

Financial statements and schedules are chains of formulas. A total feeds an average, an average feeds a ratio, and a ratio ends up in a report someone relies on. A mistake near the start of the chain travels all the way to the end.

The hardest mistakes to catch are the quiet ones. A total that leaves out December still looks like a total. A price grid that was filled with the wrong references still shows dollar amounts. Nothing on the screen says "error". You only find these by checking what the formula does, not just what it shows.

The loud mistakes are easier to spot, but they have their own trap: an error spreads. When one cell shows #DIV/0!, every formula that uses that cell shows it too. A single new product with no sales can break a summary row several steps away.

This is the spreadsheet side of fixing mistakes. In the books, an error is fixed with a correcting entry; see correcting entries for accounting errors for that. Here the data is fine and the formulas are what need correcting.

The MO-220 objective this covers

These exercises are MO-220 practice for "Prepare and analyze financial statements", under "Correct errors". That objective covers formula auditing and using conditional formatting to highlight anomalies. This lesson practices the auditing part: finding out what a formula refers to and correcting it. The flag outliers in Excel page covers the highlighting part. MO-220 task instructions describe the purpose, for example "correct the total so it includes every month", rather than naming the function or the fix. So the skill is reading the sheet, deciding what each formula ought to do, and spotting where it differs. Accounting Exercises is not affiliated with Microsoft or Certiport.

Each exercise runs in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks. The broken "As found" cells are locked. You click them to read their formulas, then write corrected formulas in the cells beside them. Your work is checked on the values your corrected cells produce.

A routine for auditing any formula

1. Say what the cell should do. Use the label next to it: "Total sales" means all twelve months. Write that down before you look at the formula.
2. Read the formula. Click the cell and look at each reference. Which rows and columns does it actually cover?
3. Compare the two. Does the range start and stop on the right rows? Does a reference point at an input, or at another result by accident?
4. Check a filled neighbor. If the formula was filled down or across, click the next cell too. A formula that is right in the first cell can be wrong in every other one.
5. Follow errors to their source. If a cell shows an error, check whether it starts there or comes from a cell it uses.

Worked example: ranges that are off by a row

Summit Bike Repair lists its 2026 monthly sales in B4:B15, with January in row 4 and December in row 15. A summary block in D4:F7 has four labels, the "As found" formulas in column E and an empty "Corrected" column F.

Every one of those numbers looks reasonable. That is the point of the exercise: none of them is right.

Step 1: the total

B4:B14 stops at row 14, November. December's 85,476.08 is missing. The corrected formula in F4 covers all twelve rows:

=SUM(B4:B15) → 529,177.38

Step 2: the average

B5:B15 starts at row 5, February, so the as-found formula averages eleven months and leaves out January's 34,343.42. In F5:

=AVERAGE(B4:B15) → 44,098.115, which is 529,177.38 divided by 12

Step 3: the best month

B4:B12 stops at September. The as-found answer, 69,743.13, is April's sales, the highest of January to September. But December, at 85,476.08, is higher still. In F6:

=MAX(B4:B15) → 85,476.08

Step 4: the fourth quarter

The fourth quarter is October, November and December: rows 13, 14 and 15. The as-found range B12:B14 is shifted up by one row. It picks up September and drops December. In F7:

=SUM(B13:B15) → 63,341.45 + 44,348.76 + 85,476.08 = 193,166.29

A quick check ties the block together: the corrected total is exactly 12 times the corrected average, and the fourth quarter is a part of the total. The as-found total and average fail that test, which is a clue that their ranges differ.

References that break when filled

The second exercise is Bluebird Veterinary's price grid. Six products and their current prices are in A6:B11. Three price increases, 2%, 4% and 6%, are in row 5. Each cell in the grid should show one product's price after one increase.

The as-found grid in D6:F11 was written once in D6 as =B6*(1+D5) and filled with no locked references. D6 itself is right: 30.71 × 1.02 = 31.32. Click its neighbors and the problem shows:

• D7 became =B7*(1+D6). The increase reference moved down from row 5 to row 6, so it now uses the price in D6 as if it were a rate. The Picture frame shows 5,882.68 instead of 185.63.
• E6 became =C6*(1+E5). The price reference moved right to column C, which is empty, so the result is 0.
• F6 became =D6*(1+F5). It now applies 6% to a price that already has 2% added, giving 33.20 instead of 32.55.

The fix is a mixed reference. The price must always come from column B, so lock the column: $B6. The increase must always come from row 5, so lock the row: I$5. The corrected grid starts in I6 with

=$B6*(1+I$5)

and fills right and down through K11. The Planter row reads 31.32, 31.94 and 32.55; the Picture frame row reads 185.63, 189.27 and 192.91. The absolute cell references page explains relative, absolute and mixed references in full.

Errors that spread

The third exercise is Harbor Bakery's product cost review. For eight products, column B holds units sold, C total cost and D revenue. The as-found formulas work out cost per unit (=C5/B5) and gross margin (=(D5-C5)/D5), then an overall cost per unit in E14.

Two new products, the Bookend set and the Side table, have no sales yet. Their units and revenue are 0. Dividing by zero has no answer, so their cost per unit and gross margin show #DIV/0!. E14, =AVERAGE(E5:E12), uses those cells, so it shows #DIV/0! as well, even though it has no division of its own.

Test the divisor first

The corrected formulas check for a zero before dividing and return 0 when there is nothing to divide by:

=IF(B5=0,0,C5/B5) for cost per unit in G5

=IF(D5=0,0,(D5-C5)/D5) for gross margin in H5

For the Oak shelf, that gives 14,105.82 ÷ 466 = 30.27 per unit and (23,556.72 − 14,105.82) ÷ 23,556.72 = 40.12% gross margin. The two new products show 0 in both columns. Testing the divisor deals with the one error you expect and leaves any other problem visible. A blanket error trap such as IFERROR would also hide mistakes you'd want to see.

Rebuild the overall figure

The overall cost per unit should weight each product by its units. So divide total cost by total units, rather than averaging the per-unit costs:

=SUM(C5:C12)/SUM(B5:B12) → 33,504.40 ÷ 2,089 = 16.04

Common mistakes

Copying the as-found formulas without fixing them

If the corrected column matches the as-found column, nothing has been fixed. For Summit Bike Repair, a total of 443,701.30 means December is still missing. For Bluebird, a grid that fills with no dollar signs repeats every as-found error. Always read the formula before you reuse it.

Leaving the fourth quarter one row too high

It's easy to fix the other three ranges and miss this one. =SUM(B12:B14) still starts at September and still shows 170,876.25. Count the rows: October is row 13, so the range is B13:B15.

Locking the price but not the increase row

=$B6*(1+I5) keeps the price in column B, so the first row is right. Fill it down, though, and I7 becomes =$B7*(1+I6), which uses the price in I6 as a rate. The Picture frame shows 5,882.68 again. Both halves need their dollar sign: $B6 and I$5.

Averaging the per-unit costs

=AVERAGE(G5:G12) gives 11.54, not 16.04. Every product counts the same, whether it sold 486 units or 57, and the two zeros for the new products pull the figure down further. Divide total cost by total units.

Dividing gross margin by cost

Gross margin is a share of revenue. (D5-C5)/C5 divides by cost instead, which turns the Oak shelf's 40.12% into 67.00%. It also makes the new products show −100%, because they have costs but no revenue. Divide by revenue, column D.

Habits that prevent formula errors

• Label every result. A clear label tells the next reader what the formula should do, which is half of any audit.
• Check filled formulas in two places. Look at the first cell and one far away. If they point at different kinds of cell, a reference has moved.
• Tie totals to each other. Quarters should add up to the year, and a total should equal its count times its average.
• Handle zeros on purpose. Decide what a ratio should show when there is nothing to divide by, and test for it.

For the functions behind the first exercise, see SUM, AVERAGE and COUNT for accounting. The free exercise below gives you a new set of sales figures each time.