Viz in Excel: Pivot your table, Pivot your life

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:

pivots1

Start by clicking on the first cell in your table.

pivots2

Now go to INSERT and select the PivotTable icon.

pivots3

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.

pivot4

You’ll get the following blank Pivot on a new sheet:pivot5

PivotTables have two major tabs: Analyze and Design. pivot6pivot7

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).

pivot8

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…

pivot9

…drag a field into the Values section.

pivot10

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.

pivot11

You can change labels if you need to:

pivot12

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.

pivot16

You can also tell PivotTable to fill in empty cells with zeros!

pivot15

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:

pivot13

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.

pivot17

Again, you’ll get this blank chart and table on a new sheet.

pivot18

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.

pivot19

There, that’s better! Editing/formatting a PivotChart is the same as any other chart in Excel.

pivot20

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.

pivot21

This is what my chart looks like with slicers for both Region and Rep:pivot22

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.

pivot23

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).

pivot24

TLDR: Select the first cell in your table and insert a PivotTable. Format and modify fields as needed. Happy Excel’ing. 

Rad Resources:

Youtube Video on Excel Dashboarding by Jon from Excel Campus

SmartSheet’s Post on Excel Dashboards

 

Advertisements

One thought on “Viz in Excel: Pivot your table, Pivot your life

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