15. Dr. Jekyll and Mr. GetPivotData

This chapter shows you a technique that solves many annoying pivot table problems. If you have been using pivot tables for a while, you might have run into the following problems:

Formatting tends to be destroyed when you refresh a pivot table. Numeric formats are lost. Column widths go away.

There is no easy way to build an asymmetric pivot table. Using named sets is one way, but they are available only in a pivot table model, not in a regular pivot cache pivot table.

Excel cannot remember a template. If you frequently have to re-create a pivot table, you must redo the groupings, calculated fields, calculated items, and so on.

The technique shown in this chapter solves all these problems. It is not new. In fact, it has been around since Excel 2002. I have taught Power Excel seminars to thousands of accountants who use Excel 40–60 hours a week. Out of those thousands of people, I have had only three people say that they use this technique.

Ironically, far more than 0.3% of people know of this feature. One common question I get at seminars is “Why did this feature show up in Excel 2002, and how the heck can you turn it off?” This same feature, which is reviled by most Excellers, is the key to creating reusable pivot table templates.

The credit for this chapter must go to Rob Collie, who spent years on the Excel project management team. He spent the Excel 2010 development cycle working on the PowerPivot product. Rob happened to relocate to Cleveland, Ohio. Because Cleveland is not a hotbed of Microsoft developers, Dave Gainer gave me a heads-up that Rob was moving to my area, and we started having lunch.

Rob and I talked about Excel and had some great conversations. During our second lunch, Rob said something that threw me for a loop. He said, “We find that our internal customers use GetPivotData all the time to build their reports, and we are not sure they will like the way PowerPivot interacts with GetPivotData.”

I stopped Rob to ask if he was crazy. I told him that in my experience with about 15,000 accountants, only 3 of them had ever admitted to liking GetPivotData. What did he mean that he finds customers actually using GetPivotData?

Rob explained the key word in his statement: He was talking about internal customers, which are the people inside Microsoft who use Excel to do their jobs. Those people had become incredibly reliant on GetPivotData. He agreed that outside Microsoft, hardly anyone ever uses GetPivotData. In fact, the only question he ever gets outside Microsoft is how to turn off the stupid feature.

I had to know more, so I asked Rob to explain how the evil GetPivotData could ever be used for good purposes. Rob explained it to me, and I use this chapter to explain it to you. However, I know that 99% of you are reading this chapter because of the following reasons:

You ran into the evil GetPivotData.

You turned to the index of this book to find information on GetPivotData.

You are expecting me to tell you how to turn off GetPivotData.

So, let’s start there.

For example, say you build the pivot table shown in Figure 15.1. Those years across the top are built by grouping daily dates into years. You would like to compare this year versus last year. Unfortunately, you are not allowed to add calculated items to a grouped field. So you follow these steps:

1. Add a % Growth heading in cell D4.

2. Copy the formatting from C4 over to D4.

3. In cell D5, type = (an equal sign).

4. Click cell C5.

5. Type / (a slash) for division.

6. Click B5.

7. Type -1 and press Ctrl+Enter to stay in the same cell. Format the result as a percentage. You see that the Midwest region grew by 180.8%. That is impressive growth (see Figure 15.2).

8. After entering your first formula, select cell D5.

9. Double-click the tiny square dot in the lower-right corner of the cell. This is the fill handle, and it copies the formula down to the end of the report.

Immediately, you notice that something is wrong because every region managed to grow by exactly 180.8% (see Figure 15.3).

There is no way that this happens in real life. The data must be fabricated.

Think about the formula you built: 2018 divided by 2017 minus 1. You probably could create that formula with your eyes closed (that is, if you use arrow keys to create formulas instead of your mouse).

This is what makes you not notice something completely evil when you built the formula. When you went through the steps to build the formula, as a rational person, you would expect Excel to create a formula such as =C5/B5-1. However, go back to cell D5 and press the F2 key to look at the formula (see Figure 15.4). Something evil has happened. The simple formula =C5/B5-1 is no longer there. Instead, Excel generated some GetPivotData nonsense. Although the formula works in D5, it does not work when you copy the formula down.

When this occurs, your reaction is something like, “What is GetPivotData, and why is it messing up my report?” Your next reaction is, “How can I turn it off?” You might even wonder, “Why would Microsoft put this evil thing in there?”

Excel started inserting GetPivotData in Excel 2002. After being stung by GetPivotData repeatedly, I grew to hate it. I was thrown for a loop in one of the Power Analyst Boot Camps when someone asked me how it could possibly be used. I had never considered that question. In my mind, and in most other people’s minds, GetPivotData was evil and no good.

