An Excel PivotTable data source is the table, cell range, workbook data model, or external connection that supplies the records summarized in the PivotTable. In Excel for Microsoft 365 on Windows, choose or inspect it by selecting the PivotTable and using PivotTable Analyze > Change Data Source; then refresh the PivotTable when its underlying data changes.
What a PivotTable data source contains
A PivotTable does not usually summarize isolated numbers: it reads a structured set of records, with one record per row and field names in the first row. For example, a sales list might have columns named Date, Region, Product, Quantity, and Sales. The PivotTable uses those fields to group and calculate information, such as sales by region or product.
The source matters because it defines which records and fields are available. If the source excludes recent rows, those records will not appear after a refresh. If a column heading is missing or the source is arranged as a report with several header rows, Excel may be unable to interpret the data correctly. A PivotTable’s layout and its data source are related but different: moving Sales from Values to Rows changes the view, while changing the source changes the underlying records or fields available to the PivotTable.
Which kinds of sources Excel can use
For a simple workbook, the source is often a cell range, such as A1:E500 on a worksheet. Excel can also use an Excel Table, which is a named, expandable structure created from a range. Tables are generally more convenient for lists that grow over time because new rows added directly below the table are normally included in the table automatically.
Other PivotTables can use data from an external connection, such as a database or a file, or from the workbook Data Model. These options are useful when records are too numerous or complex for a single flat range, when data must be refreshed from another system, or when relationships between multiple tables are needed. The appropriate choice depends on where the data lives and how it is maintained; a connection-based PivotTable is not simply a worksheet range with a different name.
- Cell range: straightforward for a stable list, but the selected range may need to be extended when records are added.
- Excel Table: a practical choice for a list that changes regularly and stays in the same workbook.
- External connection or Data Model: useful for imported, larger, or related datasets, but refresh and connection settings also matter.
How to create a PivotTable from a source
Before creating the PivotTable, check that the data is arranged in columns, has one clear header row, and contains no completely blank rows or columns inside the list. Then click a cell in the data or select the intended range. In Excel for Microsoft 365 on Windows, choose Insert > PivotTable. Excel usually detects the current data region when a cell inside it is selected; inspect the proposed table or range in the dialog before continuing.
Choose whether to place the PivotTable on a new worksheet or an existing worksheet, then select OK. Excel opens the PivotTable Fields pane, where you can place field names in areas such as Rows, Columns, Values, and Filters. If the source is an Excel Table, select a cell within that table before choosing Insert > PivotTable. This makes the intended source easier to identify and helps the PivotTable use the table as its source instead of a fixed block of cells.
If you want to use multiple related tables, a connection, or the Data Model, choose the relevant option in the PivotTable creation dialog rather than assuming that a single worksheet range will provide those capabilities. The available choices can depend on the source and Excel edition. Excel for the web has different feature coverage from desktop Excel; for complex connections or Data Model work, use the desktop application and check that the workbook’s connection is available there.
How to check or change the current source
Click inside the PivotTable so its contextual ribbon tabs appear. In Excel for Microsoft 365 on Windows, choose PivotTable Analyze > Change Data Source. In the dialog, review the table or range shown. For a range-based PivotTable, enter or select the complete new range, including the header row, and confirm. The PivotTable may then need a refresh to display the records from the revised source.
Changing the source is not the same as editing a field arrangement. If you only want to move a field, use the PivotTable Fields pane. If you added rows outside the original source range, use Change Data Source to include them—or, for future additions, consider converting the source to an Excel Table. If the PivotTable is based on an external connection or the Data Model, the usual range-editing workflow may not apply. Review the connection or model and use the appropriate data import or connection controls instead.
Changing a source can affect more than the visible result. A replacement source may have different column names, data types, or fields, so existing field placements and calculations may need attention. If the source is on another worksheet, make sure the reference points to the intended sheet and includes all required columns. Save a copy before making a substantial source change in an important workbook.
Refresh the PivotTable after source data changes
A PivotTable generally works from stored or cached source information rather than recalculating its entire display every time you edit a source cell. After changing existing values, adding records to an included table, or updating connected data, refresh it to bring the latest information into the summary. Select a cell in the PivotTable and choose PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. To refresh connections and PivotTables across a workbook, use Data > Refresh All.
Refresh does not necessarily expand a fixed cell-range source. If a range originally ended at row 500 and new records are entered in rows 501 onward, refreshing can still leave those rows out because they are not part of the source definition. Change the source range, or use an Excel Table designed to expand as records are appended. By contrast, if new rows are already included in the source table, refreshing is normally the step that updates the PivotTable’s summary.
For external data, refresh also depends on access to the source and any required credentials or connection settings. A refresh error can therefore indicate a network, permission, file path, or connection issue rather than a problem with the PivotTable layout itself.
Prepare clean source data
Good source structure prevents many PivotTable errors. Give every column a distinct, nonblank heading, and keep each heading in a single row. Store one kind of information in each column: do not combine a customer name and address in one field if you need to analyze them separately. Avoid merged cells in the source, subtotal rows inside the list, and decorative title lines above the header row.
Use consistent data types within a column. For instance, dates should be actual Excel dates rather than a mixture of dates and text that merely looks like dates. Likewise, numbers stored as text may not summarize as expected. A common symptom is that a field added to Values displays a count instead of a sum; mixed or text-formatted entries are one possible cause. Inspect the source values and correct their types, then refresh.
Repeated values are not automatically a flaw. A Region column will naturally repeat names across many sales records, and the PivotTable groups those values. The concern is inconsistent spelling or formatting, such as West, west, and West with an extra space, which Excel can treat as separate items. Standardize labels when they represent the same category.
Common source problems and practical fixes
- New rows are missing: Check whether the source is a fixed range that ends before the new records. Extend it through Change Data Source or use an Excel Table for ongoing additions, then refresh.
- A field is absent: Verify that its column is included in the source and has a valid header. If you changed the source, confirm that the new source contains the expected column.
- Excel reports an invalid field name: Look for a blank header cell, merged header cells, or a selected range that does not start with the actual headings.
- Values show as Count instead of Sum: Check whether the source column contains text, blanks, or mixed types. Correct the source values consistently and refresh; then check the value-field summary setting if necessary.
- Refresh fails: For a connection-based source, verify access to the file or service and the connection settings. For a workbook range, check that the source sheet and range still exist.
When troubleshooting, first identify the source type rather than repeatedly rebuilding the PivotTable. A range problem, a data-cleanliness problem, and a broken external connection require different fixes. The Change Data Source dialog can help confirm a worksheet source, while connection-based PivotTables may require checking the workbook’s queries or connections.
Excel Tables, fixed ranges, and external sources compared
An Excel Table is often the simplest source for a regularly updated list maintained in the workbook. To create one, select the data and choose Insert > Table, making sure the option indicating that the table has headers is correct. Give the table a useful name if desired. When new records are added as part of the table, its structured source can grow with them; refreshing the PivotTable then updates the summary. This reduces the risk of forgetting to extend a fixed range.
A fixed range is reasonable when the dataset is stable or when deliberate control over included rows is important. It can also be useful for a one-time analysis. Its limitation is that later records beyond the selected cells remain outside the PivotTable’s source until the range is changed. External connections and the Data Model make sense when data is maintained elsewhere or needs to combine related tables, but they bring additional dependencies such as refresh access and connection configuration.
There is no universally best source. Consider how often records are added, who maintains them, whether the source is stored in the workbook, and whether the analysis needs one list or multiple related tables. Choose the least complicated source that meets those needs and can be refreshed reliably by the people who use the workbook.
Frequently Asked Questions
How do I find the data source for an Excel PivotTable?
Select a cell in the PivotTable and choose PivotTable Analyze > Change Data Source. For a worksheet-based PivotTable, inspect the table or range shown in the dialog. A connection- or Data Model-based PivotTable may need to be checked through its connection or model settings instead.
Why is my PivotTable not including new rows?
The new rows may fall outside a fixed source range. Change the source to include them, or use an Excel Table that expands as records are appended. Then refresh the PivotTable so its summary reflects the included data.
Does Refresh change the PivotTable data source?
No. Refresh updates the PivotTable from its current source; it does not normally redefine a fixed range to include rows beyond that range. Use Change Data Source when the source definition itself needs to change.
Can a PivotTable use data from more than one table?
Yes, when the tables are brought into or used with the workbook Data Model and have appropriate relationships. This is different from selecting a single rectangular worksheet range. For that setup, use the Data Model options when creating the PivotTable and confirm the tables’ relationships.
Can I change a PivotTable source without rebuilding the report?
Often, yes. Use Change Data Source for a worksheet range or table, then refresh and verify the field layout. If the replacement has different columns or names, some fields or calculations may need to be adjusted. Connection-based sources can require changes through their connection or query settings.
A reliable source workflow
For a dependable PivotTable, start with a clean, headed list and choose a source that matches how the data will grow. Use a Table for a regularly appended worksheet list; use a fixed range when the boundaries are intentionally stable; and use a connection or Data Model when the data genuinely calls for it. When results look incomplete, verify the source definition first, then refresh and check the source data types and headers. This sequence separates the question of what Excel is reading from the question of how the PivotTable is displaying it.
Related
- How to Insert Speaker Notes in PowerPoint Step by Step
- Excel Often Not Responding? Causes, Fixes, and Prevention
- Free Yearly Expense Tracking Spreadsheet: Setup Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Filter Contains: How to Filter TextTo filter an Excel PivotTable for labels containing particular text, open the dropdown for the row or column…
- Excel Pivot Table: How to Expand All GroupsTo expand a PivotTable field across all its items in Excel for Microsoft 365 on Windows, right-click an…
- Excel Chart in PowerPoint: How to Copy and Link ItTo put an Excel chart in PowerPoint, select the chart in Excel, copy it, then use PowerPoint’s paste…
- Microsoft Excel Combo Charts: How to Create and FormatA Microsoft Excel combo chart displays two or more data series as different chart types, such as columns…
- Power Pivot Column Formatting: Set Formats That StickIn Excel for Microsoft 365 on Windows, open the Power Pivot window, select the column, then choose a…
- Enable Power Pivot in Excel: Steps, Versions, and FixesTo enable Power Pivot in Excel for Microsoft 365 on Windows, go to File > Options > Add-ins,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.