how to change data source in pivot table

To change the data source in a Pivot Table, you usually just select the PivotTable, use the Change Data Source command on the ribbon, then pick the new range or table and confirm.

How to Change Data Source in Pivot Table (Quick Scoop)

This guide focuses on Excel, with a quick note on Google Sheets at the end.

Step-by-step in Excel (Windows & Mac)

Basic method via Ribbon

1. Click any cell inside your PivotTable.
2. Go to the PivotTable Analyze (or Analyze / Options depending on Excel version) tab on the ribbon.
3. In the Data group, click Change Data Source → Change Data Source.
4. In the Change PivotTable Data Source dialog, check the current range in Table/Range.
5. Click into the Table/Range box, then:
   • Select the new cell range on the sheet, or
   • Type the new range (for example Sheet1!$A$1:$D$500), or
   • Enter or select an Excel Table name (for example tblSales).
6. Click OK. Your PivotTable now uses the new source.

Keyboard shortcut (Excel)

• Click inside the PivotTable.
• Press Alt + J T to open PivotTable Analyze tools.
• Then press R then R (or in some versions I then D) to open the Change PivotTable Data Source dialog.
• Adjust the range or table as above and press Enter.

Using an Excel Table as a Dynamic Source

If your data grows frequently, use an Excel Table so the PivotTable range auto-expands.

1. Select any cell in your raw data.
2. Go to Insert → Table and confirm the range, ensure My table has headers is checked, click OK.
3. With the table selected, go to Table Design and give it a clear name (for example tblOrders).

4. Go back to your PivotTable, choose PivotTable Analyze → Change Data Source.
5. In Table/Range , type the table name (for example tblOrders) instead of a fixed range.
6. Click OK , then Refresh the PivotTable when new rows are added.

Now whenever you add data to the bottom of the table, the PivotTable source automatically includes it after a refresh.

Common Pitfalls and Fixes

• New rows not showing : Your source range likely stops before the new row (for example up to row 100 while you added data in row 101). Open Change Data Source and extend the range, or switch to a Table as above.

• Wrong sheet/range : In the dialog, verify the sheet name and range reference (for example Sheet2!$A$1:$F$1000).

• External data source : In the Change PivotTable Data Source dialog, choose the option to use a different connection if your PivotTable is based on a query / data model rather than a worksheet range.

Quick Note: Google Sheets Pivot Table

To change the data source of a Pivot table in Google Sheets :

1. Click in the Pivot table; open the Pivot table editor (or click Edit under the pivot).
2. Next to Data , click the Select data range icon.
3. In the Select a data range dialog, choose the updated range on the relevant sheet.
4. Click OK ; the Pivot updates to the new data source.

SEO Extras (for your post)

• Focus keyword : “how to change data source in pivot table” can be used in the title, first paragraph, and 1–2 subheadings.

• Suggested meta description (under ~155 characters):
  • “Learn how to change the data source in a Pivot Table in Excel and Google Sheets, including dynamic ranges with tables and quick shortcuts.”