"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Avoid Numeric Heading Errors In Your Formulas

Often you will use numeric headings in your worksheets. For example, you may have a table that uses years for the headings (i.e. 2004, 2003, etc...) Sometimes these headings may incorrectly get included in the calculations for some formulas, such as when you use AutoSum to total a column which has a numeric heading. Excel's AutoSum sees the heading cell as numeric, so it includes that cell in the formula. This can easily happen and likely will lead to costly and embarrassing problems.

There are a couple of ways to avoid this error. One is to precede the numeric value with an apostrophe (i.e. '2004). However, my preferred method is to format all of the heading cells, often the entire row, as Text.

Here's how:

  1. Select the heading cells (or the entire row)
  2. Select Format, Cells, click the Number tab.
  3. Select Text from the Category box, and click OK.
Now when you use AutoSum, Excel recognizes the heading as text and doesn't include it in the formula.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.