Chapter 14
Advanced pivot table tips and techniques

In this chapter, you will learn 21 pivot table tricks and techniques:

• Tip 1: Force pivot tables to refresh automatically

• Tip 2: Refresh all pivot tables in a workbook at the same time

• Tip 3: Sort data items in a unique order, not ascending or descending

• Tip 4: Use (or prevent using) a custom list for sorting your pivot table

• Tip 5: Use pivot table defaults to change the behavior of all future pivot tables

• Tip 6: Turn pivot tables into hard data

• Tip 7: Fill the empty cells left by row fields

• Tip 8: Add a rank number field to a pivot table

• Tip 9: Reduce the size of pivot table reports

• Tip 10: Create an automatically expanding data range

• Tip 11: Compare tables using a pivot table

• Tip 12: AutoFilter a pivot table

• Tip 13: Force two number formats in a pivot table

• Tip 14: Format individual values in a pivot table

• Tip 15: Format sections of a pivot table

• Tip 16: Create a frequency distribution with a pivot table

• Tip 17: Use a pivot table to explode a data set to different tabs

• Tip 18: Apply restrictions on pivot tables and pivot fields

• Tip 19: Use a pivot table to explode a data set to different workbooks

• Tip 20: Use percentage change from previous for year-over-year

• Tip 21: Do a two-way VLOOKUP with Power Query

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!

Tip 1: Force pivot tables to refresh automatically

In some situations, you might need to have pivot tables refresh themselves automatically. For instance, suppose you create a pivot table report for your manager. You might not be able to trust that he will refresh the pivot table when needed.

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 box that appears, select the Data tab.

3. Select the check box for 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.

Tip

The Refresh Data When Opening The File Property must be set for each pivot table individually.

Tip 2: Refresh all pivot tables in a workbook at the same time

When you have multiple pivot tables in a workbook, refreshing all of them can be bothersome. There are several ways to avoid the hassle of manually refreshing multiple pivot tables. Here are a few options:

• 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 box. Here, select the Data tab and select the check box for 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.

• Option 3—Click the Refresh All button on the Data tab, as shown in Figure 14-1. The downside to this method is that it also refreshes an external data connection, which can be a lengthy process. But if you do not have any external data connections, this is an easy way to refresh all of the tables.

Note

Revisit Chapter 12, “Enhancing pivot table reports with macros,” to get more detail on using macros with pivot tables.

FIGURE 14-1 The Refresh All button in the Data tab will refresh all pivot tables and external data connections.

Tip 3: Sort data items in a unique order, not ascending or descending

Figure 14-2 shows the default sequence of regions in a pivot table report. Alphabetically, the regions are shown in the sequence Midwest, North, South, and West. If your company is based in California, company tradition might dictate that the West region be shown first, followed by Midwest, North, and South. Unfortunately, neither an ascending sort order nor a descending sort order can help you with this.

FIGURE 14-2 Company tradition dictates that the Region field should be in West–Midwest–North–South sequence.

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, as shown in Figure 14-3. The remaining regions move over to the next three columns.

FIGURE 14-3 After typing WEST in B4, the numbers for West move from column E to column B.

Tip 4: Using (or prevent using) a custom list for sorting your pivot table

The technique of typing WEST in cell B4 in the previous tip is a great way to impress your friends at a bar, but it would be tedious to do this over and over. If your source data is stored in a worksheet and not in the data model or external data, you can permanently have all future pivot tables appear in the order of West, Midwest, North, and South by creating a custom list.

Type the regions in consecutive cells in the proper sequence. Select the cells. Go to File, Options, Advanced. Scroll all the way to the bottom and click Edit Custom Lists.

Excel opens the Custom Lists dialog box. This is where Excel stores lists such as January, February, March and Monday, Tuesday, Wednesday.

If you remembered to select your list before opening the dialog box, simply click the Import button to add a new list (see Figure 14-4).

