If you work with Microsoft’s Excel regularly, you may notice a green triangle in the Excel cell (in the red circle below)and wonder what it does, if you can control it and how to remove it.
Table of contents
Understand the green notes in Excel cells
The simple answer is that it is an error checking feature in Excel. Whenever you see the green triangles it means that Excel has found something in the cell that could be a risk.
In order to see what it is indicating you need to click in the cell and click on the exclamation mark (!) button that appears. In the case below you will see that the first words are ‘Inconsistent Formula’ which is Excel’s way of telling you that something is different in this cell compared to the cells around it.
You will see you have a number of options after that which you can use. In the case of inconsistent formula the options are:
- Copy Formula from the Left- if clicked it does a copy paste from the cell on the left
- Help on this error- Goes into the help files and explains the error
- Ignore Error- Ignores the error (and removes the green triangle from that cell only)
- Edit in formula bar- takes you to the formula bar so that you can make a change
- Error Checking Options- takes you into the Error checking options where you can control the green triangles and what they highlight, or switch them off completely.
Green Triangle Options
We will look at some of the uses for this later, but if you click on the 5th item you go straight into the Excel options under the Formula tab.
The part we are looking at is the Error Checking section in the red box below
Stop the green notes from showing
First you will see that you can ‘Enable background error checking’ or not. If you want to remove the green triangles in Excel cells then you need to come here and switch it off. You will also see that you can change the default colour from green to whatever you want.
Below that you have the error checking rules.
Error checking options for the green triangles in Excel cells
This is where you can control when the green triangles should appear in the Excel cells.
Below an overview of what they do and the ones that we think are useful to leave on:
- Cells containing formulas that result in an error-NO– tells you that there is a #VALUE or #DIV error- you can normally see it anyway
- Inconsistent calculated formula in a table-MAYBE– if you use the table feature it will point out formula that are not consistent
- Cells containing year’s represented as 2 digits- YES – if the year of the date is entered as 16 for 2016, depending on the year Excel may see it as 1916 or 2016. Important if you have dates going 15- 20 years into the future
- Number formatted as text or preceded by an apostrophe- YES – warns you that a ‘number’ is not being seen as a number by excel and won’t be included in any additions. Also has a convenient option in the dropdown to convert the cells into proper numbers
- Formulas inconsistent with other formulas in the region- MAYBE – looks at the other cells and checks if the formula is consistent (as per spreadsheet best practice). Sometimes looks up when it should look left and visa versa
- Formulas that omit cells in a range- YES – will point out if you have a SUM formula and the like that are working on a range but missing out cells that are immediately adjacent.
- Unlocked cells containing formulas- MAYBE – formulas that are set to be unprotected if you use Excel’s protect cells features
- Formulas referring to empty cells- MAYBE – as it says, the formula refers to a cell that is currently blank. May mean you are accidentally pointing to a blank cell or the input is missing
- Data entered in a cell is invalid- MAYBE– used in tables where data differs from the type of data it expects e.g. it is text but the column has numbers in it
Although we sometimes find this feature annoying (in fact you can become blind to the green triangles), if you switch on the options that really concern you, you will pay more attention to the green triangles and may find your spreadsheet errors easier.
Below a YouTube video for number 4- numbers formatted as text