Microsoft Excel Tutorial: Master Pivot Tables with Get Pivot Data: Excel Tutorial.
Welcome back to the MrExcel netcast! In today's episode, we're diving into the world of pivot tables and exploring the often overlooked function, get pivot data. Now, I know what you're thinking - "Bill, why are we talking about this? It's such a headache!" And trust me, I used to feel the same way. But after discovering some useful tricks, I've come to appreciate the power of get pivot data.
Let's start by looking at a simple pivot table. We have countries listed on the side, with sales and profit data across the top. Initially, I built a formula to grab the sales data for Japan, using the cell reference B8. But as we all know, pivot tables are flexible and can change as new data is added. So, what happens when we add new rows or columns? Our formula will no longer be accurate. This is where get pivot data comes in handy.
In Excel 2003, you can use the shortcut CONTROL+L to set up a list. In Excel 2007, this is now CONTROL+T. This allows us to paste new data below our existing data, and the pivot table will automatically extend to include it. No need to revisit the pivot table wizard! Now, when we refresh the pivot table, our formula will still pull the correct data, even if the order of the countries has changed.
But what if you're using a VLOOKUP formula instead? While this can work, it's not the most efficient solution. The pivot table may grow or change, causing your VLOOKUP formula to break. Instead, use the get pivot data function. Simply type = and click on the cell containing the data you want to retrieve. This will create a formula that specifies the top left corner of the pivot table and allows you to specify pairs of arguments to retrieve the data you need. This way, even if the pivot table changes, your formula will still work.
One thing to note is that this functionality may be turned off in your Excel settings. If you're having trouble using the get pivot data function, check your toolbar customization to make sure it's turned on. And a special thank you to Dan from Philly for suggesting this topic and sharing some cool uses for get pivot data. I hope this has been helpful for you and I'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: mrexcel.com/like-mrexcel-on-youtube/
Dan from Philly checks in with some good uses for GetPivotData - the function that most people love to hate. If you've ever been annoyed when Excel starts using this function, take a step back and see the actual uses for the function. Episode 807 shows you how.
Table of Contents:
(00:00) Explanation of the get pivot data function
(00:25) Initial formula to grab data
(00:35) The problem with using VLOOKUP
(01:16) The pivot table refresh issue
(01:41) The problem with Japan moving
(02:12) The workaround using get pivot data function
(02:48) The static top left corner of the pivot table
(03:00) The flexibility of get pivot data function
(03:12) Demonstration of the function with new data
(03:35) The formula for get pivot data function
(03:54) Thanking Dan for the suggestion
(04:08) 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:
Autofiltering pivot tables in Excel
Benefits of using the get pivot data function in Excel
Customizing Excel toolbar to enable get pivot data functionality
Excel 2003 vs Excel 2007: get pivot data function differences
Exploring the features of the get pivot data function in Excel
How to extend a pivot table automatically using control+L
How to use the get pivot data function in Excel
Pivot table data manipulation with get pivot data function
Tips and tricks for working with pivot tables in Excel
Troubleshooting issues with VLOOKUP in pivot tables
Using get pivot data for dynamic data analysis in Excel
YouTube video tutorial on using the get pivot data function
Join the MrExcel Message Board discussion about this video at mrexcel.com/board/threads/1152078/
- Excel - Master Pivot Tables with Get Pivot Data: Excel Tutorial - Episode 807 ( Download)
- Excel - Master Pivot Tables and Unlock the Power of GETPIVOTDATA | Excel Tutorial - Episode 1128 ( Download)
- Understanding the GetPivotData Formula in Excel ( Download)
- Excel Formulas and Functions Tutorial - GETPIVOTDATA ( Download)
- Excel Video 310 GETPIVOTDATA Part 1 ( Download)
- Excel - Excel Pivot Tables: How to Turn off GETPIVOTDATA in Excel - Episode 377 ( Download)
- Excel - Mastering GETPIVOTDATA: Uncover Excel's Powerful Data Analysis Tool - Episode 1127 ( Download)
- Turn Off GetPivotData Formulas for Excel PivotTables ( Download)
- Excel - Mastering the GETPIVOTDATA Function: Solving Problems and Cleaning Data - Episode 870 ( Download)
- Excel - Pivot Table Data Crunching 14 - GetPivotData - Episode 1211.314 ( Download)
- Get Pivot Function Excel ( Download)
- Excel - Preventing GetPivotData from Appearing in your Formulas - Episode 1126 ( Download)
- How to Use the GETPIVOTDATA Function ( Download)
- Use the GetPivotData function in a Pivot Table to build a custom Revenue report ( Download)
- Excel Video 312 GETPIVOTDATA Part 3 ( Download)
