Excel - Create Dynamic Pivot Tables in Excel | Time-Saving Tip for Refreshing Data - Episode 825
Microsoft Excel Tutorial: Create Dynamic Pivot Tables in Excel | Time-Saving Tip for Refreshing Data.
Welcome back to the MrExcel netcast! In today's tip, we're going to talk about creating dynamic pivot tables. This tip was sent in by Joe Martin, a frequent contributor to the podcast. Joe noticed that when creating pivot tables on a static range, it can become a hassle to update the range if more data is added later on. Well, thanks to a great new feature in Excel 2003 and newer, this problem can easily be solved.
In Excel 2003, all you have to do is select one cell in your data and press CONTROL+L to define that range as a list. In Excel 2007, this was replaced with CONTROL+T, but the concept remains the same. Once you have your dynamic range set, you can create your pivot table as usual. And the best part? If you add more data to your original range, all you have to do is refresh the pivot table and it will automatically include the new data.
No more having to go back to the wizard and re-specify the range every time you add more data. This simple trick will save you time and hassle, making your pivot table experience much smoother. Before this feature was introduced, we would have had to define a name with the offset function, which was much more complicated. So, thank you Joe for sharing this great tip with us and making our lives easier.
So, next time you're creating a pivot table, remember to use this dynamic range feature and save yourself some time and effort. And don't forget to hit that like button and subscribe to our channel for more helpful Excel tips and tricks. Thanks for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Joe sends in a tip about converting your pivot table source data to a dynamic range. While this used to mean using the OFFSET function, now it simply means using Ctrl L (in Excel 2003) or Ctrl T (in Excel 2007). Episode 825 shows you how.
Table of Contents:
(00:00) Introduction by Bill Jelen
(00:20) Creating a pivot table on a static range
(00:30) The problem with using a static range
(00:40) Solution: using a dynamic range
(00:50) The new way to specify a dynamic range in Excel 2003 and newer
(01:04) Defining a range as a list using CONTROL+L or CONTROL+T
(01:21) Creating a pivot table with a dynamic range
(01:31) Adding new records to the dynamic range
(02:02) Refreshing the data in the pivot table
(02:12) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Control+L
Control+T
Data range
Dynamic range
Excel 2003
Offset function
Pivot table
Refresh data
Static range
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...