If you are one of those users who would just like to avoid GetPivotData, I’ve got great news: There are two ways to do so, as presented in the following two sections.

1. Go to cell D5; type =.

2. Type C5.

3. Type /.

4. Type B5.

5. Type -1.

6. Press Enter.

You have now built a regular Excel formula that you can copy to produce real results, as shown in Figure 15.5.

It is a relief to see that you can still build formulas outside pivot tables that point into a pivot table. I have run into people who simply thought this could not be done.

You might be a bit annoyed that you have to abandon your normal way of entering formulas. If so, the next section offers an alternative.

In Excel 2016, follow these steps:

1. Move the cell pointer back inside a pivot table so that the PivotTable Tools tabs appear.

2. Click the Analyze tab.

3. Notice the Options icon on the left side of the ribbon (see Figure 15.6). Do not click the icon; rather, next to the options icon, click the drop-down arrow.

4. Inside the Options drop-down is the choice Generate GetPivotData (see Figure 15.7). By default, this option is selected. Click that item to clear this check box.

The previous steps assume that you have a pivot table in the workbook that you can select in order to access the PivotTable Tool tabs. If you don’t have a pivot table in the current workbook, you can use File, Options. In the Formulas category, uncheck Use GetPivotData Functions for PivotTable References.

I have a theory about this that I came up with during the Excel 2007 launch. I had written many books about Excel 2007—somewhere around 1,800 pages of content. When the Office 2007 launch events were happening around the country, I was given an opportunity to work at the event. I watched with interest when the presenter talked about the new features in Excel 2007.

There were at least 15 amazing features in Excel 2007. The presenter took three minutes and glossed over perhaps 2.5 of the features.

I was perplexed. How could Microsoft marketing do such a horrible job of showing what was new in Excel? Then I realized that this must always happen. Marketing asks the development team what is new. The project manager gives them a list of 15 items. The marketing guy says something like, “There is not room for 15 items in the presentation. Can you cut 80% of those items out of the list and give me just the ones with glitz and sizzle?”

The folks who worked on GetPivotData certainly knew that GetPivotData would never have enough sizzle to make it into the marketing news about Excel 2002. So, by making it the default, they hoped someone would notice GetPivotData and try to figure out how it could be used. Instead, most people, including me, just turned it off and thought it was another step in the Microsoft plot to make our lives miserable by making it harder to work in Excel.

Figure 15.8 illustrates a typical pivot table experience: You start with raw data. You then produce a pivot table and use all sorts of advanced pivot table tricks to get it close. You conclude by converting the pivot table to values and performing the final formatting in regular Excel.

The new method introduced by Rob Collie and described in the rest of this chapter puts a different spin on the pivot table experience. In this new method, which people inside Microsoft tend to use, you build an ugly pivot table. You do not care about the formatting of this pivot table. You then go through a one-time, relatively painful process of building a nicely formatted shell to hold your final report. Finally, you use GetPivotData to populate the shell report quickly.

From then on when you get new data, you simply put it on the data sheet, refresh the ugly pivot table, and print the shell report. Figure 15.9 illustrates this process.

There are huge advantages to this method. For example, you do not have to worry about formatting the report after the first time. It comes much closer to an automated process.

The rest of this chapter walks you through the steps to build a dynamic report that shows actuals for months that have been completed and a forecast for future months.

Because you will be updating this report every month, it makes the process easier if you have a pivot table data source that grows as you add new data to the bottom. Whereas legacy versions of Excel would achieve this through a named dynamic range using the OFFSET function, you can do this in Excel 2016 by selecting one cell in your data and pressing Ctrl+T. Click OK to confirm that your data has headers.

You now have a formatted data set, as shown in Figure 15.10.

Your next step is to create a pivot table that has every possible value needed in your final report. You’ve learned that GetPivotData is powerful, but it can only return values that are visible in the actual pivot table. It cannot reach through to the pivot cache to calculate items that are not in the pivot table.

Create the pivot table by following these steps:

1. Select Insert, PivotTable, OK.

2. In the PivotTable Fields list, select the Date field. You will either see daily dates or years in the first column (see Figure 15.11). If you see years, it’s due to the new Excel 2016 AutoGroup functionality, and you should skip Step 3.

3. Select the first date cell in A4. From the PivotTable Options tab, select Group Field. Select Months and Years, as shown in Figure 15.12. Click OK. You now have actual month names down the left side, as shown in Figure 15.13.

4. Drag the Years and Date fields to the Columns area in the Pivot Table Fields list.

5. Drag Measure to the Columns area.

6. Select Region to have it appear along the left column of the pivot table.

7. Select Revenue to have it appear in the Values area of the pivot table.

