Discover Excel's revolutionary PivotTable Auto Refresh feature in this 13-minute tutorial that demonstrates how PivotTables can now instantly update when source data changes, making them behave just like formulas. Explore the long-awaited functionality that eliminates the need for manual refreshing after 31 years of waiting, and see practical examples of automatic updates when changing numbers in source data tables, modifying record attributes like product names, and adding new records to Excel Tables. Learn to group dates for monthly reports and numbers for frequency distributions while maintaining instant updates, and configure Excel's universal PivotTable settings to enable automatic refreshing for all PivotTables on your computer. Compare PivotTable limitations with formula capabilities through examples including median reports using GROUPBY functions, text join reports, direct formula inputs, XLOOKUP formula integration, and helper columns for getting formula inputs into PivotTable cache. Understand when formulas outperform PivotTables in scenarios like regression X-Y scatter chart data preparation, geometric mean summary reports, and financial calculations involving Net Present Value and Internal Rate of Return using NPV, IRR, XNPV, and XIRR functions.

Syllabus

1. 00:00 Introduction.
2. 00:07 We have waited for 31 years for the PivotTable to automatically refresh when source data changes
3. 00:35 PivotTables Can Instantly Update Notes
4. 01:08 Example of a PivotTable that automatically updates when you change the numbers in the source data table.
5. 03:08 Example of changing record attributes product name and the PivotTable updates without a manual refresh.
6. 03:41 Example of adding new records to an Excel Table, and the PivotTable updates instantly, without a manual refresh!
7. 05:59 Group dates to create a month report, and see that the PivotTable still instantly updates
8. 06:55 Group numbers to create a frequency distribution, and when the grouping categories changes, the PivotTable still updates instantly without a manual refresh.
9. 08:05 Set the Excel Options Universal PivotTable Settings so that all PivotTables on your computer will refresh automatically.
10. 09:58 Examples of Many formulas reports that the PivotTable cannot do.
11. 10:13 Median Report GROUPBY Function
12. 10:22 Text Join Report GROUPBY Function
13. 10:33 Directly formula Inputs
14. 10:55 XLOOKUP formula input
15. 11:10 Helper Column to get formula input into the PivotTable Cache, so you can use the Auto Refresh PivotTable option
16. 11:32 How Grouping is limited in a PivotTable, but accommodating with formulas formulas win.
17. 12:10 Data for a Regression X-Y Scatter Chart can NOT come from a PivotTable. But formulas can do it. GROUPBY Function
18. 12:30 Geometric Mean Summary Report
19. 12:48 Net Present Value and Internal Rate of Return NPV, IRR and the XNPV and XIRR functions
20. 13:08 Summary
21. 13:15 Closing