Excel Pivot Tables: Two Fixes for Reports You Rebuild

There is a moment I see in almost every Excel class I teach. Someone pulls a fresh export out of their accounting or CRM system, drops it into last month’s workbook, and then spends the next forty minutes rebuilding a summary they already built four weeks ago. New rows came in, the totals stopped matching, so the whole thing gets redone by hand. Excel pivot tables are supposed to prevent exactly that, and most of the time they don’t.

It is almost never because pivot tables are broken. It is two small setup choices that nobody ever mentions in the five-minute demo people learn from. Get those two right and next month’s version of that report is a right-click, not an afternoon.

Here is what goes wrong, and what to do instead.

Fix One: Build Excel Pivot Tables on a Table, Not a Range

This is the single biggest one. When you click Insert > PivotTable while sitting on a plain block of data, Excel records the source as something like Sheet1!$A$1:$G$4312. That is a fixed address. Next month you paste in 300 more rows, hit Refresh, and the pivot cheerfully ignores every one of them, because row 4313 was never part of the deal.

Do this instead, before you insert anything:

• Click any single cell inside your data.
• Press Ctrl+T. Excel proposes the range — confirm that My table has headers is checked, and click OK.
• Go to the Table Design tab and, in the Table Name box on the far left, type something you will recognize, like tblSales. Press Enter.
• Now click Insert > PivotTable. The Table/Range box will say tblSales instead of a cell address.

From here on, adding data is the whole job. Paste next month’s rows directly underneath the last row of the table and the table stretches to include them automatically — you will see the border move. Then right-click anywhere in the pivot and choose Refresh, or press Alt+F5. Your numbers are current.

The gotcha: the table only auto-expands if the new rows are immediately below the last one. Leave a single blank row as a separator and the table stops short, the pivot misses the new data, and nothing warns you. Also, if you prefer to overwrite the old data with a full fresh export, use Paste Special > Values — a straight paste can drop the table formatting and quietly convert your table back into an ordinary range.

Fix Two: Stop Typing Percentages — Let Show Values As Do It

The other thing I watch people do is build a clean pivot of sales by region, then open a blank column beside it and start typing =B5/$B$12 to work out each region’s share. Those formulas break the moment the pivot changes shape, which is exactly what pivots are built to do.

Excel will calculate it for you, and it will keep calculating it after every refresh:

• Drag the same field into the Values area a second time. You will now have two identical columns, probably called Sum of Amount and Sum of Amount2.
• Right-click any number in that second column and choose Show Values As > % of Grand Total.
• Click the column’s header cell, type % of Total, and press Enter to rename it.

The same menu has options that are worth five minutes of playing with. % of Column Total gives you each row’s share within its column. % of Parent Row Total is the one to use when you have regions with cities nested underneath and you want each city measured against its own region rather than the company. Difference From > (previous) turns a column of monthly figures into month-over-month change without a single formula.

The gotcha: set the number format from Value Field Settings > Number Format, not by selecting the cells and formatting them on the Home tab. Cell-level formatting applied to a pivot gets thrown away as soon as the pivot is refreshed or rearranged, and you will spend months wondering why your decimals keep resetting. While you are in Value Field Settings, the Custom Name box is another place to fix that ugly “Sum of Amount2” label for good.

One bonus that pairs with both fixes: right-click any date field in the pivot and choose Group, then tick Months and Years together. Ticking Months alone will pile January 2025 and January 2026 into the same bucket, which is a wrong answer that looks completely reasonable on screen.

Plenty more on the calendar

If you want to work through this live, I am teaching Excel Pivot Tables on Thursday, September 17. We go from building your first pivot through grouping, slicers, calculated fields, and the layout settings that make a pivot look like a real report instead of a draft — with time for your own questions along the way.

The full current schedule lives on the front page of PCWebinars.com, and new dates go up there as they are confirmed. Register for this one, or have a look through the others and find the session that matches what is actually slowing you down at work.

About your trainer

I’m Tom Fragale. I have been a computer trainer and consultant for more than 30 years, and I have taught well over 30,000 people how to get more out of the software they already own — Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside the teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

You can find the full schedule, the recorded courses, and everything else I offer at PCWebinars.com.