As much as I love Excel pivot tables, there are a few annoying problems that you’ll run into, while working with them. I made a list of my top 5 annoyances, and you might have other problems to add to the list.
1: Old Items in the Drop Downs
If you remove or change data in the pivot table’s source, then refresh the pivot table, those old items might still appear in the drop down lists. For example, after the Central region is merged with the East region, it still appears in the Region heading drop down, even though all the sales records were changed.
To fix this, you can change a pivot table setting, so no old data is stored in the pivot cache.
2: GetPivotData Formula
If you try to link to a value cell in a pivot table, a GetPivotData formula appears, instead of a simple link. This is a very useful function, but you might prefer to have a simple link most of the time.
To fix this, you can change an Excel setting, so the GetPivotData formula doesn’t appear automatically.
3: Change Values from Count to Sum
When you add fields to a pivot table’s Values area, they’re usually calculated as a Sum. Sometimes though, the fields are calculated as a Count, which might not be what you want.
To fix this, you manually change the field to Sum, after it’s in the pivot table. Or, fix your data, so it doesn’t contain blanks or text data in that field.
4: New Data Doesn’t Appear
When you add new records to the pivot table’s source data, then refresh the pivot table, you expect to see all the new data. Unfortunately, that doesn’t always happen.
To fix this, make sure that the source data is in a dynamic range, that will grow and shrink automatically, when data is added or removed.
5: All Items Not Listed
If you put two or more fields in the Row area, all the items might not appear in each section. For example, if a product wasn’t sold in a specific city, that city doesn’t appear under the product heading. To keep the layout consistent, you might want to see all the items, in each section.
To fix this, you can change a pivot table setting, so the items with no data appear, along with the other items.
Pivot Table Frequently Asked Questions
If you didn’t see your top pivot table annoyance in this list, there are other common problems, and their solutions (in most cases), on the Pivot Table FAQ page on my Contextures website.
And for a list of all the pivot table tutorials and videos on my site, take a look at the Pivot Table Index page.
Watch the Slide Show
Here is a slide show that I made, summarizing the 5 Annoying Pivot Table Problems. In the last slide, there is a link to my PivotPower Premium Add-in, which helps you solve most of these problems.
Or watch on the SlideShare website: 5 Annoying Pivot Table Problems
__________________
Thank you for the list, Debra
There are some other shortcomings of Pivot Table, by my opinion:
1) There is no autorefresh of pivot table (sometimes I have a source sheet and pivot table on the second sheet. Half of users forget to refresh pivot after working with source sheet. Only macro solves it by catching Worksheet_Activate event)
2) Once collapsed entire fields get some expanded records if the records are new and are added after refresh. But it would be great if the field will remain collapsed all the time (also solved with macro)
3) If collapsed table is expanded and then re-collapsed worksheet changes last cell value (UsedRange) and make printout quite bigger (solved with macro as well)
4) Overlapping is a ‘risky sphere’ in Pivot tables. Sometimes it is difficult to design report with pivot table because the latter might grow downwards or rightwards. The only ‘stable are’ is a row between report filter fields and the table itself (if report filter fields per column is set)
5) Autonumbering is still unclear for most ordinary users (the only way is adding a special column to the source and having it on the table). That would be great if autonumber would be built-in to pivot table design
6) Unlinking of the filtered table from its source as formatted values or as new shortened pivot table. On practice I need it quite often and have a special macro for it
That is my ‘wish-list’, though I consider Excel Pivot Table as a greatest tool and use it a lot
Thanks Ivan — that’s a great list!
Good list, Deb. My personal annoyance is the inability to summarize by geometric mean (GeoMean), at least as far as I can tell. Throw us finance people a bone! 🙂 If you can summarize by arithmetic mean, the geometric should be easy to add. Seems to me that they should allow us to write our own summary functions. That would be really nice.
Thanks Tim, and they’ve added new features over the past couple of versions, so maybe these will be in a future version.
so true! I have one more: the “Autofit column widths on update” setting.
Thanks MF! That Autofit setting is a bit finicky.
4 Dynamic Range:
Debra, as your link for this indicates, using a named table is great. I’ve standardized all my data into named tables, and never have to update a pivot source range. Ever!
@AlexJ, smart move! That should save you several hours of work every month, and give you more reliable reports.
Compact layout as the default with no way to change the default. Give me back tabular layout!
Oh, I forgot about that one! A way to change the default would be nice too. That’s why I had to create my own pivot table add-in. 😉
I have my pivot table created from 2 named tables, relationship created. Initial data is reflected. Trying to add new rows to each table but the refresh all is not reflecting the changes on the pivot table… Any suggestions would be very welcome.
Every month I add following formulas to pivot table
VAL GRTH (MAT 14) =’MAT NOV”14’/’MAT NOV”13’*100-100
VAL GRTH (MAT 15) =’MAT NOV”15’/’MAT NOV”14’*100-100
VAL GRTH (MAT 16) =’MAT NOV”16’/’MAT NOV”15’*100-100
is there any way in which this formula will get change automatically like for dec month formula will automatically get changed to
VAL GRTH (MAT 14) =’MAT DEC”14’/’MAT DEC”13’*100-100
VAL GRTH (MAT 15) =’MAT DEC”15’/’MAT DEC”14’*100-100
VAL GRTH (MAT 16) =’MAT DEC”16’/’MAT DEC”15’*100-100
“Old items in the dropdown” also includes old items showing up in Slicers on the PivotTable report. That problem vexed me for several months. Thanks for pointing out the solution.