Lesson 11 – Pivot Tables

In the last lesson we talked about Excel Tables and Sub-Totals, both of which are amazing tools. As you’ve seen throughout this course, Excel is packed with useful tools and neither of those are any exception. It’s just too bad that Excel Tables and Sub-Totals won’t play nice with each other, so you have the option of using one or the other, but not both. The folks over at Microsoft have a solution called Pivot Tables that expose functionality that would normally only be available to those with database access. In fact, for years Pivot Tables have been considered a game changer from the Excel data analysis point of view; they let you take data and combine what you can do with Excel Tables with formatting, structured references and combine them with the power of Sub-Totals, as well as advanced Charting capabilities with Pivot Charts, which are fantastic because as you saw in our dynamic charting example using AutoFilter, as you manipulate data in a Pivot Table any Pivot Charts that you have associated with the Pivot Table will dynamically refresh. As an added bonus, in Excel 2010 Microsoft included something called Slicers, which are an absolutely fabulous way to slice and dice your data in ways that were previously impossible. Frankly, it’s amazing that Excel can do so many powerful things, and Pivot Tables are the pinnacle of what you can do natively in Excel without having to turn to a database programmer. If you haven’t heard of Pivot Tables outside of this course, there’s ample reason: they’re misunderstood and scare a lot of people, primarily because what they can do so easily just seems downright unnatural and difficult, but we’re going to use this lesson to show you how incredibly easy they are to use. Another added bonus to Pivot Tables is they allow you to create complex data scenarios without having to recreate a lot of formulas; imagine a complex data analysis scenario taking minutes instead of hours!

• • What can Pivot Tables do? Let’s take a quick look at the corporate sales information example we’ve used throughout the course. Whether we look at the raw data, or the Table based version, it’s still in a flat-file format that is so familiar in Excel. To refresh your memory, flat-file data is simply data that’s arranged in a row/column format with each row ideally representing an individual record. While flat-file data should generally have some type of unique identifier, like a record number, as our data example shows it’s not necessary.

 

 

Figure 429  

• • Remember what you can do with Sub-Totals? Well, if Sub-Totals just don’t do it for you, then Pivot Tables will! Pivot Tables are so powerful that once you get used to using them you’ll wonder how you ever did without them. One of the reasons that people may be afraid of Pivot Tables is the misperception that if something goes wrong you’ve just screwed up your data. Fortunately, nothing could be farther from the truth, because when you create a Pivot Table, Excel takes a snapshot of your data and recreates it, so your original data doesn’t get altered in any way whatsoever. Another nice thing about this is that you won’t find an Excel workbook blow up in size when you create multiple Pivot Tables, because the data isn’t being recreated.
• • There are a few ground rules for Pivot Tables, but they’re pretty much the same as the spreadsheet design rules you’ve learned so far:
• • Your data should have unique column headers
• • There should be no gaps in your data, so no missing rows or columns
• • Don’t mix data types, e.g. don’t mix currency values in the same column that you have dates
• • Creating the Pivot Table itself is remarkably easy. Moving forward with our Excel Table data, goto Insert, Pivot Table. You can work alongside this example in the Lesson 11 companion workbook, where you’ll find data and Pivot Table & Pivot Chart examples.

 

Figure 430  

• • The Pivot Table Wizard will automatically recognize the dynamic Excel Table range for you. You also have the choice of using external data which comes in handy if you’re creating an interactive workbook for users, but don’t necessarily want to include all of your data (remember, Pivot Tables only use a snapshot of your data, leaving it intact). Finally you can choose to place the Pivot Table in the same worksheet as your data or in a new worksheet. If you have multiple tables it’s generally easier to keep your Pivot Tables separate from your data (it also keeps your data and users separated). You don’t need to use an Excel Table for Pivot Table data though, as any Excel data range will work provided it meets the Pivot Table report criteria, you’ll just need to select the range if you decide to go that way.
• • As soon as the Pivot Table Wizard does its thing you’ll see a new worksheet with the PivotTable Tools Ribbon activated. On the left will be a Pivot Table dialog box, while the right holds the Pivot Table Field List.

 

