How to adjust Pivot Table Range

PivotTables are a powerful tool in Excel that allows users to quickly summarize large amounts of data in a concise and organized manner. One of the key features of pivot tables is their flexibility, which allows users to adjust the table’s range to include new data or remove data that is no longer needed. In this Tutorial, we will explore the steps involved in adjusting the PivotTable range in Excel.

Here we have a dataset that contains the Name, Gender, and Salary of employees of a company. This dataset is updated regularly, we will learn how to update the range of the PivotTable according to the updated dataset. Let’s have a look at the dataset first.

Method – 1 Change Data Source

Step – 1 Update the Source of Data

• Click on the PivotTable that you created.
• ANALYZE tab will appear on the ribbon, click on it.
• In the Data group open the drop-down menu of Change Data Source.
• In the drop-down menu click on Change Data Source.

Step – 2 Update the range.

• After right-clicking on Change Data Source a dialog box will appear.
• In the dialog box in Table/Range blank space type or select the whole updated dataset range then click OK.
• PivotTable range will be updated.

Method – 2 Create a Table

Step – 1 Select the dataset

• Select the whole dataset.
• Go to the Insert tab and in the Table’s group click on Table.
• In the Create Table dialog box give the range of the dataset.
• Tick the check box My table has headers if your dataset contains headers.
• Click OK.

Step – 2 Create a Pivot table

• Go to the Insert Tab.
• Select the dataset.
• In the Tables group click on PivotTable.
• In the Create PivotTable dialog box tick the check box of the Existing worksheet to create the pivot table on the same sheet.
• Give the specific location where to create the pivot table in that sheet, in our case it was E1.
• Click OK.
• In the PivotTable Fields choose the fields you want in the pivot table, as shown above.

Step – 3 Refresh the Pivot Table.

• When you update the dataset you can just right-click anywhere on the pivot table and click on Refresh.
• PivotTable will get updated automatically.