Pivot Table Styles VBA: Apply, Change, and Troubleshoot

In Excel VBA, set a PivotTable’s TableStyle2 property to a built-in style name, such as PivotStyleMedium9, to apply a style. For example: pt.TableStyle2 = "PivotStyleMedium9". The same property works in Excel for Microsoft 365 on Windows and can be used to make a workbook’s PivotTables look consistent.

What the PivotTable style property does

A PivotTable style is a coordinated set of formatting for the report area, including elements such as headers, totals, and alternating bands. Unlike applying a fill color to a fixed cell range, a PivotTable style is associated with the PivotTable itself. That makes it useful when the report grows or shrinks after a refresh: the style can follow the changing PivotTable area.

In VBA, the relevant property is PivotTable.TableStyle2. Its value is a style name represented as text. Excel’s built-in names include families such as PivotStyleLight1, PivotStyleMedium9, and PivotStyleDark1. The available designs and their appearance can be previewed in the Excel ribbon; the number in a name identifies a design in its family, not a universal color or quality rating.

This property changes the PivotTable’s overall style, not every kind of formatting inside it. Number formats, conditional formatting, and some direct cell formatting are separate concerns. If a report needs a particular currency format or a color based on a value, plan to manage that separately rather than expecting the style name to do everything.

Apply a style to one PivotTable

First identify the worksheet and PivotTable by their actual names. Click inside the PivotTable and use the contextual PivotTable Analyze tab to find its name in the PivotTable Name box. The worksheet tab name is the name shown on the sheet tab. Then place this procedure in a standard VBA module:

Sub ApplyPivotStyle()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Use the actual worksheet and PivotTable names.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")

    pt.TableStyle2 = "PivotStyleMedium9"
End Sub

Change Summary and PivotTable1 to match the workbook. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the procedure with F5 while the cursor is inside it. You can also run it from Developer > Macros. On Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard and function-key settings.

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z (or Mac’s Undo command), so a backup is the safe way to recover the original formatting if the result is not what you wanted.

Apply a style to every PivotTable

For a workbook with several reports, loop through each worksheet and its PivotTables. The following example applies one consistent style throughout the current workbook:

Sub ApplyStyleToAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Apply the same style to every PivotTable in this workbook.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.TableStyle2 = "PivotStyleMedium9"
        Next pt
    Next ws
End Sub

Use ThisWorkbook when the macro is stored in the workbook whose PivotTables you want to change. If code is stored in a personal macro workbook or another file, ThisWorkbook refers to that file instead; do not assume it means the workbook currently active on screen. A macro that intentionally targets another open workbook should identify that workbook explicitly.

It is also possible to apply a style selectively. For example, place the assignment inside a check for a worksheet or PivotTable name, or run the single-table procedure only for the report that needs updating. Selective changes are helpful when a workbook uses a dark style for a dashboard but a lighter style for a print-oriented detail report.

Choose a style and control its bands

To preview built-in designs without writing code, select a cell in the PivotTable and use Design > PivotTable Styles. After choosing a suitable design in Excel, inspect its VBA name through the PivotTable’s TableStyle2 property or use the known built-in name in code. Choose based on readability at the report’s actual size: a subtle style can work well for a dense table, while a stronger header contrast can make a dashboard easier to scan.

Several PivotTable properties control whether style elements are displayed. They do not select a different style; instead, they turn particular style features on or off. For example, add these lines after the TableStyle2 assignment to use row banding and show the style’s header treatments:

pt.ShowTableStyleRowStripes = True
pt.ShowTableStyleColumnStripes = False
pt.ShowTableStyleRowHeaders = True
pt.ShowTableStyleColumnHeaders = True

Row stripes alternate formatting across rows; column stripes do the same across columns. For many reports, row stripes help readers track across a wide table, while column stripes may be useful when comparing a small number of measures. Header options control whether the PivotTable style’s row-header and column-header formatting is shown. The visual result still depends on the selected style and the PivotTable’s layout.

When to run the macro

