Đề Xuất 3/2023 # How To Refresh Pivot Table In Excel (Manually + Auto # Top 3 Like

Cập nhật nội dung chi tiết về How To Refresh Pivot Table In Excel (Manually + Auto mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Once you have created a Pivot Table, it doesn’t automatically refresh when you add new data or change the existing data.

Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update.

You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table.

This tutorial covers a couple of ways to do this.

Refresh Pivot Table

This option is best suited when there are changes in the existing data source and you want to refresh the pivot table to reflect these changes.

Here are the steps to refresh a Pivot Table:

Select Refresh.

This will instantly refresh the Pivot Table.

You can also by selecting any cell in the Pivot Table and using the keyboard shortcut ALT + F5.

Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table. If you do this, you can also use the refresh technique to update the Pivot Table even when new data (rows/columns) are added to the data source (since an Excel Table automatically accounts for new rows/columns that are added).

Update Pivot Table by Changing the Data Source

If you’ve added new rows/columns to the data source, you need to change the data source to make sure new rows/columns are a part of the dataset.

To do this:

Select any cell in the Pivot Table.

In the Change PivotTable Data Source dialog box, update the range to include new data.

Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option. You can simply refresh the Pivot Table and it’ll account for the new rows/columns.

Autorefresh Pivot Table Using a VBA Macro

To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code.

Here is the VBA code:

Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh End Sub

Decoding the Code: This is a change event which gets triggered whenever there is a change in the sheet that contains the source data. As soon as there is a change, the code refreshes the Pivot Cache of the Pivot Table with the name PivotTable1.

You need to modify this code to make it work for your workbook:

“Sheet1” – change this part of the code with the name of the sheet that has the Pivot Table.

Where to put this VBA code:

Press Alt + F11. It will open the VB Editor window.

In the VB Editor, there would be Project explorer on the left (that has the names of all the worksheets). If it’s not there, press Control + R to make it visible.

In the code window on the right, copy paste the given code.

Close the VB Editor.

Now when you change anything in the data source, the Pivot Table would automatically get refreshed.

Note: Since there is a macro in the workbook, save this with .xls or .xlsm extension.

You May Also Like the Following Pivot Table Tutorials:

How To Refresh Pivot Table On File Open In Excel?

How to refresh pivot table on file open in Excel?

In default, when you change your data in the table, the relative pivot table will not refresh at the same time. Here I will tell you how to refresh the pivot table when opening the file in Excel.

Refresh pivot table on file open

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier…

Refresh Pivot Tables Automatically When Source Data Changes

Bottom Line: Learn how to use a simple macro to refresh pivot tables automatically whenever changes are made to the source data. I also share a non-macro solution to update the pivot tables when the file is opened. Includes video tutorial and Excel file download.

Skill Level: Intermediate

Download the Excel File

If you learn best by doing it on your own, you can download the file I’m using in the video to follow along. Here is the Excel file that contains the VBA code.

Update Pivot Tables Automatically

Can your pivot tables be updated immediately and automatically when their source data changes?

Absolutely. It requires the use of a really simple macro that I will show you how to create below.

Also, if you are new to pivot tables, I have a series to walk you through what they are and how to use them. Watch the first video in that series on Pivot Tables & Dashboards

To automatically update our pivot tables, we are going to write a macro with one simple instruction. That instruction basically says: when I make a change to my worksheet, refresh all the pivot tables and data connections. Here are the steps to create the macro.

1. Open the Visual Basic Editor.

2. Open the Sheet Module that contains your source data.

3. Add a new event for worksheet changes.

Note: The SelectionChange event that is added by default will run any time the user selects a cell in the sheet. Since we only want the code to run when the user edits/changes cells , we use the Change event. Checkout my article on VBA Code Modules & How to Run Macros Based on User Events to learn more about the sheet modules and events.

4. Add the VBA code to refresh all pivot tables.

Next, just below the Worksheet_Change line, type in this instruction:

ThisWorkbook.RefreshAll

Add this line of code to the Worksheet_Change event will refresh the workbook whenever a change is made to the worksheet that the code is in.

Pivot Table & Source Data on Same Sheet

The refresh puts the event in a recursive loop, and can end up crashing Excel. Here is the code to prevent that.

Application.EnableEvents = False ThisWorkbook.RefreshAll Application.EnableEvents = True

Checking to Ensure Your Macro Is Running

One way to check if the macro is working is to test it. Make a change to the source data and see if it is reflected in your pivot table. If your change isn’t easy to spot because you have too much data, or for some other reason, there’s another way to see if your macro is firing.

The keyboard shortcut to toggle a breakpoint on/off is: F9

Now whenever an action occurs that triggers the macro, Excel will jump to the VB Editor and pause the macro so that you can check the code. In our case, that action is any change being made in the worksheet.

If you make a change to the worksheet and Excel doesn’t pull you into the VB Editor, you know there is a problem with the macro not running. If this is the case, it’s likely that you haven’t saved the file as a macro-enabled workbook (.xlsm), and/or enabled macros. You might need to save & close the file, then re-open it and enable macros.

The keyboard shortcut to clear all breakpoints is: Ctrl+ Shift+ F9

Refreshing Pivot Tables Without a Macro

There is an alternative that allows you to keep your Undo history. However, this alternative only refreshes your pivot table when the workbook is opened, not every time a change is made. Here is how you can use that option.

Starting from any cell in your pivot table:

Go to the Analyze tab in the ribbon.

Choose the Options button.

Go to the Data tab in the new window that opens.

Check the box that says, “Refresh data when opening the file.”

Again, just by way of comparison, if you use this option you retain Undo history, but it only refreshes the pivot table when the workbook is closed and reopened. If you use the macro option, you lose Undo history, but the pivot table automatically updates whenever any change is made in the workbook.

Variations for Refreshing Pivot Tables

The macro we looked at will not only refresh your pivot tables, but will also refresh any queries as well. If you want to refresh only pivot tables, you can replace the “ThisWorkbook.RefreshAll” command with this code instead:

Sub Refresh_All_Pivot_Table_Caches() 'Refresh all pivot caches in the workbook. 'Pivot tables are automatically refreshed when cache is refreshed. Dim pc As PivotCache 'Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub

Each pivot table is connected to an underlying pivot cache, which is connected to the source data. The code loops through all pivot caches in the workbook and refreshes each one. Pivot tables from the same source range can share pivot caches, so this method is faster than looping through all pivot tables.

Similarly, let’s say you only want to refresh one particular pivot table. In that case, you can swap out the “ThisWorkbook.RefreshAll” code with the code below.

Use the Deactivate Event Instead

Another option is to use the Worksheet_Deactivate event instead of Worksheet_Change. The Worksheet_Deactivate event will run every time the user leaves the sheet and selects a different sheet. This allows the user to make all the changes to the source data, then the pivot table will be automatically refreshed when they go to any other sheet, including the sheets that contain the pivot table.

Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAll End Sub

This code would still be placed in the sheet module that contains the source data. This is a good option if your pivot tables or data connections take a few seconds or longer to update, and you don’t want to wait every time a change is made to the source data.

The only time you might not want to use this is if your pivot table and source data are on the same sheet. That will usually be a rare case, and something I generally don’t recommend.

Thanks to the suggestion from Ted on this one.

Save Time & Embarrassment

I hope this article helps save you time and makes it easier for users of your files. It can also help prevent embarrassment when you forget to refresh pivot tables before sending out reports. Believe me, I’ve made this mistake more times than I’d like to admit… 🙂

How To Use The Excel Pivot Table Field List

Tips for working with the PivotTable Field List. Move it, change its layout, sort the fields, to save time and work efficiently. Also see: List all Pivot Fields with Details

Pivot Table Field List

When you create a pivot table, and select a cell in it, by default, a pivot table field list should appear, at the right of the Excel window. You can use the field list to select fields for the pivot table layout, and to move fields to a specific area in the layout.

To see the PivotTable Field List:

The PivotTable Field List pane should appear at the right of the Excel window, when a pivot cell is selected.

Change the Pivot Table Field List

To see the steps for adjusting the pivot table field list, please watch this short video tutorial. The written instructions are below the video. Download the sample file from this video, to follow along.

Change the Pivot Table Field List Layout

By default, the field list shows a list of the fields at the top, and the four pivot table areas in a square at the bottom. You can change that layout, by using a command on the field list.

To change the layout:

The default layout is at the top of the list — Fields Section and Areas Section Stacked

Select one of the other layouts, such as Fields Section and Areas Section Side-by-Side

Which layout to select:

The Fields Section and Areas Section Stacked layout is best if the pivot table has long field names, or a short list of fields

The Fields Section and Areas Section Side-by-Side layout is better when field names are short, or there is a long list of fields.

I don’t use the other layouts — Fields Section Only, Areas Section Only (2 by 2), Area Section Only (1 by 4)

Adjust the Field List Width

To adjust the width of the field list:

Point to the border between the field list and the worksheet

When the pointer changes to a two-headed arrow, drag left or right, to make the field list wider or narrower

Move the Field List

The field list can be locked in place at the right or left of the worksheet, or it can float over the worksheet.

To move the field list:

Point to the Title area of the field list

When the pointer changes to a four-headed arrow, drag the field list to a new position

To lock the floating field list into its previous position:

Point to the Title area of the field list

It will go to the side where it was last locked.

Sort the Field List

The fields in the field list can be sorted alphabetically, or in the same order that the fields are arranged in the source data.

To sort the fields in Excel 2013:

To sort the fields in Excel 2010:

Download Sample File

Download the sample file that was used in the Pivot Table Field List video. The zipped file is in xlsx format, and does not contain any macros.

More Pivot Table Resources

FAQs – Pivot Tables Pivot Table Introduction List all Pivot Fields with Details Remove Pivot Fields with Macros

Grouping Data

Summary Functions

Clear Old Items in Pivot Table

Bạn đang đọc nội dung bài viết How To Refresh Pivot Table In Excel (Manually + Auto trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!