Chapter 6
In This Chapter
Introducing pivot tables
Building your first pivot table
Customizing your pivot tables
Using pivot-driven reports
Creating top and bottom reports
I know what you’re thinking: “Am I supposed to be jumping right in with pivot tables?” My answer is an emphatic yes!
In Chapter 2, I introduce the concept of reporting models that separate the data, analysis, and presentation layers. As you discover in this chapter, pivot tables lend themselves nicely to this concept. With pivot tables, you can build reporting models that not only are easy to set up, but can also be refreshed with the simple press of a button. Then you can spend less time maintaining your dashboards and reports and more time doing other useful things. No utility in the whole of Excel allows you to achieve this efficient data model better than a pivot table.
Pivot tables have a reputation for being complicated, but if you’re new to pivot tables, relax a bit. After reading this introduction, you’ll be pleasantly surprised at how easy it is to create and use pivot tables. Later, you’ll find some time-saving techniques to help create useful pivot-driven views for your dashboards and reports.
A pivot table is a robust tool that allows you to create an interactive view of your dataset, commonly referred to as a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful analyses, and interactively perform a wide variety of calculations.
Pivot tables get their name from the way they allow you to drag and drop fields within the pivot table report to dynamically change (or pivot) perspective and give you an entirely new analysis using the same data source.
Think of a pivot table as an object you can point at your dataset. When you look at your dataset through a pivot table, you can see your data from different perspectives. The dataset itself doesn’t change, and it’s not connected to the pivot table. The pivot table is simply a tool you’re using to dynamically change analyses, apply varying calculations, and interactively drill down to the detail records.
The reason a pivot table is so well suited for dashboarding and reporting is that you can refresh the analyses shown through the pivot table simply by updating the dataset it’s pointed to. This allows you to set up your analysis and presentation layers only one time; then, to refresh your reporting mechanism, all you have to do is press a button.
Let’s start this exploration of pivot tables with a lesson on the anatomy of a pivot table.
A pivot table is composed of four areas. The data you place in these areas defines both the utility and appearance of the pivot table. Take a moment to understand the function of each of these four areas.
The values area, as shown in Figure 6-1, is the large rectangular area below and to the right of the column and row headings. In the example in Figure 6-1, the values area contains a sum of the values in the Sales Amount field.
The values area calculates and counts data. The data fields that you drag and drop here are typically those that you want to measure — fields such as Sum of Revenue, Count of Units, or Average of Price.
The row area is shown in Figure 6-2. Placing a data field into the row area displays the unique values from that field down the rows of the left side of the pivot table. The row area typically has at least one field, although it’s possible to have no fields.
The types of data fields that you would drop here include those that you want to group and categorize, such as Products, Names, and Locations.
The column area is composed of headings that stretch across the top of columns in the pivot table.
As you can see in Figure 6-3, the column area stretches across the top of the columns. In this example, it contains the unique list of business segments.
Placing a data field into the column area displays the unique values from that field in a column-oriented perspective. The column area is ideal for creating a data matrix or showing trends over time.
The filter area is an optional set of one or more drop-down menus at the top of the pivot table. In Figure 6-4, the filter area contains the Region field, and the pivot table is set to show all regions.
Placing data fields into the filter area allows you to filter the entire pivot table based on your selections. The types of data fields that you’d drop here include those that you want to isolate and focus on — for example, Region, Line of Business, and Employees.
If you’ve followed along in this chapter, you now have a good understanding of the basic structure of a pivot table, so let’s quit all the talking and use the following instructions to walk through the creation of your first pivot table.
Follow these steps:
From the drop-down menu that appears, choose PivotTable.
This step activates the Create PivotTable dialog box, as shown in Figure 6-6. As you can see, this dialog box asks you to specify the location of your source data and the place you want to put the pivot table.
Notice that in the Create PivotTable dialog box, Excel makes an attempt to fill in the range of your data for you. In most cases, Excel gets this right. However, always make sure the correct range is selected.
You will also note in Figure 6-6 that the default location for a new pivot table is New Worksheet. This means the pivot table will be placed in a new worksheet within the current workbook. You can change this by selecting the Existing Worksheet option and specifying the worksheet where you want the pivot table placed.
Click OK.
At this point, you have an empty pivot table report on a new worksheet. Next to the empty pivot table, you see the PivotTable Fields dialog box, shown in Figure 6-7.
The idea here is to add the fields you need into the pivot table by using the four drop zones found in the PivotTable Field List — Filters, Columns, Rows, and Values. Pleasantly enough, these drop zones correspond to the four areas of the pivot table you review at the beginning of this chapter.
If clicking the pivot table doesn’t activate the PivotTable Fields dialog box, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field List from the menu that appears.
Now, before you go wild and start dropping fields into the various drop zones, you should ask yourself two questions: “What am I measuring?” and “How do I want to see it?” The answers to these questions give you some guidance when determining which fields go where.
For your first pivot table report, you measure the dollar sales by market. This automatically tells you that you will need to work with the Sales Amount field and the Market field.
How do you want to see that? You want markets to go down the left side of the report and sales amounts to be calculated next to each market. Remembering the four areas of the pivot table, you’ll need to add the Market field to the Rows drop zone and add the Sales Amount field to the Values drop zone.
Select the Market check box in the list, as demonstrated in Figure 6-8.
Now that you have regions in your pivot table, it’s time to add the dollar sales.
Select the Sales Amount check box in the list, as demonstrated in Figure 6-9.
Selecting a check box that is nonnumeric (text or date) automatically places that field into the row area of the pivot table. Selecting a check box that is numeric automatically places that field in the values area of the pivot table.
What happens if you need fields in the other areas of the pivot table? Well, instead of selecting the field’s check box, you can drag any field directly to the different drop zones.
One more thing: When you add fields to the drop zones, you may find it difficult to see all fields in each drop zone. You can expand the PivotTable Fields dialog box by clicking and dragging the borders of the dialog box.
As you can see, you have just analyzed the sales for each market in only six steps! That’s an amazing feat, considering that you start with more than 60,000 rows of data. With a little formatting, this modest pivot table can become the starting point for a management dashboard or report.
Here’s the wonderful thing about pivot tables: You can add as many layers of analysis as made possible by the fields in your source data table. Say that you want to show the dollar sales each market earned by business segment. Because the pivot table already contains the Market and Sales Amount fields, all you have to add is the Business Segment field.
So simply click anywhere on the pivot table to reactivate the PivotTable Fields dialog box and then select the Business Segment check box. Figure 6-10 illustrates what the pivot table should look like now.
Imagine that your manager says that this layout doesn’t work for him. He wants to see business segments going across the top of the pivot table report. No problem. Simply drag the Business Segment field from the Rows drop zone to the Columns drop zone. As you can see in Figure 6-11, this instantly restructures the pivot table to his specifications.
Often, you’re asked to produce reports for one particular region, market, product, and so on. Instead of working hours and hours building separate reports for every possible analysis scenario, you can leverage pivot tables to help create multiple views of the same data. For example, you can do so by creating a region filter in your pivot table.
Click anywhere on the pivot table to reactivate the PivotTable Fields dialog box and then drag the Region field to the Filters drop zone. This adds a drop-down selector to the pivot table, as shown in Figure 6-12. You can then use this selector to analyze one particular region at a time.
In Hollywood, it’s important to stay fresh and relevant. As boring as your pivot tables may seem, they’ll eventually become the stars of your reports and dashboards. So it’s just as important to keep your pivot tables fresh and relevant.
As time goes by, your data may change and grow with newly added rows and columns. The action of updating a pivot table with these changes is refreshing your data.
The pivot table report can be refreshed by simply right-clicking inside it and selecting Refresh from the menu that appears, as demonstrated in Figure 6-13.
Sometimes, you’re the data source that feeds your pivot table changes in structure. For example, you may have added or deleted rows or columns from your data table. These types of changes affect the range of your data source, not just a few data items in the table.
In these cases, performing a simple Refresh of the pivot table won’t do. You have to update the range being captured by the pivot table. Here’s how:
Click the tab’s Change Data Source icon and choose Change Data Source from the menu that appears, as demonstrated in Figure 6-14.
The Change PivotTable Data Source dialog box appears.
The pivot tables you create often need to be tweaked to get the look and feel you’re looking for. In this section, I cover some of the options you can adjust to customize your pivot tables to suit your reporting needs.
Excel gives you a choice in the layout of your data in a pivot table. The three layouts, shown side by side in Figure 6-16, are Compact Form, Outline Form, and Tabular Form. Although no layout stands out as better than the others, I prefer using the Tabular Form layout because it seems easiest to read, and it’s the layout that most people who have seen pivot tables are used to.
Changing the layout of a pivot table is easy. Follow these steps:
Notice that every field in your pivot table has a name. The fields in the row, column, and filter areas inherit their names from the data labels in the source table. The fields in the values area are given a name, such as Sum of Sales Amount.
Sometimes you might prefer the name Total Sales instead of the unattractive default name, like Sum of Sales Amount. In these situations, the ability to change your field names is handy. To change a field name, do the following:
Right-click any value within the target field.
For example, if you want to change the name of the field Sum of Sales Amount, you right-click any value under that field.
Select Value Field Settings from the menu that appears, as shown in Figure 6-18.
The Value Field Settings dialog box appears.
Note that if you were changing the name of a field in the row or column area, this selection is Field Settings.
To get around this, you can name the field and add a space to the end of the name. Excel considers Sales Amount (followed by a space) to be different from Sales Amount. This way, you can use the name you want and no one will notice that it’s any different.
Numbers in pivot tables can be formatted to fit your needs — that is, formatted as currency, percentage, or number. You can easily control the numeric formatting of a field using the Value Field Settings dialog box. Here’s how:
Right-click any value within the target field.
For example, if you want to change the format of the values in the Sales Amount field, right-click any value under that field.
Select Value Field Settings from the menu that appears.
The Value Field Settings dialog box appears.
Click the Number Format button.
The Format Cells dialog box opens.
Click OK to apply the changes.
After you set the formatting for a field, the applied formatting will persist even if you refresh or rearrange the pivot table.
When creating your pivot table report, Excel, by default, summarizes your data by either counting or summing the items. Instead of Sum or Count, you might want to choose functions, such as Average, Min, Max, and so on. In all, 11 options are available, including
You can easily change the summary calculation for any given field by taking the following actions:
Select Value Field Settings from the menu that appears.
The Value Field Settings dialog box appears.
Be sure to pay attention to the fields that you place into the values area of the pivot table. If the field name starts with Count Of, Excel’s counting the items in the field instead of summing the values.
Notice that each time you add a field to your pivot table, Excel adds a subtotal for that field. There may be times however, when the inclusion of subtotals either doesn’t make sense or just hinders a clear view of the pivot table report. For example, Figure 6-21 shows a pivot table in which the subtotals inundate the report with totals that hide the real data you’re trying to report.
You can remove all subtotals at one time by taking these actions:
As you can see in Figure 6-23, the same report without subtotals is much more pleasant to review.
Maybe you want to remove the subtotals for only one field? In such a case, you can take the following actions:
Select Field Settings from the menu that appears.
The Field Settings dialog box appears.
There may be instances when you want to remove the grand totals from your pivot table:
Select PivotTable Options from the menu that appears.
The PivotTable Options dialog box appears.
A pivot table summarizes and displays all records in the source data table. There may be situations, however, when you want to inhibit certain data items from being included in the pivot table summary. In these situations, you can choose to hide a data item.
In terms of pivot tables, hiding doesn’t just mean preventing the data item from being shown on the report. Hiding a data item also prevents it from being factored into the summary calculations.
In the pivot table illustrated in Figure 6-25, you see sales amounts for all business segments by market. In this example, I want to show totals without taking into consideration sales from the Bikes segment. In other words, I want to hide the Bikes segment.
You can hide the Bikes business segment by clicking the Business Segment drop-down list arrow and deselecting the Bikes check box from the menu that appears, as shown in Figure 6-26.
After you choose OK to close the selection box, the pivot table instantly recalculates, leaving out the Bikes segment. As you can see in Figure 6-27, the Market total sales now reflect the sales without Bikes.
I can just as quickly reinstate all hidden data items for my field. I simply click the Business Segment drop-down list arrow and click the Select All check box from the menu that appears, as shown in Figure 6-28.
By default, the pivot table shows only data items that have data. This inherent behavior may cause unintended problems for your data analysis.
Look at Figure 6-29, which shows a pivot table with the SalesPeriod field in the row area and the Region field in the filter area. Note that the Region field is set to (All) and that every sales period appears in the report.
If I choose Europe in the filter area, only a portion of all the sales periods will show. (See Figure 6-30.) The pivot table will show only those sales periods that apply to the Europe region.
Displaying only those items with data could cause trouble if I plan to use this pivot table as the feeder for my charts or other dashboard components. From a dashboarding-and-reporting perspective, it isn’t ideal if half the year’s data disappeared every time customers selected Europe.
Here’s how you can prevent Excel from hiding pivot items without data:
Right-click any value within the target field.
In this example, the target field is the SalesPeriod field.
Select Field Settings from the menu that appears.
The Field Settings dialog box appears.
As you can see in Figure 6-32, after you choose the Show Items with No Data option, all sales periods appear whether the selected region had sales that period or not.
After you’re confident that the structure of the pivot table is locked down, you can use it to feed charts and other components on the dashboard.
By default, items in each pivot field are sorted in ascending sequence based on the item name. Excel gives you the freedom to change the sort order of the items in a pivot table.
As with many actions you can perform in Excel, there are lots of different ways to sort data within a pivot table. The easiest way, and the way that I use the most, is to apply the sort directly in the pivot table. Here’s how:
Right-click any value within the target field — the field you need to sort.
In the example shown in Figure 6-33, I want to sort by sales amount.
Select Sort from the menu that appears and then select the sort direction.
The changes take effect immediately and persist while you work with the pivot table.
At this point in your exploration of pivot tables, you have covered enough of the fundamentals to start creating your own pivot table reports. In this last section, I share with you a few of the techniques I use to create some of the more useful report views. Although you could create these views by hand, creating them with pivot tables helps save you hours of work and allows you to more easily update and maintain them.
You’ll often find that managers are interested in the top and bottom of things: the top 50 customers, the bottom 5 sales reps, the top 10 products. Although you may think this is because managers have the attention span of a 4-year-old, there’s a more logical reason for focusing on the outliers.
Dashboarding and reporting is often about showing actionable data. If you, as a manager, know who the bottom ten revenue-generating accounts are, you could apply your effort and resources in building up those accounts. Because you most likely wouldn’t have the resources to focus on all accounts, viewing a manageable subset of accounts would be more useful.
Luckily, pivot tables make it easy to filter your data for the top five, the bottom ten, or any conceivable combination of top or bottom records.
Here’s an example. Imagine that in your company, the Accessories business segment is a high-margin business — you make the most profit for each dollar of sales in the Accessories segment. To increase sales, your manager wants to focus on the 50 customers who spend the least amount of money on accessories. He obviously wants to spend his time and resources on getting those customers to buy more accessories. Here’s what to do:
Build a pivot table with Business Segment in the filter area, Customer in the row area, and Sales Amount in the values area; see Figure 6-34. For cosmetic value, change the layout to Tabular Form.
See the earlier section “Changing the pivot table layout” to find out how to do that.
You can find the sample file for this chapter on this book’s companion website.
Right-click any customer name in the Customer field, select Filter, and then Top 10 — as demonstrated in Figure 6-35.
Don’t let the label Top 10 confuse you. You can use the Top 10 option to filter both top and bottom records.
In the Top 10 Filter dialog box, as illustrated in Figure 6-36, you simply have to define the view you’re looking for.
In this example, you want the bottom 50 items (customers), as defined by the Sum of Sales Amount field.
In the filter area, click the drop-down button for the Business Segment field and select the check box for the filter item Accessories in the menu that appears, as shown in Figure 6-37.
At this point, you have exactly what your manager asked for — the 50 customers who spend the least amount of money on accessories. You can go a step further and format the report a bit by sorting on the Sum of Sales Amount and applying a currency format to the numbers. (See Figure 6-38.)
Note that because you built this view using a pivot table, you can easily adapt the newly created report to create a whole new view. For example, you can add the SubRegion field — shown in Figure 6-39 — to the filter area to get the 50 United Kingdom customers who spend the least amount of money on accessories. This, my friends, is the power of using pivot tables for the basis of your dashboards and reports. Continue to play around with the Top 10 filter option to see what kind of reports you can come up with.
You can remove the applied filters in your pivot tables by taking these actions:
Raw transactional data is rarely aggregated by month, quarter, or year for you. This type of data is often captured by the day. However, managers often want reports by month or quarters instead of detail by day. Fortunately, pivot tables make it easy to group date fields into various time dimensions. Here’s how:
Right-click any date and select Group from the menu that appears, as demonstrated in Figure 6-42.
The Grouping dialog box appears, as shown in Figure 6-43.
Select the time dimensions you want.
In this example, select Months, Quarters, and Years.
Here are several interesting things to note about the resulting pivot table. First, notice that Quarters and Years have been added to the field list. Keep in mind that your source data hasn’t changed to include these new fields; instead, these fields are now part of the pivot table. Another interesting thing to note is that, by default, the Years and Quarters fields are automatically added next to the original date field in the pivot table layout, as shown in Figure 6-44.
After the date field is grouped, you can use each added time grouping just as you would any other field in your pivot table. In Figure 6-45, I use the newly created time groupings to show sales for each market by quarter for 2010.
A percent distribution (or percent contribution) view allows you to see how much of the total is made up of a specific data item. This view is useful when you’re trying to measure the general impact of a particular item.
The pivot table, as shown in Figure 6-46, gives you a view into the percent of sales that comes from each business segment. Here, you can tell that bikes make up 81 percent of Canada’s sales, whereas only 77 percent of France’s sales come from bikes.
Here are the steps to create this type of view:
Right-click any value within the target field.
For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.
Select Value Field Settings from the menu that appears.
The Value Field Settings dialog box appears.
The pivot table in Figure 6-47 is formatted to give you the percent of sales for each market.
Again, remember that because you built these views in a pivot table, you have the flexibility to slice the data by region, bring in new fields, rearrange data, and, most important, refresh this view when new data comes in.
Sometimes it’s useful to capture a running-totals view to analyze the movement of numbers on a year-to-date (YTD) basis. Figure 6-48 illustrates a pivot table that shows a running total of revenue by month for each year. In this view, you can see where the YTD sales stand at any given month in each year. For example, you can see that in August 2010, revenues were about a million dollars lower than at the same point in 2009.
To create this type of view, take these actions:
Right-click any value within the target field.
For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.
Select Value Field Settings from the menu that appears.
The Value Field Settings dialog box appears.
In the Base Field list, select the field that you want the running totals to be calculated against.
In most cases, this would be a time series such as, in this example, the SalesDate field. (Refer to Figure 6-48.)
Another commonly requested view is a month-over-month variance. How did this month’s sales compare to last month’s sales? The best way to create these types of views is to show the raw number and the percent variance together.
In that light, you can start creating this view by building a pivot table similar to the one shown in Figure 6-49. Notice that you bring in the Sales Amount field twice. One of these remains untouched, showing the raw data. The other is changed to show the month-over-month variance.
Figure 6-50 illustrates the settings that convert the second Sum of Sales Amount field into a month-over-month variance calculation.
As you can see, after the settings are applied, the pivot table gives you a nice view of raw sales dollars and the variance over last month. You can obviously change the field names (see the section “Customizing field names,” earlier in this chapter) to reflect the appropriate labels for each column.
To create the view in Figure 6-50, take these actions:
Right-click any value within the target field.
In this case, the target field is the second Sum of Sales Amount field.
Select Value Field Settings from the menu that appears.
The Value Field Settings dialog box appears.
In the Base Field list, select the field that you want the running totals to be calculated against.
In most cases, this is a time series such as, in this example, the SalesDate field.
In the Base Item list, select the item you want to compare against when calculating the percent variance.
In this example, you want to calculate each month’s variance to the previous month. Therefore, select the (Previous) item.
3.142.54.153