Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Using GETPIVOTDATA in Excel

Using GETPIVOTDATA in Excel

This GETPIVOTDATA tutorial is suitable for users of Excel 2013/2016/2019 and Excel for Microsoft 365.

Objective 

Use the GETPIVOTDATA function in Excel to reference values in cells contained within a PivotTable.  

In this guide, you’ll learn:

  • What is GETPIVOTDATA?
  • Video Tutorial – GETPIVOTDATA Excel
  • How to Use GETPIVOTDATA in Excel?
    • How to Toggle GET PIVOT Off/On?
    • How to Use GET PIVOT DATA Inside a Formula?
  • The Advantages of Using GET PIVOT DATA

What is GETPIVOTDATA? 

GETPIVOTDATA is an Excel formula that belongs to the Lookup and Reference group of functions. It allows you to create a formula that references a cell inside an Excel PivotTable.  

It could come as quite a surprise if you were expecting cell references. For example, if you are constructing a simple SUM calculation that references a total in a PivotTable cell, you may expect to see a cell reference like A2. Instead, when you click on the cell, you get a very long GET PIVOT function added to the formula.  

Now, you can turn it off and use cell references. Still, it is worth noting that this setting is an application-level setting i.e., it will be turned off for you and any new workbooks you create, but it will not be turned off if you send the file to someone else unless they have also turned it off.  

So, if it is long and cumbersome, why use it? Well, it can be quite useful, particularly when creating flexible, customized reports. It uses criteria to ensure that the correct data is returned even if the PivotTable layout changed.  

To read more about the advantages of using GET PIVOT DATA, check out Brad Edgar’s post and video 3 Reasons Why You Need to Start Using Excel GET PIVOT DATA. 

Now, let us look at how you can turn it off and on and how you can use it effectively when retrieving data from a PivotTable.  

Video Tutorial – GETPIVOTDATA Excel

To see GETPIVOTDATA in action, please watch the following video tutorial. 

How to Use GETPIVOTDATA in Excel?

How to Toggle GET PIVOT Off/On?

By default, when you reference a PivotTable cell in a calculation, a GETPIVOTDATA formula will be created. If you would prefer to use cell references instead, you can turn off this default setting.  

  1. Click your mouse inside the PivotTable. 
  1. From the PivotTable Analyze tab, in the PivotTable group, click Options. 
  1. Tick or untick the Generate Get Pivot Data command depending on if you want to turn it off or on. 

How to Use GET PIVOT DATA Inside a Formula?

In this example, I have created a PivotTable that is showing the monthly sum of profit for seven products. I want to extract the totals for two of the products (Burlington and Vermont) from the PivotTable and show them in the table on the right.  

As I will be referencing PivotTable data, the GET PIVOT DATA function will be automatically added to the formula.  

  1. In the table on the right-hand side, click in the first cell for Burlington/January 
  1. Type = 
  1. In the PivotTable, select the cell that contains the sum of profit for Burlington in January 

The GET PIVOT DATA function is automatically invoked and added to the formula. 

The formula references the upper left-most cell in the PivotTable (A3), the ‘Product’ of ‘Burlington,’ and the ‘Month Name’ of ‘January’ to return the correct result.  

  1. Press the Enter key to accept the formula 

Now, even if the layout of the PivotTable is changed and the fields re-organized, the formula will still return the correct result.  

Copying the GET PIVOT DATA formula down 

So far, so good. We have the correct total, and now we want to copy the formula down as usual to complete the rest of the calculations. However, if you use the Auto-fill handle to copy down, this will be the result. 

The formula returns the result for Burlington/January in every single cell because it hard codes the criteria. If we look at the formula for July, it is the same as the formula for January. It does not change the month name to ‘July’ as the formula is copied down.  

This can be resolved by changing the hard-coded field references to cell references.  

Changing fields to cell references 

  1. Press F2 to edit the formula 
  1. Double-click “Burlington” 
  1. Replace it with the cell reference for Burlington in the table you are working in 
  1. Double-click “January” 
  1. Replace it with the cell reference for January in the table you are working in 
  1. Use the Auto-fill handle to copy down and across to complete the table 

The Advantage of using GET PIVOT DATA 

In this example, I have toggled off the GET PIVOT function and have attempted to complete the table using cell references. The results are correct.  

However, if I change the layout of the PivotTable, I lose the results for Vermont. The cell references are still correct for Burlington despite the change of layout, so I see the right results, but the figures for Vermont have changed location.  

Using GET PIVOT stops this from happening, ensuring you get the correct results even if the layout is changed.  

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

Other Excel classes you might like:

  • Pivot Table in Excel, How do You Create One?
  • Excel Crash Course – Learn Pivot Tables in 1 Hour
  • Designing Better Spreadsheets in Excel
  • Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.

 

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

More Tips with PowerPoint 2010 Shapes and Pictures

Using the Windows 8 Mobility Center

How to Use the LOOKUP Function with CHOOSE in Excel 2016

Using Project 2010 Work Breakdown Structure (WBS) Codes – Part 1

Linking Shapes and Diagrams in Visio 2010

Creating PDF and XPS documents in Excel 2010

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)