How to Refresh an Excel PivotTable Object in 4 Easy Steps

Most of us are familiar with the power of Excel’s PivotTable function, which makes it easier to manipulate large data sets. But what happens when your data changes? If you haven’t refreshed your PivotTable object in awhile, you might be in for some unwelcome surprises when you reopen your workbook. Fear not! With these 4 easy steps, you can refresh your Excel PivotTable objects in no time.

#1 – Navigate to the Data Source

The first step is to navigate to the data source. If you’re using a local file, this will be easy. Just open the file and head to the worksheet that contains your data. If you’re using a remote data source, such as a database, you’ll need to connect to it first. You can do this by opening the Data tab in Excel and selecting Connections. From there, choose your data source and click Open. Once you’ve connected to your data source, it’s time to explore the data more closely.

#2 – Select Columns: Next, select which columns of information are displayed on each of the PivotTables. To select all columns for all tables at once, use CTRL+A or CMD+A on Macs. Alternatively, if you want just one table refreshed with only certain columns selected from one row of information (e.g., column C), then highlight just those cells before continuing with #3 below.

#3 – Convert Selected Columns into Values: Once columns have been selected, convert them into values by right-clicking them or pressing CTRL+1 on Windows PCs or COMMAND+1 on Macs. A new dialogue box should appear, prompting you to rename the pivot table. In the dialogue box, find the option for Values under Data Type and check it off. You should now see a new pivot table with only your selected columns converted into numbers.

#4 – Choose a New Location: Finally, save this updated version of your pivot table so that it doesn’t overwrite the original version when you refresh again later. Simply click File > Save As > Pivot Table in Excel > Browse… > Navigate to folder where current file is located > Press Save button > Provide filename and press OK. Now you know how to update old versions of pivot tables!

 #2 – Select Only One Row or Column

One of the benefits of a pivot table is that you can quickly and easily change the view by selecting different options from the Rows and Columns drop-down menus. However, if you accidentally select more than one row or column, your pivot table will become cluttered and difficult to read. To fix this, simply click on any cell in the pivot table, then click the Undo button on the toolbar. Alternatively, you can right-click on any cell and select Clear Filters from the menu.

If you have already selected rows or columns and don’t want to undo them individually, just press Ctrl+A on your keyboard to select all cells in the pivot table. Next, right-click anywhere within the selection box and choose Clear Filters from the pop-up menu. Finally, use the arrow keys to navigate through your selections until you reach the desired row or column.

For example, suppose you want to see which products sold over $1000 last year. Simply highlight both Product and Total on the left side of the screen (with CTRL+A), then use arrow keys to navigate down until Product says Product Total. Then hit ENTER twice so that only product names are displayed in the pivot table instead of values like $1001. 01. You should now be able to identify which products sold over $1000 last year!

#3 – Switching Between Summarized Values: Another feature of a pivot table is the ability to switch between summarized values such as average and sum. The easiest way to do this is by using your keyboard shortcut – for instance, pressing S for Sum. Just remember that the summarized value doesn’t affect the other summary totals such as count and average; it only changes how numbers are represented in the current field’s summary calculation.

#3 – Update the Data Range

To update the data range of your pivot table, first select any cell within your pivot table. Then, on the Ribbon, go to the Analyze tab (in the PivotTable Tools group) and click Change Data Source. In the Change Data Source dialog box, enter a new data range for your pivot table in the Table/Range box. Click OK to close the dialog box and update your pivot table with the new data. You can also update the data source by right-clicking on any cell within your pivot table and selecting Change Data Source from the context menu. To refresh all pivot tables, use one of these methods:

#1 – Re-create the Pivot Tables Using New Data: If you have refreshed or changed your original data sources since you created your pivot tables, it is necessary to re-create them using the updated data. To do this, go back to Step 1 and create each pivot table as described above.

#2 – Repaint All Reports With Updated Data: If you have refreshed or changed only some of your original data sources since you created your pivot tables, repaint those reports that have not been updated by going back to Step

1. Once done, repaint all reports with updated data by going back to Step
2. #4 – Force Query: When you need to quickly refresh your pivot table before printing or exporting it, just force query by pressing CTRL+Q while the cursor is inside the pivot table. A warning will appear saying Do you want to recalculate the entire pivot table? It may take a moment. Select Yes to proceed. The pivot table will then be recalculated and updated to reflect the most recent data. Note that when refreshing individual pivot tables, any sorting or filtering applied to the report will remain unchanged. But if you refresh all pivot tables in the workbook, sorting and filtering will also be affected.

#4 – Refresh All

If you have multiple PivotTables on a worksheet, or even multiple worksheets with PivotTables, and you need to refresh them all at once, the Refresh All command is your best friend. Here’s how to use it:

1. On the Data tab, in the Connections group, click the arrow next to the Refresh All button.
2. Click Refresh All.
3. In the Confirm Data Source References dialog box, click OK.
4. The PivotTable is refreshed! That’s it! As always, if you find this post helpful, please share using one of the buttons below 🙂 And don’t forget to sign up for our newsletter so we can keep you updated on the latest insights and tips for Excel – it only takes about 60 seconds to sign up!

Psst…do you have a project that requires more data handling skills? If so, check out our downloadable PDF with 14 real-world formulas for tasks like auto-filling data into blank cells based on text strings or pulling results from multiple workbooks (even workbooks saved on your hard drive or network server).

It even includes a bonus section showing how to avoid common data handling pitfalls ! You can download a sample chapter here: Data Handling Sample Chapter . Don’t hesitate to contact us if you’re interested in learning more about the ESSENTIAL EXCEL DATA HANDLING FORMULAS book or any other upcoming resources by our team. We hope to hear from you soon!