FIGURE 14-4 Add a new custom list with regions in the proper sequence.

Custom lists are specific to one computer and one version of Excel. If you create a custom list in Excel 2013, it will not be available in Excel 2016. But if you are only using one version of Excel, then all future pivot tables will be sequenced using the custom list.

Note: What if you later add a Canada and Mexico region? Items that are in the custom list will appear first, followed by items missing from the custom list in alphabetical sequence. Your report will show West, Midwest, North, South, Canada, and Mexico. The solution in this case is to open the Custom Lists dialog box, edit the list, and click Add to memorize the new list.

Note in Figure 14-4 that two lists contain “West.” If there is a tie, the list nearest the bottom of the Custom Lists wins.

Sometimes you might have the opposite problem. Say that your company is really chummy and all the team members are reported by their first name. For some reason, Jan or May or Friday keeps showing up before Andy, Barb, and Chris (see Figure 14-5).

FIGURE 14-5 Jan keeps showing up at the top of your pivot table reports.

Notice in Figure 14-4 that the third list already includes Jan and May. You can’t see it, but the second list includes Friday. Because those people appear in a custom list, they get sorted before all of the people “missing” from the custom list.

I did ask the Pivot Table team at Microsoft why they split Jan and May with Friday. It is not alphabetical. It is not arranged by list from the bottom of the dialog box to the top. The apparent answer is that no one knows anymore or that they don’t have time to research a question that is only of interest to a few people.

One solution: Stop hiring people named May, Jan, or Friday. A less stressful solution: Display the Options dialog box for a pivot table. On the Totals & Filters tab, clear the entry for Use Custom Lists When Sorting (see Figure 14-6).

Figure 14-6 You can stop the sorting by custom lists using this setting.

If Jan or May will permanently be a problem, you can turn this setting off for all future pivot tables. See Tip 5.

Tip 5: Use pivot table defaults to change the behavior of all future pivot tables

Do you have a couple of pivot table default behaviors that you always turn off? For me, I always want empty cells to show with 0. I always want to start in Tabular layout with Repeat All Item Labels turned on. Maybe you need to turn off Use Custom Lists When Sorting.

In the summer of 2017, Excel added the Edit Default Layout button for pivot tables. Go to File, Options, Data to find the Edit Default Layout button.

Initially, the Edit Default Layout dialog box only offers a few settings. But if you click the PivotTable Options button, as shown in Figure 14-7, you can permanently change all the settings in PivotTable Options.

FIGURE 14-7 Change defaults for future pivot tables.

Tip 6: Turn pivot tables into hard data

Say that you created a pivot table in order to summarize and shape data. You do not want to keep the source data, nor do you want to keep the pivot table with all its overhead.

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.

Tip

You might want to consider removing any subtotals before turning a pivot table into hard data. Subtotals typically aren’t very useful when you are creating a stand-alone data set.

To remove the subtotals from a pivot table, first identify the field for which subtotals are being calculated. Then right-click the field’s header (either in the pivot table itself or in the PivotTable Fields list), and select Field Settings. Selecting this option opens the Field Settings dialog box. Here, you change the Subtotals option to None. After you click OK, your subtotals are removed.

Tip 7: Fill the empty cells left by row fields

When you turn a pivot table into hard data, you are left not only with the values created by the pivot table but also the pivot table’s data structure. For example, the data in Figure 14-8 came from a pivot table that had a Tabular layout.

Figure 14-8 It would be impractical to use this data anywhere else without filling in the empty cells left by the row field.

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 2019 to fix this problem effectively.

Option 1: Implement the Repeat All Item Labels feature

The first option for easily filling the empty cells left by row fields is to apply the Repeat Item Labels functionality. This feature ensures that all item labels are repeated to create a solid block of contiguous cells. To implement this feature, place your cursor anywhere in your pivot table. Then go to the ribbon and select Design, Report Layout, Repeat All Item Labels (see Figure 14-9).

