PivotTables are incredibly helpful to see trends or make comparisons in large data sets, but sometimes you’ll find that the data in your PivotTable doesn’t match up with your source data. In order to avoid this, it’s important to keep your PivotTables current and to refresh them on a regular basis, especially if you have multiple users accessing the same file. Here are four tips to keep your Excel PivotTables fresh and accurate and ensure that you’re able to view reliable information anytime you need it.
Basic Maintenance
One of the most important things you can do to keep your data clean is to refresh your pivot tables regularly. This will ensure that any new data that has been added to your source data is reflected in your pivot table. Here are four tips to help you keep your pivot tables up-to-date
1) Select the cells that contain your pivot table and then click Data from the top menu bar.
2) Click Refresh at the bottom of the resulting window
3) Type a name for your refresh action, such as Daily Update, into the Action field
4) Type a comment, such as This update refreshes my PivotTable with current data, into the Comment field 5) Choose whether or not you want to include subtotals by checking or unchecking the Include Subtotals box.
6) Press OK to execute your action
Refreshing Is Relative
Depending on the size and complexity of your data set, the process of refreshing a Pivot Table can take anywhere from a few seconds to a few minutes. And while it’s true that you can’t always predict when your data will change, there are a few things you can do to help keep your PivotTables up-to-date. For example, if you’re building a report based on current data but know that it might need to be refreshed with new information periodically, make sure the refresh button is in an easy-to-find location and include instructions for how often it should be used. You may also want to use conditional formatting or slicers so users know what data has been updated recently.
Use Absolute, Not Relative, Refreshing
When you set up a pivot table, you have the option to refresh the data automatically or manually. If you choose to refresh the data automatically, be sure to use absolute, not relative, references. That way, if new data is added to the source range, it will be included in the pivot table without having to manually refresh it. In order to refresh your pivot tables with absolute referencing, right-click on any of the column headers in your pivot table and select Refresh Fields from the menu that appears.
You can also type =Refresh() into an empty cell within the pivot table and press Enter. Note: Be careful when using this method because if there are multiple tabs at the bottom of your spreadsheet, you might end up refreshing all fields in all tabs rather than just one tab’s fields!
Use Time Intelligence Functions
If you manage a lot of data in Excel, then you know how important it is to keep your pivot tables up-to-date. The last thing you want is stale data muddying up your analysis. Luckily, there are a few quick and easy ways to refresh your pivot tables and keep your data clean. First, you can use the Time Intelligence functions found on the Analyze tab under the Add-Ins menu.
You can quickly get these formulas by typing =TIMEVALUE( ) into an empty cell next to the column or row headers for your time series. Second, you can use the Refresh All button found on the Data tab when selecting a PivotTable object.
Third, if you only need to update specific values within your pivot table, then clicking any cell within that value will update all related cells. Lastly, right-clicking any cell with a date/time stamp and selecting Refresh Field will also work.
