This lesson shows you how to refresh existing data, and add new data to an existing Excel pivot table. When you create a new Pivot Table, Excel either uses the source data you selected or automatically selects the data for you.
But data changes often, which means you also need to be able to update your pivot tables to reflect the new or changed data.
The situation now is that we have been given some additional data and need to incorporate this into our report. Specifically, we've been asked to include sales data for an additional line of products televisions for the same time period as the original report. Here's a sample of the sales data we used note the number of rows - obviously there is a lot more sales data in our report than is shown here: And here's the resulting Pivot Table: Add your new data to the existing data table.
In our case, we'll simply paste the additional rows of data into the existing sales data table. Here's a shot of some of our additional data.
As you can see, we now have rows of data: The next step is to click inside your pivot table so that the Pivot Table tools options appear in the ribbon toolbar, as shown here: From there, click Options in Excel or earlier, or Analyze in Excel This will show you a range of different options for managing your pivot table. We are interested in just two buttons on this toolbar - the Refresh button and the Change Data Source button: It's important to understand what these buttons do, and how they differ: The Refresh button will update your pivot table to reflect any changes in your existing data, such as any changes to our sales data due to customer returns.
Using the Refresh button won't automatically pick up any new data in your table unless you're using Excel's Table feature as the source for your pivot table - we'll come to that shortly. Note that you can also choose to refresh your data by right-clicking anywhere in your pivot table and choosing Refresh from the menu. The Change Data Source button will allow you specify a new data source for your pivot table. This is the option we want. Note that we're not actually changing to a new data source, we're simply going to update the existing data source to include the new data.
Click the Change Data Source button to see the following dialog box: As you can see, the data range for our existing sales report pivot table is already selected. You can do one of a couple of things here: Manually enter the correct data range for your updated data table.
In our case, this would mean changing to , since the last row of our table has changed from row to row Select the new range from the Data worksheet by selecting all the cells you want to include. In many cases the second of these two options may be the simplest.
The reason is that when you click the Change Data Source button, Excel automatically switches to the worksheet holding the data, and highlights the current data range for you, ready to modify, like this: