14. Advanced Pivot Table Tips and Techniques

In this chapter, you’ll discover some techniques that provide unique solutions to some of the most common pivot table problems. Take some time to glance at the topics covered here. Who knows? You might find a few unique tips that can help you tackle some of your pivot table conundrums!

You can force each pivot table to automatically refresh when the workbook opens by following these steps:

1. Right-click the pivot table and select PivotTable Options.

2. In the PivotTable Options dialog that appears, select the Data tab.

3. Place a check next to Refresh Data When Opening the File property.

When this property is activated, the pivot table refreshes itself each time the workbook in which it’s located is opened.

Option 1—You can configure each pivot table in a workbook to automatically refresh when the workbook opens. To do so, right-click the pivot table and select PivotTable Options. This activates the PivotTable Options dialog. Here, select the Data tab and place a check next to Refresh Data When Opening the File property. After you have configured all pivot tables in the workbook, they will automatically refresh when the workbook is opened.

Option 2—You can create a macro to refresh each pivot table in the workbook. This option is ideal when you need to refresh pivot tables on demand rather than only when the workbook opens. To do this, start recording a macro, and while the macro is recording, go to each pivot table in your workbook and refresh. After all pivot tables are refreshed, stop recording. The result is a macro that can be fired any time you need to refresh all pivot tables.

Revisit Chapter 12, “Enhancing Pivot Table Reports with Macros,” to get more detail on using macros with pivot tables.

Option 3—You can use VBA to refresh all pivot tables in a workbook on demand. This option can be used when it is impractical to record and maintain macros that refresh all pivot tables. This approach entails the use of the RefreshAll method of the Workbook object. To employ this technique, start a new module and enter the following code:

Sub RefreshAll ()
ThisWorkbook.RefreshAll
End Sub

You can now call this procedure any time you want to refresh all pivot tables within the workbook.

You can rearrange data items in your pivot table manually by simply typing the exact name of the data item where you would like to see its data. You can also drag the data item where you want it.

To solve the problem in this example, you simply type West in cell B4 and then press Enter. The pivot table responds by resequencing the regions. The $3 million in sales for the West region automatically moves from column E to column B. The remaining regions move over to the next three columns.

Turning a pivot table into hard data enables you to utilize the results of the pivot table without having to deal with the source data or a pivot cache. How you turn the pivot table into hard data depends on how much of the pivot table you are going to copy.

If you are copying just a portion of a pivot table, do the following:

1. Select the data you want to copy from the pivot table, right-click, and select Copy.

2. Right-click anywhere on a spreadsheet and select Paste.

If you are copying an entire pivot table, follow these steps:

1. Select the entire pivot table, right-click, and select Copy. Alternatively, you can choose the Analyze tab, click Select, and then click Entire PivotTable.

2. Right-click anywhere on a spreadsheet and select Paste Special.

3. Select Values and then click OK.

Notice that the Market field kept the same row structure it had when this data was in the row area of the pivot table. It would be unwise to use this table anywhere else without filling in the empty cells left by the row field, but how do you easily fill these empty cells?

The next sections discuss two options provided by Excel 2016 to fix this problem effectively.

Figure 14.4 shows what a pivot table with this feature applied looks like.

Now you can turn this pivot table into hard values, and you will end up with a contiguous table of data without gaps.

You start by converting your pivot table into hard data as explained in Tip 4. Next, select the range in columns A and B that extends from the first row with blanks to the row just above the grand total. In the present example, this is A4:B100. Press the F5 key to activate the Go To dialog. In the lower-left corner of the Go To dialog, choose the Special button. This activates the Go To Special dialog, which is a powerful feature that enables you to modify your selection based on various conditions (see Figure 14.5). In this dialog, choose the Blanks option and click OK. Now only the blank cells in the selection are selected.

