If you see a little green triangle in the top left corner of a cell, this means that there is some error in that cell.
Usually, these errors arise due to incorrect formulas or incorrect data types.
Sometimes you may want to choose the option to ignore the error without changing the data or the formula inside the cell. Ignoring the error will get rid of the green triangle.
Other times, you may want to resolve the error by correcting the formula or by letting Excel fix the problem for you. In either case, the green triangle will go away.
In this article, we will take a look at different methods on how to remove or ignore the error and thereby get rid of the green triangles in the cells in Excel.
Method 1: Disabling the Background Error Checking Option
In this method, we will take a look at how to disable error checking in Excel so that the green triangle goes away.
By default, error checking is turned on in Excel, so whenever an error is found by the error checking, it gets highlighted by showing a green triangle at the top left part of the cell.
For demonstration, take a look at the following data.
In the Units Sold column, the cells have a little green triangle in them.
This error is there because the numbers are stored as text. This is because the values in the cells are preceded by an apostrophe.
The Total Sale column has been calculated using the value from the Units Sold column.
Now while the Total Sale has been calculated correctly, Excel is still showing us the error (as Excel wants us to keep the numerical data as a number and not text).
Let’s disable error checking to get rid of these green triangles.
- From the ribbon, select the File option.
- From the window that appears, select Options from the panel on the left.
- Inside the Options Window, select Formulas from the left panel.
- Under the Error Checking section, uncheck the box next to ‘Enable background error checking’.
- Click on OK.
- Go back to your worksheet, and you will see that the green triangles have disappeared.
So in this method, we have seen how to get rid of the green triangles by disabling background error checking.
Note that just because the green triangles have gone, the error has also been rectified. With this method, we have stopped Excel from highlighting errors in cells by disabling background error checking. Use this option when you’re sure what you’re doing.
Method 2: Disabling a Specific Error-Checking Rule
We saw in the previous method that disabling error-checking will remove the green triangles.
However, if you want to disable the error caused by numbers that are stored as text while leaving all other error-checking options unchanged, then follow the steps below.
In this method, we will disable a specific error check, i.e., the error due to numbers formatted as text.
For the demonstration, we will use the same data set as we saw in the first method.
In the above data set, the cells of the ‘Units Sold’ column have green triangles in them.
This is because the units sold are stored as text instead of a number, and they are being used to calculate the Total Sale.
- From the ribbon in the workbook, select the File option.
- From the panel on the left, select Options.
- From the panel shown on the left, select Formulas.
- Under the Error checking rules section, un-check the box next to ‘Numbers formatted as text or preceded by an apostrophe’.
- Click on OK.
Go back to your worksheet, and you will see that the green triangles have disappeared.
The number stored as text is a standard error, and if we are aware of this, we can only disable this error while leaving the other error-checking options unchanged.
In this method, we have disabled one of the reasons that Excel shows a green triangle (which is the number stored as a text issue) while keeping the overall error checking enabled. So if there are other cells in your worksheet that have an error that can cause it to show a green triangle, it won’t be impacted.
Method 3: Ignoring the Error
If you do not wish to disable error checking but still want to remove the green triangles, then simply ignoring the error, for the time being, is the way to go.
In this method, we will take a look at how to get rid of the green triangles by ignoring the error and without disabling any error check.
As an example, we will use the same data set that we used in the previous examples.
- Select all the cells containing the green triangle. In this case, this will be from B2 to B9.
- After you have selected the cells, a little yellow icon with an exclamation mark will appear next to the selected cells.
- Click on the button, and the following menu will appear.
- From this menu, select the Ignore Error option.
- You will see that the green triangles have disappeared.
This method is helpful as it only removes the green triangle from the selected cell or range of cells where we ignored the error. The rest of the cells can still show the error if there is a cause for it.
Method 4: Resolving the Error
As opposed to all the previous methods, the best approach to get rid of the green triangle is to actually resolve the error that is causing it.
Let me show you an example.
We will use the same dataset that we have used in the previous methods to see how to do that.
- Select all the cells containing the green triangle. In this case, this will be from B2 to B9.
- After you have selected the cells, a little button with an exclamation mark will appear next to the selected cells.
- Click on the button, and the following menu will appear.
- At the top of this menu, you will see what is causing this error, highlighted in gray. In this case, Excel is telling us that the error is ‘Number Stored as Text’. The second option in the menu is the action that Excel is suggesting to us to resolve this error.
- From this menu, select the second option, which is the corrective action that Excel is suggesting to us, i.e., Convert to Number.
- You will see that the green triangles have disappeared, and the error has been resolved.
In my opinion, this is the best way to remove the green triangle as we address the underlying reason for the error in this case.
However, if this is not possible or suitable in your situation, you can use any of the methods covered prior to this one.
Common Reasons Excel Shows the Green Triangle
Errors can arise due to different reasons. One such reason is numbers stored as text.
Normally numbers in Excel are stored as values. It may happen that in your Excel worksheet, numbers are stored as text.
Then if you use or reference these numbers in a formula, a little green triangle will appear.
Formulas do not take text as input. This is why Excel throws an error if you use a number stored as text in a formula.
Furthermore, if your formula is performing a division by zero, an error will be shown in the form of a little green triangle in the top left corner of the cell.
Errors can also occur if a formula is referencing from a cell or range that has been deleted.
How to Change the Color of the Green Triangle Error Icon
If you want to change the color of the green triangle error icon then follow the steps shown below. For demonstration, let’s use the same data set as we did in the first Method.
- Click on the File option in the ribbon.
- From the panel on the left, select Options.
- From the panel shown on the left, select Formulas.
- Under the Error Checking section, click on the drop-down button next to ‘Indicate errors using this color’.
- From this drop-down menu, select any color you want. For demonstration, I will select the blue color.
- Click on OK.
- The result will look like the one shown below.
As you can see, the green triangles have now turned blue.
In this article, we have seen multiple methods on how to get rid of the green triangles, which are caused due to errors in cells.
Methods 1 and 2 involve disabling Excel’s built-in error checking. While Method 1 disables error-checking entirely, Method 2 allows you to disable certain error-checking rules.
Method 3 gets rid of the green triangles by telling Excel to ignore the error. This method does not involve disabling any error checking which is a very useful Excel feature to have turned on.
Method 4 is the correct approach to removing the green triangle by actually resolving the root cause of the error. This is, in my opinion, the best way of removing any green triangles in Excel.
Apart from this, we have seen common reasons behind these green triangle errors and how to change the color of the green triangle.
Other Excel articles you may also like: