Excel VBA Pivot Table Source Types Explained

In Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for an external connection, xlConsolidation for multiple ranges, and xlPivotTable for another PivotTable. Choose the type that describes where the data comes from; a formatted Excel Table is still an xlDatabase source, not a separate source type.

What the source type controls

A PivotTable is built from a PivotCache, which stores or connects to the data the PivotTable summarizes. In VBA, Workbook.PivotCaches.Create creates that cache. Its SourceType argument identifies the broad kind of source, while SourceData tells Excel which range, connection, or other PivotTable to use. The source type is not the name of a worksheet, a field, or a data format.

The four types commonly used with PivotCaches.Create are members of Excel’s XlPivotTableSourceType enumeration:

xlDatabase (value 1): worksheet data, including a normal range or an Excel Table.
xlExternal (value 2): data supplied through an external connection, such as a workbook connection.
xlConsolidation (value 3): multiple ranges combined into a classic consolidation PivotTable.
xlPivotTable (value 5): an existing PivotTable used as the source for another PivotTable.

The enumeration also contains xlScenario (value 4), a legacy scenario-related member. It is not one of the ordinary source choices documented for creating a PivotCache with PivotCaches.Create. For typical automation, use one of the four types above and use the named constants rather than typing numeric values into code.

How PivotCaches.Create uses SourceType and SourceData

Think of SourceType as the category and SourceData as the particular source. For a worksheet range, for example, the category is xlDatabase and the particular source is the address of the range containing the headers and records. For an external source, the category is xlExternal and the particular source is generally a workbook connection.

A PivotCache is distinct from the visible PivotTable. You create the cache first, then call its CreatePivotTable method to place a PivotTable on a worksheet. This distinction matters when troubleshooting: the PivotTable’s location is not its source, and changing the report layout does not by itself change the cache’s source.

For worksheet data, Microsoft’s VBA documentation advises passing a string for SourceData rather than passing a Range object directly. A range address expressed in R1C1 style and including the workbook and sheet reference is a practical way to avoid ambiguity about which workbook Excel should use.

Worksheet ranges and Excel Tables: xlDatabase

Use xlDatabase when the source is data on a worksheet in the workbook. It covers both a plain rectangular range and a structured Excel Table. The word “database” in this constant is historical Excel terminology; it does not mean that the data must be stored in a separate database application.

This example uses the contiguous block around cell A1 on a worksheet named Data. It creates a new report sheet, a cache, and an empty PivotTable. Before running it, save a copy of the workbook: macros cannot be undone with Ctrl+Z.

Sub CreatePivotFromWorksheetData()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsReport As Worksheet
    Dim sourceRange As Range
    Dim sourceAddress As String
    Dim cache As PivotCache
    Dim pivot As PivotTable

    ' Use the workbook containing this macro.
    Set wb = ThisWorkbook
    Set wsSource = wb.Worksheets("Data")
    Set sourceRange = wsSource.Range("A1").CurrentRegion

    ' Include workbook and sheet details in the source address.
    sourceAddress = sourceRange.Address( _
        RowAbsolute:=True, _
        ColumnAbsolute:=True, _
        ReferenceStyle:=xlR1C1, _
        External:=True)

    Set cache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceAddress)

    Set wsReport = wb.Worksheets.Add
    Set pivot = cache.CreatePivotTable( _
        TableDestination:=wsReport.Range("A3"), _
        TableName:="SalesPivot")
End Sub

CurrentRegion takes the contiguous block around A1, stopping at blank rows or columns. That is convenient for a clean data list but can select too little if the source contains gaps, or too much if unrelated data touches the list. For a stable automated report, an Excel Table is often easier to maintain because it expands as rows are added. The table remains an xlDatabase source.

A useful source list has one header row, a distinct name for every header, and one record per row. Avoid merged header cells, completely blank columns inside the list, and subtotals mixed into the records. Those layout problems can cause missing fields, incorrect ranges, or confusing summaries even when the VBA source type is correct.

External connections: xlExternal

Use xlExternal when the PivotTable is based on a connection rather than an ordinary worksheet range. The connection might represent a database, another supported data source, or a workbook connection created through Excel’s data-import features. In a workbook that already has a connection named SalesConnection, the cache can be created like this:

Sub CreatePivotFromConnection()
    Dim wb As Workbook
    Dim cache As PivotCache

    ' Save a copy of the workbook before running a macro.
    Set wb = ThisWorkbook

    Set cache = wb.PivotCaches.Create( _
        SourceType:=xlExternal, _
        SourceData:=wb.Connections("SalesConnection"))
End Sub

This example assumes the connection exists and is available to the workbook; it does not create or authenticate the connection. Connection names must match the names in the workbook’s Connections collection. Credentials, provider installation, network access, and permission to refresh may also affect whether the cache can retrieve data.

Excel’s Data Model is accessed through a workbook connection, so it is generally treated as an external-style source in VBA rather than as a worksheet range. The exact connection setup and supported features depend on the Excel version and source. On Windows, Microsoft 365 supports a broader set of Power Pivot and Data Model workflows than Excel for the web; Mac support for particular external providers and Data Model workflows differs. Do not assume a macro that depends on a Windows provider will run unchanged on Mac.