As shown in Figure 15.14, you now have one ugly pivot table. You might not like the words “Row Labels” and “Column Labels.” And having a total of January Actuals plus January Budget in column D is completely pointless. This is ugly. But for once, you do not care because no one other than you will ever see this pivot table.

At this point, the goal is to have a pivot table with every possible data point you could ever need in your final report. It is fine if the pivot table has extra data you will never need in the report.

Insert a blank worksheet in your workbook and then follow these steps:

1. Put a report title in cell A1.

2. Use the Cell Styles drop-down on the Home tab to format cell A1 as a Title.

3. Put a date in cell A2 by using the formula =EOMONTH(TODAY(),0). This enters the serial number of the last day of the previous month in cell B1. If you want to see how the formula is working, you can format the cell as a Date. If you are reading this on July 14, 2018, the date that appears in cell B1 is June 30, 2018.

4. Select cell A2. Press Ctrl+1 to go to the Format Cells dialog. On the Number tab, click Custom. Type the custom number format “Actuals Through” mmmm, yyyy. This causes the calculated date to appear as text.

5. Because there is a chance that the text in cell A2 is going to be wider than you want column A to be, select both cells A2 and B2. Press Ctrl+1 to format the cells. On the Alignment tab, select Merge Cells. This allows the formula in cell A2 to spill over into B2 if necessary.

6. Type a Region heading in cell A5.

7. Down the rest of column A, type your region names. These names should match the names in the pivot table.

8. Where appropriate, add labels in column A for Division totals.

9. Add a line for Total Company at the bottom of the report.

10. Month names stretch from cells B4 to M4. Enter this formula in cell B4: =DATE(YEAR ($A$2),COLUMN(A1),1).

11. Select cell B4. Press Ctrl+1 to format the cells. On the Number tab, select Custom and type the custom number format MMM.

12. Right-justify cell B4. Use the Cell Styles drop-down to select Heading 4.

13. Copy cell B4 to cells C4:M4. You now have true dates across the top that appear as month labels.

14. Enter this formula in cell B5: =IF(MONTH(B4)<=MONTH($A$2),"Actuals","Budget"). Right-justify cell B5. Copy across to cells C5:M5. This should provide the word Actuals for past months and the word Budget for future months.

15. Add a Total column heading in cell N5. Add a Total Budget column in cell O5. Enter Var % in cell P5.

16. Fill in the regular Excel formulas needed to provide division totals, the total company row, the grand total column, and the variance % column. For example:

Enter =SUM(B6:B7) in cell B8, and copy across.

Enter =SUM(B6:M6) in cell N6, and copy down.

Enter =IFERROR((N6/O6)-1,0) in cell P6, and copy down.

Enter =SUM(B10:B12) in cell B13, and copy across.

Enter =SUM(B15:B16) in cell B17, and copy across.

Enter =SUM(B6:B18)/2 in cell B19, and copy across.

17. Apply the Heading 4 cell style to the labels in column A and to the headings in rows 4:5.

18. Apply the #,##0 number format to cells B6:O19.

19. Apply the 0.0% number format to column P.

You now have a completed shell report, as shown in Figure 15.15. This report has all the necessary formatting your manager might desire. It has totals that add up the numbers that eventually come from the pivot table.

In the next section, you’ll use GetPivotData to complete the report.

Go to cell B6 on the shell report (this is the cell for Northeast region, January, Actuals), and then follow these steps:

1. Type = to start a formula (see Figure 15.16).

2. Move to the pivot table worksheet and click the cell for Northeast, January, Actuals. In Figure 15.17, this is cell B9.

3. Press Enter to return to the shell report and complete the formula. Excel adds a GetPivotData function in cell B6.

The formula says that the Northeast region actuals are $277,435.

The initial formula is as follows:

Click here to view code image

=GETPIVOTDATA("Revenue",UglyPivotTable!$A$3,"Region","Northeast",
"Date",1,"Measure","Actuals","Years",2018)

After years of ignoring the GetPivotData formula, you need to look at this monster formula closely to understand what it is doing. Figure 15.18 shows the formula in Edit mode, along with the formula tooltip.

The syntax for the function is =GetPivotData(data_field, pivot_table, [field1, item1], ...). In this case, there are four pairs of fieldn, itemn arguments. Here are the arguments in the formula:

Data_field—This is the field in the Value area of the pivot table. Note that you use Revenue, not Sum of Revenue.

Pivot_table—This is Microsoft’s way of asking, “Which pivot table do you mean?” All you have to do here is point to one single cell within the pivot table. The entry of UglyPivotTable!$A$3 is the first populated cell in the pivot table. You are free to choose any cell in the pivot table you want. However, because it does not matter which cell you choose, don’t worry about getting clever here. Leave the formula pointing to $A$3, and you will be fine.

