How To Get Rid Of Getpivotdata

GetPivotData Formula

If y'all're creating a formula in Excel, and you lot click on a pivot tabular array value, Excel might create a GetPivotData formula for you, automatically, instead of a normal cell reference.

For example, in the screen shot below, I wanted to create a link to cell B5

• Start, I typed an equal sign in cell A9.
• Next, I clicked on cell B5, in the pin table
• Excel automatically created this foreign-looking formula:
  • =GETPIVOTDATA("Total",$A$three,"Product","File Folders")

Why did Excel Create GetPivotData Formula?

Commonly, if you're edifice a formula, and you click on a cell, Excel creates a uncomplicated link to that prison cell, like this: =B5

Still, if you click on one of the summarized numbers in a pin table, Excel tries to help you, and it creates a GetPivotData formula.

Tips

ane) Turn it off: If you don't need that automated formula feature, the short video in the adjacent section shows how to turn it off. (This will touch ALL Excel workbooks on your reckoner)

2) Fix Information technology: When y'all elevate down a GetPivotData formula, information technology might show the aforementioned upshot in every row. Come across how to set the formula, then it works correctly.

iii) Benefits: There are advantages to using GetPivotData, and so check out the list of GetPivotData Pros and Cons, before you turn it off forever!

How do I plow off GetPivotData?

To run into the steps for turning off the GetPivotData in Excel , you can watch this curt video tutorial. At that place are written steps below the video, using two different methods to stop GetPivotData formulas.

Video Timeline

• 00:00 Automatic GetPivotData formula
• 00:24 Re-create downwards GetPivotData formula
• 00:38 Turn Off GetPivotData feature
• 01:01 Test Cell Link
• 01:18 Plough On GetPivotData feature

Steps to Turn Off GetPivotData

By default, when y'all outset use a newly installed Excel, the GetPivotData setting is turned on. If you prefer, you tin can plough that setting off, by using either the Excel Ribbon, or Excel Options.

• Note: This is an application-level setting in Excel, and affects ALL workbooks on your reckoner, not just the active workbook.

1) Use Ribbon Command

To turn off the Generate GetPivotData command, using the Excel Ribbon, follow these steps:

Note: This will affect ALL Excel workbooks, non just the agile workbook

1. Select any cell in a pivot table.
2. On the Ribbon, click the PivotTable Clarify tab
• Or, under PivotTable Tools, click the Options tab
3. At the left click the arrow on the PivotTable command
4. Next, click the drop down arrow for Options
5. Click the Generate GetPivotData command, to plough the feature off or on.

2) Change Excel Options

Another way to plough the Generate GetPivotData setting on or off is with the Excel Options.

Remember - This will affect ALL Excel workbooks, not just the active workbook

Follow these steps to change the setting:

• Select any cell on the worksheet - the active jail cell does NOT need to be in a pivot table
• At the top left of the Excel window, click the File tab
• In the listing at the left, click Options (or click More, then click Options)
• In the Excel Options window, at the left, click the Formulas category
• Whorl downwards to the Working with formulas section
• To plough off GetPivotData, remove the check marking for this option:
  • Utilize GetPivotData functions for PivotTable references
• Click OK, to close the Options window

Simple Link to Pivot Cell

If you're creating a formula in Excel, and you click on a pivot table value, Excel might create a GetPivotData formula for you, automatically, instead of a normal cell reference.

• Yous can turn off GetPivotData characteristic, as described in the department to a higher place
• OR, follow the steps below, to create a simple cell reference to a pivot table value

Get a Simple Cell Reference

To become a simple cell reference to a pivot table value cell:

• If Excel has already created a GetPivotData function in your formula, delete that part of your formula
• Then, instead of clicking on the pin table prison cell, type its cell address, eastward.g. : =B5

In this screen shot, I deleted the long formula in jail cell A9, and typed the prison cell reference for cell B5.

Create GetPivotData Formula

In the GetPivotData function syntax, in that location are the following arguments.

The first 2 arguments are required:

• data_field: Proper name of pivot table field in the Values expanse, that has the data y'all need. Enter a text cord, inside double quotation marks, or refer to prison cell that contains the pivot field proper name
• pivot_table: Reference to whatever cell in the pin tabular array that contains the data you need
• field1, item1: (optional) From 1 to 126 pairs of field names and pivot item names, to pull specific data from the information field.

GetPivotData Formula

