Excel stores every value as a number — even dates, times, and Boolean values. But that number isn’t always what appears in the cell, and that mismatch is why VLOOKUP fails on ‘identical’ dates or SUM ignores ‘zeroes’ you can see.
The Setup
You’re auditing Q1 sales for Alibaba’s regional partners. Finance sent you Sheet1, raw export from their ERP — no formatting, inconsistent headers, and mixed data types in column C (some entries say "N/A", others are blank, some show "0", and a few have actual numbers like 45200). You need to calculate total revenue per region, but first, you must understand what Excel *actually sees* in each cell — not what it shows.
Partner
Region
Revenue
Status
Last Updated
Sarah Chen
APAC
45200
Active
2024-03-15
Diego Mora
EMEA
0
Pending
2024-02-28
Akira Tanaka
APAC
Inactive
2024-01-10
Lena Petrova
EMEA
N/A
Active
2024-03-22
Rajiv Mehta
APAC
38750
Active
2024-03-05
Maria Santos
AMER
0
Active
2024-02-19
James Wu
AMER
Pending
2024-03-18
Tanya Ito
EMEA
62100
Active
2024-03-12
Oliver Reed
AMER
N/A
Inactive
2024-01-30
Zara Khan
APAC
29400
Active
2024-02-07
The Challenge
You need to sum revenue by region — but only real numeric values count. The problem? Excel treats "N/A" as text (so SUM ignores it), treats blank cells as zero in some contexts but not others, and interprets "0" as a number — even when it should mean "no data". Worse, dates like "2024-03-15" are stored as serial numbers (45366), and if column E has mixed date formats — some as text, some as true dates — Excel won’t let you sort or filter reliably.
And here’s the counterintuitive part: pressing Ctrl + ` (grave accent) reveals hidden apostrophes — meaning some "numbers" are actually text. But even that doesn’t tell you whether a blank cell contains ="" or is truly empty.
Walking Through It
Start by checking what Excel *really* stores. Select C2:C11. Press Alt + H + F + C to open Format Cells. Look at the Number tab: most show "General", but C4 and C9 say "Text". That’s your first clue.
Now test cell content type. In F2, enter =ISTEXT(C2). Drag down. You’ll get TRUE for C4 ("N/A") and C9 ("N/A"), FALSE elsewhere — except C3 and C7, which return FALSE *but* also return 0 for =ISNUMBER(C3). Why? Because truly blank cells return FALSE for both ISTEXT and ISNUMBER.
So we need a reliable way to isolate *real* numbers. Use this formula in G2:
=IF(OR(ISBLANK(C2),C2="N/A",C2=""),0,IF(ISNUMBER(C2),C2,0))
That converts blanks, "N/A", and empty strings to zero — but leaves real numbers untouched. Copy down to G11.
C2:C11 (Raw)
G2:G11 (Cleaned)
45200
45200
0
0
0
N/A
0
38750
38750
0
0
0
62100
62100
N/A
0
29400
29400
Next, verify date integrity. In H2, enter =ISNUMBER(E2). You’ll find E3 ("2024-02-28") returns TRUE, but E4 ("2024-01-10") returns FALSE — because it’s stored as text. Fix it: in I2, use =IF(ISNUMBER(E2),E2,DATEVALUE(E2)). Then copy down. Now all dates are serial numbers Excel can sort.
The Result
With cleaned revenue (G2:G11) and validated dates (I2:I11), pivot the data. Insert > PivotTable > select A1:I11 > place Region in Rows, Sum of G2:G11 in Values.
Region
Total Revenue
APAC
113,350
EMEA
62,100
AMER
29,400
Note: APAC includes Sarah, Rajiv, and Zara — but *not* Akira (blank) or Diego (0, retained as intentional zero). That distinction only works because we understood how Excel stores “0”, “”, and “N/A” differently.
What Could Go Wrong
Mistake #1: Using COUNT instead of COUNTA on revenue column. COUNT(C2:C11) returns 7 — ignoring blanks and text. But COUNTA(C2:C11) returns 10, counting "N/A" and "0" as non-blank. If you’re calculating average deals per region, this gives wildly wrong denominators.
Mistake #2: Sorting dates without checking storage type. If E4 ("2024-01-10") is text while E2 is a serial number, sorting puts "2024-01-10" at the top — not the earliest date. Excel sorts text alphabetically, so "2024-01-10" comes before "2024-02-28"… but "2024-10-01" would sort *after* "2024-02-28" — breaking chronological order.
Mistake #3: Assuming =A1+B1 always adds. If A1 contains 45200 (number) and B1 contains "45200" (text), =A1+B1 returns 90400 — Excel coerces text to number silently. But if B1 is "45,200", =A1+B1 returns #VALUE!. That inconsistency breaks macros and Power Query imports.
Here’s how to spot these fast — no formulas needed:
Signal
What It Means
Shortcut
Top-left corner of cell has a tiny green triangle
Excel thinks data type is inconsistent (e.g., number mixed with text)
Alt + A + V + A (to ignore error)
Numbers aligned left instead of right
Almost certainly stored as text
Ctrl + ` (toggle formula view) to check for leading apostrophes
DATEVALUE returns #VALUE! on a date-looking string
Not just formatting — it’s text with non-breaking spaces or invisible characters
Alt + H + F + D → select 'Trim' after pasting
Cell shows 0 but =LEN(A1) returns 0
Truly empty — no space, no apostrophe, no zero-length string
