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

I’m sure you love to use pivot tables.


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. 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.


…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