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

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

No Blank Column and Row in the Source Data

No Blank Cell in the Value Column

Data should be in Right Format

🔥 | Use Table for Source Data

Remove Totals from the Data

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

Recommended Pivot Tables

Creating a Pivot Table from Quick Analysis

🔥 | External Workbook as a Source for the Pivot Table

The Classic Pivot Table and Pivot Chart Wizard

Search for Fields

Change Pivot Table Field Window Style

Sort Order of your Field List

Open/Show Field List

Naming a Pivot Table

Create a Pivot Table in Excel Online Version

🔥 | VBA Code to Create Pivot Table in Excel

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

Changing Pivot Table Style or Creating a New Style

🔥 | Preserve Cell Formatting when you Update a Pivot Table

🔥 | Disable Auto Width Update when you Update a Pivot Table

🔥 | Repeat Item Labels

Formatting Values

Change Font Style for Pivot Tables

Hide/Unhide Subtotals

Hide/Unhide Grand Total

Two Number format in a Pivot Table

Applying a Theme to Pivot Table

Changing the Layout of a Pivot Table

Banded Columns and Rows

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

Turn Off/On Filters

Current Selection to the Filter

Hide Selection

Value and Label Filter

Use Label & Value Filter Together

Filter Top 10 Values

Filter Fields from the PivotTable Fields Window

Add a Slicer

Format a Slicer and Other Options

Single Slicer for all the Pivot Tables

Add a Timeline

Format a Timeline Filter and Other Options

Filter using Wildcards

Clear All Filters

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

Refresh a Pivot Table Manually

Refresh a Pivot Table while Opening a File

Refresh Data After a Specific Time Interval

Replace Error Values

Replace Blank Cells

Define a Number Format

Add a Blank Row after Each Item

Drag and Drop Items in a Pivot Table

Creating many Pivot Tables from One

Value Calculation Option

Running Total Column in a Pivot Table

Add Ranks in a Pivot Table

Create a Percentage Share

Move a Pivot table to a New Worksheet

Turn OFF GetPivotData

Group Dates in a Pivot Table

Group Numeric Data in a Pivot Table

Groups Columns

Ungroup Rows and Columns

Using Calculation in a Pivot Table

List of Formulas Used

Get a List of Unique Values

Show Items with No Data

The Difference from the Previous Value

Disable Show Details

Pivot Table to PowerPoint

Add Pivot Table to Word Document

Expand Or Collapse Field Headings

Hide-Unhide Expand Or Collapse Buttons

Only Count Numbers in Values

Sort Items According to a Corresponding Value

Custom Sort Order

Deferred Layout

Changing Field Name

Select the Entire Pivot Table

Convert to Values

Use Pivot Table in a Protected Worksheet

Double Click to Open Value Field Settings

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

Reduces the Size of a Pivot Table Report

Delete the Source Data and the Pivot Table still Works Fine

Save a Pivot Table as a Web Page [HTML]

🔥 | Creating a Pivot Table Through a Workbook from a Web Address

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

Applying General CF Options

Highlight Top 10 Values

Remove CF from a Pivot Table

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

Inserting a Pivot Chart

Creating a Histogram using Pivot Chart and Pivot Table

Turn off the Buttons from a Pivot Chart

Add Pivot Chart to PowerPoint

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

Create a Pivot Table

Group Selected Pivot Table Items

Ungroup Selected Pivot Table Items

Hide Selected Item or Field

Open Calculated Field Window

Open Old Pivot Table Wizard

Open Field List for the Active Cell

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

Content Protection by DMCA.com
2018-11-16T06:13:17+00:00

9 Comments

  1. Jim 28 Nov, 18 at 2:44 pm - Reply

    Great sharing!

  2. Ron 18 Nov, 18 at 5:58 pm - Reply

    Good collection of tips.

    Please be more careful to not recycle web addresses. You had an earlier article:
    “Top 31 Pivot Table Tips & Tricks for Advanced Excel Users ” that used the same web page address… oops.

    Although it is a “top # …” article, it covered different topics. It would be nice if you could restore the other tip too ..

  3. Marc Blanchard 16 Nov, 18 at 9:25 pm - Reply

    Thank you so much Puneet…you’re amazing !

  4. Ravi Kumar 16 Nov, 18 at 3:17 am - Reply

    #Gazzzab #Awesome #Extremely Usefull

    Are you giving online training for VBA & Access

  5. Raj 10 Nov, 18 at 5:05 am - Reply

    Very nice sir

    • Puneet Gogia 11 Nov, 18 at 10:26 am - Reply

      Thanks for your Words.

  6. Lallan Prasad 8 Nov, 18 at 2:01 pm - Reply

    Very useful sir

Leave A Comment