Inconsistent Formulas in Excel 2007

By Cliff Kapatais | July, 15, 2008 | 2 comments

Just a little note about something I spent half an hour yesterday getting really mad at: Inconsistent Formulas, or those annoying little green flags you sometimes get in Excel even though your formula is completely valid and correct.

What is an inconsistent formula? MS Excel is once again trying to out-think it’s users and while that usually may be a good idea it annoys the hell out of me and other people who actually know what they are doing. An inconsistent formula simply refers to a formula that “looks” different then it’s neighbours. An example in column A you sum some values, in Column B you sum some more values and in column C you calculate the percentage difference between columns A and B. For Excel that would be an inconsistent formula…

How can I get rid of these errors/warnings? Just turn them off, in Excel 2003 you can find the settings at “Tools”-> “Options”, select the “Error Checking” tab, and deselect the “Inconsistent formula in region” box, or to turn off all error checking depending on your needs. Under Vista, running Excel 2007 it took me a little longer to find these settings, as with everything else they have been shuffled around. If you go to “Formulas” in the quick access toolbar (the funky big icon menu replacement toolbar as it’s also known) and then click on “Error checking” you only trigger the error checking process and if you go to the options there and turn of the “inconsistent formula” option, you will only turn it off for the one cell you are currently in. Pretty annoying. To turn of the error checking for the whole spreadsheet you need to go to the “Excel Options”, hidden in plain sight on the main menu in the top right corner:

MS Excel 2007 Options

MS Excel 2007 Options

Then head to the “Formulas” Tab and at the bottom you can then disable the error checking rules that bother you, including my favourite, the inconsistent formula…

Happy number crunching! ;)

2 Responses to Inconsistent Formulas in Excel 2007

  • Riz

    thanks for this… i can’t tell you how many hours i spent on my accounting assignment thinking i was doing it wrong. i knew i was right but just couldn’t figure it out completely.

  • satya

    Thanks for the tip. I did spend a lot of time checking and rechecking the formulas. Finally this tip has saved important time. Adding further, To remove the tags for the entire column in vista(excel2007), just select the total rows u want to remove the tag and then select the ignore error in the option box near the tag.

Leave a Reply

Post Comment

Currently you have JavaScript disabled. In order to post comments, please make sure JavaScript and Cookies are enabled, and reload the page. Click here for instructions on how to enable JavaScript in your browser.