#Excel 2016 pivot chart tutorial update#
Uncheck the Autofit column widths on update option ( Figure E).įigure E Disable the pivot table’s autofit default.Īfter disabling this feature, custom column widths will persist after refreshing.Right-click anywhere inside the pivot table.Similar to preserving direct formatting, you can disable the autofit default as follows: As you arrange your data, you might increase the width of columns, only to have them snap back to autofit when you refresh the pivot table. Pivot tables use an autofit column width by default. It’s an easy way to save yourself a lot of frustration. At the bottom of the options, check Preserve cell formatting on update ( Figure D).Right-click the pivot table and choose PivotTable Options from the resulting submenu.Why spend your time if Excel’s going to remove it? Fortunately, you can tell Excel to keep its hands off your direct formatting as follows: Perhaps you bold a few headings or add a fill color to highlight a particularly important value. It’s irritating to add direct formatting to a finished pivot table, only to have it disappear when you refresh the pivot table. This setting is particularly helpful for dashboards and external data sources. To the right, enter an interval in minutes ( Figure C). In the resulting dialog, check the Refresh every option in the Refresh control section.Click the contextual Analyze tab, and then choose Connection Properties from the Change Data Source dropdown (in the Data group).When you’re done, you can set the interval as follows: (Adding data to the data model can take a minute.)įigure B Add the new pivot table to the data model.Īt this point, arrange the pivot table as you normally would. In the resulting dialog, check the Add this data to the Data Model option ( Figure B).Click the Insert tab, and then click PivotTable in the Tables group.You’ll have to save the pivot table to the data model when you create it as follows: Excel can update the pivot table at intervals to ensure viewers get the current data. Refresh at intervalsĭepending on how critical data is or if the pivot table is part of a dashboard, you might need to refresh often, which is annoying. If you have other pivot tables, be sure to set this option for all that have a different data source. Check the Refresh data when opening the file option ( Figure A).įigure A This option forces Excel to update the pivot table when opening the file.Ĭhecking this option will update the selected pivot table or all pivot tables with the same data source.In the resulting dialog, click the Data tab.Right-click any pivot table and choose PivotTable Options from the resulting submenu.The best way to guarantee that viewers see the most up-to-date information is to force Excel to update pivot tables when you open the file, as follows: In addition, if the pivot tables are linked to an external source, you’ll certainly want current values. Users won’t always remember to refresh after changing the underlying data. Then, choose Refresh All from the Refresh option in the Data group. First, click inside any pivot table to access the contextual Analyze tab. There are two quick ways to refresh all pivot tables in the current workbook at the same time. Or, choose Refresh from the Refresh dropdown in the Data group (on the contextual Analyze tab). To refresh only the current pivot table, right-click it and choose Refresh from the resulting submenu.
#Excel 2016 pivot chart tutorial how to#
SEE: Software Usage Policy (Tech Pro Research) How to refreshīefore we look at refreshing tips, let’s quickly review how to refresh pivot tables. There’s no demonstration file you won’t need one. You can refresh single pivot tables in the browser, but the Analyze contextual tab isn’t accessible nor can you set PivotTable options. I’m using Office 365’s Excel 2016 (desktop), but these tips apply to earlier versions. How to return first and last times from timestamps in Microsoft ExcelĬhecklist: Microsoft 365 app and services deployments on Macs Master Microsoft Office with this accredited training Depending on the information your pivot tables convey, refreshing might be extremely important, so in this article, I’ll share four tips that should help make the refresh process easy and flexible. If the pivot tables are part of a dashboard, they might not reflect updates at the source level. PivotTable objects are only as good as their underlying data and that can change quickly. For more info, visit our Terms of Use page. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. Up-to-date information can be critical these 4 tips will add flexibility and increase efficiency when refreshing pivot tables. 4 tips for refreshing Excel PivotTable objects