VBA Pivot Table Formatting: A Practical Guide

VBA & macros · Updated November 18, 2026

To format an existing Excel PivotTable with VBA, set its TableStyle2 property for an overall design, then set the NumberFormat property of its data fields for values such as currency or dates. For example, pt.TableStyle2 = "PivotStyleMedium2" applies a built-in style; the rest of the approach depends on which PivotTable and fields you want to format.

What VBA PivotTable formatting controls

A PivotTable is a summary built from source data, and it can change size or shape when its fields, filters, or source data change. VBA formatting is useful when you need a consistent appearance after creating or refreshing a report, or when a workbook contains several PivotTables that should follow the same rules. The key is to format the PivotTable object or its fields rather than relying only on fixed worksheet cell addresses.

Common formatting tasks include applying a built-in PivotTable style, displaying alternating row bands, setting decimal or currency formats for summarized values, and arranging row labels in a tabular layout. These are separate choices: a style controls much of the overall visual design, a number format controls how values appear, and layout settings control how fields are presented.

VBA can also format cells in a PivotTable’s range directly, but direct cell formatting is often less reliable after a refresh. Excel may expand, shrink, or rearrange the report. Prefer PivotTable and PivotField properties for repeatable formatting, and use cell-level formatting only when you need a detail that the PivotTable object model does not provide.

Identify the worksheet and PivotTable

Before writing formatting code, identify the worksheet name and PivotTable name. In Excel for Microsoft 365 on Windows, click inside the PivotTable and open the PivotTable Analyze tab; the PivotTable Name box shows its current name. Give the table a descriptive name, such as ptSales, rather than relying on a default name like PivotTable1. Use the worksheet tab name, not the workbook’s file name, in VBA.

For an existing table, the basic reference looks like this:

Dim ws As Worksheet
Dim pt As PivotTable

Set ws = ThisWorkbook.Worksheets("Summary")
Set pt = ws.PivotTables("ptSales")

This code assumes that the worksheet is named Summary and that it contains a PivotTable named ptSales. If either name is wrong, VBA will stop with an error rather than silently selecting another table. A workbook can have multiple PivotTables on one worksheet, so using the PivotTable name is safer than selecting whichever table happens to be active.

To work with VBA in Windows Excel, open the Visual Basic Editor with Alt+F11, insert a standard module, and place a procedure there. On Excel for Mac, the Visual Basic Editor is available through the Developer tab or Tools > Macro > Visual Basic Editor, depending on the ribbon setup; keyboard shortcuts differ by keyboard and macOS version. Excel for the web can display a workbook containing macros, but it cannot run VBA. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Apply a built-in PivotTable style

Excel includes built-in PivotTable styles with names such as PivotStyleLight16, PivotStyleMedium2, and PivotStyleDark4. The exact appearance depends on the style selected, and the style gallery in Excel is the easiest way to compare the available designs. In VBA, assign the style name to the table’s TableStyle2 property:

pt.TableStyle2 = "PivotStyleMedium2"
pt.ShowTableStyleRowStripes = True
pt.ShowTableStyleColumnStripes = False

Row stripes create alternating bands through the report; column stripes apply bands across columns. These options work with styles that include the relevant banding. A setting can be enabled without producing an obvious visual change if the chosen style does not make that feature prominent. You can also control style elements such as row headers and column headers with ShowTableStyleRowHeaders and ShowTableStyleColumnHeaders.

Use the built-in style rather than manually coloring every cell when the goal is a consistent report theme. Styles are easier to maintain and generally follow the PivotTable as it changes size. If your organization uses a custom PivotTable style, its name may be available in that workbook, but a macro that expects a custom style may fail when run in a workbook where the style is not present.

Set number formats on summarized values

Number formats belong on the data field that produces a value, not just on the cells currently displaying it. For instance, a Sales field summarized as a sum can be shown as currency with two decimal places. If it is formatted only by selecting the visible worksheet cells, a refresh or layout change may leave new cells with a different appearance.

When the PivotTable has one value field, this example formats its first data field:

Dim dataField As PivotField

If pt.DataFields.Count > 0 Then
    Set dataField = pt.DataFields(1)
    dataField.NumberFormat = "$#,##0.00"
End If

The format string shown is a common US-style currency display; choose a suitable format for the workbook’s currency and audience. For whole-number counts, a format such as #,##0 omits decimal places. For percentages, use a format such as 0.0%, but only when the underlying values represent proportions—for example, 0.25 displayed as 25.0%. Formatting does not change the underlying value or the summary calculation.

A PivotTable may have several data fields, and the first one is not necessarily the one you want. To target a particular field, inspect the field names in the PivotTable Fields pane and the captions shown in the Values area. Captions can change when a user renames a value field, such as changing “Sum of Sales” to “Revenue.” For a macro used by other people, verify the field exists before formatting it and decide how the code should respond if it does not. Also distinguish the source field from its displayed caption: a field can be summarized by sum, average, or count, and those summaries can need different number formats.

Arrange labels and report layout

Layout settings affect readability as much as color and font choices. Compact Form nests multiple row fields in one column, while Tabular Form gives each row field its own column. For reports intended for reading or export, a tabular layout can make the hierarchy easier to scan. The following method sets all row fields to a tabular layout:

pt.RowAxisLayout xlTabularRow

Depending on the report, repeating labels can make each row understandable when printed or copied. A PivotTable can repeat item labels with:

pt.RepeatAllLabels xlRepeatLabels

