Excel Pivot Table Not Working? Here Are the Most Common Fixes

Pivot tables are one of Excel’s most powerful features — until they start behaving strangely. You refresh the data and the numbers are wrong. A field you deleted is still showing up in the dropdown. You try to add a new pivot table and get a cryptic overlap error.

Most pivot table problems have a small set of root causes, and once you know what to look for, they’re quick to fix.

---

Problem 1: The Numbers Are Wrong — It’s Counting Instead of Summing

You drag a number field into the Values area and expect a sum. Instead, you get a count. Or you get a sum, but it’s clearly wrong.

Why it happens: Excel defaults to COUNT (instead of SUM) whenever it detects even one non-numeric value in that column — including blank cells, a stray space, or a cell formatted as text.

The fix:

• Right-click any value in the Values area → Summarize Values By → Sum
• Then find and fix the source data: filter the column, look for blanks or text entries, and clean them up
• A quick way to check: select the whole column in your source data and look at the status bar at the bottom of Excel. If it shows “Count” but not “Sum,” you have non-numeric cells in there.

---

Problem 2: New Data Isn’t Showing Up After Refresh

You add rows to your source data, click Refresh, and the new rows don’t appear in the pivot table.

Why it happens: Your pivot table’s data source is a fixed range (like A1:D500). New rows you added below row 500 aren’t included.

The fix — the proper way: Convert your source data to an Excel Table first. Click anywhere in your data, press Ctrl + T, and confirm. Now when you add rows, the table expands automatically. Any pivot table built on a Table will pick up the new rows on the next refresh.

If you don’t want to use a Table, you can manually update the data source: click anywhere in the pivot table → PivotTable Analyze tab → Change Data Source and update the range.

---

Problem 3: Old Items Still Appear in Filters After Deleting Them

You deleted some values from your source data. After refreshing, those deleted values still show up in the pivot table’s filter dropdowns — they just show as blank or “(blank)“.

Why it happens: Excel caches the old field values for performance. It doesn’t automatically clean them out when you refresh.

The fix:

1. Right-click anywhere in the pivot table → PivotTable Options
2. Go to the Data tab
3. Under “Retain items deleted from the data source,” change the dropdown to None
4. Click OK, then Refresh

That clears the cache and removes the ghost values.

---

Problem 4: “A PivotTable report cannot overlap another PivotTable report”

You refresh and get this error even though your pivot tables look like they’re in different areas.

Why it happens: When a pivot table expands (because you added more data), it tries to push into rows that another pivot table occupies. Excel refuses rather than overwrite the other table.

The fix: Add several empty rows or columns between your pivot tables as a buffer. As a rule of thumb, leave at least 10–15 empty rows below any pivot table that might grow, especially if you’re working with dynamic data.

---

Problem 5: Dates Are Grouping Incorrectly (or Not at All)

You have a date column and want to group by month or quarter, but the grouping option is greyed out, or the dates group into weird buckets.

Why it happens: At least one cell in your date column isn’t actually formatted as a date — it might be stored as text, which is a common side effect of importing data from other systems.

The fix:

• Select your date column, go to Data → Text to Columns, click Next twice, choose “Date” format → Finish. This forces Excel to recognise all values as real dates.
• Alternatively, add a helper column with =DATEVALUE(A2) to convert text dates to real ones, then rebuild your pivot table using that column.

---

Problem 6: Column Widths Reset Every Time You Refresh

You spend time formatting your pivot table — setting column widths, number formats, etc. — and then the next refresh undoes it all.

The fix:

1. Right-click in the pivot table → PivotTable Options
2. On the Layout & Format tab, uncheck “Autofit column widths on update”
3. Click OK

Your formatting will now survive refreshes.

---

Keeping Your Source Data Healthy

Most pivot table problems come from messy source data — mixed types, blank rows, inconsistent formatting. A few habits that prevent 80% of issues:

• Keep your source data in an Excel Table (Ctrl + T)
• Never have blank rows or columns within your data range
• Use consistent data types in each column — all numbers, all dates, or all text, never mixed
• Avoid merged cells anywhere near your data

The cleaner your source data, the more reliably your pivot tables will behave.

---

If you find yourself spending a lot of time cleaning up pivot tables just to share summary numbers with others, there’s a better path. Sheetglow reads your Excel or Google Sheets data and generates a polished, shareable dashboard automatically — no pivot table wrestling required. The people who need to see the results just get a clean view, without touching your spreadsheet at all.