FIGURE 14-9 The Repeat All Item Labels option enables you to show your pivot data in one contiguous block of data.

Figure 14-10 shows what a pivot table with this feature applied looks like.

FIGURE 14-10 The Repeat All Item Labels option fills all cells with data items.

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

Option 2: Use Excel’s Go To Special functionality

The other way to easily fill the empty cells left by row fields involves using Excel’s Go To Special functionality.

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. Choose Home, Find & Select, Go To Special. (Alternatively, use Ctrl+G or F5 to open the Go To dialog box and then press the Special button.) This activates the Go To Special dialog box, which is a powerful feature that enables you to modify your selection based on various conditions (see Figure 14-11). In this dialog box, choose the Blanks option and click OK. Now only the blank cells in the selection are selected.

FIGURE 14-11 Using the Go To Special dialog box enables you to select all the blank cells to be filled.

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-12 shows, with these few keystrokes, you enter a formula to fill in all the blank cells at once.

FIGURE 14-12 Pressing Ctrl+Enter enters the formula in all selected cells.

At this point, there is no need for the formulas. You 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.

Tip 8: Add a rank number field to a pivot table

When you are sorting and ranking a field with a large number of data items, it can be difficult to determine the number ranking of the data item you are currently analyzing. Furthermore, you might want to turn your pivot table into hard values for further analysis. An integer field that contains the actual rank number of each data item could be helpful in analysis outside the pivot table.

Start with a pivot table like the one shown in Figure 14-13. Notice that the same data measure, Sum Of Sales_Amount, is shown twice.

Figure 14-13 Start with a pivot table where the data value is listed twice.

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

FIGURE 14-14 Adding a rank field is simple with the Show Values As option.

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

Figure 14-15 Your final pivot table, with ranking applied.

Tip 9: Reduce the size of pivot table reports

When you initiate the creation of a pivot table report, Excel takes a snapshot of your data set and stores it in a pivot cache, which is a special memory subsystem in which your data source is duplicated for quick access. That is to say, Excel literally makes a copy of your data and then stores it in a cache that is attached to your workbook.

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.

Delete the source data worksheet

If your workbooks have both your pivot table and your source data worksheet, you are wasting space. That is, you are essentially distributing two copies of the same data.

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.

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!

Tip 10: Create an automatically expanding data range

You will undoubtedly encounter situations in which you have pivot table reports that are updated daily (that is, records are constantly being added to the source data). When records are added to a pivot table’s source data set, you must redefine the range that is captured before the new records are brought into the pivot table. Redefining the source range for a pivot table once in a while is no sweat, but when the source data is changed on a daily or weekly basis, it can start to get bothersome.

The solution is to turn your source data table into an Excel table using Ctrl+T. This works even if you convert the table after the pivot table has been built. 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 one cell in your source data and then click the Table icon on the Insert tab (see Figure 14-16). 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.

FIGURE 14-16 Convert your source data into an Excel table.

Tip

Keep in mind that although you won’t have to redefine the source range anymore, you will still need to trigger a Refresh in order to have your pivot table show the current data.

Tip 11: Compare tables using a pivot table

If you’ve been an analyst for more than a week, you’ve been asked to compare two separate tables to come up with some brilliant analysis about the differences between them. This is a common scenario where leveraging a pivot table can save you some time.

Say that you have two tables that show customers in 2011 and in 2012. Figure 14-17 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.

FIGURE 14-17 You need to compare these two tables.

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-18, a column called Fiscal Year serves this purpose.

FIGURE 14-18 Combine your tables into one table.

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-19, years are in the column area and customers are in the row area. The data area contains the count records for each customer name.

FIGURE 14-19 Create a pivot table to get an easy-to-read visual comparison of the two data sets.

As you can see in Figure 14-19, you instantly get a visual indication of which customers are only in the prior year table, which are in the current year table, and which are in both tables.

