This article will give you a summary of pivot table data refresh, and introduce three methods of dynamically refreshing data: VBA automatically refreshes pivot tables, super tables, and existing connections. There is always one of these refresh methods that suits you, and the operation is super simple. You can complete dynamic refresh with just a few clicks of a button. Remember to bookmark it!
Pivot table is a commonly used skill in EXCEL, which can help us quickly statistically analyze large amounts of data. And as the layout changes, the pivot table will immediately recalculate the data according to the new arrangement, which is very practical in daily work. However, if the data source is added, the PivotTable cannot be updated synchronously. So today I will introduce to you several ways to dynamically refresh data in Excel pivot tables.
As shown in the figure, this data source lists sales in different cities.
1. Select any cell in the table area and click under the Insert tab "Pivot table".
2. In the "Create Pivot Table" window, all contiguous areas are automatically selected in the table area. In order to facilitate viewing, put the Pivot Table location in the same work under the table. Click OK.
#3. Now put "City" into the row label and "Sales" into the value range. For the convenience of comparison, subsequent creation is done in the same way.
Complete as follows:
4. Next, add the following data in the last row of the table. At this time, the total value is: The original 255418 became 258418.
5. Select the Pivot Table, and the Pivot Table tool will appear above the menu bar. Click under the "Analysis" tab "refresh".
But the pivot table has not changed.
#6. This situation is because the new row is not added to the data source area of the pivot table, so the data source needs to be modified. Select the PivotTable and click "Change Data Source" under the "Analysis" tab below the PivotTable tool.
Then re-select the area in the table area of the "Change PivotTable Data Source" window and select the new rows. The area is changed to "Sheet1!$A$1:$D$71".
Just click refresh again.
Through this example, we found that if the data increases, the pivot table needs to change the data source to update, but in actual work, if Due to frequent data changes, is there any way to quickly refresh the Excel pivot table?
2. Excel pivot table dynamically refreshes data
The settings are completed as follows:
2. Click Insert under the "Development Tools" tab, in the ActiveX control Command button, creates a button on the worksheet.
If the form does not have the Development Tools tab, click File-Options, in the "Customize Ribbon" on the left side of the "EXCEL Options" window, select "Main Tab" Development Tools" is added to the custom ribbon on the right.
#3. Right-click the button you just added in the worksheet and select "View Code". Enter the following code in the VBA window that pops up.
Private Sub CommandButton1_Click() ActiveSheet.PivotTables("数据透视表9").PivotCache.Refresh End Sub
PivotTable 9 in the code is the name of the PivotTable.
4. Then click "Design Mode" on the Development Tools tab to cancel the design mode of the button. The button can be clicked normally.
5. Add a row of data at the end of the worksheet data source as follows. After adding, the total value is 258418
6 .Then click the button to refresh, and the pivot table will be updated in real time.
Summary: This method includes other blank areas when selecting the data source, and it can be dynamically updated if data is added later. And adding a button through VBA makes the refresh operation more convenient. However, the problem is that once invalid data appears in other selected areas, the PivotTable will also include it.
1. Click "Existing Connections" under the Data tab. Click "Browse More" in the pop-up window.
2. Find the workbook in the "Select Data Source" window and click to open
In the "Select Table" window, find the worksheet where the data is placed and click "OK".
3. In the "Import Data" window, select the data to display in a pivot table. In order to facilitate viewing the effect, place it here on the existing worksheet. .
Complete as follows:
4. Also add data in the last row as follows. After adding, the total value becomes 258418
5. Select the PivotTable, click the "Analysis" tab under the PivotTable tool, and click "Refresh". The pivot table will automatically refresh the data.
Summary: This method turns the EXCEL worksheet into a connection and inserts the pivot table through the connection. The advantage is that changes in the worksheet can be updated in time, but again, when we choose this method, the worksheet cannot put other data, and the pivot table should be built on other worksheets as much as possible to avoid errors.
1. Select any cell in the worksheet area, hold down Ctrl T, the table data in the following window The source will automatically select the worksheet area. The first row of the table here is the title, so check "Table contains title".
#2. Insert a pivot table based on this super table. Select any cell in the table range and insert a PivotTable report in the same worksheet. The table area will be set to the name of the supertable: Table 5.
# Also put "City" in the row label and "Sales" in the value area. Complete as follows:
3. Add the following data in the last row of the table. After adding, the total value is 258418
4. Select the pivot table and click "Analyze" under the pivot table tool. ” tab and click “Refresh”. This achieves dynamic updating.
Super table is a function added since Excel2007. It solves the problem that the first two methods cannot intelligently select the data source area. Super table can automatically increase or decrease the data source area, which is its biggest advantage as a dynamic data source.
The methods have been introduced. Each of the above three methods has its own advantages and disadvantages. I hope you can choose flexibly according to the actual needs of your work. If you think it’s good, give me a like!
Related learning recommendations:excel tutorial
The above is the detailed content of Excel Pivot Table Learning: Three Methods of Dynamically Refreshing Data. For more information, please follow other related articles on the PHP Chinese website!