Each month, the workflow becomes: Add new data to the data set. Refresh the ugly pivot table. Print the nicely formatted report that draws its numbers from the pivot table.
Here are some examples.
This figure shows a very ugly pivot table. Excel adds Plan+Actual in column D which is useless. There is no way to get Actuals for Jan through May and Plan for June through December without showing both fields for every month.
Figure 817 An ugly pivot table.
Typically, each month, you would create this pivot table, copy the table and paste as values. You would get rid of the columns you don’t need. You would resequence the stores in a geographic fashion. It would take half an hour to format the copied report.
Instead, build a report shell on a new worksheet. Format the report the way that you want it to be shown. If you want underlines and double-underlines, add them. If you want $ on row 1 and the total row, do that. If you want (gasp) a blank row, add it. You can do whatever you want, since this is not a pivot table. It is just Excel.
Figure 818 Do any formatting in a non-pivot table report.
The first data cell in the report is for Baybrook Mall, January, Actuals. Choose that cell. Type an equals sign. Navigate to the pivot table worksheet and find the cell for Baybrook, January, Actual. Click on that cell and click OK.
Most of the time when Excel inserts a GETPIVOTDATA formula, it is an annoying side-effect of building a formula with a mouse. This time, it is crucial to building this report.
Gotcha: Most people are annoyed by Excel insert GETPIVOTDATA. Later in this book, in “Calculations Outside of Pivot Tables" on page 415, I will show you how to turn this feature off. If someone has turned the feature off on your computer, you need to turn it back on.
The fundamental problem with the automatically generated GETPIVOTDATA function is that the label values are hard-coded in the formula instead of pointing to cells in the worksheet.
Figure 819 Changes this text to point to cells in the report.
The accountants at Microsoft who use this trick regularly call this next step, “Parameterizing the Formula." Change the three text values in the formula to point to cell addresses. Make sure to use the proper dollar signs. Replace Baybrook with $D6. Replace Jan with E$3. Replace Actual with E$4.
Figure 820 Replace text with cell addresses.
You can now copy that first formula and Paste Special Formulas to all the other report cells.
Figure 821 Copy the first formula and paste the formula throughout.
The result is a beautifully formatted report that is getting the data from the pivot table. Next month, add the May actuals, refresh the pivot table, and the report will update with new values.
Figure 822 This doesn’t look like a pivot table, but harnesses the power.
Additional Details: Cell P1 in the report is a date that I type manually each month. Formulas in row 4 use that date to show “Actual" or “Plan" based on the date. =IF(MONTH(DATEVALUE(E3&" 1, 2014"))<=MONTH($P$1),"Actual","Plan").