Instead of Count Of Customer, you could add Revenue to the Values area and see a comparison of this year’s revenue to last year’s revenue.

Tip 12: AutoFilter a pivot table

The conventional wisdom is that you can’t apply the Filter found on the Data tab to a pivot table. Technically, that’s true. But there is a way to trick Excel into making it happen.

The trick is to place your cursor directly adjacent to the last title in the pivot table, as shown in Figure 14-20. (You can actually use any empty cell immediately to the right of your pivot table.) Once you have it there, you can go to the ribbon, select Data, and then select Filter.

Figure 14-20 Place your cursor just outside your pivot table.

At this point, you have Filter drop-down arrows added to C3 and D3! You can now do cool things like apply a custom filter to find all customers with above-average transaction counts (see Figure 14-21).

FIGURE 14-21 With a Data filter implemented, you can take advantage of custom filtering that’s not normally available with pivot tables.

If you have product lines stretching across the top of your report, you might filter to show the top ten customers for one specific product (see Figure 14-22).

FIGURE 14-22 Choose a Top 10 filter from one column by using the filter trick.

Another use is to create a Top 10 report using the Top 10 filter but ask for the Top 11 values, as shown in Figure 14-23. To the Data filter, the largest “customer” is the Grand Total row. Asking for customers 2 through 11 will show the customers ranked 1 through 10. The advantage of this method is that the Percentage Of Total column will be as a percentage of the Grand Total instead of the percentage of only 10 customers.

FIGURE 14-23 Using the Filter option and asking for top 11 customers will get you the grand total and 10 customers.

Caution

Using the filter from the Data tab is not supported by Microsoft. If you refresh or change the shape of your pivot table, expect to have to reapply any filters created with this method.

Also, to turn off the filters, you have to select a cell immediately to the right of your pivot table.

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

Tip 13: Force two number formats in a pivot table

Every now and then, you have to deal with a situation where a normalized data set makes it difficult to build an appropriate pivot table. For example, the data set shown in Figure 14-24 contains metrics information for each market. Notice that there is a column that identifies the measure, and there is a column that specifies the corresponding value.

FIGURE 14-24 This metric table has many different data types in one Value field.

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 Value 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 Value field. After all, the rule is one field, one number format.

As you can see in Figure 14-25, trying to set the number format for the percentage measures also changes the format for the measures that are supposed to be straight numbers.

Figure 14-25 You can have only one number format assigned to each data measure.

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 box, click Custom and then enter the following syntax in the Type input box (see Figure 14-26):

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

FIGURE 14-26 Apply a custom number format, telling Excel to format any number less than 1.5 as a percentage.

The result, shown in Figure 14-27, 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.

FIGURE 14-27 Two formats in one data field. Amazing!

Tip 14: Format individual values in a pivot table

If you have Office 365, a new feature lets you format certain values in a pivot table and that formatting will stick even after pivoting or refreshing the pivot table.

For example, in Figure 14-28, right-click the cell containing East sales of Cherry and choose Format Cells. Apply a red fill to that cell.

Figure 14-28 Use Format Cells in the right-click menu to format one or more cells in the Values area of a pivot table.

When you pivot the data, the red formatting sticks with that cell (see Figure 14-29). Move Product from columns to rows. Move Region from rows to columns. After each move, the red cell moves with the $13,036 value.

FIGURE 14-29 After pivoting, the formatting sticks with that value.

Even more amazing: Add a new row field so that the East Cherry values occupy more cells. The red fill expands to include the new cells. I am not sure why the East Total for Cherry is not red in Figure 14-30.

FIGURE 14-30 Even if the pivot table expands, the red formatting stays with East Cherry.

The red formatting will stick if East is removed due to a filter or slicer. But if you completely remove Product or Region out of the pivot table, the formatting will be lost.

