Top 100 Pivot Table Tips and Tricks You Need to Learn this Year

I’m sure you love to use pivot tables.

Right?

No doubt, a pivot table is the single most important tool which can help you to become an advanced Excel user.

You agree with me?

The real thing is...

...when it comes to data analysis, quick and, effective reporting or presenting summarized data nothing can beat a pivot table.

It is dynamic and flexible.

Even if you compare formulas and pivot tables, you will find that pivot tables are easy to use and manage.

…now, here’s the idea:

If you want to take your Excel skills to a whole next level you need to get better at pivot tables.

And, the best way is to have a list of tips and trick which you can learn.

So for this, I’ve listed some of the most important tips and trick which can help you to level up your game in pivot tables.

…let’s get started.

Top 100 Pivot Table Tips and Tricks to Get Better at Excel

For you, I have categorized these tips in a simple way so that you can read and learn them in the way you want.

...now let's learn each tip one by one.

Read This First

Things you should Consider while Preparing a Source Data for your Pivot Table

When you’re about to create a new pivot table it’s important to spare a few minutes to take a look the source data...

...and make sure it's fine...

...and ready to go ?

1. No Blank Column and Row in the Source Data

2. No Blank Cell in the Value Column

3. Data should be in Right Format

4. Use Table for Source Data

5. Remove Totals from the Data

Tips to help you while creating a Pivot Table

Once you are OK to with your source data then creating a pivot table is just a cup of cake.

But…

…here I have listed some of the important points which you can use while creating a pivot table.

1. Recommended Pivot Tables

2. Creating a Pivot Table from Quick Analysis

3. External Workbook as a Source for the Pivot Table

4. The Classic Pivot Table and Pivot Chart Wizard

5. Search for Fields

6. Change Pivot Table Field Window Style

7. Sort Order of your Field List

8. Open/Show Field List

9. Naming a Pivot Table

10. Create a Pivot Table in Excel Online Version

11.  VBA Code to Create Pivot Table in Excel

Formatting a Pivot Table like a PRO

We often use pivot tables as reports...

...and it’s must to know formatting options which can help you to give a makeover to your default pivot table.

1. Changing Pivot Table Style or Creating a New Style

2. Preserve Cell Formatting when you Update a Pivot Table

3. Disable Auto Width Update when you Update a Pivot         Table

4. Repeat Item Labels

5. Formatting Values

6. Change Font Style for Pivot Tables

7. Hide/Unhide Subtotals

8. Hide/Unhide Grand Total

9. Two Number format in a Pivot Table

10. Applying a Theme to Pivot Table

11. Changing the Layout of a Pivot Table

12. Banded Columns and Rows

Filter Data in a Pivot Table

The thing which makes the pivot table one of the most powerful data analysis tools is “Filters”.

The options which I have listed ahead you can make more out of a pivot table.

1. Turn Off/On Filters

2. Current Selection to the Filter

3. Hide Selection

4. Value and Label Filter

5. Use Label & Value Filter Together

6. Filter Top 10 Values

7. Filter Fields from the PivotTable Fields Window

8. Add a Slicer

9. Format a Slicer and Other Options

10. Single Slicer for all the Pivot Tables

11. Add a Timeline

12. Format a Timeline Filter and Other Options

13. Filter using Wildcards

14. Clear All Filters

Tips to Make Best out of Pivot Tables

Working with pivot table can be easier if you know the tips which I have mentioned ahead.

This tips will help you to save more than 2 hours every week.

1. Refresh a Pivot Table Manually

2. Refresh a Pivot Table while Opening a File

3. Refresh Data After a Specific Time Interval

4. Replace Error Values

5. Replace Blank Cells

6. Define a Number Format

7. Add a Blank Row after Each Item

8. Drag and Drop Items in a Pivot Table

9. Creating many Pivot Tables from One

10. Value Calculation Option

11. Running Total Column in a Pivot Table

12. Add Ranks in a Pivot Table

13. Create a Percentage Share

14. Move a Pivot table to a New Worksheet

15. Turn OFF GetPivotData

16. Group Dates in a Pivot Table

17. Group Numeric Data in a Pivot Table

18. Groups Columns

19. Ungroup Rows and Columns

20. Using Calculation in a Pivot Table

21. List of Formulas Used

22. Get a List of Unique Values

23. Show Items with No Data

24. The Difference from the Previous Value

25. Disable Show Details

26. Pivot Table to PowerPoint

27. Add Pivot Table to Word Document

28. Expand Or Collapse Field Headings

29. Hide-Unhide Expand Or Collapse Buttons

30. Only Count Numbers in Values

31. Sort Items According to a Corresponding Value

32. Custom Sort Order

33. Deferred Layout

34. Changing Field Name

35. Select the Entire Pivot Table

36. Convert to Values

37. Use Pivot Table in a Protected Worksheet

38. Double Click to Open Value Field Settings

Sharing Pivot Tables with Other

Pivot tables are one of the most effective and easiest ways to create reports.

And we need to share reports with others all the time.

Ahead I have shared some of the useful tips which can help you to share a pivot table easily.

1. Reduces the Size of a Pivot Table Report

2. Delete the Source Data and the Pivot Table still Works Fine

3. Save a Pivot Table as a Web Page [HTML]

4. Creating a Pivot Table Through a Workbook from a Web Address

Things you can do in a Pivot Table with CF

For me, conditional formatting is smart formatting. I’m sure you agree with on this. Well, when it comes to pivot table CF works like a charm.

1. Applying General CF Options

2. Highlight Top 10 Values

3. Remove CF from a Pivot Table

Using Pivot Charts with Pivot Tables to Visualize your Reports

I’m a big fan of pivot chart.

If you know how to use a pivot chart properly you can make best out of one of the best Excel tools.

Here are some of the tips which you can use to be pivot chart PRO in no time…

1. Inserting a Pivot Chart

2. Creating a Histogram using Pivot Chart and Pivot Table

3. Turn off the Buttons from a Pivot Chart

4. Add Pivot Chart to PowerPoint

Keyboard Shortcuts to Sky Rocket your Pivot Table Work

We all love keyboard shortcuts.

Right?

Here I’ve listed some of the common but useful keyboard shortcuts which you can use to speed up your pivot table work.

1. Create a Pivot Table

2. Group Selected Pivot Table Items

3. Ungroup Selected Pivot Table Items

4. Hide Selected Item or Field

5. Open Calculated Field Window

6. Open Old Pivot Table Wizard

7. Open Field List for the Active Cell

8. Insert a Pivot Chart from a Pivot Table

Conclusion

As I said pivot tables are one those tools which can help you get better in creating reports and analyzing data in no time.

And with these tips and trick, you can even save more time.

If you ask me, I want you to start using at least 10 tips first and then go for next 10 and so on.

But you need to tell me one thing now:

What’s your favorite pivot table tip?

Share words with me in the comment section. I'd love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.


Don’t Miss to Read these

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

19 thoughts

Leave a Comment

Your email address will not be published.

  1. Fantastic. Keep up the good work and let these handy tips and trickes coming! Greetz from the Netherlands.

  2. Great job! Can you possibly help me with the following problem …

    I’ve created pivot tables based on the PowerPivot data model. The following problem: The “Include new items in manual filter” option is greyed out as soon as I place a field in the filter area. (not in row area and not in column area)

    Have you ever had such a thing … is there another solution, except – put the field in rows or columns?

    Thank you in advance!!