Pivot tables are a powerful tool for turn-

ing thousands of records of detail data

into a concise summary in a few clicks.

They aren’t known for their formatting.

Whether you are using the 14 Auto-

Formats in Excel 2003 or the 54 formats

in Excel 2007, it’s unlikely that you’ll find

a way to format your pivot tables for

final presentation. It’s so bad, I generally

repeat these steps every month:

◆ Delete last month’s pivot table;

◆ Add new data to the bottom of the

data set;

◆ Recreate the pivot table, getting it as

close as possible;

◆ Copy the pivot table and paste as

values; and

◆ Delete columns, add blank rows,

change formatting, and so on.

If you find yourself creating pivot

tables every month only to use Paste Val-

ues to get results that you can format,

the techniques in this article will put an

end to that monthly routine.

Instead, you will create an ugly pivot

table and then use GetPivotData func-

tions to populate a great-looking report.

Each month you can paste new data

to the bottom of the data set, refresh

the pivot table, and your great-looking

report will automatically get new data

from the pivot table.

Figure 1 shows a pivot table in the

standard Excel 2007 layout. All of the

stores appear in alphabetical sequence.

Column D adds the January Actual and

January Plan for a number that’s com-

pletely meaningless. If your goal is to

show Actual for January through April

and Plan for May through December, it

will never happen in an Excel 2007 pivot

table. (Excel 2010 will offer support for

named sets, which would allow asym-

metric reporting, but it’s limited to either

OLAP or PowerPivot data).

In contrast, Figure 2 shows a section

of the perfect report. Stores are orga-

nized by geographic region even though

geography isn’t a part of the original

data set. Actual is shown for January

through April, and Plan is shown for

May and beyond. The cells inside the

report use the proper formatting, with

currency symbols shown on the first row

and subtotal row only. Single-underlines

appear before the subtotals, and double-

underlines appear under the grand total.

Row 11 is actually blank! (You can’t

insert blank rows in the middle of a reg-

ular pivot table.)

Building an Ugly Pivot TableBefore you create the pivot table, turn

your original data set into either an Excel

2007 table with Ctrl+T or an Excel 2003

list with Ctrl+L. Having the source data

as a table allows you to paste new Actu-

als below the data each month and

refresh the pivot table without respecify-

54 S T R AT E G IC F I N A N C E I A p r i l 2 0 1 0

TECHNOLOGY

EXCELPerfect Pivot Table Formatting

By Bill Jelen

Figure 1

ing the range for the source data.

Build the pivot table so that it has all

the fields you will potentially need. Don’t

spend any time formatting the pivot

table. For the moment, you don’t care

about the extraneous subtotals in

columns D, G, and J of Figure 1 or that

the pivot table contains Plan data for

past months and many extra Actual

columns of zero for future months.

Now the Surprise: GetPivotDataOn a new worksheet, build the perfect

report similar to the one shown in Figure

2. Leave the actual number cells blank

for now, but add in any formulas to cal-

culate subtotals and grand totals. In Fig-

ure 2, row 3 contains actual dates that

are formatted with the “MMM” format.

A formula in row 4 uses an IF function

to put either “Plan” or “Actual” in the

cell based on if the date in row 3 is

greater than a Date Ending cell outside

of view in the figure.

Select the first numeric cell in your

report. Type an equals sign (=) in that

cell. Using the mouse, click on the sheet

tab for the pivot table, and click on the

cell that contains January, Actual, Bay-

brook. As shown in the formula bar of

Figure 1, Excel inserts a formula of

=GETPIVOTDATA(“Sales”,Sheet1!$A$3,

“Store”,“Baybrook”,“Month”,“Jan”,

“Type”,“Actual”).

Most people hate when Excel automat-

ically inserts the GetPivotData function.

For the perfect formatting, you’ll actually

embrace the GetPivotData function. Here’s

the secret: By default, GetPivotData is

essentially hard-coded to return values

from one absolute reference. The way to

make GetPivotData incredibly useful is to

edit the default formula and make it a rel-

ative reference formula.

The first two arguments of the

function—“Sales”,Sheet1!$A$3—

shouldn’t be changed. They identify the

numeric field and the top-left cell of the

pivot table, respectively. The remaining

pairs of arguments specify a field name

and then a value. Edit the formula to

replace the quoted value arguments with

mixed cell references.

For example, the default formula

refers to “Baybrook.” Edit the formula

so that it points to cell $D6. Instead of

referring to “Actual,” point to cell E$4.

And instead of referring to “Jan,” use

TEXT(E$3,”MMM”).

The formula at the top of Figure 2

shows the edited GetPivotData formula.

Once you’ve created this formula in the

first cell of the report, use Copy and

Paste Special Formulas to copy it

throughout your report.

Refreshing the ReportNext MonthNext month, paste new data below the

Excel table or list on the original work-

sheet. You should see the table format-

ting extend to the new rows. Stop briefly

on the pivot table sheet and click the

Refresh button (a red exclamation point

in Excel 2003 or a large Refresh icon in

Excel 2007). Change the date in cell P1

of the Report worksheet so that the

word “Plan” in May changes to “Actu-

al.” Your perfectly formatted report is

ready to print.

To see an example of this report in

action, download the workbook from

http://tinyurl.com/SFAprXL. SF

Bill Jelen is the author of PowerPivot for

the Excel Data Analyst and will be pre-

senting at the IMA Annual Conference &

Exposition in Baltimore, Md. Send ideas

for future articles to IMA@MrExcel.com.

A p r i l 2 0 1 0 I S T R AT E G IC F I N A N C E 55

Figure 2