Green Triangles
In Excel XP (aka 2002 or version 10) and later, you may see a green triangle in your cell. It looks like the red triangle that appears when you have a comment in the cell, but of course, it’s green.
The green triangle is Excel telling you that there is a potential error in the cell. Sometimes the error is obvious like a #DIV/0! error. Other times it’s not. One such time is when you have text in a cell that looks like a number. Like this
You can click on the little exclamation point as shown above to see what Excel thinks the potential error is and to help you find a solution if it’s a real error.
Now I will read your mind. No, I don’t know why error triangles are green and comments are red. Poor planning, I suppose. Under Tools>Options>Error Checking, you can change the color among other things.
Dick…I’ve found the error triangles tend to show up more when I import or paste data from another source, such as an Access query. I’ve also found that the main reason for the error triangle is that other apps tend to add spaces to the data, usually enough spaces to fill up the length of the field. One thing, in my case, that helps in removal of the error triangle is to remove all excess spaces from the worksheet when it’s imported. I use John Walkenbach’s PUP5 utility to remove the excess spaces. This works for me 90% of the time in removing the error triangles.
Hi,
I am also facing a similar issue. can i avoid these errors by writing some code in the server side. i.e in my Java program. i can’t use any utilities.
please help me in this regard.
my email is varadhagopal@yahoo.com
Thanks
Varada.
he cant really read your mind Varada!
Thank you…I just upgraded at work to Office XP and we do a lot of cut and paste from QMF etc. This was driving me insane. I just turned off the Number as Text option and that got rid of all of my problems.
To turn this feature off: Go to Tools/Options on the menu bar and select the Error Checking tab. Deselect the checkbox which states Enable background error checking.
Thank you, thank you Neil!!!
That tip about turning off the error checking in the options tab really did the trick. Those triangles were driving me nuts.
Thanks again, Neil!
Joanne
Think about the consequences of turning error-checking off.
You may regret this when your figures don’t add up because some numbers were imported as text.
You can selectively turn off items such as “refers to empty cells” but turning them all off is simply sweeping a possible problem under the carpet. A more severe analogy is like suppressing pain and continuing to use the damaged limb. In my opinion, it’s better to know than be in the dark. You can click on the yellow alert diamond to ignore a specific error for the specific cell entry. For others, you might be very glad that you were warned about an inconsistent formula, a number stored as text, etc.
BTW in Excel 2007 you can change the triangle colour.
Thanks for the advice. I got rid of the green triangles by doing an error check and telling it to ignore the error wherever it appeared, one cell at a time. The error in this case was that I was adding numbers together (i.e. B1+C1+D1 into cell E1, to simplify) and not adding in the number in A1, so it thought I was making a possible error and gave me that annoying little green triangle.
I very much appreciated your comments. One of the on-line courses that I am taking this Summer 2009 did not fully explain the meaning of the “green triangle.”
Hi All,
The main body of this forum is all about gettin rid of the green triangles but does anyone know a way of creating them?
I am combining an excel spreadsheet to a GIS shapefile. Now, to carry out this task I have to have a column similar in both the excel spreadsheet and the attributes table of the GIS shapefile. Lets call the column common in both the X-column. I have found that when the X-column I need to combine to the GIS shapefile does not have the green triangle in the excel spreadsheet the joining of the two tables does not work.
Does anyone have any other suggestions how to combine both the tables together!?
If you need me to clarify any of the above please do not hesitate to ask.
Cheers,
Aoife
Vlookup and the green triangle. I have a spreadsheet that has over 60000 rows. In one comumn I have a combination of numbers and text. When I format the column to be text I get the little green triangle for each number in the column. Normally I can ignore that however I have found that when doing a Vlookup a cell with a green triangle does not equate to a cell without a green triangle even though both have the same number in them. I can solve the problem by using the delete key to remove the contents, reformatting the cell to general, reformat it to text and then type the exact same number back in as text. I end up with no green triangle and the Vlookup works. My problem is that I can not do that for the 30,000 plus numbers in the spreadsheet. I have tried formatting the column as general and then reformatting as text and the green triangle reappears as soon as I go back to text. I have also tried to reformat the column to general, insert a blank column, do a equal the old cell in the new column then picking the whole column up and pasting special using values into a blank column that is formatted as text. No luck still get green triangles. I need a way to solve this problem without having to hand type over every number in the column. Can anyone help? If so please email me at beverett@printinc.com Thank you
Hello Bill,
I’ve experienced that before as well.
This is the solution.
Put somewhere in a empty cell the value 0 (ZERO). Copy/paste-special this source to the your green-triangle-range with the option ADD-UP. (I’m not sure how it’s called in the english version. I’ve got dutch language).
What Excel does is add-up the value 0 to each cell. The outcome is not changed of course but the property turns to VALUE instead of TEXT. Problem fixed.
By the way, the problem can be fixed also, by multiplting with the value 1.
Regards, Peter.
Hi Peter,
I wanted to get back and let you know where I ended up.
In a Vlookup a cell with a green triangle will not match with one that has a green triangle. A way must be found to make the green triangle go away to make a Vlookup or a Match work.
If the green triangle appears in the source file then =Vlookup(G2&”",$a$2:$b$1721,2,FALSE) where the &”" is added at the end of the first G2 (or whatever) in the beginning of the Vlookup will take care of the problem. This appends nothing to the number and makes it text. I found this part of the solution several pages down in a Google lookup for “Green Triangles Excell”.
If the green triangle appears in the data then formatting a column as general and putting the formula =a2&”" and copying the data from comumn A (assuming that is where your data starts) into column B and then using Vlookup (G2,$b$2:$c$1721,2,FALSE) will solve the problem. Again you are appending nothing on the number to make it text and get rid of the green triangle.
I am sorry to say I now have about 3 hours into solving this problem in all cases where green triangles keep Vlookup and Match from working.
Thanks,
Bill
Bill: Another way to do it is using the
TEXT()andVALUE()functions. Depending on which way you need to go, your formula might look likeor
You have to get that second argument of the TEXT just right though.
Hey Peter (and Bill) - thanks so much for bringing up the green arrow dilemma with Vlookup - very timely! I needed this today. Peter, your solution with ‘0′ worked perfectly for me.
thanks again, Linda
Hi Linda,
You’re welcome. For once I helped somebody. Most of the times I rely on other experts on a forum instead
Peter.