Spreadsheets

Blank cells in debit and credit columns breaking your formulas

Blank-looking cells in debit and credit columns can return #VALUE! or wrong totals; learn empty vs empty string vs space and a one-step clean-up.

Editorial illustration for Blank cells in debit and credit columns breaking your formulas

A cell can look blank in three different ways: truly empty, holding an empty string, or holding a space. Only the first behaves as zero in arithmetic. The other two return #VALUE! when you subtract or add them, and they distort counts. Test with ISBLANK and LEN, then clean the column once.

What is the difference between empty, "" and a space?

A truly empty cell holds nothing. An empty string is text of zero length, which a formula such as =IF(A1>0,A1,"") or a pasted-as-values import can leave behind. A space is a one-character text. All three look the same on screen.

Cell holdsISBLANKLENC2+5SUM(C2)
Nothing at allTRUE050
Empty stringFALSE0#VALUE!0
One spaceFALSE1#VALUE!0
The number 0FALSE150

That is why a net formula like =E2-D2 works on most rows and then suddenly fails on one. The row that fails has an invisible entry, not a true blank.

Where do the impostors come from?

  • Cells that were formulas returning "" and were then copied and pasted as values.
  • Text copied from a PDF or a web page, where empty-looking cells carry a space.
  • Imports where a column was padded to a fixed width.
  • A cell cleared by typing a space rather than pressing Delete.

How do you find them?

Try these in cells outside the data:

  • =SUMPRODUCT(--(LEN(D2:D500)>0),--NOT(ISNUMBER(D2:D500))) counts non-empty cells that are not numbers. It should be 0 in a numeric amount column.
  • =COUNTBLANK(D2:D500) counts truly empty cells and also cells holding "". ISBLANK does not count the latter, so compare =SUMPRODUCT(--ISBLANK(D2:D500)) with COUNTBLANK. A difference means empty strings are present.
  • Add a filter, open the drop-down on the column, and look for a blank entry listed as a separate item or an entry with only whitespace.

What is the one-step clean-up?

Once amounts in the column are real numbers, remove everything that is not one:

  1. Make a copy of the sheet.
  2. Select only the data cells of the debit column, not the header.
  3. Press F5, Special, Constants, untick Numbers so only Text is ticked, and press OK.
  4. Press Delete. Every impostor goes at once.
  5. Repeat for the credit column, then run the check formula again.

Is there a formula alternative?

  • =IF(LEN(TRIM(D2))=0,0,D2) turns an empty string or space into 0 and leaves numbers untouched. It still fails on a text amount, which is useful, because the error points at the real problem.
  • =N(D2) returns 0 for empty strings, spaces and any text. It is the shortest route but also turns a text amount into 0, losing the value silently.
  • =SUM(D2) never errors and counts text as 0, so a total built on it is safe from #VALUE! but can be too low.

Choose the formula that complains about genuine oddities and forgives the invisible ones. The first of the three does that best.

Why do totals go wrong without any error?

SUM and SUMIFS skip text silently. If a few debit cells hold amounts stored as text, the total comes out lower with no warning at all. COUNT and COUNTA disagreeing is the clue. Say the debit column has 140 filled cells, of which 138 are numbers: the totals miss two transactions, whatever their size.

Worked example with hypothetical figures

Say D2:D6 holds 2,350, an empty string, 18,000, a space and 500. =SUM(D2:D6) gives 20,850 and looks fine. =D2+D3 gives #VALUE!, and a net column built from it shows errors on two rows. After running the clean-up, those two cells are empty. Now =D2+D3 returns 2,350 and the net column works everywhere.

Quick checklist

  • Run the non-numeric count formula on each amount column.
  • Clean on a copy with Go To Special, Constants, Text.
  • Re-check COUNT against COUNTA.
  • Leave truly empty cells empty rather than typing spaces.

SpendixAI takes statements as PDF, CSV or Excel and shows the rows for review before saving, so odd-looking cells are visible before they reach your records.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports