Anatomy of a PivotTable
You have a blank slate, a clean PivotTable. What’s great about PivotTable is that you can make pretty much any attribute into any one of features. It has Filters, Columns, Rows, and Values. Drop your attribute from the top of the field box into any four of these fields.
Filters:
This will filter your data by any attribute you desire. We are going to use the filter to select only Quarter 1 data.
Rows:
These are data attributes that you want listed sideways on the report. Select Region and District.
Columns:
These are the attributes to list at the top. PivotTables will only fit 256 fields onto the column header. Believe it or not, many of your real-life projects will exceed this count. For this reason, I usually do not use columns as a field for attributes.
Values:
This is the meat of your data — the actual values. Drop in both sales and profit.
You should have something like this.
Looks pretty, right? One of my favorite things about PivotTable is that you don’t need to do any formatting. But are the numbers right? You know that the sales and profit do not both equal 10. What’s going on? Check the column label. It is counting your values, not adding them.
For some reason, Excel defaults to counting values in a PivotTable. I rarely if ever use this feature. Usually, analysts work with sums of data. Let’s see how to switch the value into a sum.
Switch over the sales value to sum and do the same for profit. Double-click on your column label where it says “Count of Sales.” The below dialogue box comes up.
There are many ways to summarize your values in a PivotTable — average, maximum, etc. We are going to look at sum. Click on sum.
While we’re here, let’s format our values too. At the lower-left you will see “Number Format.” Click on that, then move to format these values as currency with no decimal places (shown below).
Now your sales are summed, not counted. Do the same for profits.
This looks more useful, right?
You’ve got the basics — congratulations!
Leave a Reply