Why does Excel show a green triangle on numbers?

Excel detected a number stored as text or another inconsistency. Click the icon and choose Convert to Number, or use Text to Columns or multiply by 1.

Will converting text to number break leading zeros?

Yes. IDs like 00123 may become 123. Format the column as Text before import if leading zeros must be preserved.

SUM ignores my numbers — is it a text problem?

Often yes. Check ISNUMBER on a sample cell. If FALSE, convert the column before relying on SUM or pivot totals.

What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Should I still learn VLOOKUP in 2026?

Yes for legacy files and teams on older Excel. Learn XLOOKUP as well if you use Microsoft 365 — many new models start there.

Tutorials - 2026-06-09

How to Convert Text to Number in Excel (Green Triangle Fix)

Fix numbers stored as text in Excel: remove the green triangle, repair SUM and VLOOKUP errors, and use VALUE, multiply by 1, or Text to Columns.

Why Excel Stores Numbers as Text

When you import CSV files, copy data from the web, or type an apostrophe before a number, Excel may store values as text. They look like numbers but fail in SUM, AVERAGE, VLOOKUP, and pivot tables. Signs of text-numbers: Green triangle in the cell corner Values left-aligned while neighbors are right-aligned \=ISNUMBER(A2)\ returns FALSE SUM ignores the cells or returns a wrong total This is one of the most common causes of #VALUE! and VLOOKUP #N/A in real workbooks.

Method 1: Error Checker (Green Triangle)

Select the affected cells Click the warning icon next to the cell Choose Convert to Number Best for small ranges after an import.

Method 2: Multiply by 1

In a helper column: \\\ =A2*1 \\\ Copy values and Paste Special → Values back over the original column if needed. Works when text is numeric but stored as text.

Method 3: VALUE Function

\\\ =VALUE(A2) \\\ Use when the text includes currency symbols you have already removed, or when building a one-time cleaning column.

Method 4: Text to Columns

Select the column Data → Text to Columns Choose Delimited → Next → clear all delimiters → Finish Excel converts text-numbers to real numbers without a formula column.

Method 5: Paste Special Multiply

Type \1\ in an empty cell and copy it Select the text-number range Paste Special → Multiply Fast for large blocks of imported data.

Fix VLOOKUP and SUMIF After Conversion

After converting, re-test: \\\ =SUM(B2:B100) =VLOOKUP(E2, A:D, 3, FALSE) \\\ If VLOOKUP still fails, check TRIM on IDs and confirm exact match with FALSE. Related troubleshooting: VLOOKUP returning #N/A SUMIF not working Fix Excel formula errors VALUE function

Prevent Text-Numbers on Import

Open CSV through Data → From Text/CSV and set column types to Number where appropriate Use Power Query to set data types before loading Avoid leading apostrophes (\'123\) unless the field must stay text (SKU with leading zeros)

Quick Audit Column

Add a helper to count problem rows: \\\ =IF(ISNUMBER(A2), "OK", "Text number") \\\ Filter on "Text number" before closing month-end reports.

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons \nLookup help: VLOOKUP function · VLOOKUP tutorial · Fix #N/A · vs XLOOKUP

Frequently asked questions

Why does Excel show a green triangle on numbers? Excel detected a number stored as text or another inconsistency. Click the icon and choose Convert to Number, or use Text to Columns or multiply by 1.
Will converting text to number break leading zeros? Yes. IDs like 00123 may become 123. Format the column as Text before import if leading zeros must be preserved.
SUM ignores my numbers — is it a text problem? Often yes. Check ISNUMBER on a sample cell. If FALSE, convert the column before relying on SUM or pivot totals.
What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.
Should I still learn VLOOKUP in 2026? Yes for legacy files and teams on older Excel. Learn XLOOKUP as well if you use Microsoft 365 — many new models start there.