Creating a basic custom PivotTable style in Excel 2010

July 9, 2012 8:00 AM

Any good resources for help in creating custom PivotTable styles in Excel 2010?

Currently, when putting together a new PivotTable, I go into PivotTable Options --> Display and check Classic PivotTable layout. I then go into the Design tab on the PivotTable ribbon and choose "Clear". This gives me the basic layout I like. I then format the header row by manaully doing Cell Formats on each of the column headers with a fill and a font color.

I'd like to save this as a PivotTable style to avoid the few steps involved in doing this each time I create a new PivotTable, but I can't seem to match this exact style when using the "New PivotTable Style" dialogue box (more specifically, I can't get the cell borders to behave the same way).

Any suggestions? Any way to save current formatting as a style? Any resources for explanations

posted by undercoverhuwaaah to Computers & Internet (4 answers total)

I think you could record a macro to do the formatting. Here's a brief tutorial.
posted by amarynth at 8:45 AM on July 9, 2012

The kind of thing you are doing is probably best handled in a Macro if you are truly repeating these formatting steps over and over again. I would reccommend looking into Excel 20[XX] Power Programming with VBA by Mr. Spreadsheet. Yes, I reccommended coding a solution. There is no other way to get consistent styles in the manner you want.

If you are moving into a land of data display for manipulation by end users, I might also suggest exploring Tableau as a viable alternative (not cheap, but it is worth it for frequent reporting output).
posted by Nanukthedog at 8:47 AM on July 9, 2012 [1 favorite]

Response by poster: The only issue I see with recording / coding a macro is that it's not the same report every time. I produce a variety of reports with varying numbers of columns and data points. Maybe it betrays my lack of VBA knowledge, but would you be able to code something so that all headers in a PivotTable go to a certain formatting? Or are you limited to simply formatting cells, and the PivotTable has to fall within that range of cells in order to be formatted correctly?

And Nanuk, we've already started down the road of improving our business intelligence capabilities (including seeing presentations from Tableau, among others), but according to the project clock, implementation is approximately 400 days away, so I'm looking for an easier solution I can put in place today.
posted by undercoverhuwaaah at 9:01 AM on July 9, 2012

Definitely doable - the following might not be exactly what you want (I couldn't tell from the information in your post) but it should get you close


Sub pvtformat()

Dim pt as PivotTable

Set pt = ActiveSheet.PivotTables(1)

pt.TableStyle2 = ""

pt.DataLabelRange.Interior.ColorIndex = 1
pt.DataLabelRange.Font.ColorIndex = 2

End Sub


I'm not sure the DataLabelRange object is what you want, though.
The 1 and 2 are Excel Color Indicies.
posted by hot soup at 10:46 AM on July 9, 2012 [1 favorite]