4 tips for refreshing Excel PivotTable objects



Picture: Getty Pictures/iStockphoto

PivotTable objects are solely nearly as good as their underlying knowledge and that may change shortly. If the pivot tables are a part of a dashboard, they won’t mirror updates on the supply degree. Relying on the data your pivot tables convey, refreshing could be extraordinarily necessary, so on this article, I will share 4 ideas that ought to assist make the refresh course of simple and versatile.

I am utilizing Workplace 365’s Excel 2016 (desktop), however the following pointers apply to earlier variations. You may refresh single pivot tables within the browser, however the Analyze contextual tab is not accessible; nor are you able to set PivotTable choices. There is not any demonstration file; you will not want one.

SEE: Software program Utilization Coverage (Tech Professional Analysis)

The way to refresh

Earlier than we take a look at refreshing ideas, let’s shortly overview the right way to refresh pivot tables. To refresh solely the present pivot desk, right-click it and select Refresh from the ensuing submenu. Or, select Refresh from the Refresh dropdown within the Information group (on the contextual Analyze tab). You too can press Alt+F5.

There are two fast methods to refresh all pivot tables within the present workbook on the similar time. First, click on inside any pivot desk to entry the contextual Analyze tab. Then, select Refresh All from the Refresh choice within the Information group. Second, press Ctrl+Alt+F5.

1. Refresh when opening the workbook

Customers will not all the time bear in mind to refresh after altering the underlying knowledge. As well as, if the pivot tables are linked to an exterior supply, you may definitely need present values. One of the best ways to ensure that viewers see probably the most up-to-date info is to drive Excel to replace pivot tables while you open the file, as follows:

  1. Proper-click any pivot desk and select PivotTable Choices from the ensuing submenu.
  2. Within the ensuing dialog, click on the Information tab.
  3. Examine the Refresh knowledge when opening the file choice (Determine A).
  4. Click on OK and ensure the change.

Determine A


This feature forces Excel to replace the pivot desk when opening the file.

Checking this feature will replace the chosen pivot desk or all pivot tables with the identical knowledge supply. When you’ve got different pivot tables, be sure you set this feature for all which have a special knowledge supply.

2. Refresh at intervals

Relying on how vital knowledge is or if the pivot desk is a part of a dashboard, you may have to refresh usually, which is annoying. Excel can replace the pivot desk at intervals to make sure viewers get the present knowledge. You may have to avoid wasting the pivot desk to the information mannequin while you create it as follows:

  1. Click on wherever inside the information set.
  2. Click on the Insert tab, after which click on PivotTable within the Tables group.
  3. Within the ensuing dialog, test the Add this knowledge to the Information Mannequin choice (Determine B).
  4. Change another settings.
  5. Click on OK. (Including knowledge to the information mannequin can take a minute.)

Determine B


Add the brand new pivot desk to the information mannequin.

At this level, prepare the pivot desk as you usually would. Whenever you’re carried out, you’ll be able to set the interval as follows:

  1. Click on wherever contained in the pivot desk.
  2. Click on the contextual Analyze tab, after which select Connection Properties from the Change Information Supply dropdown (within the Information group).
  3. Within the ensuing dialog, test the Refresh each choice within the Refresh management part. To the proper, enter an interval in minutes (Determine C).
  4. Click on OK.

Determine C


Enter the interval in minutes.

This setting is especially useful for dashboards and exterior knowledge sources.

three. Protect cell formatting

It is irritating so as to add direct formatting to a completed pivot desk, solely to have it disappear while you refresh the pivot desk. Maybe you daring a number of headings or add a fill colour to spotlight a very necessary worth. Why spend your time if Excel’s going to take away it? Happily, you’ll be able to inform Excel to maintain its arms off your direct formatting as follows:

  1. Proper-click the pivot desk and select PivotTable Choices from the ensuing submenu.
  2. Click on the Format & Format tab.
  3. On the backside of the choices, test Protect cell formatting on replace (Determine D).
  4. Click on OK.

Determine D


Shield direct formatting.

It is a straightforward solution to save your self a variety of frustration.

four. Disable AutoFit

Pivot tables use an autofit column width by default. As you prepare your knowledge, you may enhance the width of columns, solely to have them snap again to autofit while you refresh the pivot desk. Much like preserving direct formatting, you’ll be able to disable the autofit default as follows:

  1. Proper-click wherever contained in the pivot desk.
  2. Click on the Format & Format tab.
  3. Uncheck the Autofit column widths on replace choice (Determine E).
  4. Click on OK.

Determine E


Disable the pivot desk’s autofit default.

After disabling this function, customized column widths will persist after refreshing.

Ship me your query about Workplace

I reply readers’ questions after I can, however there isn’t any assure. Do not ship information except requested; preliminary requests for assist that arrive with connected information will likely be deleted unread. You may ship screenshots of your knowledge to assist make clear your query. When contacting me, be as particular as potential. For instance, “Please troubleshoot my workbook and repair what’s fallacious” in all probability will not get a response, however “Are you able to inform me why this formulation is not returning the anticipated outcomes?” may. Please point out the app and model that you simply’re utilizing. I am not reimbursed by TechRepublic for my time or experience when serving to readers, nor do I ask for a price from readers I assist. You may contact me at [email protected]

See additionally


Source link