Getting started

Fixing spreadsheet formula errors in a monthly expense report

Why a monthly expense report in Excel or Google Sheets shows #REF!, #VALUE! or wrong totals, with a dollar example and a fix for each error.

Editorial illustration for Fixing spreadsheet formula errors in a monthly expense report

Most formula errors in a monthly expense report come from five causes: amounts or dates stored as text, ranges that do not line up, deleted rows or columns, a divide by zero, and a misspelt function or category. Each error code points at one of them, so you can fix the sheet in a few minutes once you know what to look for. This guide uses dollars, but nothing here depends on the currency.

What each error code is telling you

Error shownUsual cause in an expense sheetFirst thing to check
#REF!A column or row that a formula pointed at was deletedClick the cell and look for #REF! inside the formula text
#VALUE!A formula is doing arithmetic on text, such as an amount pasted as "$1,250.00" with a stray spaceIs the amount left-aligned? Numbers normally sit on the right
#DIV/0!Dividing by an empty or zero cell, common in a "percent of income" columnIs the income cell blank for that month?
#NAME?A function name is misspelt, or a text label is missing its quotation marksRetype the function name and watch for autocomplete
#N/AA lookup did not find its key, for example a merchant that is not in your category listCompare spelling and trailing spaces in both lists

Why does SUM or SUMIFS give a wrong total without any error?

The silent errors are worse than the loud ones. A total that looks plausible but is too low usually means some amounts are text. Pasted bank or card data often arrives that way. Select the amount column and look at the status bar: if it shows a Count but no Sum, the cells are text. Try =ISNUMBER(C2) in a spare column. FALSE confirms it.

To convert, use =VALUE(TRIM(C2)) in a helper column, copy the results, and paste back as values only. If a dollar sign or comma is the problem, =VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",","")) strips both. The other silent cause is a SUMIFS range that is one row shorter than another. Every range inside a SUMIFS must be the same size.

A worked example with made-up numbers

Say your sheet has dates in column A, category in B and amount in C, rows 2 to 200. Cell F1 holds the first day of the month and F2 holds the first day of next month. A clean monthly total for Groceries is:

  • =SUMIFS($C$2:$C$200, $B$2:$B$200, "Groceries", $A$2:$A$200, ">="&$F$1, $A$2:$A$200, "<"&$F$2)
  • Suppose the real groceries are $412.50, $96.20 and $58.30, which should total $567.00.
  • If the $96.20 row was pasted as text, the formula returns $470.80 with no warning. That is a $96.20 gap you would never notice without the ISNUMBER test.
  • If the date in that row is text such as "3/7/2026" typed with a different layout, it silently drops out of the month filter too. Same symptom, different cause.

How do I stop #DIV/0! in the percent-of-income column?

Wrap the division so an empty month shows a dash instead of an error: =IF($F$5=0,"-",C10/$F$5). IFERROR also works, as in =IFERROR(C10/$F$5,"-"), but it hides every error, including real mistakes. Prefer the IF version for division, and keep IFERROR for lookups where "not found" is an expected answer.

What should I do before I touch the formulas?

  1. Make a copy of the file so you can compare against the original.
  2. Turn on Show Formulas (Excel: Formulas tab; Google Sheets: View menu, Show formulas) and scan for ranges that look shorter than their neighbours.
  3. Use Find and Replace to remove leading or trailing spaces from category names, or add a helper column with =TRIM(B2).
  4. Convert the date and amount columns to real dates and numbers, then re-run the totals.
  5. Replace deleted-column #REF! formulas by retyping the reference, not by guessing.
  6. Lock the category list in a dropdown (Data validation) so typos cannot return.

When a sheet stops being the right tool

If you spend more time repairing the sheet than reading the report, the monthly import is the weak point. SpendixAI, an expense tracker built around India and rupees, is one option for people who mainly deal in INR and want uploaded statement rows reviewed before saving; it is not set up for US banks or dollar budgeting, so a sheet may still be the better fit for you. A tidy sheet with data validation and one reconciliation line is perfectly good for years.

Before you close the file each month, glance at the reconciliation line, save a dated copy, and note any category you had to correct. Repeated corrections tell you which column to validate next.

SPENDIXAI JOURNAL

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

Explore your reports