How do I fix numbers stored as text in Google Sheets?

Select the affected range and check cell alignment - numbers stored as text left-align instead of right-aligning. To convert, use `=VALUE(A1)` for individual cells or `=ARRAYFORMULA(VALUE(A2:A))` for an entire column. For bulk fixes across 6,000-10,000 rows, placing the ARRAYFORMULA in a dedicated Staging tab column is more reliable than in-place conversion. You can verify the fix by checking that SUMIFS and SUM now return non-zero results on the converted range. If VALUE() still returns errors, the cell likely contains currency symbols or thousand-separator commas - use `=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",",""))` to strip them first.

Why does SUMIFS return zero even when data looks correct in Google Sheets?

The three most common causes are mismatched data types, whitespace, and inconsistent category capitalization. Check each one: (1) cell alignment in the sum range - left-aligned numbers are text; (2) hidden whitespace using `=LEN(A2)` vs `=LEN(TRIM(A2))` - a mismatch confirms spaces exist; (3) capitalization variants by scanning the criteria column with a pivot table or COUNTIF. Wrapping the criteria column reference in `ARRAYFORMULA(TRIM(CLEAN(...)))` inside the SUMIFS resolves whitespace and encoding issues. If the column contains text-formatted numbers, wrap the sum range in `ARRAYFORMULA(VALUE(...))`.

What's the best way to normalize dates across tabs in a Google Sheets financial model?

Use a Staging tab column with `=ARRAYFORMULA(IFERROR(DATEVALUE('Raw Data'!A2:A), 'Raw Data'!A2:A))` to catch text-formatted dates while leaving real Date-type cells intact. According to Google's Sheets documentation, DATEVALUE only processes text strings and returns an error on cells already formatted as Date type - so the IFERROR wrapper is required, not optional. All model tabs then reference the Staging date column, which means the normalization survives every data refresh. For date strings in non-standard formats, wrap the raw cell in TEXT() first to force a consistent format before passing to DATEVALUE.

How do I handle inconsistent category labels in a multi-tab SUMIFS model?

Build a normalization lookup table in your Assumptions tab: raw values (including common misspellings and variants) in column 1, canonical labels in column 2. Then in Staging, use `=ARRAYFORMULA(IFERROR(VLOOKUP(TRIM(LOWER('Raw Data'!C2:C)), Assumptions!$H$2:$I$50, 2, FALSE), TRIM('Raw Data'!C2:C)))` to map every raw value to its canonical form. Anything not in the lookup passes through trimmed rather than dropping silently to zero. When new variants appear, you add one row to the Assumptions table - no formulas to touch, no downstream references to update.

Does ARRAYFORMULA slow down Google Sheets data cleaning significantly?

It depends on row count and formula complexity. On 6,000-10,000 rows (a typical monthly close dataset), ARRAYFORMULA with TRIM, CLEAN, and VALUE adds 3-8 seconds of recalc. On 50,000+ rows across 4 cleaning columns, expect 20-30 seconds per refresh. The performance hit is front-loaded at the Staging tab, so model tabs that pull from Staging don't re-run the cleaning logic on every edit - only when Staging formulas recalculate. If recalc time is a constraint, add a QUERY pre-filter before the ARRAYFORMULA to reduce the row count the cleaning formulas process, or split large Staging columns into separate sheets to limit recalc scope.

Google Sheets Data Cleaning for Financial Models (2026)

5 Data-Cleaning Errors That Break Google Sheets Financial Models

These aren't edge cases. Every one shows up in models built from ERP exports, CRM pulls, or accounting system dumps.

1. Numbers Stored as Text

A cell showing "$2,847,000" left-aligned is not a number. SUMIFS and SUM ignore it. Your revenue total is wrong. The tell is alignment: real numbers right-align by default, text-formatted numbers left-align.

The fix is VALUE() or NUMBERVALUE():

=SUMIFS(
  ARRAYFORMULA(VALUE('Raw Data'!C:C)),
  'Raw Data'!B:B, ">=" & Assumptions!$B$3,
  'Raw Data'!B:B, "<=" & Assumptions!$B$4
)

That VALUE() wrapper adds 3-8 seconds of recalc on a 50,000-row dataset. Worth knowing before you wrap every SUMIFS in it.

2. Date Strings That DATEVALUE Can't Parse

Dates imported from Salesforce or NetSuite often arrive as text: "2024-07-14", "Jul 14, 2024", "14/07/2024". They look like dates. They're not.

According to Google's official Sheets function documentation, DATEVALUE "converts a provided date string in a known format to a date value" but only processes text strings - it returns an error on cells already formatted as the Date type. That distinction matters in practice: wrap everything from an import in DATEVALUE() and you'll get errors on columns that are already real dates. The safer pattern in a Staging column:

=ARRAYFORMULA(
  IF('Raw Data'!D2:D="","",
    IFERROR(DATEVALUE('Raw Data'!D2:D), 'Raw Data'!D2:D)
  )
)

3. Whitespace and Non-Printable Characters

