If you work with Excel regularly, you may notice a green triangle in the excel cell and wonder what it does, if you can control it and how to remove it.
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
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.
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 vica 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.