Values area

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.

Row area

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.

Column area

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.

Filter area

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.

Changing and rearranging your pivot table

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.

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.

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.

Adding a report filter

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.

Keeping your pivot table fresh

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:

1. Click anywhere inside the pivot table to activate the PivotTable Tools contextual tab on the Ribbon.
2. Select the Analyze tab on the Ribbon.

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

4. Change the range selection to include any new rows or columns. (See Figure 6-15.)
5. Click OK to apply the change.

Changing the pivot table layout

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.

The layout you choose affects not only the look and feel of your reporting mechanisms but may also affect the way you build and interact with any dashboard models based on your pivot tables.

Changing the layout of a pivot table is easy. Follow these steps:

1. Click anywhere inside the pivot table to activate the PivotTable Tools context tab on the Ribbon.
2. Select the Design tab on the Ribbon.
3. Click the Report Layout icon and choose the layout you like from the menu that appears. (See Figure 6-17.)

Customizing field names

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:

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

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

3. Enter the new name in the Custom Name input box, shown in Figure 6-19.
4. Click OK to apply the change.

If you use the name of the data label used in your source table, you receive an error. For example, if you rename Sum of Sales Amount as Sales Amount, you get an error message because there’s already a Sales Amount field in the source data table. Well, this is kinda lame, especially if Sales Amount is exactly what you want to name the field in the your pivot table.

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.

Applying numeric formats to data fields

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:

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

2. Select Value Field Settings from the menu that appears.

   The Value Field Settings dialog box appears.

3. Click the Number Format button.

   The Format Cells dialog box opens.

4. Apply the number format you desire, just as you typically would on your spreadsheet.

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

Changing summary calculations

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

• Sum: Adds all numeric data.
• Count: Counts all data items within a given field, including numeric-, text-, and date-formatted cells.
• Average: Calculates an average for the target data items.
• Max: Displays the largest value in the target data items.
• Min: Displays the smallest value in the target data items.
• Product: Multiplies all target data items together.
• Count Nums: Counts only the numeric cells in the target data items.
• StdDevP and StdDev: Calculates the standard deviation for the target data items. Use StdDevP if your dataset contains the complete population. Use StdDev if your dataset contains a sample of the population.
• VarP and Var: Calculates the statistical variance for the target data items. Use VarP if your data contains a complete population. If your data contains only a sampling of the complete population, use Var to estimate the variance.

You can easily change the summary calculation for any given field by taking the following actions:

1. Right-click any value within the target field.

2. Select Value Field Settings from the menu that appears.

   The Value Field Settings dialog box appears.

3. Choose the type of calculation you want to use from the list of calculations. (See Figure 6-20.)
4. Click OK to apply the changes.

Did you know that a single blank cell causes Excel to count instead of sum? That’s right. If all the cells in a column contain numeric data, Excel chooses Sum. If just one cell is either blank or contains text, Excel chooses Count.

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.

Suppressing subtotals

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.

Removing all subtotals at one time

You can remove all subtotals at one time by taking these actions:

1. Click anywhere inside the pivot table to activate the PivotTable Tools context tab on the Ribbon.
2. Select the Design tab on the Ribbon.
3. Click the Subtotals icon and select Do Not Show Subtotals from the menu that appears, as shown in Figure 6-22.

As you can see in Figure 6-23, the same report without subtotals is much more pleasant to review.

Removing the subtotals for only one field

Maybe you want to remove the subtotals for only one field? In such a case, you can take the following actions:

1. Right-click any value within the target field.

2. Select Field Settings from the menu that appears.

   The Field Settings dialog box appears.

3. Choose the None option under Subtotals, as demonstrated in Figure 6-24.
4. Click OK to apply the changes.

Showing and hiding data items

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.

Hiding or showing items without data

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:

1. Right-click any value within the target field.

   In this example, the target field is the SalesPeriod field.

2. Select Field Settings from the menu that appears.

   The Field Settings dialog box appears.

3. Select the Layout & Print tab in the Field Settings dialog box.
4. Select the Show Items with No Data option, as shown in Figure 6-31.
5. Click OK to apply the change.

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.

Sorting your pivot table

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:

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

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

Producing top and bottom views

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:

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

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

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

4. Click OK to apply the filter.

5. 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 may notice that in Figure 6-39, the Bottom 50 report is showing only 27 records. This is because there are fewer than 50 customers in the United Kingdom market that have accessories sales. Because I asked for the bottom 50, Excel shows as many as 50 accounts, but fewer if there are fewer than 50. If there’s a tie for any rank in the bottom 50, Excel shows you all the tied records.

You can remove the applied filters in your pivot tables by taking these actions:

1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab on the Ribbon.
2. Select the Options tab on the Ribbon.
3. Click the Clear icon and select Clear Filters from the menu that appears, as demonstrated in Figure 6-40.

Creating views by month, quarter, and year

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:

1. Build a pivot table with Sales Date in the row area and Sales Amount in the values area, similar to the one in Figure 6-41.

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

3. Select the time dimensions you want.

   In this example, select Months, Quarters, and Years.

4. Click OK to apply the change.

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.

Creating a percent distribution view

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:

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

2. Select Value Field Settings from the menu that appears.

   The Value Field Settings dialog box appears.

3. Click the Show Values As tab.
4. Select % of Row Total from the drop-down list.
5. Click OK to apply the change.

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.

Creating a YTD totals view

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.

In the sample data for this chapter, you don’t see Months and Years. You have to create them by grouping the SalesDate field. Feel free to review the section “Creating views by month, quarter, and year” earlier in this chapter to find out how.

To create this type of view, take these actions:

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

2. Select Value Field Settings from the menu that appears.

   The Value Field Settings dialog box appears.

3. Click the Show Values As tab.
4. Select Running Total In from the drop-down list.

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

6. Click OK to apply the change.

Creating a month-over-month variance view

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.

In the sample data for this chapter, you don’t see Months and Years. You have to create them by grouping the SalesDate field. Feel free to review the section “Creating views by month, quarter, and year,” earlier in this chapter, to find out how.

To create the view in Figure 6-50, take these actions:

1. Right-click any value within the target field.

   In this case, the target field is the second Sum of Sales Amount field.

2. Select Value Field Settings from the menu that appears.

   The Value Field Settings dialog box appears.

3. Click the Show Values As tab.
4. Select % Difference From from the drop-down list.

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

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