Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog

Pivot Tables - overview

I always wondered what “Pivot Tables” were, until I finally took the time to figure it out.  Again, not one of the most intuitive aspects of Excel, but very, very useful once you get addicted used to them. I’ve probably taught Pivot Tables more than any other single aspect of Excel, and everyone who uses them calls them “the coolest thing about Excel”.  I must say, PowerPivot is my most-current “coolest thing ever” for Excel, but Pivot Tables are still really cool. A Pivot Table is an “abstraction” layer on top of a data set.  An abstraction layer is a set of instructions that hides its operations - simply put, it’s the “secret sauce” that works to aggregate/summarize data into more-easily consumable chunks.  It’s easiest to understand in an example, as we’ll walk through below. To use a Pivot Table, you need a data set.  The whole value behind a Pivot Table is that it allows you to summarize data and present it in a pretty attractive way.  Any data set will do, but it should ideally be, say, at least 100 cells of data - enough that you can’t easily get your hands around it.  For this presentation, we’ll use a 5-year data set of daily revenues over 11 stores; 2200 rows long and 15 columns wide.  This is light work for a Pivot table; with current processors, they can easily handle hundreds of thousands of rows of data (millions if you use PowerPivot). The table of data is pretty meaningless on its own.  Does any cell present important information in relation to the cells around it? Hard to know, because we don’t have enough context to understand it.  By summarizing large data sets, Pivot tables served to provide that context, so you can understand the stories your data is trying to tell. First off, we’ll have to generate a Pivot Table from our data set.  Start by selecting the full data set (go to cell A4, hold CTRL and SHIFT, click the down arrow and then the right arrow).  Now, on the INSERT tab, in the Tables Group, select “PivotTable”.  You will see the following dialog box: The data set is already set up as a Table, so Excel has referred to that Table, rather than the range I had already highlighted.  This is another great thing about Tables - if you use one as a base for a Pivot table, the Pivot tables source range will expand as the Table expands.  If you just choose a range, you need to increase that range every time your data set grows - that gets tedious pretty quickly. In the second half of the popup window, we are asked where we want to put the Pivot Table.  I usually just leave the default “New Worksheet” selected; always easiest to start with a clean slate.  Note that the Worksheet is within your current Workbook; it is NOT a new Workbook. At the bottom, you have option to add the data to a “the Data Model”.  This creates a relationship between multiple tables, which Excel enables through the use of it’s new PowerPivot tool.  That is outside the scope of this article, and will require its own series of articles.  For now, simply ignore this.    We have our data set, so click “OK”. The Pivot Table will display its summary values in the Area starting at A3, on the left.  The setup of the Pivot Table happens in the window on the right (the “field list”).  Notice that all of the columns from the data set are now “fields” in the field list. Finally, notice the area in the bottom right.  This represents the 4 portions of the Pivot Table: 1. Rows 2. Columns 3. Values 4. Filters We’ll review these in detail, below.

1  Rows

Rows are the cornerstone of a Pivot table.  Your Row will present the records you are showing.  For example, if we were to move “Year” into the Rows section of the Pivot table, we would see this: The “Year” field is now in the “Rows”, and we can see the years in the left-most column of the Pivot Table, in numerical order.  Pivot tables always sort your data, usually in alphabetical or numerical order.  You can also change the sort to use custom sorts <add link>. The Pivot table has automatically created a “Grand Total” row to sum up our “Years”.  This summation will only occur once we have Values to sum.

2  Columns

Let’s put the “Day” field into the Columns section: You can see now that we are developing a “CrossTab” report - a report that sums down and across. I’ll now drop the “Vancouver” field into the Values section, so we can finally have some values to summarize: Could do with some formatting, I’ll agree, but it sure is a quick way to get summary amounts into a table.  Compare this with what we went through to create a similar table when we were looking at SUMPRODUCT.  Always remember that, with Excel, there are many ways to do the same thing, and it’s your final goal which will dictate the approach you use. We’ll look at formatting under Pivot table Properties, along with other important features to make your Pivot tables work for you.
1 2 3 1 2 3