Excel For Mac Getpivotdata

admin



In Excel 2008 for Mac: Pivot Tables for Data Analysis, Microsoft Most Valuable Professional Curt Frye helps dispel the common fear of the Pivot Table feature, demonstrating how to use this powerful tool to discover valuable business intelligence. Curt shows how to create Pivot Table reports from internal Excel data and outside data sources, use. Excel for Windows 2007, 2010, 2013, 2016, 2019, Office 365 and Excel for Mac 2011 and 2016. This course is for Excel Beginners, Intermediates or Advanced Users. No prior Microsoft Excel knowledge is required. GETPIVOTDATA Formula. DATA MANAGEMENT. NEW EXCEL 2013 PIVOT TABLE FEATURES. Welcome to the Excel group! This is the place to discuss best practices, news, and the latest trends and topics related to Excel.

This GETPIVOTDATA in Excel 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.

Getpivotdata

What is GETPIVOTDATA?

Getpivotdata excel 2016

GETPIVOTDATA is a function in Excel 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.

GETPIVOTDATA 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 GETPIVOTDATA function added to the formula.

Now, you can turn off GETPIVOTDATA 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 GETPIVOTDATA off.

So, if GETPIVOTDATA is long and cumbersome looking, 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 GETPIVOTDATA, check out Brad Edgar’s post and video 3 Reasons Why You Need to Start Using Excel GETPIVOTDATA.

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

Video Tutorial

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

Method

Toggle GETPIVOTDATA 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 GetPivotData command depending on if you want to turn it off or on.

Using GETPIVOTDATA in 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 GETPIVOTDATA 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 GETPIVOTDATA 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 GETPIVOTDATA 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 the GETPIVOTDATA function hard codes in 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 GETPIVOTDATA 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
For

Excel Help Getpivotdata

  1. Use the Auto-fill handle to copy down and across to complete the table

Excel Getpivotdata Ref Error

The Advantage of using GETPIVOTDATA

In this example, I have toggled off the GETPIVOTDATA 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 GETPIVOTDATA 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:

Excel Getpivotdata Filter Items

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

Excel 2010 Getpivotdata

Similar Posts