Devon asked me for pivot tables in Excel – and what Devon wants, she will only get if it is reasonable, inexpensive, and appropriate. Pivot tables are life changing. Between using them to organize and clean up odd datasets to creating complex Excel dashboards, pivoting is an essential skill for any Excel user.
This post is picture heavy again. Check out the TLDR near the bottom if you are confident.
First things first, I downloaded the example spreadsheet from Contextures – it turns out making up data is harder than I expected.
It is best to start with data that is fairly clean. Here is what I am starting with:
Start by clicking on the first cell in your table.
Now go to INSERT and select the PivotTable icon.
This dialog box will appear. Click OK. Alternatively, if your table wasn’t automatically selected by the PivotTable wizard, type in the Sheet#!ColumnStart$#:ColumnEnd$# to grab your data.
You’ll get the following blank Pivot on a new sheet:
PivotTables have two major tabs: Analyze and Design.
You can click or drag Fields into sections of your PivotTable. For example, I dragged the Region field to the Row area and the PivotTable automatically summarized all the Regions in my data table (Central, East, or West).
You can drag fields to Columns as well (like I did with sales representatives). I now have a crosstab forming of my Regions and Reps. To make this matrix useful…
…drag a field into the Values section.
You can change how we summarize these Value fields by clicking the down arrow and selecting “Value Field Settings”. We can now choose between looking at the sum of Units sold, count of Units sold, average…max…you get the picture. I’m going to pretend my boss wants the average units sold for each rep by region.
You can change labels if you need to:
Or go into PivotTable Options and uncheck the “Show expand/collapse buttons” and “Display field captions and filter drop downs”. This this only useful if you don’t want anyone filtering your table.
You can also tell PivotTable to fill in empty cells with zeros!
Say we are satisfied with our table but want to pretty it up. Head on over to DESIGN and select a PivotTable Style. I have saved templates and colors so this is what mine looks like:
Isn’t that nice? I’m more of a Chart/Graph person though. I (usually, not always) dislike sending a table to anyone. So, let’s use the PivotChart function and turn out PivotTable into a sweet graph.
Again, you’ll get this blank chart and table on a new sheet.
After pulling in my desired fields and setting my values to whatever I want to look at, PivotChart gives me this. Obviously there needs to be formatting done.
There, that’s better! Editing/formatting a PivotChart is the same as any other chart in Excel.
But now I kind of want to give someone else the option to narrow now my chart as they want. Usually, Excel charts don’t have this option, but a Pivotchart does! While the PivotChart is selected, head on over to the ANALYZE tab and hit “Insert Slicer”. Select the fields from your PivotTable you want and click OK.
This is what my chart looks like with slicers for both Region and Rep:
Whenever you click on a slicer, you edit your PivotTable and PivotChart to match. By selecting Central for my Region and Kivell as my Rep, I’m showing Kivell’s units sold by product item in the Central US.
Fun fact time: you can move your PivotTables and Charts using the Move Chart function. If I’m creating a dashboard in Excel, I move my final Charts and Slicers over to new sheet (usually titled DASH for clarity).
TLDR: Select the first cell in your table and insert a PivotTable. Format and modify fields as needed. Happy Excel’ing.