Errors in Excel formulas with multiple functions and operations can be difficult to diagnose. To check these formulas, it’s best to check each component separately to find out what is causing the error.

For example, say you’ve entered the following formula in H10 of your 2007 Summary worksheet: =SUM('1st Qtr Sales'!B15:'1st Qtr Sales'!P15)+SUM('2nd Qtr Sales'!B15:'2nd Qtr Sales'!N15)

You find that the formula is producing invalid results. To find out why, check the first SUM function for errors by following these steps:

  1. Click in the cell to display the formula in the Formula Bar.
  2. In the formula bar, select SUM(‘1st Qtr Sales’!B15:’1st Qtr Sales’!P15).
  3. Press [F9].
  4. After examining the result, press [Esc].

If this function is working correctly, check the second function by again selecting the formula cell and following these steps:

  1. In the formula bar, select SUM(‘2nd Qtr Sales’!B15:’2nd Qtr Sales’!N15).
  2. Press [F9].
  3. After examining the result, press [Esc].

Remember to press [Esc] after examining a component’s result; otherwise, Excel will replace the function or operation in the formula bar with the actual result.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.