Look Who it Was Random video

Excel - Mastering the GETPIVOTDATA Function: Solving Problems and Cleaning Data - Episode 870

3,675 views · Published 6 January 2009 · 3:16 · Indexed 4 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Mastering the GETPIVOTDATA Function: Solving Problems and Cleaning Data.

Welcome back to the MrExcel netcast! In this video, we will be discussing the often misunderstood function, GETPIVOTDATA. Many of us have a love-hate relationship with this function, as it was forced upon us by Microsoft and can be quite confusing to use. However, it does have its benefits, and we will be exploring one of them today.

In our previous video, we looked at Richard's Pivot Table and how to solve a specific problem using a formula. However, there is another solution to this problem, and that is where GETPIVOTDATA comes in. By using this function, we can easily retrieve data from a Pivot Table without having to manually reference cells.

To use GETPIVOTDATA, we simply enter the formula and specify the criteria we want to retrieve data for. For example, in our video, we use the formula =GETPIVOTDATA("Reason",$A$3) to retrieve the total number of reasons from the Pivot Table in cell A3. This function is very flexible and can handle multiple criteria, making it a powerful tool for data analysis.

But what about those pesky extra zeros at the end of our data? Well, we can easily get rid of them by using the IF and ISBLANK functions. By adding =IF(ISBLANK(G5),"", our formula will only display data if there is a corresponding value in column G. This way, our data will appear clean and organized, without any unnecessary zeros.

So there you have it, a better way to solve yesterday's problem and a useful modification to make our data more presentable. Thank you for tuning in to this netcast from MrExcel. Don't forget to subscribe to our channel for more helpful tips and tricks. See you next time!

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-on-youtube/


You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.

Table of Contents:
(00:00) Introduction to GETPIVOTDATA
(00:12) Another solution to Richard's Pivot Table
(00:22) Explanation of GETPIVOTDATA
(00:32) Example of using GETPIVOTDATA formula
(00:46) Understanding the parameters in GETPIVOTDATA
(01:24) Using GETPIVOTDATA for Grand Total
(01:44) Flexibility of GETPIVOTDATA
(01:58) Removing extra zeros with IF(ISBLANK)
(02:29) Formula still present but only shows data
(02:41) 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:
Branch 31
Criteria in Pivot Table
Excel formula
Flexibility of GETPIVOTDATA function
GETPIVOTDATA
Grand Total
IF(ISBLANK) function in Excel
Pivot Table
Reason C
Reason counts
Removing extra zeros in formula


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1151994/

More from this channel