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

...

How to avoid displaying the removed items in the pivot table?

To avoid displaying the removed items we have an option in the pivot table. Before that let me explain in detail about what we are speaking here.

As we have seen, there are two teams which are

1. Old Sales Team and

2. New Sales Team

For example: The new sales team is performing better than old sales team. So, the management decided to wave-off the old sales team. After wave-off, those information is not required in pivot to compare so decided to remove the complete data for old sales team. Then what we will do - Going to the dump and removing the rows related those representatives. Right?

Now, when we refresh the data the old items should not reflect. Yes, the old items will remove from the pivot table. But, when we click on dropdown we can still see the "Old Sales Team".

HOW TO AVOID DISPLAYING DELETED ITEMS:

To avoid this displaying from the dropdown's:

• GoTo ANALYZE in the PIVOTTABLE TOOLS Ribbon and click on Options
   
• PivotTable Options window will appear.
   
• GoTo Data and select "None" in "Number of items to retain per field"
   
• That's it. Click on Ok and refresh the pivot to see the changes.

That's it! Magic Works!!!

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,