Figure 431 Pivot table ribbon elements

• • This is where people tend to get confused, because Excel didn’t actually create a Pivot Table for you; it merely analyzed your data and loaded it and your headers into the Pivot Cache, waiting for you to pull everything together. But the great thing about this is that it’s a copy of your data, so you can do whatever you want without fear of harming your data.
• • The first thing to do is determine which fields you want in your Pivot Table, so you simply check each one. As you do you’ll see the Pivot Table dialog converted to a Pivot Table and the data elements that you check will be automatically added. As you’ll see, the initial result isn’t all that glamorous.

Figure 432  

• • Excel will automatically detect your numeric data and place it in the Values section, and it will also determine the Row labels for you. This is not always 100% accurate, because Excel is simply doing its best to interpret your data, but it won’t necessarily get it right. That’s one reason that the Pivot Table interface is so easy to manipulate and allows you to quickly move your data around until it displays the way that you want. The flip side to that is it adds to the misperception that Pivot Tables are hard to use because they do require a bit of work on your part; similar to what you need to do to get your Excel Charts the way that you want them. If you walk away from this lesson with just one thought about Pivot Tables you’ll be in good shape: even a poorly constructed Pivot Table can literally save you hours vs. trying to do the same thing manually, and they are a lot more flexible.
• • Once you’ve gotten to this point with your Pivot Table it’s time to dive into making it do what you really want. Most of this is going to involve a certain degree of experimentation and even the most seasoned Pivot Table users will go through a certain degree of adjustment before each Pivot Table does what they want. Can that be somewhat time consuming? Sure, but it’s a lot faster than the alternative.
• • Here’s the initial Pivot Table once you’ve made the selections displayed in the previous example.

 

Figure 433  

• • It’s about as plain and boring as it gets, but if you look closely you’ll see that Excel has manipulated the data in a way that would be very difficult for you to try to recreate manually. Take a look at how Region, Product, Date and Customer have been stacked in comparison to our Excel Table data. You might also notice that Excel has added an Auto-Filter to the Row Labels, which will let you narrow the field list or chose from various sorting options.

 

Figure 434  

• • Pivot Table Field List - The first thing you should do is give the Pivot Table Field List a workout and see how easy it is to add or remove elements. If you want to remove entire fields from the Table just uncheck them from the list, or you can drag them from pane to pane. You’ll also find that each Field List item has its own Auto-Filter criteria. Simply click on the label and you’ll see a drop-down indicator on the very right. Clicking it will expose your Auto-Filter options, which will let you narrow down your Pivot Table criteria and apply custom sorts.
• • Report Filter – This lets you narrow down your view by removing certain elements from the Pivot Table itself and moving it up above. In this example we’ll move the Region out of the Pivot Table and use it as a Report Filter (all you need to do is drag it from the Row Labels group up into the Report Filter group).

 

Figure 435  

• • The Regions have now been moved out of the Pivot Table and into their own Auto-Filter group. Depending on the level of detail that you want you may decide not to use Report Filters in certain circumstances, because it does create a cumulative effect. So if you were to use the Filter option to select all of the Regions, all of your Product data would be summarized, not broken out into their representative Regional groups like you saw in the first Pivot Table example. Again, it comes down to you and what you want your data to say. The wonderful thing about Pivot Tables is that they are so incredibly flexible you can change that back in a few seconds if it doesn’t give the detail that you’re expecting. Many times you’ll see this feature used to filter out date ranges. You can also use multiple Report Filters as you’ll see in this example, which has filtered the date range down to 2009 transactions only.

 

Figure 436  

 

Figure 437  

• • Column & Row Labels – These determine in which position your data will be presented, whether laterally or vertically. When you experiment with these a bit you’ll find that you can create some very interesting scenarios. Some might be useful, while others will be decidedly not so, and you’ll probably reverse them almost immediately. You’ll find that some data should just be presented in certain ways, but that is one of the Pivot Table’s learning curves, and there’s no escaping it. Fortunately, unlike a report you spend untold hours working on, a Pivot Table view can be changed almost effortlessly!

 

