Tips for PivotTables in Excel

I keep advising people to use SQL Queries instead of OLAP Cubes, and I feel like I need to support that advice with a bit of a “primer” to help you on your way.  I’ve tried a lot of combinations and a lot of methods, and what I’m up to is working well for me, so why not you?

Tip 1:  Use PowerPivot when Designing Queries

I don’t really love PowerPivot (understatement) because of the way it builds the PivotTable into your worksheet, but I do love using it to build queries, because you can validate your SQL statement as you go.

Tip 2: Put Column Name Aliases in Query

It’s tempting to just rename your column names inside the PivotTable once you pull in the data, but if you end up needing to rebuild the table or use the same query somewhere else, you’ll have to look at each column individually to determine what everything is called. When you have a lot of reports and a lot of columns, that can be annoying.

Tip 3: When formatting Won’t Stick

I’ve noticed that sometimes the formatting from the PivotTable style you have selected will stop displaying correctly.  The last entry in your table will not have the right shading on the row, or the text will not be bold, or something like that.  Often you can clear it by selecting the whole field, then selecting “Clear Formats” from the “Home” tab.   This doesn’t work sometimes, and the only other way I’ve found to fix it is to clear the entire pivot table (Pivot table options => Clear => Clear All) which is annoying because you lose groupings, but at least it stops looking ugly.

Tip 4:  Do Not Use Alignment Buttons on Pivot Tables!

I have discovered that if you format the alignment of columns and rows the way you normally would, it will tragically destroy the layout of the Pivot Table.  Specifically, the PivotTable indent settings (PivotTable Options => Layout =>When in compact form indent row labels: ) will be ignored and your data will look ugly.  Once again, the only way to fix this that I’ve found is to clear the whole table, just like in Tip 3.

Tip 5: Use the Right Report Layout for the Right Data

The more data you have, the harder it is for people to look at it.  Sometimes simple is better, sometimes collapsing confusing data makes it easier to find what you need.  Sometimes the whole report needs to be tabulated and the field set to “repeat data labels”.  Play around with the different layouts (Compact, Outline, Tabulated) and the different combinations until you get a layout that makes sense.  You can have two rows tabulated and the next one in compact form, etc.

Tip 6: Table Layouts and Charts…

This kind of goes with Tip 5, but that one was getting a bit long.  The important thing to know is that sometimes the layout you select so that your PivotChart looks nice will make absolutely no sense for the PivotTable.  This especially true when you’re dealing with time-phased data.  Sometimes information that needs both a chart and a table has to be on separate sheets and with a hidden ugly table just to support the beautiful chart.

Tip 7: Right Clicky FTW!

Right clicking is really helpful when you’re tooling around a PivotTable.  You can hide and show data, you can remove whole columns, move rows, change field settings and PivotTable Options all from the right-click menu.

Ok, that’s it for now.  I am sure I will come up with more as time goes on.  I’m also happy to answer questions if any of my speedy little posts aren’t specific enough or if anything is missing!

Advertisements

2 thoughts on “Tips for PivotTables in Excel

  1. Hi For Tip 4, Select the whole field, then select “Clear Formats” from the “Home” tab. Saves clearing the pivot table.

    • Thanks for the comment! I should probably update the tip to mention that step, though it doesn’t always work (in my experience) and my solution was meant for the times where even that won’t clear it. I should have been more specific, good catch!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s