You sum a column and Excel shows 0, or a VLOOKUP says #N/A although the value is clearly there. The reason is often that the numbers are stored as text. This is very common with data copied from a website, a PDF or an accounting system.
How to spot it
- Numbers are left aligned instead of right aligned.
- A small green triangle appears in the corner of the cell.
- The status bar shows Count but no Sum when you select the cells.
Fix 1: the green triangle
Select the cells, click the yellow warning icon that appears and choose Convert to Number. This is the fastest fix for a small range.
Fix 2: Paste Special multiply
Type 1 in any empty cell and copy it. Select the text numbers, right click, choose Paste Special, then Multiply, then OK. Excel multiplies every cell by 1 and turns it into a real number.
Fix 3: Text to Columns
Select the column, go to Data, then Text to Columns, and click Finish without changing anything. This forces Excel to re-read each cell.
Fix 4: the VALUE function
=VALUE(A2) converts one cell. If the text also has extra spaces or odd characters, clean it first:
=VALUE(TRIM(CLEAN(A2)))
Fix 5: double minus
=--A2 is a short trick that forces the number conversion.
Stop it happening again
Before typing numbers, format the column as Number or General, not Text. A column formatted as Text keeps everything as text even when you type digits.