• • Here’s an example with our Column and Row labels swapped.

 

Figure 438  

• • As you can see it’s probably not the most useful view around, but the nice thing is that it’s entirely up to you. Just be aware that Excel will allow you to create Pivot Tables that make absolutely no sense to any one, maybe even you.
• • Values – As you saw, when you checked your numeric data in the Field list Excel automatically evaluated that data and placed it into the Values pane. By default Excel will apply a Sum to the Value field, but you can change the calculation type simply by clicking the drop-down arrow to the right of each category and choosing the Value Field Settings option. You also see the additional Move options, which are just another way to move elements. It’s probably faster to just drag and drop items instead of going through this menu.

 

Figure 439  

• • In this case we’re going to change the Quantity from a Sum to an Average, which you’ll immediately see reflected in your data.
• • As you look in the menu, you also see the additional Move options, which are just another way to move elements. It’s probably faster to just drag and drop items instead of going through this menu, unless you’re already here.

 

Figure 440  

• • Field Names - You can change the Field Name if you want, but you can’t use any of the names that are already in your Pivot Table 2data. So if you were to try to name the Quantity field “Quantity” Excel will tell you that name already exists. You can use an underscore or a leading/trailing space though.

 

Figure 441  

• • Value Field Settings – This brings us to the second tab of the Field Settings, which holds the Show Values As options. This is where you can use field settings to create different data views, like % of Grand Total or even drill down as far as Percent of a particular customer. You have multiple options:
• • % of Grand Total
• • % of Column Total
• • % of Row Total
• • % of
• • % of Parent Row Total
• • % of Parent Column Total
• • % of Parent Total
• • Difference From
• • % Difference From
• • Running Total In
• • % Running Total In
• • Rank Smallest to Largest
• • Rank Largest to Smallest
• • Index

 

Figure 442  

• • The Number Format option will launch the Format Cells dialog (Ctrl+1), and will allow you to format your Pivot Table data. It’s generally advisable to format your data and the rest of the report once you have the Pivot Table arranged the way that you want it, otherwise you might find yourself making multiple changes as you adjust the Pivot Table to display the data you want.
• • Pivot Table Ribbon Items – Options Group
• • Pivot Table – Here you can name your Pivot Table and move into some more detailed options. You would probably come here once you’ve gotten your Pivot Table set up the way you want with regards to how the data is summarized. Under options you’ll see three distinct choices. First is Options which we’ll discuss in detail next.

 

Figure 443  

• • Show Report Filter Pages – This creates individual worksheets for any filters that you might have in place. In the companion workbook you’ll see that two views were created, first for our report Regions, second for 2009.

 

Figure 444  

• • Generate GetPivotData - allows you to interact with your Pivot Table data via the GetPivotData function. If you exit the Pivot Table by clicking on any cell outside of the Pivot Table range, then use = and link back to any cell in the Pivot Table you’ll see a GetPivotData function created for you.

 

Figure 445  

• • In this case the result is $16,892, representing the intersection of the Sum of Profit column, where Product = ABC, and Customer = Bank of America. If you uncheck the Generate GetPivotData option you’ll get direct cell references, so the above example would revert to =E7. GetPivotData is an important function if you plan on referencing a Pivot Table and then manipulating it. If you had straight cell references you could end up with erroneous results depending on the changes you made in the Pivot Table, but your GetPivotData functions will continue to refer to the same information even if you change the Pivot Table. The only catch is that the referenced Pivot Table data needs to remain visible. If you’re just starting out with Pivot Tables then this one can be surprising, somewhat like Structured References in Excel Tables and odds are slim that you’ll use it, but it’s best that you’re at least familiar with it.
• • Options – This is an Excel 2003 dialog, but it’s still enormously handy. Within it you’ll find six individual tabs. Most of the options are relatively straightforward, and there are just too many to go into individually, so you should experiment with them yourself, but we’ll discuss the things that you probably want to leave alone. Remember, you can’t screw up your original data by doing anything when you manipulate a Pivot Table!
• • Layout & Format – Definitely play with the Layout and Format options, but you shouldn’t touch the Autofit column widths on update or Preserve cell formatting on update check boxes. If you do uncheck those you’ll find yourself reformatting your Pivot Table when you’re done refreshing it.

 

