I asked here last week as to why the box that allowed you change from numbers being text to numbers.

Question

I asked here last week as to why the box that allowed you change from numbers being text to numbers.

William Auld 160

Its annoying having any numbers on your spreadsheet little black triangles in the left/hand top corner. I got a reply:, "rather than the table heading, and press Alt+Shift+F10 to open its error-checking menu. Choose Convert to Number if that is the reported issue." This didn't work, so I found that if you hoover over the black triangle a box appears stating "Fix this error" or "Ignore error" if you hit the "Ignore error" and providing you have highlighted all the affected cells the error clears. The triangles may reappear in which case go to format cells and under custom change to 0:00 to 0.

0 comments

1 answer

Your answer

Answer 1

The black triangles are Excel error indicators showing that numbers are stored as text. To remove them for the selected cells without converting the values, select the cells, select the warning box, and choose Ignore Error. If the triangles return, that usually means new or existing values are still being treated as text.

If the values should be real numbers, use Convert to Number instead:

Select the affected cell or range.
Select the error indicator or press Alt+Shift+F10.
Choose Convert to Number.

If Convert to Number is not available or does not work, use a formula-based conversion:

Insert a new column next to the text values.
Enter =VALUE(cell) using the first affected cell reference.
Fill the formula down.
Copy the results.
Use Home > Paste > Paste Special > Values to replace the original text values.

If the goal is only to hide the triangles, error checking for this condition can be turned off:

Select File > Options.
Select Formulas.
Under Error checking rules, clear Numbers formatted as text or preceded by an apostrophe.
Select OK.

If the entries are intended to stay as text, format the cells as Text before entering them. Excel notes that left-aligned numbers usually indicate they are not formatted as numbers.