This new formatting trick in Office 365 would make the previous solution simpler. Rather than using a custom number format of [>=1.5]$#,##0;[<1.5]0.0%, you can simply select all of the cells in the Revenue column. Right-click. It is tempting to choose Number Format, but that will not work. Instead, choose Format Cells (see Figure 14-31). Apply a currency format.

FIGURE 14-31 To change all of the number formats for Revenue values, select those cells, right-click, and select Format Cells.

Tip 15: Format sections of a pivot table

While the new formatting trick in Tip 14 is great for Office 365, there is another legacy way to format areas of a pivot table available in all versions of Office.

To enable the method, open the Select drop-down menu on the Analyze tab and choose Enable Selection (see Figure 14-32).

FIGURE 14-32 The legacy Selection feature is turned off by default.

Once you have enabled selection, hover the mouse over the left portion of any cell in a pivot table. Watch for the mouse cursor to change to a right-facing black arrow. In Figure 14-33, hovering over the left quarter of cell H5 will select all Rep total rows.

FIGURE 14-33 Hover over the left edge of a cell until you see the black arrow.

Click on the left side of Andy Total and all similar-level rows in the pivot table are selected. Apply a fill color and you’ve effectively formatted all of the sales rep subtotal rows, as shown in Figure 14-34.

FIGURE 14-34 When you click on the left side of Andy Total, all the cells at a similar level are selected.

There are many ways to select all. If you hover over a Cherry cell in column I of Figure 14-35, all the Cherry cells will be selected.

FIGURE 14-35 Click on the left edge of one Cherry cell to select all the Cherry cells and their associated sales.

Tip 16: Create a frequency distribution with a pivot table

If you’ve created a frequency distribution with the FREQUENCY function, you know it can quickly devolve into a confusing mess. The fact that it’s an array formula doesn’t help matters. Then there’s the Histogram functionality you find in the Analysis ToolPak, which doesn’t make life much better. Each time you have to change your bin ranges, you have to restart the entire process again.

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-36, the Sales_Amount field is placed in the Rows area.

FIGURE 14-36 Place your data measure in the Rows area.

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

FIGURE 14-37 Use the Grouping dialog box to create your frequency intervals.

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

In Figure 14-38, 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.

FIGURE 14-38 The frequency distribution of customer transactions by dollar amount.

Tip 17: Use a pivot table to explode a data set to different tabs

One of the most common requests an analyst gets is to create a separate pivot table report for each region, market, manager, or whatever. These types of requests usually lead to a painful manual process in which you copy a pivot table onto a new worksheet and then change the filter field to the appropriate region or manager. You then repeat this process as many times as you need to get through each selection.

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

Figure 14-39 Start with a pivot table that contains a filter field.

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, click the Options drop-down menu, and then select Show Report Filter Pages, as shown in Figure 14-40.

FIGURE 14-40 Click the Show Report Filter Pages button.

A dialog box 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-41 illustrates the result. Note that the newly created tabs are named to correspond with the filter item shown in the pivot table.

FIGURE 14-41 With just a few clicks, you can have a separate pivot table for each market!

Note

Be aware that you can use Show Report Filter Pages on only one filter field at a time. This feature will not automatically replicate any pivot charts.

Tip 18: Apply restrictions on pivot tables and pivot fields

I often send pivot tables to clients, coworkers, managers, and other groups of people. In some cases, I’d like to restrict the types of actions users can take on the pivot table reports I send them. The macros outlined in this section demonstrate some of the protection settings available via VBA.

Pivot table restrictions

The PivotTable object exposes several properties that allow you, as a developer, to restrict different features and components of a 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 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-42 that the Refresh, Pivot Table Options, and Show Field List commands are grayed out.

FIGURE 14-42 The commands for restricted features will be grayed out in all menus.

Pivot field restrictions

Like pivot table restrictions, pivot field restrictions enable you to restrict the types of actions users can take on the pivot fields in a pivot table. The macro shown in this section demonstrates some of the protection settings available via VBA.

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 menus on each pivot field.

You can independently set any or all 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.

