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.

NOTE: Only the Fields and Items that are included in the Pivot Table report (Row/Column Labels and Values area) can be used to extract their values.

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!

GET THEM FREE!.

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.

Open up workbook 11.2 Create a custom report with GETPIVOTDATA to follow along with the video. Download the workbooks by clicking the GET THEM FREE button above.

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.

Open up workbook Business Metrics to follow along with the video. Download the workbooks by clicking the GET THEM FREE button above.

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.

Open up workbook 11.7 Live Forecasting With GETPIVOTDATA to follow along with the video. Download the workbooks by clicking the GET THEM FREE button above.

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]