I’m super excited today to announce the first guest blogger post on bradedgar.com. The best part about this is that this guest blogger is a veteran in Excel especially when it comes to Excel Pivot Tables. The person I am talking about is John Michaloudis who is the Chief Inspirational Officer from My Excel Online. John is the author/teacher of an amazing Excel pivot table course called The Xtreme Pivot Table Course (disclaimer: affiliate link) which currently has thousands of students and includes literally everything you need to know about pivot tables. In today’s post I’ve asked John to talk about why our fellow Excellers should really start using Excel GETPIVOTDATA. The majority of people that I talk to seem to always be reluctant or scared to use GETPIVOTDATA so I thought I’d have him share some of the super useful things that can be done using this function.
John will review with you and answer the question, “What is Excel GETPIVOTDATA?” and then he’ll review the 3 reasons why you need to start using Excel GETPIVOTDATA. Without further ado, here’s John.
What is Excel GETPIVOTDATA?
The Excel GETPIVOTDATA is formula that returns data stored in a Pivot Table.
So essentially it extracts the Pivot Table data to enable a user to create customized reports.
Think of the Pivot Table like your data source, so anything you see in the Pivot Table report can be extracted with the GETPIVOTDATA formula and put into a cell within your worksheet.
The GETPIVOTDATA formula becomes powerful when you reference cells to create shell reports, which you can see from the videos below.
Download All of the Workbooks for Free Before we Begin
The Excel files are indeed free. When you click the ‘GET THEM FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Excel GETPIVOTDATA Formula breakdown?
GETPIVOTDATA(data_field,pivot_table,[field1,item1,[field2,item2],…)
| data_field | The Field in the Values area of the Pivot Table eg Sum/Count/Average of SALES | ||||||||||
| pivot_table | The pivot table you are selecting i.e. Can choose anywhere in the Pivot Table but we usually select the cell in the top left hand corner | ||||||||||
| field1 | Field name from your Pivot Table | ||||||||||
| item1 | Item from within your Field i.e. This can be referenced to a cell outside the Pivot Table | ||||||||||
An Introduction to Excel GETPIVOTDATA
With this video, you can download the Excel files above and refer to workbook – 11.1_Intro to GETPIVOTDATA.
3 Reasons Why You Need to Start Using Excel GETPIVOTDATA Now
REASON # 1: You Can Use Excel GETPIVOTDATA to Customize Your Worksheet
You can customize your worksheet to your liking so you are not limited to the Pivot Table formats and layouts. A Pivot Table has several styles but most people do not like these, so by extracting the Pivot Table values, you can customize your layout, adding your favorite colors, borders, fonts, as well as inserting comments into a cell.
REASON # 2: You Can Use Excel GETPIVOTDATA to Add Extra Business Metrics Like Budget & Forecast Values
You can add any other data to your custom report that you have made with your GETPIVOTDATA formula. So if your Pivot Table does not include any budget or forecast figures, don’t worry! You can add these amounts to your custom report and then create your variance reports. See how by watching the gif below and downloading the sample workbook.
REASON # 3: You Can Use Excel GETPIVOTDATA to Perform Live Forecasting
You can use the GETPIVOTDATA formula to create a shell report that grabs your Actual and Budget/Plan numbers, put them in a table and (based on the current month we are in) show Actuals for the current month to date and Plan numbers for the remaining months, thus creating a Live Forecast report.
See how this is done by watching the video below.
Summary – Brad:
This post was an awesome roundup of a several different skills and reasons why you need to start using Excel GETPIVOTDATA in your day to day dealings in Excel. John has outlined and showed us some pretty cool techniques that you can start applying immediately in your work.
I want to give a shout out to John for taking time out of his busy schedule to build this blog post because I know he’s a crazy busy guy. If you have any questions about anything, be sure to leave your comments below and I’m sure John would be more than happy to answer them when he has a chance.
For More on How to Become an Excel Pivot Table Guru
Be sure to checkout John’s world class Xtreme Pivot Table Course if you’re looking to further expand your Excel Pivot Table skills. His lessons are easy to follow and will allow you to learn everything you need to know at your own pace.
[et_bloom_inline optin_id=optin_1]
Hi Brad. Thanks for sharing John’s post about GetPivotData. There are times when the GetPivotData function is very useful. Even if we don’t use it, it’s important to know how it works when reading or auditing Excel files.
Cheers,
Kevin Lehrbass
http://www.myspreadsheetlab.com/blog/
No problem at all Kevin. Yeah it seems that most people who use pivot tables are still slightly scared of using get pivot data but John does a great job at showing that there isn’t anything to be scared of.
Thanks for checking it out!
Brad