Tip 19: Use a pivot table to explode a data set to different workbooks

Imagine that you have a data set with 50,000+ rows of data. You have been asked to create a separate workbook for each market in this data set. In this tip, you’ll discover how you can accomplish this task by using a pivot table and a little VBA.

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

FIGURE 14-43 Create a simple pivot table with one data field and a filter.

As you know, you can manually select a market in the page/filter field and then double-click Sum of Sales_Amount. 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
Dim FName as variant

'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 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
 FName = ThisWorkbook.Path & "\" & PvtItem.Name & ".xlsx"
 ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlOpenXMLWorkbook
 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-44, you see the trigger cell in A4.

FIGURE 14-44 After running the macro, you will have a separate workbook for each filtered dimension.

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

Tip 20: Use percentage change from previous for year-over-year

If you have grouped daily dates to years, you are not allowed to add calculated fields or calculated items. With the pivot table in Figure 14-45, you have revenue from last year and this year in columns B and C. You would like to show a % change in column D.

FIGURE 14-45 Build a report with Revenue in the Values area twice.

One hack is to remove the grand total that normally appears in column D and build formulas outside the pivot table, such as =C6/B6-1. This is not the best method, because you have to recopy the formula if the size of your pivot table changes.

Instead, use this method:

1. Drag Revenue to the Values area a second time.

2. In the Columns area, move the new Σ Values tile above the Date tile. This will produce the report shown in Figure 14-45.

3. Change the calculation for the second Revenue field. Double-click cell D4 in Figure 14-45 to open the Value Field Settings.

4. In the Value Field Settings, type a new name such as % Change.

5. Choose the Show Values As tab.

6. Open the Show Values As drop-down menu and select Choose % Difference From.

7. For the Base Field, choose Date.

8. For the Base Item, choose (previous). The settings from steps 4 through 8 are shown in Figure 14-46.

FIGURE 14-46 Change the calculation for the second Revenue to % Difference From.

9. The results are correct, but column D will be blank. Select one cell in column D and change the column width to 0.1. This will allow the % Change heading from D4 to show up in the pivot table (see Figure 14-47).

FIGURE 14-47 The percentage change column is natively part of the pivot table.

10. Before you swap any fields in the row area, right-click the pivot table and choose PivotTable Options. On the Layout & Format tab, clear the check box for Autofit Column Widths On Update.

11. You can now quickly change the report. Unselect Sector. Choose Product. You can repeat to show year-over-year data for Region, Customer, or Product (see Figure 14-48).

Figure 14-48 Change the row field to produce a different year-over-year report.

Tip 21: Do a two-way VLOOKUP with Power Query

A note in Chapter 10, “Unlocking features with the Data Model and Power Pivot,” mentioned that you could do a VLOOKUP while importing data with Power Query. This example will show you how to do a two-way VLOOKUP while importing with Power Query.

The source data in Figure 14-49 is a large sales database in columns F through H. For each sale, you have a Region and a Product. You need to report sales by product manager.

FIGURE 14-49 The table to assign product managers is shown in A1:D6.

The product manager matrix is shown in A1:D6. Sometimes a manager handles one product across all regions (such as Johnny for Apples). But other assignments are not straightforward.

The strategy is to process the lookup table first using Power Query and to leave it as a connection-only query.

You will then move the Sales data quickly through Power Query to create a second connection.

Finally, you will use a Merge query to join the two of them:

1. Select A1 and use Data, From Table/Range. The Power Query Editor opens. The Product column is selected by default.

2. In Power Query, choose Transform, Unpivot, Unpivot other columns.

3. Right-click the Attribute column and rename to Region.

4. Go to the Home tab in Power Query. Be very careful to not click Close & Load. Instead, open the drop-down menu at the bottom of Close & Load. Choose Close & Load To… (see Figure 14-50).

FIGURE 14-50 Unpivot the data. Choose Close & Load To….

