I am sure we see these everyday across almost all Excel files. But do you know the meaning and significance of these green marks?
These are WARNINGS – it is Excel’s way of telling you NOT to trust what you see. Billions of people are ignoring these green marks for over a decade. If you are one of them, please read this article and share it with everyone who matters to you.
What warning? I never saw it…
You did. Here is an example.
That green mark is a warning. It is a POTENTIAL error. There are 9 type of situations where this green mark automatically appears. One of these is the traditional formula errors like #DIV/0 , #VALUE, #N/A or #REF.
Other 8 situations are also equally important.
Why is Microsoft showing a WARNING in GREEN color?
Let me answer this obvious question. What were those programmers thinking? How would anyone in the world recognize GREEN color as a warning. Is Microsoft trying to confuse the world? Good question. Here is the answer.
Excel is around for more than 25 years. This feature of green warnings was added 10 years back (Office 2003). For 15 years before 2003, RED color was already used for showing comments in Excel. I am sure you know about it and use it as well.
Therefore, Microsoft COULD NOT use the red color to indicate another meaning – a warning in this case. That would confuse every user of Excel!
Therefore, they had to choose a different color. They chose a diagonally opposite color (color wheel) – GREEN. In addition, from a biological perspective, our eyes are most sensitive to GREEN color.
Of course, all this theory was of zero practical use because most of us just ignored the green mark – and we are continuing to do so.
What is the risk of ignoring this warning?
The risk is very significant. Every user of Excel faces this risk. The green mark indicates that there is some problem with the data in that cell. In fact, there could be more than one problem with the data.
If you don’t realize the problem and ignore the warning, you face the risk of misinterpreting or mis-calculating the data. That is why it should not be ignored.
You must understand what the green mark is trying to tell you and take corrective action.
Green Mark = one or more of 9 possible errors
These errors are automatically detected by Excel and the green mark is assigned. You do not have to activate any particular feature for it. It is always active and alive. Trying to help you.
These are the type of errors. Some more dangerous than others. But we must handle all of them to be sure that the data is accurate.
Check if this feature is activated on your PC
I have observed that in some organizations, the IT team has disabled this feature to avoid the irritation of seeing so many green marks every day. This is a disastrous decision – governed by ignorance about the practical importance of this feature. Very sad. But true.
Therefore, it is in your interest to activate this feature.
Open Excel, File tab – Options – Formulas –
Activate the checkbox Enable background error checking
If you want, you can change the color of the marker. But I suggest that you don’t do it because it will affect only your PC.
We will discuss the importance of Reset Ignored Errors option in a future article.
The best practice
The concept is simple. But the implementation does require extra effort from you as well as every other person in the organization.
The idea is to realize that green mark means a POTENTIAL error. Therefore you must check each one of these errors and take relevant action. This must be done BEFORE you interpret anything in the workbook.
We will refine this best practice in the upcoming articles when we learn the nuances of various types of errors and how to handle them.
This is a mandatory Standard Operating Procedure for every person who uses Excel for any useful activity.
You don’t need to force it on people. Just educate them about the disadvantages and risks of NOT doing this. It is in individual as well as organizational interest do follow this best practice.
Example: Error and Corrective Action
Here is a simple example. The value of 63 has an error mark. Clicking there shows an icon. Clicking on the icon shows the actual error – and available options.
The error is Number stored as text. Obviously, this number will not be used in any numeric calculation – giving you wrong results. What do you want to do about it?
Excel gives you the options of correcting it or ignoring it. In this case, we will have to correct it by choosing the Convert to number option because it should have been a number.
When NOT to correct the error?
Here is another example. Here all the data in column CustomerID shows green marks. All these are Number stored as text type of errors.
However, in this case, we DO NOT want to convert it to a number because the ID is not going to be used in any numeric calculation. Therefore you should choose Ignore Error in this case.
Of course, there is a need to apply this action to multiple such errors – you don’t want to waste time doing this one by one. This particular error can be corrected by selecting multiple cells having the same issue. We will discuss that in a later article.
Not handling these errors is a serious and real
Operational Risk
This is obvious. But I still want to stress upon it. This is because, having worked with many banking, finance and insurance companies globally, I have realized that this risk is often unnoticed and unmitigated. This is a very serious issue and needs immediate attention.
Everyone who uses Excel for any useful purpose faces this risk. So please read this article and further related articles carefully and create a proactive mitigation plan for your entire organization.
In subsequent articles, I will provide you with a recommended approach which works across the organizations of all sizes and complexity.
I am the boss. I don’t create Excel files. I get readymade files. I did not see any green mark. So am I safe?
Unfortunately this is not true. Green marks are added in the cells which have issues. If you have another calculation based upon these cells, the green mark is not displayed.
Here is an example. The formula omits the fourth cell therefore the green mark shows in the cell F3.
Now if this formula is used in another formula Revenue After Tax which is simple subtraction.
There is no warning shown in cell H3.
Why? Because the second formula is not inaccurate.
In short, unlike formula errors (which we are used to), these errors DO NOT flow across the formula chain. This makes them very dangerous and increases the chances of being ignored.
When we see final reports, plans, forecasts, summary data etc. as a senior person, you will usually not see the raw calculations. You will see final formulas which are the last ones in a long chain of dependent formulas.
Therefore, even if the final results do not show a green mark, it DOES NOT mean that the data is accurate.
To make matters worse, very often the final results are copy pasted as values in a new file (to reduce file size). This is even more dangerous because you do not even have the chance of cross checking the dependencies and look for unhandled errors.
How to find all errors?
Excel is checking for potential errors while you work. Whenever Excel finds a potential error, it will add the warning sign of green mark.
Now that you are interested in proactively correcting all errors, the first step is to find the errors and the next step is to correct them.
Instead of you finding the errors by manually searching across large sheets and files, Excel can help you in finding the errors.
Click on Formula Tab and choose Error Checking from the Formula Editing section.
Now, like a spell checker, Excel will move your active cell to first cell containing an error in the current sheet. It will show possible actions as a dialog rather than as a drop down menu this time.
You decide which action is relevant and choose it. Now Excel will move to the next cell and again ask you what to do. This process has to be repeated till you have handled all the errors.
Finally you get to see one reassuring message like this…
Remember, your work is still not finished. You must repeat this Error Checking process for every sheet contained in the workbook.
Yes, this is time consuming. However, it is not a waste of time. This is a way to mitigate the risk of wrong decision making and its resultant side effects on business.
With Word documents or PowerPoint presentations, spell check is the last step. Whereas in case of Excel, this is the FIRST step.
Can I select all errors and correct them in one action
Technically this is possible. However, practically this is not a good idea for all types of errors. Some type of errors can be globally corrected, where as some type of errors REQUIRE you to correct them one by one. More on this in future articles.
What next: Error checking series
We will discuss this in more detail in the upcoming articles.
***
