web 2.0

How to: Excel 2007 Classic PivotTable AutoFormat

A few days ago, I was writing an article and someone asked me: how the heck do I use the classic AutoFormat for my Excel 2007 PivotTable?

The image below shows xlReport4 AutoFormat (the data is not in English, as the original article was not in English – but I am sure you get the picture):

Figure 1: Excel 2007 PivotTable Classic AutoFormat

Well, an alternative to this is to use VBA. The following code would suffice:

Sub Test()
    ActiveSheet.PivotTables("PivotTable1").Format xlReport6
End Sub

In this case, I use
xlReport6 instead of xlReport4. However, this approach may not be acceptable to all users, after all, not everyone is fluent with VBA. You could, of course, create a simple customization to apply this classic PivotTable format, but what about the other AutoFormat which are available?

Well, there is a simpler way to achieve that. Simply use the accelerator key combination. Follow these steps:

1. Select the Excel 2007 PivotTable
2. Type the key sequence: Alt à o à a. Notice that this sequence will vary according to the language. This sequence is for English. The sequence in Portuguese (BR), the language I was writing the original article, is Alt à f à a.

The Excel 2003 AutoFormat dialog box will open:

Figure 2: Excel 2003 Classic AutoFormat dialog box

Simply choose the AutoFormat you want and click OK. You’re set to go.

Tags: , , ,

Microsoft Excel | Microsoft Excel - VBA | PivotTable


Comments are closed