Chapter 18

Analyzing data with PivotTables

Tables and external databases can contain hundreds or even thousands of records. Analyzing that much data can be a nightmare without the right kinds of tools. To help you, Excel offers a powerful data analysis tool called a PivotTable. This tool enables you to summarize hundreds—or even hundreds of thousands—of records in a concise tabular format. You can then manipulate the layout of the table to see different views of your data. Because this is a book about Excel formulas and functions, I don’t provide instructions on building and customizing PivotTables. Instead, I focus on the extensive work you can do with built-in and custom PivotTable calculations.

The next few sections show you how to manipulate both the grand totals and the subtotals.

1. Select a cell inside the PivotTable.

2. Select the Design tab.

3. Select Grand Totals > Off for Rows and Columns. Excel removes the grand totals from the PivotTable.

1. Select a cell in the field.

2. Select the Design tab.

3. Select Subtotals > Do Not Show Subtotals. Excel removes the subtotals from the PivotTable.

• To change the subtotal calculation, select Custom in the Subtotals group, select one of the calculation functions (Sum, Count, Average, and so on) in the Select One Or More Functions list, and then select OK.

• To add extra subtotal calculations, select Custom in the Subtotals group, use the Select One Or More Functions list to select each calculation function you want to add, and then select OK.

Follow these steps to change the value field summary calculation:

1. Right-click any cell inside the value field.

2. Select Summarize Values By. Excel displays a partial list of the available summary calculations.

3. If you see the calculation you want, select it and skip the rest of these steps; otherwise, select More Options to open the Value Field Settings dialog box.

4. Select the summary calculation you want to use.

5. Select OK. Excel changes the value field calculation.

However, it’s also useful to compare one part of the data with another. In the PivotTable shown in Figure 18-2, for example, it would be valuable to compare each customer’s invoice totals in 2019 with those in 2018.

In Excel, you can perform this kind of analysis by using PivotTable difference calculations:

• Difference From: This difference calculation compares two numeric items and calculates the difference between them.

• % Difference From: This difference calculation compares two numeric items and calculates the percentage difference between them.

In each case, you must specify both a base field (the field in which you want Excel to perform the difference calculation) and the base item (the item in the base field that you want to use as the basis of the difference calculation). In the PivotTable shown in Figure 18-2, for example, Order Date would be the base field, and 2018 would be the base item.

Here are the steps to follow to set up a difference calculation:

1. Right-click any cell inside the value field.

2. Select Show Values As and then select either Difference From or % Difference From. Excel displays the Show Values As dialog box.

3. In the Base Field list, select the field you want to use as the base field.

4. In the Base Item list, select the item you want to use as the base item.

5. Select OK. Excel updates the PivotTable with the difference calculation.

Figure 18-3 shows both the completed Show Values As dialog box and the updated PivotTable with the Difference From calculation applied to the report from Figure 18-2.

Click here to view code image

Sub ToggleDifferenceCalculations()
    ' Work with the first value field
    With Selection.PivotTable.DataFields(1)
        ' Is the calculation currently Difference From?
        If .Calculation = xlDifferenceFrom Then
            ' If so, change it to % Difference From
            .Calculation = xlPercentDifferenceFrom
            .BaseField = "Order Date"
            .BaseItem = "2018"
            .NumberFormat = "0.00%"
        Else
            ' If not, change it to Difference From
            .Calculation = xlDifferenceFrom
            .BaseField = "Order Date"
            .BaseItem = "2018"
            .NumberFormat = "$#,##0.00"
        End If
    End With
End Sub

Similarly, knowing the raw invoice totals for each rep in a given quarter gives you only the most general idea of how the reps did with respect to each other. If you really want to compare them, you need to convert those totals into percentages of the quarterly grand total.

When you want to use percentages in your data analysis, you can use Excel’s percentage calculations to view data items as a percentage of some other item or as a percentage of the total in the current row, column, or the entire PivotTable. Excel offers the following percentage calculations:

• % Of: This calculation returns the percentage of each value with respect to a selected base item. If you use this calculation, you must also select a base field and a base item upon which Excel will calculate the percentages.

• % of Row Total: This calculation returns the percentage that each value in a row represents with respect to the Grand Total for that row.

• % of Column Total: This calculation returns the percentage that each value in a column represents with respect to the Grand Total for that column.

• % of Parent Row Total: If you have multiple fields in the row area, this calculation returns the percentage that each value in an inner row represents with respect to the total of the parent item in the outer row. (This calculation also returns the percentage that each value in the outer row represents with respect to the Grand Total.)

• % of Parent Column Total: If you have multiple fields in the column area, this calculation returns the percentage that each value in an inner column represents with respect to the total of the parent item in the outer column. (This calculation also returns the percentage that each value in the outer column represents with respect to the Grand Total.)