These layout changes can alter the shape of the table, so apply them deliberately. A downstream process that expects a particular cell layout may be affected if fields move into separate columns. Test the report with representative data and filters, especially if another macro, chart, or formula refers to cells in the PivotTable’s output range.

Subtotals and grand totals are also presentation choices. They can usually be changed through PivotTable options or field settings, but they are not the same thing as cell formatting: removing a subtotal changes what the report displays, not merely its appearance. Decide whether totals help readers interpret the figures before hiding them.

Use a refresh-aware formatting macro

Formatting can be lost or altered when a PivotTable refreshes, so set the table’s preservation options before refreshing it. PreserveFormatting asks Excel to retain formatting applied by the user, while HasAutoFormat controls automatic formatting during updates. The following example targets an existing report named ptSales, applies a built-in style, formats its first value field as currency, and refreshes it.

Change the worksheet name, PivotTable name, style, and number format to suit the workbook. This example assumes the first data field is a sales amount; it should not be used unchanged if that field is a count, percentage, or another kind of measure.

Option Explicit

Sub FormatSalesPivot()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim dataField As PivotField

    ' Target the named worksheet and existing PivotTable.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("ptSales")

    ' Keep user formatting when the report is updated.
    pt.PreserveFormatting = True
    pt.HasAutoFormat = False

    ' Apply the overall style and row banding.
    pt.TableStyle2 = "PivotStyleMedium2"
    pt.ShowTableStyleRowStripes = True
    pt.ShowTableStyleColumnStripes = False

    ' Format the first value field, if one is present.
    If pt.DataFields.Count > 0 Then
        Set dataField = pt.DataFields(1)
        dataField.NumberFormat = "$#,##0.00"
    End If

    ' Refresh the PivotTable after setting its format options.
    pt.RefreshTable
End Sub

Save a copy of the workbook before running this procedure because macro actions cannot be reversed with Ctrl+Z. The macro refreshes the PivotTable, which may take time or update the report from its data source. If the source requires credentials or access to an external connection, resolve that separately; formatting code does not grant access or repair a data connection.

Common problems and practical fixes

  • “Subscript out of range” when setting the worksheet: Check the worksheet tab name character for character. The worksheet name in VBA is not necessarily the same as its CodeName.
  • “PivotTable” not found: Confirm the name in the PivotTable Analyze tab and make sure the table is on the worksheet referenced by the code. A PivotTable name is not the same as its source range or data connection name.
  • The style assignment fails: Check the spelling of the built-in style name and confirm the workbook supports the named style. Try a built-in style visible in the PivotTable Styles gallery.
  • Formatting disappears after refresh: Set PreserveFormatting to True and HasAutoFormat to False, then test a refresh. Some changes made directly to cells can still be less dependable than formatting the PivotTable or its fields.
  • Currency appears on the wrong measure: Confirm the order and identity of fields in the Values area. Do not assume that DataFields(1) always represents Sales if users can rearrange the report.
  • The macro works on one computer but not another: The other workbook may have different sheet or PivotTable names, a missing custom style, different source data, or macro security settings that prevent execution. Test the actual distributed workbook.

One frequent misconception is that changing a number format converts the data. It does not: it changes only the display. If a value is stored as text or the source data has the wrong unit, a currency format will not correct it. Likewise, changing a PivotTable style does not change its calculations, filters, or source records.

When to use VBA instead of manual formatting

Manual formatting is often best for a one-off report: select the PivotTable, use the PivotTable Design tab to choose a style, and set number formats through the field’s value settings or number-format controls. VBA is useful when the same workbook is rebuilt regularly, when several reports need a consistent look, or when a procedure already refreshes and distributes the report.

For a repeatable workbook workflow, a useful compromise is to design the PivotTable manually first, confirm the desired style and field layout, then automate those stable settings. Avoid a macro that paints fixed ranges unless the PivotTable is guaranteed not to change size. If the task is simply to make a static report look good and it will not be refreshed, direct cell formatting may be sufficient; for a recurring report, object-based formatting is usually easier to maintain.

Excel for Mac supports VBA macros, though the editor and some interface details differ from Windows. Excel for the web does not execute VBA, so an automation process that must run in a browser needs another approach, such as Office Scripts where available, or a manually refreshed workbook. Office Scripts use a different language and object model; VBA code cannot simply be pasted into an Office Script.

Frequently Asked Questions

How do I change a PivotTable style with VBA?
Reference the PivotTable object and assign a built-in style name to its TableStyle2 property, for example pt.TableStyle2 = "PivotStyleMedium2". The PivotTable Styles gallery shows the available designs.

How do I format PivotTable values as currency?
Set the relevant value field’s NumberFormat property, such as dataField.NumberFormat = "$#,##0.00". Make sure you target the correct data field and choose a currency format appropriate to the workbook.

How can I keep formatting after refreshing a PivotTable?
Set pt.PreserveFormatting = True and pt.HasAutoFormat = False before refreshing, then test the result. Formatting applied directly to worksheet cells may still need attention if the table’s shape changes.

Can VBA format every PivotTable in a workbook?
Yes. VBA can loop through each worksheet’s PivotTables collection and apply shared settings. Use care with number formats: a shared visual style may suit every report, but sales, counts, dates, and percentages need different formats.

Can I run a VBA PivotTable macro in Excel for the web?
No. Excel for the web can open a macro-enabled workbook but does not run VBA. Use desktop Excel to run the macro, or consider a separate browser-compatible automation approach such as Office Scripts where available.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.