Enter a formula to copy the pivot item values from the cell above to the blank cells. You can do this with four keystrokes: Type an equal sign, press the up arrow key, and hold down the Ctrl key while pressing Enter. The equal sign tells Excel that you are entering a formula in the active cell. Pressing the up arrow key points to the cell above the active cell. Pressing Ctrl+Enter tells Excel to enter a similar formula in all the selected cells instead of just the active cell. As Figure 14.6 shows, with these few keystrokes, you enter a formula to fill in all the blank cells at once.

At this point, there is no need to the formulas. You can will want to convert those formulas to values. Reselect the original range A4:B100. You can then press Ctrl+C to copy and choose Edit, Paste Special, Values to convert the formulas to values. This method provides a quick way to easily fill in the Outline view provided by the pivot table.

Start with a pivot table like the one shown in Figure 14.7. Notice in Figure 14.7 that the same data measure, Sum of Sales_Amount, is shown twice.

Right-click the second instance of the data measure, select Show Values As, and then select Rank Largest to Smallest (see Figure 14.8).

When your ranking is applied, you can adjust the labels and formatting so it looks as shown in Figure 14.9. This gives you a clean-looking ranking report.

Of course, the benefit you get from a pivot cache is optimization. Any changes you make to the pivot table report, such as rearranging fields, adding new fields, and hiding items, are made rapidly and with minimal overhead.

The downside of the pivot cache is that it basically doubles the size of a workbook. So every time you make a new pivot table from scratch, you essentially add to the file size of your workbook.

You can delete your source data, and your pivot table will function just fine. After you delete the source data, when you save the pivot table, the file shrinks. Your clients can use the pivot table as normal, and your workbook is half as big. The only functionality you lose is the ability to refresh the pivot data because the source data is not there.

So what happens if your clients need to see the source data? Well, they can simply double-click the intersection of the row and column grand totals. This tells Excel to output the contents of the pivot table’s cache into a new worksheet. So, with one double-click, your clients can re-create the source data that makes up the pivot table!

The solution is to turn your source data table into an Excel table before you create a pivot table. Again, Excel tables enable you to create a defined range that automatically shrinks or expands with the data. This means that any component, chart, pivot table, or formula tied to that range can keep up with changes in your data.

To implement this trick, simply highlight your source data and then click the Table icon on the Insert tab (see Figure 14.10). Confirm the range to be included in your table and then click OK.

After your source data has been converted to an Excel table, any pivot table you build on top of it automatically includes all records when your source data expands or shrinks.

Say that you have two tables that show customers in 2011 and in 2012. Figure 14.11 shows that these are two separate tables. For this example, the tables were made small for instructional purposes, but imagine that you’re working with something bigger here.

The idea is to create one table you can use to pivot. Be sure you have a way to tag which data comes from which table. In Figure 14.12, a column called Fiscal Year serves this purpose.

After you have combined the tables, use the combined data set to create a new pivot table. Format the pivot table so that the table tag (the identifier that tells which table the data came from) is in the column area of the pivot table. In Figure 14.13, years are in the column area and customers are in the row area. The data area contains the count records for each customer name.

As you can see in Figure 14.13, you instantly get a visual indication of which customers are only in the 2011 table, which are in the 2012 table, and which are in both tables.

The trick is to place your cursor directly adjacent to the last title in the pivot table, as shown in Figure 14.14. Once you have it there, you can go to the application menu, select Data, and then select AutoFilter.

At this point, you have AutoFilter on your pivot table! You can now do cool things like apply a custom AutoFilter to find all customers with above-average transaction counts (see Figure 14.15).

This is a fantastic way to add an extra layer of analytical capabilities to pivot table reports.

Although this is generally a nicely formatted table, notice that some of the measures are meant to be Number format, whereas others are meant to be Percentage. In the database where this data set originated, the Values field is a Double data type, so this works.

The problem is that when you create a pivot table out of this data set, you can’t assign two different number formats for the Values field. After all, the rule is one field, one number format.

So as you can see in Figure 14.17, trying to set the number format for the percentage measures also changes the format for the measures that are supposed to be straight numbers.

The solution is to apply a custom number format that formats any value greater than 1.5 as a number and any value less than 1.5 as a percentage. In the Format Cells dialog, click Custom and then enter the following syntax in the Type input box (see Figure 14.18):