5. The Import Data dialog box appears. Choose Only Create Connection and click OK (see Figure 14-51). When you return to Excel, the Queries & Connections panel appears. A single query of ProductManagers is listed in the panel.

FIGURE 14-51 Only Create Connection.

Steps 6 and 7 are similar to steps 1 through 5, so there will not be any figures.

6. Select one cell in the Sales table. Select Data, From Table/Range.

7. When Power Query opens, there are no changes needed. Select Home, Close & Load To….

8. In the Import Data dialog box, choose Only Create Connection. Click OK. You should now see two queries in the Queries & Connections panel (see Figure 14-52).

Figure 14-52 You’ve created connections to the data and the lookup table.

9. Select Data, Get Data, Combine Queries, Merge, as shown in Figure 14-53.

FIGURE 14-53 Choose to merge the two queries.

Note

A Merge query is used to join two data sets with different fields. An Append query is used to join two data sets with the same fields.

10. The Merge dialog box is confusing. Make sure to follow steps 10 through 17 and then compare to Figure 14-54 before clicking OK. In the top drop-down menu, choose the Sales table.

11. In the bottom drop-down menu, choose the ProductManagers table. The next steps are not obvious, and there are no instructions in the dialog box.

12. In the data preview for Sales, click the Region heading.

13. In the data preview for Sales, Ctrl+click the Product Heading.

14. In the data preview for ProductManagers, click the Region heading (even though it is listed second).

15. In the data preview for ProductManagers, Ctrl+click the Product heading.

16. Note the small “1” and “2” in each heading.

17. For the Join Kind, choose Left Outer Join. The dialog box reports that all 1535 rows have been matched (see Figure 14-54).

FIGURE 14-54 Choose the two tables and the key fields in common between them.

18. Click OK. The Power Query Editor opens. Where you expect to see the product manager, you see a column that says Table over and over (see Figure 14-55).

FIGURE 14-55 The appended query is loaded as a table.

19. Click the Expand icon next to the ProductManagers heading. Clear the fields that are duplicates: Product and Region. Clear the check box for Use Original Column Name As Prefix (see Figure 14-56).

FIGURE 14-56 Choose which fields from the lookup table to show.

20. Rename the Value column to be Product Manager. Drag that column to the left of Revenue (see Figure 14-57).

FIGURE 14-57 The two tables have been joined.

21. Choose Home, Close & Load. Your data will be returned to a new worksheet.

22. Build a pivot table to report sales by product manager (see Figure 14-58).

Figure 14-58 The final pivot table shows sales by product manager. Recall that this data started out in different tables.

It would have been very easy in this example to replace the Sales table with a Data, Get Data, From File, From Workbook. Every time the underlying data changed, a simple Data, Refresh All would refresh all three queries. You will have to refresh the pivot table after the Refresh All. But it will be two steps to update instead of many steps.

Also, it would have been possible in Power Query to group by Product Manager and Sum Of Sales to produce a finished query that had all of the same data as F4:G9 in Figure 14-58.

Power Query is the real game-changer in Excel. This book about pivot tables has made significant use of Power Query in several locations:

• Power Query was used in Chapter 2 while cleaning data before pivoting.

• Power Query was used in Chapter 5 to create a calculated column.

• Power Query was used extensively in Chapter 7.

• Power Query was used in Chapter 10 for dealing with more than 1,048,576 rows of data.

• Power Query was mentioned as a way to get two data sets into 3D Map in Chapter 11.

• Chapter 12 proposed Power Query is an alternative to the VBA Macro Recorder.

• Chapter 14 used Power Query as the lucky twenty-first tip to do a two-way VLOOKUP before pivoting.

Bottom line: You should really take a look at Power Query.

Next steps

In Chapter 15, “Dr. Jekyll and Mr. GetPivotData,” you’ll learn about one of the most hated pivot table features: the GetPivotData function. However, you’ll also learn how to use this function to create refreshable reports month after month.