• • Totals & Filters – This lets you detail how you want to display or edit Totals and/or any filters that you might have applied. If you’ve taken advantage of Custom Lists from earlier on in the course then don’t turn the Sorting Custom Lists options off.

 

Figure 446  

• • Display – The primary option here is the Classic Pivot Table view option, which reverts back to some of the detail available in earlier Excel versions, in which you dragged data points on to the physical Pivot Table itself instead of in the Field List. You can also choose to sort the Field List in alphabetical order.
• • Printing – This is straightforward and simply gives you the options of displaying the Grouping buttons and Labels when you print your Pivot Table.

 

Figure 447  

• • Data – You have several options here that are important especially if you plan on distributing your Pivot Tables.

 

Figure 448  

• • Save Source data with file – If you want your users to be able to manipulate the Pivot Table, then you’ll want to leave this option checked. Otherwise you risk them not having a data connection and the Pivot Table can fail under certain circumstances. If you only want them to have a snapshot or if you have an unusually large dataset that can be challenging to distribute then uncheck it.
• • Enable show details – This allows you to drill down into the data that supports the Pivot Table. If you double click on a primary field in the table itself you’ll get a dialog that asks which field list item you would like to explore deeper.
• • Drill-Down Capability - If you double-click on a Pivot Table data item Excel will automatically create a new Auto-Filtered worksheet with that just item’s underlying details. Drill-down functionality is a fantastic tool, but there may well be times that you don’t want to share the underlying Pivot Table data with end-users. Unchecking the Show Details option will prevent you from doing this and Excel will give you a nasty message if you double-click on a Pivot Table detail.

 

Figure 449  

• • Refresh data when opening file – If you link your Pivot Table to an external data source you might want to enable this option. Although if your data rarely refreshes or it takes a long time to refresh you’ll probably want to opt for manually refreshing your dataset. You’ll also want to be mindful of this option if you share your Pivot Table workbook with users who might not have access to your data source, in which case they’ll receive error messages when trying to open the workbook.
• • Alt Text – This is dedicated for people with disabilities who may choose to use Excel’s Text-to-Speech options. It’s not likely that you’ll need to enable this feature.
• • Active Field - This will show you which field item is active at any given time.

 

Figure 450  

• • Field Settings – Depending on whether you’re in a category or value field you’ll get one of two different detail dialogs.
• • Field Dialog – This contains two tabs for the selected Field item.
• • Subtotals & Filters – You can select from Automatic Sub-Totals, None or Custom. You can also choose to add a new manual Auto-Filter. In the following example CitiGroup has had a Count applied to it and you’ll see that the Filter indicates that it contains one record.

 

Figure 451  

• • Layout & Print – This simply defines your Pivot Table print options.
• • Value Dialog – If you select a value item you will see a different dialog that lets you change how your numeric items will be displayed. These are the same options that you have if you click on any of the Value items in the Pivot Table Field List and select the Value Field Settings.

 

• • Expand/Collapse Field – These options will only be enabled if you’re in a Field item area. It allows you to Expand or Collapse an entire Field items group as opposed to clicking the Expand/Collapse buttons in the Pivot Table itself.
• • Group – This allows you to add Groups within Groups. As you start defining deeper levels of details in your Pivot Table experimentation you might find this handy to isolate certain data. Depending on the Field List item you select you’ll have different options. To the right you’ll see the dialog for grouping a Date field, while the Pivot Table example shows a grouping on the Customer Field List item.

 

Figure 452  

