Spreadsheets

Pivot table not showing new expense rows: how to fix it

Pivot table not showing new expense rows? Refresh it, extend the data source, turn the data into a Table so ranges grow, and use whole columns in Sheets.

Editorial illustration for Pivot table not showing new expense rows: how to fix it

An Excel pivot table does not pick up new rows on its own. Right-click it and choose Refresh, or press Alt+F5. If the new rows still do not appear, the pivot's source range stops above them: use PivotTable Analyze, Change Data Source and extend it, or better, turn the data into a Table so the range grows automatically. Google Sheets pivots update by themselves but only inside their range.

Why does the pivot ignore my new rows?

A pivot table does not read your sheet live. It takes a snapshot of the source range into a hidden store, and only a refresh updates that snapshot. There are two separate problems that look the same: the snapshot is stale, or the range never included the new rows. Fix them in that order.

How do I refresh it?

  • Right-click inside the pivot and choose Refresh.
  • Or click inside it and press Alt+F5.
  • Or choose Data, Refresh All, which also updates every other pivot and query in the workbook.
  • To do it automatically, right-click the pivot, open PivotTable Options, Data, and tick Refresh data when opening the file.

Say your pivot showed May total ₹28,900 and you pasted twelve June rows underneath the data. After refreshing, June appears as a new column only if those rows sit inside the source range. If June is still missing, move to the next step.

How do I extend the data source?

  1. Click inside the pivot table and open the PivotTable Analyze tab.
  2. Choose Change Data Source.
  3. Check the range in the box. If it says Sheet1!$A$1:$E$300 and you now have 312 rows, the last twelve are outside it.
  4. Edit the range to end at the last row, or select the new range with the mouse, and press OK.
  5. Refresh again and check the grand total against =SUM of your Amount column.

What is the lasting fix?

Click anywhere in the data and press Ctrl+T to make it a Table, then build the pivot from it, or change the pivot's data source to the Table name. A Table expands each time you type or paste directly beneath it, and the pivot sees the larger range after a refresh. You never edit the range again. For an existing pivot, Change Data Source with the Table name, such as Expenses, does the switch in one step.

SymptomLikely causeFix
New month missingNot refreshedAlt+F5 or Refresh All
Still missing after refreshRange ends above new rowsChange Data Source or use a Table
New rows appear but add as zeroAmounts stored as textConvert text to numbers
Deleted category still listedOld items retainedSet items to retain per field to None, refresh
New category not visibleA manual filter excludes itClear or reapply the filter

What about Google Sheets?

A Google Sheets pivot recalculates whenever its source data changes, so there is no Refresh button. The usual trap is the range. If the Data range reads A1:E200, rows below 200 are ignored. Open the pivot, click the range box, and change it to a whole-column range such as A:E, so every row you add is included. The blank rows that come with whole-column ranges can show as a (blank) item; untick it in the Filters section.

What else causes stale results?

  • Date text. New rows pasted from a bank CSV may bring dates as text and will not group with older months.
  • Header changes. If you rename a column in the data, the pivot field breaks and shows an error until you rebuild it.
  • Another copy of the file. Check you are editing the workbook that holds the pivot, not a downloaded copy.
  • Manual calculation mode. If Formulas, Calculation Options is on Manual, press F9.

How do I avoid the problem altogether?

Keep a single data Table, use consistent column names, and press Refresh All as a habit after every paste. When adding rows by hand becomes the chore, SpendixAI reads your statements instead: upload a PDF, CSV or Excel file, review the rows, and the reports are current once the import finishes, with no source range to maintain.

  • Data in a Table, pivots built from the Table.
  • Refresh data when opening the file switched on.
  • Grand total compared with the data total.
  • In Sheets, a whole-column range.
SPENDIXAI JOURNAL

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

Explore your reports