• % of Parent Total: If you have multiple fields in the row or column area, this calculation returns the percentage of each value with respect to a selected base field in the outer row or column. If you use this calculation, you must also select a base field upon which Excel will calculate the percentages.

• % of Grand Total: This calculation returns the percentage that each value in the PivotTable represents with respect to the Grand Total of the entire PivotTable.

Here are the steps to follow to set up a difference calculation:

1. Right-click any cell inside the value field.

2. Select Show Values As and then select the percentage calculation you want to use. Excel displays the Show Values As dialog box.

3. If you selected either % Of or % Of Parent Total, use the Base Field list to select the field you want to use as the base field.

4. If you selected % Of, use the Base Item list to select the item you want to use as the base item.

5. Select OK. Excel updates the PivotTable with the percentage calculation.

Figure 18-5 shows both the completed Show Values As dialog box and the updated PivotTable with the % Of calculation applied to the report from Figure 18-4.

Sub ReapplyCurrencyFormat()
    With Selection.PivotTable.DataFields(1)
        .NumberFormat = "$#,##0.00"
    End With
End Sub

Excel PivotTable reports come with a Running Total summary calculation that you can use for this kind of analysis. Note that the running total is always applied to a base field, which is the field on which you want to base the accumulation. This is almost always a date field, but you can use other field types, as appropriate.

Here are the steps to follow to set up a running total calculation:

1. Right-click any cell inside the value field.

2. Select Show Values As > Running Total In. Excel displays the Show Values As dialog box.

3. Use the Base Field list to select the field you want to use as the base field.

4. Select OK. Excel updates the PivotTable with the running total calculation.

Figure 18-6 shows both the completed Show Values As dialog box and a PivotTable with the Running Total In calculation applied to the Order Date field (grouped by month).

Sub ResetCalculationToNormal()
    With Selection.PivotTable.DataFields(1)
        .Calculation = xlNoAdditionalCalculation
    End With
End Sub

Another good example of this is trying to determine the relative importance of the results in the value field. For example, consider the PivotTable report shown in Figure 18-7. This report shows the unit sales of four items (mouse pad, HDMI cable, card reader, and USB charger), broken down by the type of advertisement the customer responded to (social media, blog network, and search).

You can see, for example, that 1,012 mouse pads were sold via the search ad (the second-highest number in the report), but only 562 USB chargers were sold through the search ad (one of the lower numbers in the report). Does this mean that you should only sell mouse pads in search ads? That is, is the mouse pad/search combination somehow more “important” than the USB charger/search combination?

You might think the answer is yes to both questions in the previous paragraph, but that’s not necessarily the case. To get an accurate answer, you’d need to take into account the total number of mouse pads sold, the total number of USB chargers sold, the total number of units sold through the search ad, and the number of units overall. This is a complicated bit of business, to be sure, but each PivotTable report has an Index calculation that handles it for you automatically. The Index calculation returns the weighted average of each cell in the PivotTable value field, using the following formula:

Click here to view code image

(Cell Value * Grand Total) / (Row Total * Column Total)

In the Index calculation results, the higher the value, the more important the cell is in the overall results. Here are the steps to follow to set up an Index calculation:

1. Right-click any cell inside the value field.

2. Select Summarize Values By and then select the summary calculation you want to use.

3. Right-click any cell inside the value field.

4. Select Show Values As > Index. Excel updates the PivotTable with the index summary calculation.

Figure 18-8 shows the updated PivotTable with the Index applied to the report from Figure 18-7. As you can see, the mouse pad/search combination scored an index of only 0.90 (the second-lowest value), whereas the USB charger/search combination scored 1.17 (the highest value).

However, this isn’t very convenient. If you add sales reps, you need to add formulas; if you remove sales reps, existing formulas generate errors. And, in any case, one of the points of generating a Pivot-Table report is to perform fewer worksheet calculations, not more.

The solution in this case is to take advantage of Excel’s calculated field feature. A calculated field is a new value field based on a custom formula. For example, if your invoice’s PivotTable has an Extended Price field and you want to award a 5% bonus to those reps who did at least $75,000 worth of business, you’d create a calculated field based on the following formula:

Click here to view code image

=IF('Extended Price' >= 75000, 'Extended Price' * 0.05, 0)

A slightly different PivotTable problem is when a field you’re using for the row or column labels doesn’t contain an item you need. For example, suppose your products are organized into various categories: Beverages, Condiments, Confections, Dairy Products, and so on. Suppose further that these categories are grouped into several divisions: Beverages and Condiments in Division A, Confections and Dairy Products in Division B, and so on. If the source data doesn’t have a Division field, how do you see PivotTable results that apply to the divisions?

One solution is to create groups for each division. (That is, select the categories for one division, select Analyze, Group Selection, and repeat for the other divisions.) That works, but Excel gives you a second solution: Use calculated items. A calculated item is a new item in a row or column where the item’s values are generated by a custom formula. For example, you could create a new item named Division A that is based on the following formula:

=Beverages + Condiments

Before getting to the details of creating calculated fields and items, you should know that Excel imposes a few restrictions on them. Here’s a summary:

• You can’t use a cell reference, range address, or range name as an operand in a custom calculation formula.

• You can’t use the PivotTable’s subtotals, row totals, column totals, or Grand Total as an operand in a custom calculation formula.

• In a calculated field, Excel defaults to a Sum calculation when you reference another field in your custom formula. However, this can cause problems. For example, suppose your invoice table has Unit Price and Quantity fields. You might think that you can create a calculated field that returns the invoice totals with the following formula:

  =Unit Price * Quantity

  This won’t work, however, because Excel treats the Unit Price operand as Sum of Unit Price, and it doesn’t make sense to “add” the prices together.

• For a calculated item, the custom formula can’t reference items from any field except the one in which the calculated item resides.

• You can’t create a calculated item in a PivotTable that has at least one grouped field. You must ungroup all the PivotTable fields before you can create a calculated item.

• You can’t use a calculated item as a filter.

• You can’t insert a calculated item into a PivotTable in which a field has been used more than once.

• You can’t insert a calculated item into a PivotTable that uses the Average, StdDev, StdDevp, Var, or Varp summary calculations.

1. Select any cell in the PivotTable’s value field.

2. Select Analyze > Fields, Items, & Sets > Calculated Field. Excel displays the Insert Calculated Field dialog box.

3. Use the Name text box to enter a name for the calculated field.

4. Use the Formula text box to enter the formula you want to use for the calculated field.

   Note

   If you need to use a field name in the formula, position the cursor where you want the field name to appear, select the field name in the Fields list, and then select Insert Field.

5. Select Add.

6. Select OK. Excel inserts the calculated field into the PivotTable.

Figure 18-9 shows a completed version of the Insert Calculated Field dialog box, as well as the resulting Bonus field in the PivotTable. Here’s the full formula that appears in the Formula text box:

Click here to view code image

=IF('Extended Price' >= 75000, 'Extended Price' * 0.05, 0)

1. Select any cell in the row or column field to which you want to add the item.

2. Select Analyze >Fields, Items, & Sets > Calculated Item. Excel displays the Insert Calculated Item in “Field” dialog box (where Field is the name of the field you’re working with).

3. Use the Name text box to enter a name for the calculated item.

4. Use the Formula text box to enter the formula you want to use for the calculated item.

   Note

   To add a field name to the formula, position the cursor where you want the field name to appear, select the field name in the Fields list, and then select Insert Field. To add a field item to the formula, position the cursor where you want the item name to appear, select the field in the Fields list, select the item in the Items list, and then select Insert Item.

5. Select Add.

6. Repeat steps 3–5 to add other calculated items to the field.

7. Select OK. Excel inserts the calculated item or items into the row or column field.

Figure 18-10 shows a completed version of the Insert Calculated Item in “Field” dialog box, as well as three items added to the Category row field:

Click here to view code image

Division A: =Beverage + Condiments
Division B: =Confections + 'Dairy Products'
Division C: ='Grains/Cereals' + 'Meat/Poultry' + Produce + Seafood

If you want to include a PivotTable result in a formula and you want that result to remain accurate even as you manipulate the PivotTable, use Excel’s GETPIVOTDATA() function. This function uses the value field, PivotTable location, and one or more (row or column) field/item pairs that specify the exact value you want to use. Here’s the syntax:

Click here to view code image

GETPIVOTDATA(data_field, pivot_table[, field1, item1...])

Note that you always enter the fieldn and itemn arguments as a pair. If you don’t include any field/item pairs, GETPIVOTDATA() returns the PivotTable Grand Total. You can enter up to 126 field/item pairs. This might make GETPIVOTDATA() seem like more work than it’s worth, but the good news is that you’ll rarely have to enter the GETPIVOTDATA() function by hand. By default, Excel is configured to generate the appropriate GETPIVOTDATA() syntax automatically. That is, you start your worksheet formula, and when you get to the part where you need the PivotTable value, just select the value. Excel then inserts the GETPIVOTDATA() function with the syntax that returns the value you want.

For example, in Figure 18-11, you can see that I started a worksheet formula in cell F5 by typing an equal sign (=), and then I selected cell B5 in the PivotTable. Excel generated the GETPIVOTDATA() function shown.

If Excel doesn’t generate the GETPIVOTDATA() function automatically, that feature may be turned off. Follow these steps to turn it back on:

1. Select File > Options to open the Excel Options dialog box.

2. Select Formulas.

3. Select the Use GetPivotData Functions For PivotTable References check box.

4. Select OK.

Sub ToggleGenerateGetPivotData()
    With Application
       .GenerateGetPivotData = Not .GenerateGetPivotData
    End With
End Sub