You can run the macro on demand after a report is created, or call a formatting procedure from a larger process that builds or refreshes reports. A style assignment does not require the PivotTable’s source data to change. If a report is recreated or its layout is substantially changed by other VBA, setting the style after those operations can make the intended order explicit.

A style normally remains attached when a PivotTable refreshes. However, other formatting choices, layout changes, or direct cell formatting can affect what users see. It is useful to test the complete workflow—not just the initial macro—including refreshes, expanding or collapsing fields, and changing filters. If a workbook is intended for regular use, a controlled refresh-and-format procedure is often more reliable than asking users to repair appearance manually.

Macros do not run in Excel for the web. The web app can display a workbook containing PivotTables, but VBA must be run in desktop Excel. On Mac, Excel supports VBA, though ribbon details and keyboard shortcuts can differ from Windows; the TableStyle2 approach remains the relevant object-model method.

Direct formatting and other formatting features

A common misunderstanding is that applying a PivotTable style will necessarily erase all existing formatting or control every cell’s appearance. The visible result can depend on direct formatting already applied to cells and on the PivotTable’s formatting settings. If a fill or font seems unchanged, test on a copy and remove or adjust the conflicting direct formatting rather than repeatedly assigning the same style.

Number formats deserve separate attention. A style is not a dependable way to define the precision, date display, or currency format of values. Set the number format through PivotTable field settings where appropriate, or manage it deliberately in VBA if the report’s design requires it. Conditional formatting is also separate: rules may add color scales, data bars, or value-based emphasis, and their range behavior should be checked when the PivotTable changes size.

For reliable results, keep the responsibilities distinct: use a PivotTable style for the report’s overall visual theme, field settings for number formats, and conditional formatting for rules driven by values. That separation makes later changes easier to understand and reduces the chance that a broad style change will interfere with a specific reporting requirement.

Common problems and fixes

Run-time error when finding the PivotTable: Check the worksheet tab name and the PivotTable name for spelling, spaces, and punctuation. A PivotTable’s displayed title is not necessarily its object name.
The style appears not to change: Confirm that the macro reached the intended PivotTable and that the style name is valid. Then check for direct cell formatting, existing conditional formatting, and disabled style options such as row stripes.
Formatting looks different after refresh: Compare the result before and after refresh, and check the PivotTable’s layout and formatting settings. If another macro rebuilds the report, apply the style after that code has finished.
The macro cannot be run: Save a macro-enabled workbook as .xlsm when keeping VBA in that file, and follow your organization’s macro-security policy. Do not enable macros in files from untrusted sources.
A name copied from an example does not work: Built-in style names must be spelled correctly. If using a custom style, make sure that style exists in the workbook where the code is running; a custom style may not be available in another file.

Alternatives to VBA

If the goal is simply to style one report, using the ribbon is often quicker: select the PivotTable and choose a design under Design > PivotTable Styles. That avoids macro security prompts and is appropriate when users can make the choice manually.

VBA is more useful when the same style must be applied repeatedly, across many PivotTables, or as part of a reporting routine. For workbooks shared with people who cannot run macros, consider applying the style manually and saving the workbook, or documenting the intended ribbon steps. Remember that Excel for the web does not execute VBA, so a macro-driven formatting process needs desktop Excel.

Frequently Asked Questions

What is the VBA property for a PivotTable style? Use PivotTable.TableStyle2. Assign a valid name such as PivotStyleMedium9 to choose a built-in style.

Can VBA apply a PivotTable style to every sheet? Yes. Loop through ThisWorkbook.Worksheets, then through each worksheet’s PivotTables collection, and assign the style to each PivotTable.

How do I turn on banded rows in VBA? Set ShowTableStyleRowStripes to True on the PivotTable. The effect depends on the selected style, so check the result in the report.

Will the style remain after a PivotTable refresh? A PivotTable style is generally retained on refresh. Other formatting, layout changes, or code that rebuilds the report can affect the appearance, so test the full refresh process.

Can PivotTable styles VBA run in Excel for the web? No. Excel for the web can display PivotTables, but it does not run VBA macros. Use desktop Excel for Microsoft 365 on Windows or a supported desktop Excel installation on Mac.

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.