3 Ways Excel can Betray You —
We regularly hear harrowing stories of an accounting sheet gone wrong with an errant formula. These small mistakes are hard to catch and can lead to drastic consequences.
If the total at the bottom of a chart doesn’t look quite right, it might be quite wrong. The following are common mistakes, leading to sums that don’t add up.
Text Instead of Numbers —
Just because a number is a number, doesn’t mean it’s a number. A “5” might mean the number 5 or it might be the text “5.” This issue happens most often when data is copied and pasted from Word or an email. Also, typing a dollar sign ($) or using a comma (1,000) (rather than using the cell formatting feature to insert these) may cause the number to be stored as text.
Watch the alignment. If one number aligns to the left and the other aligns to the right, then the left aligning number is probably text. Excel might also point out the error to you with a small green triangle in the top left-hand corner of the formula cell.
Not Including Enough Numbers —
I was once told by a contractor that his company underbid a contract by tens of thousands of dollars because a sum formula didn’t include all of the correct cells.
Excel warns you of this issue with a green triangle. Hover over the triangle and then click on the exclamation point to see the error message “Formula omits adjacent cells.” Either click on “Update formula to include omitted cells” or manually fix the formula.
When adding more rows to a chart, confirm that any formulas include the new cells. If you insert a row between the last row and the “total” row, Excel may not automatically add the new row data to the formula.
Avoid this by inserting rows in the middle of the data. Excel will then expand the formula to include the new row. Always double-check your formula and update as needed after adding new rows or columns.
$b$16, Using Absolute Cell References
Using the F4 key to make a cell reference absolute is a very useful trick when copying formulas, but be careful to use it correctly.
Formulas using absolute cell references always refer to an exact cell, even if the formula is copied and pasted elsewhere in the document. Relative cell references, by contrast, refer to the cell relative to the position of the cell containing the formula — the default option if you don’t add the $$$ in your formula cell references.
If copying a cell with a relative formula that adds the 3 cells above it, wherever you paste the formula, it will add the 3 cells above the new location. If the formula uses absolute references, however, it will still add the original 3 cells.
Whether copying and pasting Relative or Absolute formulas, use the “Trace Precedents” option in the “Formula Auditing” group of the “Formulas” tab on the Ribbon to get a better look at exactly how the formulas are working.
