How to Get Rid of the Green Triangle in Excel

This tutorial will demonstrate how to get rid of the green triangle in Excel.

If the data contained in a cell has an error as far as Excel is concerned, and background error checking is switched on, then a small green triangle will be shown in the left hand top corner of a cell.   If you were to click in the cell, a drop down list of solutions to the error will be offered.   On some occasions, you may not want to amend the data in your cell, but you may want to hide the green triangle, thus ignoring the error.

Switch off Background Error Checking

To stop the green triangle error from showing, you can switch off the background error checking in Excel.

In the Ribbon, select File > Options > Formulas > Error Checking.

Remove the tick from the Enable background error checking option, and then click OK.

NOTE: This will remove background error checking for the active workbook as well any workbooks opened in the future.

Amending Error Checking Rules

Instead of turning off Background error checking completely, we can switch off individual rules that are by default switched on in Excel.  For example, in the data above, the numbers are formatted as text and therefore cannot be used in calculation formulas.   The green triangle will let us know this, and provide a drop down list of solutions.   We can switch off this specific error check.

In the Ribbon, select File > Options > Formulas > Error Checking.

In the Error checking rules section, remove the check mark from “Numbers formatted as text or preceded by an apostrophe” to stop Excel from checking for this error, and then click OK.

Ignore Error

Normally you would want to keep the background error checking switched on as it is very useful in showing us if we have an error in the data or, more importantly, in a formula.

To remove the green triangles from cells where you are aware of the error, but do not want to see the triangle, select the cells and then click on the little yellow exclamation mark icon.

 

Click Ignore Error to remove the green triangles, or, if you want to solve the error, click Convert to Number – this will then enable you to use the numbers stored as text in formula calculations.

Solving the Error with the Drop Down List

If you error is for a different reason, for example a divide by zero formula error, then a different drop-down list will be shown.

You can also ignore the error, or you you can click Show Calculation Steps to resolve the error.  If you click Edit in Formula Bar, Excel will put the formula into Editing mode and place your mouse pointer in the formula bar in order for you to amend the formula.

 

Another reason for the error could be an inconsistent formula.  This occurs when the formula in the cell is different to the formula that is in the cell above.  If this is the case, a different drop down list will appear:

To solve this problem and remove the green triangle, we can click on the Copy Formula from Above option, or, if we do not want to amend the formula, we can once again click on Ignore Error.

Common Errors in Excel

A few more common Excel errors will cause the green triangle to show in the Excel sheet – all of which can be removed using the Ignore Error option, or can be solved using Excel’s error checking options.

#REF – this error normally occurs when a formula is referring to a range that is cannot find – or if rows/columns have been removed.

Resting your mouse on the exclamation mark icon will show you the reason for error, while clicking on the drop down will show you a dropdown list of possible solutions to error.

#VALUE – this error normally occurs when you are trying to include a cell that has text in it, in a calculation.

Formula Omits adjacent cells – this error normally occurs when you have a function like the sum function and are not including all the possible cells in the calculation.