How to Change the Data Source of an Excel Pivot Table

Table of contents:

How to Change the Data Source of an Excel Pivot Table
How to Change the Data Source of an Excel Pivot Table
Anonim

The Microsoft Excel program is designed to allow users to sort and interpret data using advanced features such as pivot tables, formulas and macros. It may happen that a user wants to modify the input data to carry out evaluations on the results. Changing the origin of a PivotTable may not be an easy task, because the source data is often located on a separate sheet, but it is possible to do so without losing the formatting of your table.

Steps

Change an Excel Pivot Table Source Step 1
Change an Excel Pivot Table Source Step 1

Step 1. Open Microsoft Excel

You can use the icon on the desktop, search for it in the Programs in the Start menu or the icon in the Quick Launch bar, depending on the configuration of your computer

Change an Excel Pivot Table Source Step 2
Change an Excel Pivot Table Source Step 2

Step 2. Open the file that contains the pivot table and data

Change an Excel Pivot Table Source Step 3
Change an Excel Pivot Table Source Step 3

Step 3. Make the necessary changes to the source data

  • You may need to insert or delete columns and rows.
  • Make sure that all the columns you have entered have a title that describes them.
Change an Excel Pivot Table Source Step 4
Change an Excel Pivot Table Source Step 4

Step 4. Select the sheet containing the pivot table by clicking on the appropriate tab

Change an Excel Pivot Table Source Step 5
Change an Excel Pivot Table Source Step 5

Step 5. Click inside the pivot table to open the pivot table tools menu

  • On Excel 2007 and 2010, the PivotTable Tools menu will appear, highlighted in red, above the Options and Style tabs in the ribbon.
  • In Excel 2003, select "PivotTable and PivotChart Reports" from the Data menu.
Change an Excel Pivot Table Source Step 6
Change an Excel Pivot Table Source Step 6

Step 6. Edit the source range of your pivot table

  • On Excel 2007 and 2010, select "Edit Data Source" from the Data option group.
  • On Excel 2003, start the wizard by right clicking inside the pivot table and selecting "Wizard" from the menu that appears. Click the "Next" button to the screen with the data source range.
  • In all versions of Excel, after selecting the source data range, click and drag to select the new range for your data.
  • You can change the range to include multiple columns and rows.
Change an Excel Pivot Table Source Step 7
Change an Excel Pivot Table Source Step 7

Step 7. Refresh the pivot table by clicking the "Refresh" button

This button may have a red exclamation point, the green recycle icon, or just the word "Update" depending on the version and configuration of your copy of Excel

Advice

  • You cannot change the data by manipulating the pivot table. All changes must be made in the source data and then the table must be updated.
  • Make sure you update your PivotTable every time you make changes to the source data. The changes will otherwise not appear in the pivot table.
  • To change the source data of a pivot chart, the method to follow is the same. Remember to change the source data and then update the chart.

Recommended:

How to Add a Column to a Pivot Table

How to Add a Column to a Pivot Table

This article explains how to create and insert a new column in a PivotTable in Microsoft Excel with the PivotTable tools. You can turn a row, field or value into a column, or create a new one calculated with a custom formula. Steps Method 1 of 2:

How to Add Data to a Pivot Table: 11 Steps

How to Add Data to a Pivot Table: 11 Steps

This article explains how you can add new data to an existing Microsoft Excel PivotTable. You can perform this change on both a Windows and a Mac computer. Steps Step 1. Open the Excel file which contains the pivot table Double-click the document icon to open it directly in Excel.

How to Add Rows to a Pivot Table

How to Add Rows to a Pivot Table

Pivot tables are used to group and organize data in a spreadsheet. The primary advantage of pivot tables is that you can organize and manipulate data in a variety of ways, based on user needs and the conclusions that can be drawn from the information.

How to Add a Custom Field to a Pivot Table

How to Add a Custom Field to a Pivot Table

On some occasions, you need to enter more information than a normal pivot table can show. In these rare cases, it might be helpful to add a custom and calculated field to the pivot table. You can configure these fields to show averages, percentages, or the maximum and minimum values of a field.

How to Add a Filter to a Pivot Table

How to Add a Filter to a Pivot Table

PivotTables can provide great support for analyzing the data in a worksheet, but sometimes even the best (best designed) PivotTable can show more information than needed. In these cases, it may be useful to add filters. Once set, a filter can be changed to suit the viewing needs of different users.