A category value of " Enterprise " (leading space) won't match "Enterprise" in a SUMIFS. Your enterprise segment revenue shows zero. The board pack goes out wrong.

TRIM() removes leading and trailing spaces. For invisible characters - line breaks, null bytes, ASCII garbage from copy-pasted Excel files - you need CLEAN(). According to Google Sheets documentation, CLEAN "removes all non-printable characters from text imported from other applications" - specifically characters in the ASCII 0-31 range, which covers most encoding junk that crawls in from web exports.

The combo in a cross-tab SUMIFS:

=SUMIFS(
  'P&L'!D:D,
  ARRAYFORMULA(TRIM(CLEAN('P&L'!B:B))), "Enterprise",
  'P&L'!A:A, ">=" & Assumptions!$B$3
)

4. Inconsistent Category Labels

"SMB", "smb", "Small & Medium Business", "SMB " - all represent the same segment. SUMIFS treats them as 4 different categories. A SUMIFS-based P&L will silently miscategorize $2.8M in revenue without a single error message.

Build a canonical-values table in Assumptions and VLOOKUP against it in your Staging tab:

=IFERROR(
  VLOOKUP(TRIM(LOWER('Raw Data'!F2)), Assumptions!$H$2:$I$20, 2, FALSE),
  TRIM('Raw Data'!F2)
)

Anything not in the lookup passes through trimmed, so new variants surface as unrecognized values rather than silently dropping to zero.

5. Mixed Types in Numeric Columns

A column that's 95% numbers but contains "N/A" or "Pending" in 5% of rows forces Google Sheets to treat the column as text in some formula contexts. AVERAGE() breaks. MIN()/MAX() return wrong results.

IFERROR plus VALUE() in the Staging column catches this cleanly:

=IFERROR(VALUE('Raw Data'!G2), 0)

Map to empty string instead of zero if zeroes would distort averages:

=IFERROR(VALUE('Raw Data'!G2), "")

Static Cleaning vs. Dynamic Cleaning

Static cleaning means fixing the source data directly - selecting the column, running Data → Split text to columns, reformatting in place. It takes 20 minutes for a 6,000-10,000 row monthly close dataset. It also disappears the moment someone drops in a new export.

Dynamic cleaning means formulas that clean in real time, every time the data refreshes. That's what you want for any source that updates more than once (which is all of them). The cost is recalc time: on 50,000 rows with ARRAYFORMULA cleaning across 4 columns, expect 20-30 seconds per refresh. That's fine for a monthly board pack pull. It's annoying for a model someone's actively editing.

The practical split: dynamic cleaning for any automated feed or recurring export; static cleaning only for legacy historical data that won't change again.

Staging Tab Architecture for Google Sheets Data Cleaning

The cleanest pattern for multi-tab financial models is a dedicated Staging tab that sits between Raw Data and the model tabs. Raw Data is untouched. Model tabs reference only Staging. Cleaning logic lives entirely in Staging.

Tab	Contents	References
Raw Data	Unmodified import or paste	Nothing - read-only
Staging	Cleaning formulas only	Pulls from Raw Data
Assumptions	Parameters, lookups, normalization tables	Self-contained
P&L	Model logic	Pulls from Staging + Assumptions
Balance Sheet	Model logic	Pulls from Staging + Assumptions
Cash Flow	Derived statements	Pulls from P&L + Balance Sheet

A typical Staging column setup for a revenue feed:

' Date column normalized (Staging!A:A):
=ARRAYFORMULA(IFERROR(DATEVALUE('Raw Data'!A2:A), 'Raw Data'!A2:A))

' Amount as numbers (Staging!B:B):
=ARRAYFORMULA(IFERROR(VALUE('Raw Data'!B2:B), 0))

' Segment normalized (Staging!C:C):
=ARRAYFORMULA(IFERROR(
  VLOOKUP(TRIM(LOWER('Raw Data'!C2:C)), Assumptions!$H$2:$I$50, 2, FALSE),
  TRIM('Raw Data'!C2:C)
))

The P&L then references Staging, not Raw Data:

=SUMIFS(
  Staging!B:B,
  Staging!C:C, "Enterprise",
  Staging!A:A, ">=" & Assumptions!$B$3,
  Staging!A:A, "<=" & Assumptions!$B$4
)

The payoff: when data updates, you replace Raw Data. Everything downstream cleans and recalculates automatically. Setup takes 4-6 hours the first time. A monthly close that used to involve 45 minutes of manual cleaning drops to paste-and-refresh.

The Part That Doesn't Scale

The Staging tab architecture works well when the cleaning rules are known in advance. What it doesn't handle is discovering new dirty patterns in a fresh dataset: figuring out that this month's CRM export has a new date format, or that the accounting system started prepending account numbers to category names.

That's where ModelMonkey helps. The AI assistant in the sidebar can inspect a range, identify which cells are text-formatted numbers or inconsistent labels, and write the cleaning formula into your Staging tab. It doesn't replace the architecture above - it shortens the time to set it up for a new data source.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.