Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac
Pivot Tables are one of the Intermediate Excel Skills and this is an Advanced Pivot Table Tutorial that shows you the top 100 tips and tricks to master this skill.
The 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.
why this list:
If you want to take your pivot table skills the best way is to have a list of tips and tricks which you can learn.
Right? So let’s get started…
Top 100 Pivot Table Tips and Tricks to Get Better at Excel (Categories)
- Before You Create a Pivot Table it is Important to
- Tips to help you while creating a Pivot Table
- Formatting a Pivot Table like a PRO
- Filter Data in a Pivot Table
- Tips to Make Best out of Pivot Tables
- Sharing Pivot Tables with Other
- Things you can do in a Pivot Table with CF
- Using Pivot Charts with Pivot Tables to Visualize your Reports
- Keyboard Shortcuts to Sky Rocket your Pivot Table Work
…now let’s learn each tip one by one.
Read this first
- In this entire list, I’ve used the words “Analyze Tab” and “Design Tab”. To get both of these tabs on the Excel ribbon you need to select a pivot table first.
- Apart from this make sure to download this sample file from here to use to try these tips.
Before you create a pivot table it is important to
Before you create a pivot table you need to spend a few minutes to work on the data source that you are going to use to check if there’s any correction that needs to be done.
1. No Blank Column and Row in the Source Data
One of the things you need to keep in check in the source data that there shouldn’t be any blank row or column.
While creating a pivot table if you have a blank row or a column in it, Excel will only take data up to that row or column.
2. No Blank Cell in the Value Column
Apart from the blank row and column, you must not have a blank cell in the column where you have values.
The biggest reason to keep a check on this is that if you have a blank cell in the values field column: Excel will apply count in the pivot instead of the SUM of the values.
3. Data should be in Right Format
When you using source data for a pivot table then it must be in the right format.
Let suppose, you have dates in a column and that column is formatted as text. In that case, it wouldn’t be possible to group dates in the pivot table that you have created.
4. Use Table for Source Data
Before you create a pivot table, you should convert your source data into a table.
A table expands itself whenever you add new data into it and it makes changing pivot table data source easy (almost automatic).
Here are the steps:
- Select your entire data or any of the cells.
- Press shortcut key Ctrl + T.
- Click OK.
5. Remove Totals from the Data
Last but not least, make sure to delete the total from the data source.
If you have source data with grand totals, Excel will take those totals as values and the values in the pivot table will be increased by doubles.
Here’s a quick tip: If you have applied a table on the data source, Excel won’t include that total while creating a pivot table.
Tips to help you while creating a Pivot Table
Now, these tips you can use when the data prepared and you are all set to create a pivot table with it.
1. Recommended Pivot Tables
There is an option in the “Insert Tab” to check for the recommended pivot tables. When you click on the “Recommended Pivot Tables”, it shows you a set of the pivot tables that can be possible with the data you have.
This option is quite useful when you want to see all the possibilities you have with the available data.
2. Creating a Pivot Table from Quick Analysis
There is a tool in Excel called “Quick Analysis” which is like a quick toolbar that appears whenever you select the data range.
And from this tool, you can create a pivot table as well.
Quick Analysis Tool ➜ Tables ➜ Blank Pivot Table.
3. External Workbook as a Source for the Pivot Table
This is one of the most useful pivot table tips from this list which I want you to start using for now onward.
Let’s say, you want to create a pivot from a workbook that is in a different folder and you don’t want to add data from that workbook into your current sheet.
You can link that file as a source without adding data into the current file, here are the steps.
- In the create pivot table dialog box, select “Use an external data source”.
- After that, go to the Connections tab and click on “Browse for more”.