In the screen shot below, the GETPIVOTDATA formula has the following values for its arguments:

=GETPIVOTDATA("Total",$A$three,"Product","File Folders")

• data_field: "Total" is the proper name of the pivottable field in the values area
• pivot_table: $A$iii refers to a cell in the pivot table
• field1, item1: (optional) Go data from "Product" field, and "File Folders" item in that field

Ex one) Cell References in GetPivotData

To make a GetPivotData formula more flexible, y'all tin can refer to worksheet cells, instead of typing particular or field names in the GetPivotData arguments.

This makes information technology easier to copy a GetPivotData formula down a column, or to meet dissimilar results, without changing the GetPivotData formula.

For example, blazon the following formula in cell E4, to get the full sales for the newspaper product:

• =GETPIVOTDATA("Total",$A$three,"Product","Newspaper")

Adjacent, follow these steps to brand the formula flexible:

• Select cell E4
• In the formula bar, select the particular name, including the quote marks: "Paper"
• Next, click on prison cell A4 on the worksheet, where the Newspaper production name appears
• The jail cell reference, A4, will automatically replace the selected text in the formula
  • =GETPIVOTDATA("Total",$A$iii,"Product", A4)
• Printing the Enter fundamental, to complete the formula change

Elevate GetPivotData Formula Down

Subsequently you change the pin detail argument to a cell reference, instead of difficult-coded text in the formula, it'south more flexible.

Instead of creating a separate formula for each production, you tin can drag downwards the formula in prison cell E4.

• The formula has a relative reference to cell A4, then the reference will automatically change in each row.
• In column E, in that location is a right total for each of the Product field items, based on the item names in column A.

Ex 2) Cell References For Value Field

Cell references work well for the pin fields and pin items, but can cause problems if you effort to refer to a information field.

In this example, prison cell E2 contains the word "Qty", and you lot'd like to refer to that cell, instead of having "Qty" in the GetPivotData formula.

However, if y'all change the kickoff argument, data_field, to a reference to cell E2, the consequence is a #REF! mistake

=GETPIVOTDATA(E2,$A$3,"Product","Paper")

Add an Empty String

To fix this problem, you can concatenate an empty cord ( "" ) at the get-go or stop of the jail cell reference:

=GETPIVOTDATA(E2&"",$A$iii,"Product","Paper")

With that simple alter to the formula, it returns the right event.

GetPivotData Examples - Using Dates

To prevent errors for dates, you tin use one of the following methods, and there are detailed examples beneath:

-- Match the pivot tabular array'southward date format

-- Utilise the DATEVALUE role

-- Utilise the Date role

-- Refer to a cell with a valid engagement

-- Use the TEXT function

Lucifer Date and Date Format

To go the correct results when typing a date in the GetPivotData formula, utilise the aforementioned appointment format that is shown in the pivot table.

In jail cell E4, the formula uses the date format that'due south in the pivot table -- dd/mmm/yy -- and the result is the correct quantity for that date:

=GETPIVOTDATA("Quantity",$B$3,"OrderDate","01/Jan/xiii")

Utilize DATEVALUE Office

Instead of just typing the date in the formula, add together the DATEVALUE function to the appointment.

In prison cell E4, the date is entered inside the DATEVALUE function -- and the result is the correct quantity for that date:

=GETPIVOTDATA("Quantity",$B$3,"OrderDate",DATEVALUE("i/ane/13"))

Use Engagement Role

Instead of only typing the date in the formula, employ the DATE role to create the engagement.

In cell E4, the engagement is created within the DATE part -- and the effect is the right quantity for that date:

=GETPIVOTDATA("Quantity",$B$3,"OrderDate",Engagement(2013,1,one))

Refer to Cell With a Engagement

Instead of typing the date in the formula, you lot can refer to a prison cell that contains a valid date, in any format recognized as a engagement past Excel.

In cell E4, the formula refers to the date in cell E2 -- and the event is the correct quantity for that date:

=GETPIVOTDATA("Quantity",$B$3,"OrderDate",E2)

Video: Select Specific Pivot Tabular array in GetPivotData

If y'all have multiple copies of a pivot table in a workbook, on different sheets, you can use GETPIVOTDATA to pull an amount from a specific pivot table.

Spotter this video to see the steps, and the written instructions are below the video.

Video Timeline

• 00:00 Worksheets and Pivot Tables
• 00:50 Dashboard Formula
• 02:07 Add INDIRECT Function
• 03:28 Test Dashboard Drop Downwardly

