To update an Excel PivotTable’s data range, select the PivotTable and choose PivotTable Analyze > Change Data Source, enter the new range, and select OK. Then refresh the PivotTable; if you only need to recalculate values already inside its existing source range, use PivotTable Analyze > Refresh instead.
Change the source range in Excel for Microsoft 365
These steps apply to Excel for Microsoft 365 on Windows when the PivotTable uses a worksheet range or table as its source. Click a cell inside the PivotTable first so that the contextual PivotTable tabs appear on the ribbon. Then follow these steps:
- Select PivotTable Analyze > Change Data Source > Change Data Source. Depending on the ribbon layout, the Change Data Source command may appear directly on the PivotTable Analyze tab.
- In the dialog, enter or select the complete source range, including its header row. For example, if the data is on a sheet named Sales and now runs from A1 to F850, the range is
Sales!$A$1:$F$850. - Select OK. If Excel prompts you to confirm the change, accept it.
- Refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh.
When selecting the range with the mouse, switch to the source worksheet and drag across the full data block. Check that the selection starts at the header row and includes all records and fields. A range that leaves out a new column will not make that column available in the PivotTable Field List. A range that stops above newly added records will leave those records out, even after you refresh.
Refresh versus changing the data range
Refresh and changing the source are related but different operations. Refresh asks Excel to reread the PivotTable’s current source. It can bring in edited values or new records that are already inside that source. It does not normally expand a fixed cell reference to include rows or columns added beyond its edges.
Change Data Source changes what the PivotTable reads. For example, suppose the source is Sales!$A$1:$F$500 and you add records through row 560. Refreshing alone still uses the original 500-row range. Change the source to include row 560, then refresh. Conversely, if a sales amount inside the original range changes from 120 to 150, a refresh is usually enough.
To refresh all PivotTables and other refreshable connections in a workbook, use Data > Refresh All. Refresh All still does not enlarge a manually fixed range. If a PivotTable continues to omit rows after Refresh All, inspect its source rather than repeatedly refreshing.
Use an Excel Table to include future rows
For data that grows regularly, an Excel Table is usually easier to maintain than a manually typed range. Tables expand as records are added directly below them or entered in the next table row, and a PivotTable based on the table can read the expanded table after it is refreshed. This avoids editing a range address every time a report gains new records.
To set up a table as the source, make sure the source has one header row and no blank header cells. Select a cell in the data and choose Insert > Table, or use Home > Format as Table. Confirm that My table has headers is selected, then create the PivotTable from the table. Excel gives the table a name, such as Table1; you can view or change it on the Table Design tab.
If an existing PivotTable uses a fixed worksheet range, convert the source data to a table and then use PivotTable Analyze > Change Data Source to select the table as the source, if the dialog offers it, or recreate the PivotTable from the table. After adding records, refresh the PivotTable. Appending data somewhere else on the sheet does not automatically make it part of the table; confirm the table boundary includes the new rows before refreshing.
A table also helps keep the source organized, but it does not fix incomplete or inconsistent data. Each column should have a meaningful, unique header, and each row should represent one record. Avoid merged header cells, blank rows within the list, and totals rows mixed into the records.
When a manual range is the better choice
A fixed range can be appropriate when the source is stable, the report should include a deliberately limited set of records, or the PivotTable is part of a workbook with a controlled layout. In that case, set the range to cover the actual source and update it when the source boundaries change. Use absolute references in the range displayed by Excel, such as Orders!$A$1:$G$1200; Excel manages the source reference when you select it through the dialog.
If the source data is on another worksheet, include the sheet name as part of the range. If a sheet name contains spaces, Excel may display it in single quotes, for example 'Quarterly Sales'!$A$1:$E$300. It is safer to select the range in the dialog than to type a complex reference from memory. Confirm that the chosen cells form one rectangular block and include all intended field columns.
Changing the source can affect the PivotTable’s available fields. When adding a new source column, open the PivotTable Field List and place that field in the appropriate area, such as Rows, Columns, Values, or Filters. Updating the source makes the field available; it does not automatically decide how that field should appear in the report.
Common problems and how to diagnose them
- New rows do not appear: Check the source address in Change Data Source. If it is a fixed range, extend it to include the new records. If it is a table, confirm the table itself expanded, then refresh.
- A new field is missing: The source range may not include the new column, or the column may not have a valid header. Extend the source, refresh, and inspect the Field List.
- Excel reports that the PivotTable field name is not valid: Look for an empty header cell, a merged heading, or a selection that excludes the header row. Give each source column a nonblank heading and try again.
- Some records are still excluded: Check for gaps in the selected range, data stored outside the source block, and filters in the PivotTable. Also check whether the source contains blank or inconsistent values that affect the result.
- Refresh is unavailable or the source cannot be changed: The PivotTable may use an external connection, an OLAP source, or the workbook Data Model. These sources do not always behave like a simple worksheet range. Update the underlying connection, query, or model rather than trying to replace it with an ordinary cell range.
- The values look old after changing the source: Refresh the PivotTable after confirming the new range. For a connection-based report, check its connection and refresh status as well; a worksheet PivotTable refresh does not necessarily refresh every upstream source automatically.
When investigating, select the PivotTable and open PivotTable Analyze > Change Data Source to see what it currently uses. That is a more useful check than assuming the PivotTable is reading the whole worksheet. A PivotTable ordinarily uses a defined source, not every occupied cell on its sheet.
Excel for Mac and Excel for the web
In Excel for Mac, select the PivotTable and look for the contextual PivotTable Analyze tab, where refresh and source controls are available in supported desktop versions. Ribbon labels and layout can vary by release, so the command may be arranged differently. The key distinction remains the same: a refresh updates the current source, while changing the source alters the cells or table being read.
Excel for the web can support working with and refreshing PivotTables, but its available source-editing controls may differ from the Windows desktop app and can depend on the workbook’s source. If you cannot find Change Data Source in the web ribbon, open the workbook in the desktop version of Excel to change a worksheet-based source. For a Data Model or external connection, use the desktop tools appropriate to that source.
Good practices for reliable PivotTable sources
Clean source data makes updates more predictable. Keep one header row, one record per row, and one kind of information per column. For example, store dates in a Date column and amounts in a Sales column rather than combining several values in one cell. Avoid blank rows that divide the list, and do not place explanatory notes or subtotals inside the source block.
Before changing a source used in a shared report, consider whether other PivotTables depend on the same data or cache. A source change can alter the fields available to the PivotTable being edited, and a layout built around the old fields may need adjustment. Save the workbook before making a substantial change, especially if the report is used for recurring work. Afterward, compare a few totals with the source data to confirm that the intended records are included.
Frequently Asked Questions
How do I update a PivotTable when I add rows? If its source is a fixed range, use PivotTable Analyze > Change Data Source to extend that range, then refresh. If it is based on an Excel Table, make sure the new rows are part of the table and select Refresh.
Why does Refresh not include my new data? Refresh rereads the existing source; it does not necessarily extend a fixed range. Check the source boundary in Change Data Source and include the added rows or columns. For a table source, check that the table expanded to cover them.
How can I tell what range a PivotTable uses? Click inside it and choose PivotTable Analyze > Change Data Source. The dialog shows the source reference for a worksheet-based PivotTable. Close the dialog without changing anything if you only wanted to inspect it.
Can I make a PivotTable update automatically when source data changes? A PivotTable generally needs to be refreshed to show changed source values. A table can automatically include appended records in its source boundary, but the PivotTable still needs a refresh to display updated results. Automatic refresh behavior can also depend on the source type and workbook settings.
Can I change a PivotTable’s source to a different worksheet? Yes, for a standard worksheet-based PivotTable, select the new worksheet range in Change Data Source, including its headers, and refresh. For a PivotTable built from the Data Model or an external connection, change the underlying model or connection instead; a cell range may not be a valid replacement.
Related
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Bar Chart in Excel: How to Build and UseA pivot bar chart is a horizontal bar chart connected to a PivotTable, so you can summarize and…
- Excel Chart Slicers: How They Work and How to Add ThemAn Excel chart slicer is a clickable control that filters chart data, so a reader can focus on…
- Box Plot in Excel: How to Chart Multiple Data SeriesYes. In Excel for Microsoft 365 on Windows, put each group of numeric observations in its own column,…
- Pie Chart Creation in Excel: A Practical Step-by-Step GuideTo create a pie chart in Excel for Microsoft 365 on Windows, place category names in one column…
- Excel Histogram Bin Range: Set or Change BinsTo set an Excel histogram’s bins in Microsoft 365 for Windows, select the chart’s horizontal axis, choose Format…
- Excel Dashboard Design Templates: How to Choose and BuildExcel dashboard design templates are prebuilt workbooks or layouts that organize key metrics, charts, and filters on a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.