exceltoxl.com

Hi there,

This complete week, we will learn about few inner techniques of Pivot Table. These are few of my learnings in near days so thought of sharing to power your skills on Pivot. In my previous post, I spoke about How Human Parts Resembles Excel Features where Pivot is compared with Kidney. So, we will do some cleansing to our Kidney. :)

In this week we will learn about:

1. Adding few points to a Simple Pivot Table

2. Grouping data from months and years

3. Grouping data for words

4. About GETPIVOTDATA and its Advantages

5. How to avoid displaying the removed items in the pivot table

...

INTRODUCTION TO GETPIVOTDATA:

GETPIVOTDATA: This function is designed in such a way that the data can be pulled out especially from PIVOT TABLE. When we use this function, it displays as below:

=GETPIVOTDATA("Sum of Revenue",$A$3,"OrderDate",DATE(2014,7,2))

The above function works well even if the pivot table feature is dismissed and the values are acurate even after the feature is disabled.

HOW TO DISABLE GETPIVOTTABLE REFERENCE?

If we feel this function is little complicated or not required then we can disable this option.

To disable this option:

• GoTo ANALYZE TAB in PIVOTTABLE TOOLS RIBBON (this ribbon will popup only when we select the cell in the pivot table).
   
• GoTo Options in PivotTable Group and unselect "Generate GetPivotData"

That's it! GETPIVOTDATA function will be disabled and from now only cell references will display when we try to extract the data.

Note: This effects only for newly generated formulas or cell references.

DIFFERENCE BETWEEN:

The difference between the normal range and the pivot table in respect to GETPIVOTDATA is, pivot table will work accurately even if the range is converted to normal range but vice-versa is not accurate.

GETPIVOTDATA WORKS WELL WITH DATES:

This function will also works well with cell references like

Actual formula:

=GETPIVOTDATA("Sum of Revenue",$A$3,"OrderDate",DATE(2014,7,2))

Modified to cell reference for "OrderDate":

=GETPIVOTDATA("Sum of Revenue",$A$3,A4)

Similarly, the below one will also works well.

=GETPIVOTDATA("Sum of Revenue",$A$3,"OrderDate",DATEVALUE("7/2/2014")

which means the dates in GETPIVOTDATA works well with cell references or using DATEVALUE function or by using DATE function.

Here, makesure when the DATEVALUE function is used, the source date should exactly match with the target date. I mean the date in pivot table and the date in the GETPIVOTDATA function.

That's it for now. See you tomorrow in the next episode.

DO YOU USE PIVOT?

Have you learnt something out of these episodes? Want to share anything with us. Use this space and post your valuable ideas or suggestions using the below comments section.

If you like this post, please do share it on your social networking sites just by one click on the icons placed around the blog.

If you want to support me for maintaining this blog, please do REGISTER here for free. 

Thanks,