[>=1.5]$#,##0;[<1.5]0.0%

The result, shown in Figure 14.19, is that each measure is now formatted appropriately. Obviously, you have to get a little lucky with the parameters of the situation you’re working in. Although this technique doesn’t work in all scenarios, it does open up some interesting options.

In this tip, you’ll learn how to use a pivot table to quickly implement a simple frequency distribution.

First, you need to create a pivot table where the data values are plotted in the Rows area (not the Values area). Notice that in Figure 14.20, the Sales_Amount field is placed in the Rows area.

Next, right-click any value in the Rows area and select Group. In the Grouping dialog (shown in Figure 14.21), set the start and end values and then set the intervals. This essentially creates the frequency distribution.

After you click the OK button, you can leverage the result to create a distribution view of your data.

In Figure 14.22, you can see that Customer_Name has been added to get a frequency distribution of the number of customer transactions by dollar amount.

The obvious benefit of this technique is you can use the pivot table’s Report Filter to interactively filter the data based on other dimensions, such as Region and Market. Also, unlike with the Analysis ToolPak Histogram tool, you can quickly adjust your frequency intervals by simply right-clicking any number in the Rows area and selecting Group.

Creating separate pivot table reports is one area where Excel really comes to the rescue. Excel has a function called Show Report Filter Pages that automatically creates a separate pivot table for each item in the filter fields. To use this function, simply create a pivot table with a filter field, as shown in Figure 14.23.

Place your cursor anywhere on the pivot table and then go up to the ribbon to select the Analyze tab. On the Analyze tab, go to the PivotTable group and click the Options drop-down and then select Show Report Filter Pages, as shown in Figure 14.24.

A dialog opens, enabling you to choose the filter field for which you would like to create separate pivot tables. Select the appropriate filter field and click OK.

Your reward is a sheet for each item in the filter field, with each one containing its own pivot table. Figure 14.25 illustrates the result. Note that the newly created tabs are named to correspond with the filter item shown in the pivot table.

EnableWizard—Setting this property to False disables the PivotTable Tools context menu that normally activates when you click inside a pivot table.

EnableDrilldown—Setting this property to False prevents users from getting to detailed data by double-clicking a data field.

EnableFieldList—Setting this property to False prevents users from activating the field list or moving pivot fields around.

EnableFieldDialog—Setting this property to False disables the users’ ability to alter the pivot field via the Value Field Settings dialog box.

PivotCache.EnableRefresh—Setting this property to False disables the ability to refresh the pivot table.

You can independently set any or all of these properties to either True or False. The following macro applies all the restrictions to the target pivot table:

Click here to view code image

Sub ApplyPivotTableRestrictions ()

'Step 1: Declare your Variables
    Dim pt As PivotTable

'Step 2: Point to the PivotTable in the activecell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3:  Exit if active cell is not in a PivotTable
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside of a PivotTable."
    Exit Sub
    End If

'Step 4:  Apply Pivot Table Restrictions
    With pt
      .EnableWizard = False
      .EnableDrilldown = False
      .EnableFieldList = False
      .EnableFieldDialog = False
      .PivotCache.EnableRefresh = False
    End With

End Sub

In this macro, step 1 declares the pt pivot table object variable that serves as the memory container for the pivot table.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found. It does this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot table.

Step 3 checks to see whether the pt variable is filled with a pivot table object. If the pt variable is set to Nothing, the active cell was not on a pivot table, and thus no pivot table could be assigned to the variable. If this is the case, the macro says this to the user in a message box, and then it exits the procedure.

Step 4 applies the pivot table restrictions.

Once your chosen features have been restricted, Excel disables the menu commands for the features you turned off. You can see in Figure 14.26 that the Refresh, Pivot Table Options, and Show Field List commands are grayed out.

The PivotField object exposes several properties that allow you as a developer to restrict different features and components of a pivot table:

DragToPage—Setting this property to False prevents users from dragging any pivot field into the report filter area of the pivot table.

