5 Annoying Pivot Table Problems

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.

5 Annoying Pivot Table Problems http://www.pivot-table.com/

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.

old items in drop downs http://www.pivot-table.com/

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.

getpivotdata formula http://www.pivot-table.com/

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.

change count to sum http://www.pivot-table.com/

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.

new data not in pivot table http://www.pivot-table.com/

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.

missing items in pivot table http://www.pivot-table.com/

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

__________________

This entry was posted in Excel Pivot Table. Bookmark the permalink.

13 Responses to 5 Annoying Pivot Table Problems

  1. Ivan M. says:

    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

  2. Tim Mayes says:

    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.

  3. MF says:

    so true! I have one more: the “Autofit column widths on update” setting.

  4. AlexJ says:

    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!

  5. Pingback: Excel Roundup 20140908 « Contextures Blog

  6. Bill Jelen says:

    Compact layout as the default with no way to change the default. Give me back tabular layout!

  7. Dennis Lyon says:

    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.

  8. SHUBH says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *