Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two CSV files, test_a.csv and test_b.csv. The green-tag info-box highlights "Inconsistent formula". Why does Excel green tag A4 in test_b.csv? How can I prevent Excel from green tagging my cells?

Random 'green-tag' in a csv file

Contents of test_a.csv:

="66526"
="86207"
="76830"
="85244"
="46830"

Contents of test_b.csv:

="36064"
="36207"
="36830"
="35244"
="36830"
share|improve this question
    
What error info is there when you point at the tag? –  CharlieRB Jun 29 '12 at 17:44
    
The Info-Box highlights "Inconsistent formula". –  zundarz Jun 29 '12 at 17:46
add comment

3 Answers

up vote 7 down vote accepted

The specific error you are getting is an Inconsistent Formula. The reason is Excel detects the cell above and cell below are the same, so it thinks the one in the middle may be a mistake. This is arguably a bad design on the part of Microsoft, seeing how easy it is to trigger it, but let's not get into that.

I've listed a few options for preventing this error from being indicated:

  • Ignore it
  • Disable error checking for inconsistent formulae
  • Use an alternative notation for indicating text
  • Store it as a number instead of text

Ignore it

You can just ignore it. All it really is is just a little triangle in the cell; it should not affect functionality in any way. Alternatively, you can tell Excel to ignore it for just this cell:

  1. Select the cell

  2. Click the exclamation mark to the right

  3. Select Ignore Error

    Screenshot illustrating process


Disable error checking for inconsistent formulae

You can disable error checking for inconsistent formulae entirely. This has the side effect of not flagging actual issues with inconsistent formulae for your attention.

  1. Select the cell

  2. Click the exclamation mark to the right

  3. Select Error Checking Options...

    Screenshot illustrating process

  4. Uncheck Formulas inconsistent with other formulas in the region. Notice how the tooltip example describes your situation almost perfectly. This option may also be reached through File > Options > Formulas if you wish to re-enable it. I believe it applies it applies to your user settings on the computer, i.e. every time you use Excel after changing the option, and not just this file.

    Screenshot illustrating options box


Use an alternative notation for indicating text

Prefix the string with an apostrophe ('), e.g. '35244. This indicates text in Excel, and is not a formula. Unfortunately, this flags it under the Number Stored as Text error, but this error check should be safer to disable than the Inconsistent Formula one.


Store it as a number

Don't store your numbers as text if possible. This also makes using numerical formulae easier, but you may have some other reason it must be stored as text.

You can do this by omitting the quotes.

share|improve this answer
add comment

Excel is checking because of the = in the cell, which tells it what follows is a formula. Turning off error checking is a work around if you aren't going to use formulas anywhere else in the sheet. Before turning off error checking, try removing the extra characters in the cell leaving just the data.

Before ="35244" After 35244

share|improve this answer
    
If Excel thinks my value could be a formula, why are not all my cells so tagged? What makes this one value ="35244" worthy of the tag, yet not the others? –  zundarz Jun 29 '12 at 19:02
1  
because the "formula" in A3 and A5 are the same and A4 is different. –  laurent Jun 29 '12 at 19:32
add comment

Go to File > Options. Click the formulas tab and uncheck "Enable background error checking"

Edit: Types of errors that are checked for are here: http://office.microsoft.com/en-us/excel-help/use-error-checking-to-correct-common-errors-in-formulas-HA010342331.aspx

Edit2: And you can see an example about "inconsistent formula".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.