GetPivotData Example: Select Specific Pivot Table

If you have multiple copies of a pivot table in a workbook, on different sheets, you can use GETPIVOTDATA to pull an amount from a specific pivot table.

In this instance, there are 3 pivot tables:

• East
• Northward
• All

The pivot tables are prepare using consistent names and locations:

• Each sheet name begins with "PT_", followed by the region description.
• Each pin tabular array body range begins in prison cell B4

On another canvas in the workbook, a information validation drop down list is added in jail cell C6, showing all the regions, which are besides used in the sheet names.

Create the Formula

The GETPIVOTDATA formula will be entered in cell D6, then the first pace will be to create a simple formula in that location:

1. In jail cell C6, select East from the drop downwards list
2. Select cell D6, and type an equal sign
3. Click on the PT_East sheet
4. Click on the 1000 Total cell, and press the Enter central

At that place is a GETPIVOTDATA formula in the jail cell, and the cell displays the full sales for the E region.

The formula refers to the Total Price field, and to cell B4 on the PT_East sheet.

=GETPIVOTDATA("TotalPrice",PT_East!$B$4)

Generalize the Sail Reference

Instead of leaving the hard-coded reference to the PT_East sheet, you can use the INDIRECT role in the GetPivotData role to create a range reference based on the text in jail cell C6.

The INDIRECT function requires one argument, INDIRECT(ref_text) and returns the range specified by the reference text statement.

Each reference in this workbook will begin with "PT_", followed by the range description in cell C6, and catastrophe with "!$B$iv". So, in this case, the formula will be:

INDIRECT("PT_" & C6 & "!$B$4")

Replace the Sheet Reference

The last pace is to replace the current sheet reference in the GETPIVOTDATA formula, with the INDIRECT formula:

=GETPIVOTDATA("TotalPrice",PT_East!$B$4)

changes to:

=GETPIVOTDATA("TotalPrice",INDIRECT("PT_" & C6 & "!$B$iv"))

Now, when you change the region in cell C6, the full amount changes in cell D6. Information technology shows the total from the specified pivot table.

GetPivotData with Custom Subtotals

With a default subtotal, the GetPivotData function works well, and returns the correct result. In the screen shot below, an equal sign was typed in prison cell B1, and then the Bars subtotal amount was clicked.

A GetPivotData formula was automatically created, and information technology returns the quantity of Bars sold.

=GETPIVOTDATA("Quantity",$A$3,"Category","Confined")

However, if the subtotal is a custom office, instead of the default function, the GetPivotData formula might evidence an error.

In the screen shot beneath, the we right-clicked on the Confined Total label, and clicked Field Settings. And so, Custom was selected for Subtotals, and Sum and Average selected.

Now, if you type and equal sign and click on either of the Bars subtotal cells, the consequence is a #REF! error. The GetPivotData formula looks unlike too, with foursquare brackets in information technology.

=GETPIVOTDATA($A$3,"Category[Confined;Data,Sum]")

To fix the #REF! mistake, you tin remove the "Information," from the GetPivotData formula. In this example, the corrected formula is:

=GETPIVOTDATA($A$3,"Category[Bars;Sum]")

With that simple modify to the formula, the correct outcome is returned.

Pinnacle or Bottom Subtotals

The GetPivotData formulas take different requirements, depending on the location and type of the Subtotals.

There are two GetPivotData formula types:

• Normal -- =GETPIVOTDATA("Quantity",$A$three,"Category","Confined")
• [List] ----- =GETPIVOTDATA($A$3,"Category[Bars;Sum]")

This table summarizes where the formula types can be used, with subtotals shown at the top or bottom, and how many subtotals are immune in each location.

FAQ: GetPivotData Part

1) How do I turn off GetPivotData in Excel?

To turn off the automatic GetPivotData formulas, follow these steps:

1. Select whatsoever jail cell, in whatever pivot table
2. On the Excel Ribbon, click the PivotTable Analyze tab
3. Click the drib-down pointer on the PivotTable command
4. Next, click the drib-down arrow for Options
5. In the list of options, click the Generate GetPivotData control, to toggle that feature off or on

Note: This is an application-level setting, and volition affect ALL Excel files that you open up on your computer.

Download the Sample File

Download the zipped sample file for this tutorial. The file is in xlsx format, and does not contain macros

Source: https://www.contextures.com/xlpivot06.html

Share this post