DragToRow—Setting this property to False prevents users from dragging any pivot field into the row area of the pivot table.

DragToColumn—Setting this property to False prevents users from dragging any pivot field into the column area of the pivot table.

DragToData—Setting this property to False prevents users from dragging any pivot field into the data area of the pivot table.

DragToHide—Setting this property to False prevents users from dragging pivot fields off the pivot table. It also prevents the use of the right-click menu to hide or remove pivot fields.

EnableItemSelection—Setting this property to False disables the drop-down lists on each pivot field.

You can independently set any or all of these properties to either True or False. The following macro applies all the restrictions to the target pivot table:

Click here to view code image

Sub ApplyPivotFieldRestrictions()

'Step 1: Declare your Variables
    Dim pt As PivotTable
    Dim pf As PivotField

'Step 2: Point to the PivotTable in the activecell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

'Step 3:  Exit if active cell is not in a PivotTable
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside of a PivotTable."
    Exit Sub
    End If

'Step 4:  Apply Pivot Field Restrictions
    For Each pf In pt.PivotFields
        pf.EnableItemSelection = False
        pf.DragToPage = False
        pf.DragToRow = False
        pf.DragToColumn = False
        pf.DragToData = False
        pf.DragToHide = False
    Next pf

End Sub

Step 1 declares two object variables, using pt as the memory container for the pivot table and pf as a memory container for the pivot fields. This allows looping through all the pivot fields in the pivot table.

Step 2 sets the pt variable to the name of the pivot table on which the active cell is found. It does this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.

Step 3 checks whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a pivot table, and thus no pivot table could be assigned to the variable. If this is the case, the macro notifies the user via a message box and then exits the procedure.

Step 4 of the macro uses a For Each statement to iterate through each pivot field and apply all the specified pivot field restrictions.

Once your chosen features have been restricted, Excel disables the menu commands for the features you set to FALSE.

Place the field you need to use as the group dimension (in this case, Market) into the filter field. Place the count of Market into the data field. Your pivot table should look like the one shown in Figure 14.27.

As you know, you can manually select a market in the page/filter field and then double-click Count of Market. This gives you a new tab containing all the records that make up the number you double-clicked. Imagine how you could do this for every market in the Market field and save the resulting tabs to their own workbook.

Using this same concept, you can implement the following VBA that goes through each item in the chosen page field and essentially calls the ShowDetail method for you, creating a raw data tab. The procedure then saves that raw data tab to a new workbook:

Click here to view code image

Sub ExplodeTable()
Dim PvtItem As PivotItem
Dim PvtTable As PivotTable

'Change variables to suit your scenario
        Const strFieldName = "Market"      '<-Change Field Name
         Const strTriggerRange = "B4"      '<-Change Trigger Range

'Set the pivot table name if needed
        Set PvtTable = ActiveSheet.PivotTables("PivotTable1") '<-Change Pivot-
Table Name if Needed

'Start looping through each item in the selected field
        For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems
                PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name
                Range(strTriggerRange).ShowDetail = True

                'Name the temp sheet for easy cleanup later
                ActiveSheet.Name = "TempSheet"

                'copy data to new workbook and delete the temp sheet
                ActiveSheet.Cells.Copy
                Workbooks.Add
                ActiveSheet.Paste
                Cells.EntireColumn.AutoFit

                Application.DisplayAlerts = False
                ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" &
PvtItem.Name &".xls"
                ActiveWorkbook.Close
                Sheets("Tempsheet").Delete
                Application.DisplayAlerts = True

        Next PvtItem

End Sub

To implement this technique, enter this code into a new VBA module. Be sure to change the following constants as appropriate for your scenario:

Const strFieldName—This is the name of the field you want to separate the data by (that is, the field you put in the page/filter area of the pivot table).

Const strTriggerRange—This is essentially the range that holds the one number in the pivot table’s data area. For example, if you look at Figure 14.28, you see the trigger cell in A4.

As you can see in Figure 14.28, running this macro procedure outputs data for each market into its own separate workbook.