Skip to content
Learni
View all tutorials
Excel

How to Create Pivot Tables with VBA in 2026

14 minINTERMEDIATE
Tableaux croisés dynamiquesAnalyse de donnéesExcel VBAAutomatisation Excel
Lire en français

Introduction

Pivot tables are essential for analyzing large volumes of data in Excel, but recreating them manually every time is time-consuming. With VBA, you can automate their creation, configuration, and updates, saving hours on recurring reports. This intermediate tutorial guides you step by step to generate a complete pivot table from sales data: adding row fields, columns, values, filters, and groupings. Imagine turning a 1000-row table into an interactive dashboard with one click. We use concrete examples with sales data (products, regions, amounts), with 100% functional copy-paste code for the VBA editor (Alt+F11). By the end, you'll handle advanced scenarios like slicers or custom calculations. Ideal for intermediate BI analysts or finance professionals.

Prerequisites

  • Excel 2016 or later (Microsoft 365 recommended for best performance).
  • Basic VBA knowledge: Sub procedures, loops, Worksheet objects.
  • Source data ready: a 'Data' sheet with headers in A1:E1 (Date, Product, Region, Quantity, Amount).
  • Enable macros: File > Options > Trust Center > Trust Center Settings > Enable all macros.

Insert the sample data

Module1.bas
Sub InsertSampleData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Data"
    
    ' Headers
    ws.Range("A1:E1").Value = Array("Date", "Product", "Region", "Quantity", "Amount")
    
    ' Sample data (10 realistic rows)
    ws.Range("A2:E11").Value = Array( _
        Array(#1/1/2026#, "Laptop", "North", 5, 2500), _
        Array(#1/2/2026#, "Mouse", "South", 20, 25), _
        Array(#1/3/2026#, "Laptop", "North", 3, 2500), _
        Array(#1/4/2026#, "Keyboard", "East", 10, 80), _
        Array(#1/5/2026#, "Monitor", "West", 2, 400), _
        Array(#1/6/2026#, "Laptop", "South", 4, 2500), _
        Array(#1/7/2026#, "Mouse", "East", 15, 25), _
        Array(#1/8/2026#, "Keyboard", "North", 8, 80), _
        Array(#1/9/2026#, "Monitor", "West", 1, 400), _
        Array(#1/10/2026#, "Laptop", "East", 6, 2500) _
    )
    
    ws.Columns("A:E").AutoFit
    MsgBox "Sample data inserted into 'Data'"
End Sub

This code creates a 'Data' sheet with 10 realistic sales rows, including dates and amounts. It uses Array for fast value insertion, avoiding slow loops. Run it first to test the following pivot tables; pitfall: ensure dates are in Date format for groupings.

Step 1: Understand the PivotCache

The PivotCache is the engine behind pivot tables: it stores data in memory for ultra-fast refreshes (up to 10x faster than direct queries). Without it, every pivot table recopies the data, wasting RAM. We create it from the A1:E11 range in 'Data', serving as the base for all pivot tables. Analogy: like a water reservoir feeding multiple taps (pivot tables).

Create the PivotCache and first pivot table

Module1.bas
Sub CreateFirstPivotTable()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    Set wsData = Sheets("Data")
    Set wsPivot = Sheets.Add
    wsPivot.Name = "Pivot_Sales"
    
    ' Create PivotCache
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsData.Range("A1:E11"))
    
    ' Create pivot table
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="Pivot_Sales_Total")
    
    MsgBox "First pivot table created in 'Pivot_Sales'"
End Sub

This Sub generates a PivotCache from your data and an empty pivot table in a new sheet. SourceType xlDatabase targets tabular ranges; TableDestination places the pivot table at A3 to leave room for titles. Pitfall: if the range changes, update it manually to avoid 'Invalid procedure' errors.

Step 2: Add row fields and values

Now, populate the pivot table: row fields (Region, Product) for hierarchy, values (Sum of Amount, Average of Quantity). VBA accesses fields via PivotFields: .Orientation = xlRowField positions them in rows. Concrete example: total sales by region/product.

Configure rows and values

Module1.bas
Sub ConfigureRowsValues()
    Dim pt As PivotTable
    Set pt = Sheets("Pivot_Sales").PivotTables("Pivot_Sales_Total")
    
    ' Row fields: Region then Product
    With pt.PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Product")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ' Values: Sum Amount, Average Quantity
    With pt.AddDataField(pt.PivotFields("Amount"), "Sum of Amount", xlSum)
        .NumberFormat = "$#,##0"
    End With
    pt.AddDataField pt.PivotFields("Quantity"), "Average Quantity", xlAverage
    
    MsgBox "Rows and values configured"
End Sub

This code adds Region/Product as hierarchical rows and two calculated values. .NumberFormat formats currency; Position controls order. Pitfall: forgetting .Subtotals(1) = False can overload with unnecessary subtotals.

Step 3: Add filters and columns

Filters (Date) enable data slicing; columns (e.g., monthly grouping) cross with rows for a matrix view. Use PivotItems for dynamic filtering, like 'North' only.

Add filters and columns

Module1.bas
Sub AddFiltersColumns()
    Dim pt As PivotTable
    Set pt = Sheets("Pivot_Sales").PivotTables("Pivot_Sales_Total")
    
    ' Filter on Date
    With pt.PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
        .PivotItems("1/1/2026").Visible = True  ' Example filter
    End With
    
    ' Columns: Quantity (for cross-tab)
    With pt.PivotFields("Quantity")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    MsgBox "Filters and columns added"
End Sub

xlPageField filter creates a dropdown; .Visible hides unused items. xlColumnField crosses Quantity with rows. Pitfall: for dates, use ClearAllFilters first to avoid conflicts.

Step 4: Group and format

Grouping on dates (by month) automates temporal aggregations. Add conditional formatting via VBA to highlight top sales.

Group dates and format

Module1.bas
Sub GroupAndFormat()
    Dim pt As PivotTable
    Set pt = Sheets("Pivot_Sales").PivotTables("Pivot_Sales_Total")
    
    ' Group Date by month
    Dim pfDate As PivotField
    Set pfDate = pt.PivotFields("Date")
    pfDate.ClearManualFilter
    pfDate.PivotItems.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, False)
    
    ' Formatting: grand total in bold
    pt.TableStyle2 = "PivotStyleLight16"
    pt.RowGrand = True
    pt.ColumnGrand = True
    
    MsgBox "Grouping and formatting applied"
End Sub

Periods Array groups by month (index 4). TableStyle2 applies a professional theme. Pitfall: grouping fails on duplicates; sort source data first.

Refresh and add slicer

Module1.bas
Sub RefreshAndSlicer()
    Dim pt As PivotTable
    Set pt = Sheets("Pivot_Sales").PivotTables("Pivot_Sales_Total")
    
    ' Refresh cache
    pt.PivotCache.Refresh
    
    ' Add slicer on Region (Excel 2010+)
    Dim sc As SlicerCache
    Dim sl As Slicer
    Set sc = ThisWorkbook.SlicerCaches.Add2(pt, "Region")
    Set sl = sc.Slicers.Add(Sheets("Pivot_Sales"), , "Slicer_Region", "Regions", 400, 50, 200, 200)
    
    MsgBox "Pivot table refreshed with Region slicer"
End Sub

Refresh updates after source changes. SlicerCaches.Add2 links interactively to the pivot table. Pitfall: slicers unavailable before Excel 2010; check ActiveWorkbook.SlicerCaches.Count > 0.

Best practices

  • Always use a shared PivotCache: Link multiple pivot tables to the same cache to save 50-70% RAM.
  • Dynamically validate SourceData range: Use wsData.UsedRange instead of fixed A1:E11.
  • Handle errors: On Error Resume Next before .PivotItems to avoid crashes on missing items.
  • Optimize: Disable ScreenUpdating before (Application.ScreenUpdating=False) and re-enable after.
  • Document: Add comments for each PivotField for easier maintenance.

Common errors to avoid

  • Invalid SourceData range: Error 1004 if data empty; solution: If wsData.UsedRange.Rows.Count < 2 Then Exit Sub.
  • Fields already oriented: Double-add crashes; clear first with pt.PivotFields("Field").Orientation = xlHidden.
  • Refresh ignored: Without .Refresh, source changes invisible; force it after inserts.
  • Memory leak: Don't Set objects without Nothing; add Set pt=Nothing at Sub end.

Next steps

  • MS Documentation: PivotTable VBA Reference.
  • Power Pivot for datasets >1M rows.
  • Integrate with Power Automate for auto-sent pivot tables.
Check out our advanced Excel VBA trainings to monetize your BI skills.