Field1, item1—The formula generated by Microsoft shows Region as the field name and Northeast as the item value. Aha! So this is why the GetPivotData formulas that Microsoft generates cannot be copied. They are essentially hard-coded to point to one specific value. You want your formula to change as you copy it through your report. Edit the formula to change Northeast to $A6. By using only a single dollar sign before A, you are enabling the row portion of the reference to vary as you copy the formula down.

Field2, item2—The next two pairs of arguments specify that the Date field should be 1. When the original pivot table was grouped by month and year, the month field retained the original field name Date. The value for the month is 1, which means January. You probably thought I was insane to build that outrageous formula and custom number format in cell B4. That formula becomes useful now. Instead of hard-coding a 1, use MONTH(B$4). The single dollar sign before row 4 indicates that the formula can get data from other months as it is copied across, but it should always reach back up to row 4 as it is copied down.

Field3, item3—The field name is Measure, and the item is Actuals. This happens to be correct for January, but when you get to future months, you want the measure to switch to Budget. Change the hard-coded Actuals to point to B$5.

Field4, item4—This is Years and 2018. I was almost ready to leave this one alone because it would be months before we have a new year. However, why not change 2018 to YEAR($A$2)?

The new formula is shown in Figure 15.19. Rather than a formula that is hard-coded to work with only one value, you have created a formula that can be copied throughout the data set.

When you press Enter, you have exactly the same answer that you had before editing the formula. Compare this with the number you jotted down earlier to make sure.

The edited formula is as follows:

Click here to view code image

=GETPIVOTDATA("Revenue", UglyPivotTable!$A$3,"Region",$A6,
  "Date", MONTH(B$4),"Measure",B$5,"Years",YEAR($A$2))

Copy this formula to all the blank calculation cells in columns B:M. Do not copy the formula to column O yet. Now that you have real numbers in the report, you might have to adjust some column widths.

You can tweak the GetPivotData formula for the months to get the total budget. If you copy one formula to cell O6, you get a #REF! error because the word Total in cell O4 does not evaluate to a month. Edit the formula to the pairs of arguments for Month and Years. You still have an error.

Because the original pivot table had Measure as the third column field, there is no actual column for Budget total. Move the Measure field to be the first Column field, as shown in Figure 15.20.

When you return to the shell report, you find that the Total Budget formula in cell O6 is now working fine. Copy that formula down to the other blank data cells in column O (see Figure 15.21). Note with amazement that all the other formulas work, even though everything in the underlying pivot table moved.

The formula in O6 is as follows:

Click here to view code image

=GETPIVOTDATA("Revenue",UglyPivotTable!$A$3,"Region",$A6,"Measure",O$5)

You now have a nicely formatted shell report that grabs values from a live pivot table. It certainly takes more time to set up this report for the first month that you have to produce it, but it will be a breeze to update the report in future months.

1. Paste actuals for the new month just below the original data set. Because the original data set is a table, the table formatting automatically extends to the new rows. The pivot table source definition also extends.

2. Go to the pivot table. Click the Refresh button on the Options tab. The shape of the pivot table changes, but you do not care.

3. Go to the shell report. In real life, you are done, but to test it, enter a date in cell B2 such as 8/30/2018. You will initially see a #REF error, but it will disappear after you add August actuals to the real data and refresh the pivot table.

This process is so simple that you will probably forget about the pain that you used to endure to create these monthly reports. The one risk is that a company reorg will add new regions to the pivot table.

To be sure that your formulas are still working, add a small check section outside of the print range of the report. This formula in cell A22 checks to see if the budget total calculated in cell O19 matches the budget total back in the pivot table. Here is the formula:

Click here to view code image

=IF(GETPIVOTDATA("Revenue",UglyPivotTable!$A$3,"Measure","Budget")=$O$19,"", _
    "Caution!!! It appears that new regions have been added to the pivot
    table.
You might have to add new rows to this report for those regions."

In case the new region comes from a misspelling in the actuals, this formula checks the YTD actuals against the pivot table. Enter the following formula in cell A23:

Click here to view code image

=IF(SUMIF(B5:M5,"Actuals",B19:M19)=GETPIVOTDATA("Revenue",UglyPivotTable!$A$3, _
    "Measure","Actuals"),"","Caution!!! It appears that new regions have been _
    added to the pivot table. You might have to add new rows to this report for _
    those regions.")

Change the font color of both these cells to red. You do not even notice them down there until something goes wrong.

At one time I thought that I would never write these words: GetPivotData is the greatest thing ever. How could we ever live without it?