Multiple ranges: xlConsolidation

xlConsolidation is for combining multiple source ranges into a classic consolidation PivotTable. It is not simply a command to append several well-structured tables into one detailed list. Consolidation PivotTables use a special layout with generic row, column, and page fields; they do not retain the same straightforward field structure as a PivotTable built from one normalized table.

For example, two similarly shaped blocks from separate worksheets can be supplied as an array of range-address strings. The ranges should be planned carefully, and the workbook’s data should suit the limitations of classic consolidation. If the goal is to analyze transactions from several sheets by fields such as Date, Region, and Product, a better approach is usually to append the records into one table first—using Power Query or another data-preparation step—and then create an xlDatabase PivotTable from that unified list.

Consolidation can be useful for a small, fixed set of summary blocks when preserving a conventional row-by-row data structure is not possible. It is a poor fit when users need to filter and group by the original fields, add new source tables regularly, or trace individual records. Before choosing it, ask whether the sources are detail records that should be appended or already summarized matrices that genuinely need consolidation.

Using another PivotTable: xlPivotTable

xlPivotTable identifies an existing PivotTable as the source for another PivotTable. This can support a second report based on a PivotTable’s output, but it is not the same thing as sharing the original PivotCache. A report built from summarized output may only expose the fields and detail retained in that output, so it can limit later analysis.

If the intention is simply to create another report from the same underlying records, sharing or reusing a cache is often more suitable than treating the first PivotTable as a new data source. Shared caches can reduce duplicate storage and keep reports connected to the same source, although cache-sharing behavior and refresh needs should be considered when designing a workbook. If the second report needs different detail or a different source, create its cache from that actual source instead.

Where a PivotTable is deliberately used as the source, verify that the source report has the fields and records the new report needs. Avoid assuming that a displayed total is equivalent to the original transaction rows. A pivot based on aggregated values cannot recover detail that the first report has already summarized away.

How to choose the right type

Choose based on the immediate source that Excel will read, not on the report’s purpose. A sales summary made from a worksheet Table still uses xlDatabase; the fact that the report summarizes sales does not make it an external source. Use this checklist:

Data is in a worksheet range or Excel Table: choose xlDatabase.
Data is provided by a saved workbook connection or supported external source: choose xlExternal.
Several separate summary ranges need classic range consolidation: consider xlConsolidation.
An existing PivotTable is intentionally the input to a second PivotTable: consider xlPivotTable.
Several detail tables need to be analyzed as one dataset: append or combine them first, then use xlDatabase for the resulting table or xlExternal if the prepared data is accessed through a connection.

Also consider how the source changes. A fixed range can omit newly added rows unless the macro recalculates its address. A Table normally expands when records are added. A connection may need a refresh and could depend on credentials or network availability. A consolidation may require code changes when another range is introduced. Picking a type that matches the maintenance plan prevents recurring source errors.

Common errors and refresh behavior

A type mismatch often means the source argument does not suit the chosen type. For example, a worksheet range address belongs with xlDatabase, while an existing workbook connection belongs with xlExternal. Confirm that the source exists, that the workbook and worksheet names are spelled correctly, and that any connection name is present in the workbook.

Other frequent problems include a source range that excludes its header row, duplicate or blank field names, a range that stops before new records, and a destination cell that overlaps an existing PivotTable. If a PivotTable is created but has no fields arranged, that is not necessarily a source-type failure: creating the cache and report does not automatically choose rows, columns, values, or filters. Field placement is a separate step.

Creating a cache also does not guarantee that its data is current. Worksheet-based PivotTables generally need a cache refresh after source values change; connection-based sources may need a successful external refresh as well. A refresh cannot fix an incorrect source address or a missing connection. Check the PivotCache’s source and refresh status separately from the PivotTable’s layout.

Frequently Asked Questions

Is an Excel Table a separate PivotTable source type?
No. A Table is a structured worksheet data source and uses xlDatabase. The Table name can make the source easier to maintain, but it does not change the source-type category.

What is the difference between xlDatabase and xlExternal?
xlDatabase reads worksheet data such as a range or Table. xlExternal uses an external-style connection, including supported workbook connections. The key difference is how Excel obtains the records, not whether the records originally came from a database.

Can I use a Range object for SourceData?
Excel VBA may accept a Range object in some situations, but Microsoft recommends passing a string for worksheet source data to avoid unexpected type-mismatch errors. A fully qualified range address is a reliable choice.

Which source type should I use for the Data Model?
A Data Model PivotTable is generally created through the workbook’s model connection, so it is handled as a connection-based, external-style source rather than a normal worksheet range. The exact VBA setup depends on the workbook connection and Excel version.

Does creating a PivotCache automatically refresh the PivotTable?
No. Creating the cache and PivotTable establishes the source and report structure; it does not mean the data will stay current indefinitely. Refresh the relevant cache or connection after source data changes, and confirm that the source reference itself includes the new records.

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.