• • Sort & Filter – The Sort options should be easy enough to understand, but Slicers on the other hand represent a whole new level of detail options. Slicers are a tool that Microsoft created that allows you to set up a Pivot Table so that even the least experienced user can manipulate your Pivot Table data with ease. Think of Slivers as individual sort items that quickly display your Field List items in a handy dialog that’s easy for just about anyone to understand. If you plan on sharing your Pivot Table data with people then you’ll find Slicers to be an invaluable to. When you first select the Slicers option you’ll receive a dialog that lists your Field List items. A Slicer will be created for each item that you check from the list. In the following example we’ll create Slicers for Region, Product and Customer. Note that Excel will create your Slicers and lump them on top of each other in the middle of the worksheet. It’s up to you to move them around where you want them.
• • Slicer Tools - You’ll also notice that as soon as you create Slicers that a new Ribbon group called Slicer Tools has been created. This allows you to format your Slicers, name them, move or size them in relation to the rest of the worksheet objects, etc. As you apply Slicers you’ll see the Filter icon in the upper right-hand corner of each Slicer change, and you’ll see each Slicer selection highlighted.

 

Figure 453  

 

Figure 454 slicers applied

• • Data – These options allow you to Refresh or change your data source. You probably won’t change data sources too often, but you will likely find yourself Refreshing it. This is where you come if you decided not to let your data refresh when you open the workbook. Remember if you have a large dataset you might want to be prepared when you do this, as Excel will be tied up until the data has refreshed.

 

Figure 455  

• • Actions – This is another straightforward series of options.
• • Clear – This will reset your Pivot Table to where you started, leaving you with a clean slate and a Field List ready for you to start selecting items. If you activate this by accident CTRL+Z will restore your Pivot Table to its previous state.

 

Figure 456  

• • Select – This allows you to select various items on the Pivot Table itself, be it Labels, Values, both, or even the entire Pivot Table itself. The Enable Selection option allows you to manually select Pivot Table groups by clicking on them. Selection comes in handy if you want to apply Groupings from the Group options. In the Group by Selection example you saw what selected items looks like.

 

Figure 457  

• • Calculations – The Summarize Values By and Show Values As options are the same as you would find in the Value Field Settings. This is just another place to expose that functionality for you.

 

Figure 458  

• • Fields, Items & Sets – These are where you can start getting creative with your data and generate calculations that you might not have been able to do in your data. The data examples we’re using came out of an Excel Table that had already been calculated, but there will be times that you won’t have that luxury. Retrieving data from an external source like a database is a perfect example. In many cases you’ll only be retrieving transactional (raw) data and might not be able to calculate things like our Net Profit category. But with Calculated Fields you can quickly perform calculations, in many times faster than if you were to try to create a formula to do it.

 

Figure 459  

• • Calculated Field - Here is an example of recreating the Profit category. When you look back at the example in the companion workbook you’ll see that Excel didn’t actually create a formula within the Pivot Table for you it just applied the calculation. If you were to refresh your data the Calculated Field would automatically update itself with the rest of the Pivot Table items.

 

Figure 460  

• • Calculated Item – This lets you combine two or more fields and create a new one, like the total of the Central & West regions.
• • Solve Order – If you have created Calculated Items this will show you the order in which those items are being calculated.
• • List Formulas – This will create a summary report displaying all of the Calculated Fields that you have in your Pivot Table. Here you’ll see our Net Profit calculation.

 

Figure 461  

• • Tools – This list some additional tools that you can use with your Pivot Tables. For this course we’ll only be reviewing Pivot Charts, as OLAP Tools and What-If Analysis deal with external data sources, and could have an entire lesson devoted to them.
• • Pivot Charts – These allow you to create charts based off of your Pivot Table data. As you’ll see in the following example, Pivot Charts are as easy to create as a regular Excel chart. However, you’ll generally want to collapse your data down to the smallest group as too much visible data can be more than difficult to read. Fortunately, the tools that you have to Expand/Collapse Pivot Tables are incredibly useful for that, especially as only visible data will plot in Excel charts.

 

Figure 462  

• • Unlike Excel charts you’ll see that your Pivot Chart contains all of the Pivot Table Field & Value List items and you can interactively filter each of those items. The Value List items are at the top of the chart, while the Field List items are at the bottom. Both series of items can be manipulated by right-clicking any one of them. This is another one of those areas that just has too many potential variables to discuss here, but you should certainly experiment with your charting options in the companion workbook.
• • Show – This merely exposes the Pivot Table features that you can view. To start you’ll probably want to leave them all visible. When you distribute your Pivot Table you might want to hide the Field List though, just to keep your users from easily changing your Pivot Table.

 

