You may have seen these green triangles but don’t really know what they are trying to tell you but if you understand them they can be a really useful tool in troubleshooting spreadsheets.
The green triangles in Excel tell you if there is potentially some sort of error with your spreadsheet and often gives you a way to quickly solve the problem. There are 10 possible reasons why the green triangle might appear which are detailed below. On any cell that has the green triangle you can click on the cell and a yellow diamond symbol will appear. If you hover over this it will give you a screen tip with minimal information regarding what the error is. Clicking on it gives more information and often an option to resolve the issue.
You can switch on and off which options are viewed as being errors by selecting options from the File tab then formulas and ticking the relevant boxes for the error checking rules.
But what do the errors all mean?
10 Reasons Green Triangles are seen
1. Cells containing formulas that result in an error
This could be any error message such as #DIV/0!, #VALUE! or others. It indicates that Excel cannot formulate an answer to a calculation. The yellow diamond gives the options for “Help on this error” which will give a detailed description as to why this error occurs and ways to solve it.
2. Inconsistent calculated column formula in tables
This appears if there is a formula in a column which is different to the others either side of it. This normally occurs when 1 formula has been changed without changing all the similar ones. This can lead to one random calculation giving the wrong result while the rest in the column are correct. “Copy formula from above” restores the consistency.
3. Cells containing years represented as 2 digits
This option will appear if there is a date that is formatted as text which only had 2 figures in the year part such as 19 instead of 2019. There are 2 available options which could be of use – convert XX to 19XX or convert XX to 20XX
4. Numbers formatted as text or proceeded by an apostrophe
Sometimes when data is exported to Excel from other packages numbers export as text. They look the same as normal numbers but perform differently. For example, if a column containing a mixture of numbers formatted as numbers and numbers formatted as text are added then only those formatted as numbers will be included. Another common issue is when performing a VLOOKUP. It won’t find the number correctly. The option from the yellow diamond of “Convert to Number” will solve the problem.
5. Formulas inconsistent with other formulas in the region
This can happen if there is a range of cells (multiple rows and columns) which have been autofilled and one has been changed differently to the others. The choice of “Copy formula from left” is a solution that will normally provide the required result.
6. Formulas which omit cells in a region
This might occur if you autosum a column or row but don’t include all the cells, missing off the last one for example. Choosing the option of “Update Formula to include Cells” will correct the problem.
7. Unlocked cells containing formulas
As soon as a cell containing a formula is marked as unlocked it will show with the green triangle. The option to solve this is “Lock Cell” from the yellow diamond.
8. Formulas referring to empty cells (off by default)
This is sometimes useful but is switched off by default as often templates are set up without data initially and these would all be viewed as a problem. It shows that a calculation contains one or more cells that currently don’t have any numbers in them. “Trace empty cell” is the option that can help. It points at the empty cell and can then be filled in or removed from the calculation as required.
9. Data entered in a table is invalid
This will only show if there is an inconsistent data type for data connected to SharePoint.
10. Misleading number formats
This error can occur when you link to a cell that has one style of formatting applied, but the destination cell is another format. For instance, cell A1 is formatted as currency, but a linked cell with =A1 is formatted as a date. “Update format” changes the format of the linked cell containing the calculation to the same format as the cell it is linking to.
Although, initially, these green triangles appear annoying and sometimes get in the way they are actually very useful in solving problem issues that may occur.
If you found this useful see what Excel courses are available or email firstname.lastname@example.org to discuss any training requirements.