To change a PivotTable’s source range in Excel for Microsoft 365 on Windows, click inside the PivotTable, choose PivotTable Analyze > Change Data Source > Change Data Source, enter or select the new source in the Table/Range box, and choose OK. Include the header row and every row and column you want the PivotTable to use; then refresh the PivotTable if the displayed results have not updated.
What changing the data range actually does
A PivotTable summarizes data from a source, such as a worksheet range, an Excel table, or an external connection. Its source determines which records and fields are available to summarize. If the source is a fixed range such as Sales!$A$1:$D$500, adding records below row 500 does not automatically add them to that PivotTable. Refreshing the PivotTable updates its results from its current source; it does not necessarily enlarge a fixed source range.
Changing the range is useful when new rows or columns have been added outside the original source, or when you want the PivotTable to use a different block of data. It is not the same as changing the PivotTable layout. To show a field in a different position, drag it among the Rows, Columns, Values, and Filters areas in the PivotTable Fields pane. To include newly added source records, update the source range or use a source that expands with the data.
Change the source range in Excel for Windows
Use the desktop Excel app and follow these steps:
- Check the source data first. It should have one clear header row, with a distinct heading for each column. Remove merged header cells and avoid completely blank rows or columns inside the data.
- Click any cell inside the PivotTable. Excel displays the contextual PivotTable Analyze tab on the ribbon.
- Choose PivotTable Analyze > Change Data Source > Change Data Source. The Change PivotTable Data Source dialog opens.
- In Table/Range, enter the complete range, including headers. For example, if a sheet named Sales has headers in row 1 and records through row 650 in columns A through D, use
Sales!$A$1:$D$650. - Alternatively, use the range-selection button in the dialog and select the source cells on the worksheet. Make sure the selection includes all intended rows and columns, not the PivotTable output.
- Choose OK. Review the PivotTable fields and results, and use PivotTable Analyze > Refresh if required.
When the source is on another sheet, Excel may display the sheet name and cell references in the box. If a sheet name contains spaces, Excel handles the required apostrophes when you select the range; for example, a reference may appear as 'Quarterly Sales'!$A$1:$D$650. Selecting cells in the dialog is often less error-prone than typing a long reference.
Use an Excel table to handle future rows
If the source grows regularly, an Excel table is usually more convenient than repeatedly editing a fixed address. A table expands as you add data directly beneath it or enter new rows within it, and it gives the source a recognizable name. This does not mean the PivotTable updates instantly: you still need to refresh it after adding records.
- Select a cell in the source data and choose Insert > Table. Confirm the selected range and check My table has headers if the first row contains field names.
- Give the table a descriptive name from the Table Design tab, such as
SalesData. Table names cannot contain spaces. - For an existing PivotTable, choose PivotTable Analyze > Change Data Source and set the source to the table name, if the dialog accepts it. Another option is to create a new PivotTable from the table using Insert > PivotTable.
- Add records to the table and refresh the PivotTable when you want the summary to include them.
A table helps prevent the common mistake of adding rows just beyond a fixed range. It also makes the source easier to recognize than a long cell address. However, it cannot correct inconsistent field names or misplaced records, and refresh remains necessary for the PivotTable to recalculate its summary.
Choose the right source for your situation
Use a fixed worksheet range when the source is stable and you deliberately want to limit the records included. For a one-time extension, change the last row in the range—for example, from Sales!$A$1:$D$500 to Sales!$A$1:$D$650. If the data gains rows every week or month, a table is generally easier to maintain.
Changing to a different range is appropriate when the source has moved or when the PivotTable should summarize a separate dataset with the same general structure. Before switching, check that the new range has the intended columns and compatible headers. A PivotTable using a table or range generally expects a single header row and a consistent set of columns; it is not a tool for automatically combining unrelated blocks with different layouts.
For data imported from a database, a Power Query query, or another workbook connection, the source may be managed through the connection rather than a simple worksheet address. In that case, updating the query or connection can be the right change, rather than typing a new worksheet range into the PivotTable dialog.
Refresh the PivotTable and understand what refresh changes
After the source is corrected, refresh the PivotTable to recalculate its summary. Click inside it and choose PivotTable Analyze > Refresh. To refresh all PivotTables and connections in the workbook, use Data > Refresh All. Refreshing reads from the current source; it does not fix a source definition that stops before the new rows.
For example, suppose a PivotTable summarizes 500 sales records and a further 150 are added in rows 502 through 651. If its source still ends at row 501, a refresh can update edits to the original 500 records but will not include the extra 150. Extend the range to row 651 or use an expanding table as the source, then refresh. If the extra records appear but the totals seem unchanged, check filters, date groupings, and whether the new rows contain values in the fields being summarized.
Excel may retain old field items in a PivotTable’s filter lists after source records have been removed. Those stored items are separate from whether the current source range is correct. If obsolete entries remain visible, check the PivotTable options for retaining deleted items and refresh the PivotTable; the exact behavior can depend on the workbook and source type.
Common problems and practical fixes
- Change Data Source is unavailable: The PivotTable may use the Excel Data Model, an OLAP cube, or another external connection. These sources are managed through their model or connection, so the standard range dialog may not apply. Check the workbook’s queries and connections or use the relevant Power Pivot tools.
- Excel rejects the source: Confirm that the range exists, includes the header row, and does not contain blank or duplicate column headings. Make sure you have not selected the PivotTable itself or an incomplete block.
- New rows are missing after refresh: Look at the source address in the Change Data Source dialog. If it is a fixed range, extend its last row or change to a table-based source. Refreshing alone does not expand every fixed range.
- New columns do not appear as fields: Expand the source range horizontally to include the new columns, then check the PivotTable Fields pane. A new column should have a header in the source’s header row.
- The result contains duplicate or unexpected totals: Inspect the source for repeated records, subtotals mixed into the data, or text stored where numeric values are expected. Changing the range can include those rows, but it does not clean or deduplicate the source.
- The PivotTable changes shape: A larger source may expose new field values or fields. Check report filters and the field layout; the source range controls available data, while the layout controls how Excel displays the summary.
Excel for Mac and Excel for the web
In current desktop Excel for Mac, select a cell in the PivotTable and look for PivotTable Analyze > Change Data Source. Depending on the ribbon presentation and version, the contextual tab may be labeled Analyze. The dialog performs the same basic job: select or enter the complete source and confirm. If a command is not visible, check that the active cell is inside the PivotTable and that the ribbon is expanded.
Excel for the web can handle many everyday PivotTable tasks, but available source-management commands can differ by feature and workbook connection. If you cannot find Change Data Source in the browser, open the workbook in the desktop app to change a range or manage an external source. A PivotTable based on a model or connection may require its original data-management tools even in desktop Excel.
A reliable workflow before sharing the workbook
Changing a source range can alter every summary that depends on it, so verify the result rather than assuming the dialog change is sufficient. A careful check is especially important when the PivotTable feeds a report, chart, or presentation.
- Save the workbook before making a significant source change.
- Confirm the source has one header row, consistent columns, and the expected number of records.
- Update the PivotTable source and refresh it.
- Compare a known total, such as total sales, with a direct check of the source data.
- Inspect filters, slicers, and charts connected to the PivotTable, since they can affect what viewers see.
If several PivotTables use the same source, identify which ones need updating. PivotTables created separately may not all share the same source definition or cache, so changing one does not guarantee every other summary has changed. Verify each relevant PivotTable and refresh as appropriate.
Frequently Asked Questions
How do I include new rows in an existing PivotTable?
Click the PivotTable, choose PivotTable Analyze > Change Data Source, extend the range to cover the new rows, and confirm. Refresh afterward if needed. For recurring additions, use an Excel table as the source and refresh the PivotTable after adding records.
Why does refreshing my PivotTable not include the latest data?
Refresh updates the summary from its existing source; it does not necessarily expand a fixed range. Open Change Data Source and check whether the source ends above the new records. Extend it or use a table-based source, then refresh.
Can I change a PivotTable’s source to another worksheet?
Yes, if the PivotTable uses a standard worksheet range. In the Change Data Source dialog, select the cells on the other worksheet or enter that sheet’s range, including its header row. For a connection-based PivotTable, change the connection or query instead.
Can an Excel table automatically update a PivotTable?
The table can expand to include new rows, but the PivotTable’s displayed summary still needs a refresh to read those rows. A table reduces the need to edit the source address each time; it does not guarantee an immediate recalculation.
Why is Change Data Source greyed out?
The PivotTable may be based on the Data Model, an OLAP source, or an external connection that cannot be changed with the ordinary range dialog. Review the workbook’s connection or query settings, or use the tools that manage the model or source.
Does changing the range change the PivotTable layout?
No. The source determines which fields and records are available. The Rows, Columns, Values, and Filters areas determine how those fields are arranged and summarized. Adjust the field layout separately if you want a different report.
Related
- Circular Cell Reference in Excel: Causes and Fixes
- Circular Reference Settings in Excel: How They Work
- Change the Print Area in Excel: Steps and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Dashboard Software Alternatives and How to ChooseExcel dashboard software alternatives include Microsoft Power BI, Tableau, Looker Studio, Qlik Sense, Zoho Analytics, and Metabase. The…
- Excel Combo Chart: Stacked Bar and LineIn Excel for Microsoft 365 on Windows, you can combine stacked columns and a line by selecting your…
- Excel Pivot: Add Week, Month, and Quarter SlicersTo add week, month, and quarter slicers to an Excel PivotTable, add week-start, month-start, and quarter-start fields to…
- Link a Pivot Chart to a Slicer in ExcelTo link a slicer to an Excel PivotChart, select the PivotTable or PivotChart, choose PivotTable Analyze > Insert…
- Excel Pivot Table: How to Remove Zero ValuesTo remove zero-valued results from an Excel PivotTable, open the drop-down for the row or column field, choose…
- Limit Pivot Chart Data in Excel with Filters and Top 8To limit the data shown in an Excel PivotChart, filter its PivotTable fields: use the field dropdowns on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.