Figure 463  

• • Pivot Table Ribbon Items – Design Group

You’ll most likely come to the Design Group after you’ve completed your Pivot Table. As mentioned earlier in the Lesson, you can make so many adjustments to a Pivot Table when you’re initially constructing it that you won’t necessarily want to apply design elements until you’re done.

• • Layout – Layout options let you define how you want your Pivot Table data to be displayed. These are more personal than they are functional and they won’t alter your Pivot Table data in any way.
• • Subtotals – Here you can opt to turn off Subtotals or show them at the top or bottom of each data series.

 

Figure 464  

• • Grand Totals – You can opt to toggle Grand Totals for Rows and or Columns.
• • Report Layout – Here you define the display structure you want for your Pivot Table. In the companion workbook you’ll see examples of each. You have your choice of Compact, which is good for data with a lot of Field Items, Outline and Tabular form, both of which can get a bit unwieldy if you have a lot of Field Items.
• • Blank Rows – This just lets you insert a blank row between each line item. If you have a lot of data it might make it easier to read, but you’ll want to be careful when you print as it can create extra pages if you have lot of rows to begin with.
• • PivotTable Style Options – Here you have the option to toggle Headers and Banding. While you can change these without applying a PivotTable Style, they generally work better in conjunction with them.
• • Row & Column Headers/Banded Rows & Columns

 

Figure 465  

Pivot Table Styles – This is nearly identical to your Excel Table styles in that it’s a pre-defined set of Gallery styles that you can select for your Pivot Table. The nice thing is that if you add or remove items from your Pivot Table the applied style will automatically adjust with the changes. This holds true when you apply filters or add calculated fields. If you find that the Pivot Table Style gallery isn’t extensive enough for you, you can create your own.

 

Figure 466  

 

Unit Summary: Lesson 11 – Pivot Tables

• • In this lesson you learned about what Excel Pivot Tables, which let you manipulate your flat-file data in ways previously only achievable with a database application. We showed by example how Pivot Tables should be embraced by anyone who needs to analyze their data, and how easy they are to use.
• • You learned that Pivot Tables do not physically alter your data in any way, but only take a snapshot of it. A benefit of this is that your Excel workbooks won’t blow up in size if you create multiple Pivot Tables based on the same data since it’s not recreating it.
• • You saw how easy it is to add or remove items from Pivot Tables by either clicking items in the Field List or dragging and dropping them.
• • We explored the different ways to create different valuations with a click of a button, for instance changing a Sum to an Average.
• • We discussed how to create Calculated Fields if your base Pivot Table data doesn’t already have them.
• • We showed you how to add and format Slicers to make data analysis a snap for you and your users.
• • We look at adding Pivot Charts to your data and how a Pivot Table’s filtering and grouping functionality can help you expand and contract your Chart data.
• • Finally we reviewed your Pivot Table options when it comes to Sub and Grand Totals, as well as formatting with Pivot Table Styles.

Review Questions – Lesson

1. What can a Pivot Table do for you & why might you want to use one?

a. __________________________________________________

b. __________________________________________________

2. Should Pivot Tables be scary? If Yes, why? If No why? (Prepare to answer each argument with yourself)

a. __________________________________________________

3. Can you combine Excel Tables and Sub-Totaled Data?

a. __________________________________________________

4. If you haven’t calculated column differences in an Excel Table or Dataset, can you do it with a Pivot Table?

a. __________________________________________________

5. How can you add a Pivot Table Report Filter?

a. __________________________________________________

6. What steps should you take with a Pivot Table before adding a Pivot Chart?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

7. How many Chart styles can you use with Pivot Tables?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

Lesson Assignment – Lesson 11 – Pivot Tables

• • Your assignment is to review the Pivot Table companion workbook and explore all of the different tables that you see.
• • You’re encouraged to not only work with the example data, but bring in some of your own and see what you can do. The comfort factor with Pivot Tables will come with working with them, not by avoiding them.
• • See what kind of data manipulation you can arrange with Pivot Tables.