Pivot Tables in Excel – How To Use Tutorial

puneet-gogia-excel-champs-09-06-23

- by Puneet

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.

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.

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 working on the data source that you are going to use to check if there is 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 is that there shouldn’t be any blank rows or columns. 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.

excel pivot tables tips and tricks to delete balnk rows and column from the source data

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.

excel pivot tables tips and tricks to there should be no blank cell in the value column

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 the Right Format

When you are using source data for a pivot table then it must be in the right format. Let’s 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.

excel pivot tables tips and tricks to data format should be right

4. Use a Table for Source Data

Before you create a pivot table, you should convert your source data into a table.

excel pivot table tips tricks to insert a pivot chart

A table expands itself whenever you add new data to it and it makes changing the pivot table data source easy (almost automatic). Here are the steps:

  1. Select your entire data or any of the cells.
  2. Press the shortcut key Ctrl + T.
  3. Click OK.

5. Remove Totals from the Data

Last but not least, make sure to delete the total from the data source.

excel pivot tables tips and tricks to delete total from the source data

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.

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 is prepared, and you are all set to create a pivot table with it.

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.

excel pivot table tips tricks to create a pivot table using recommended pivot option

This option is quite useful when you want to see all the possibilities you have with the available data.

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.

excel pivot table tips tricks to create a pivot table using quick analysis tool

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”.
excel pivot table tips tricks to select external data source
  • After that, go to the Connections tab and click on “Browse for more”.
excel pivot table tips tricks to click on more
  • Locate the file that you want to use and select it.
  • Click OK.
  • Now select the sheet in which you have data.
excel pivot table tips tricks to select select worksheet
  • Click OK (Twice).
excel pivot table tips tricks to file is connected

Now you can create a pivot table with all the field options from the external source file.

Instead of creating a pivot table from the Insert tab, you can use “Classic Pivot Table and Pivot Chart Wizard” as well.

The one thing which I love about classic wizard is there is an option to pull data from multiple worksheets before creating a pivot table.

excel pivot table tips tricks to create a pivot table using pivot table wizard

A simple way to open this wizard is by using the keyboard shortcut: Alt + D + P.

In the pivot table field settings, there is an option for searching for the fields. You can search for the field where you have large with hundreds of columns.

excel pivot table tips tricks to add fields through search bar

When you start typing in the search box it starts filtering columns.

There is an option that you can use to change the style of the “Pivot table Field Window”. Click on the gear icon on the top right side and select the style you want to apply.

excel pivot table tips tricks to change field window style

If you have large data set then you can sort the field list using A to Z order to make it easy for you to find the required fields.

Click on the gear icon on the top right side and select the “Sort A to Z”. By default, fields are sorted as per source data.

excel-pivot-table-tips-tricks-to-sort-field-list

It happens with me that when I create a pivot table and again when I click on it shows “Field List” at the right side and this happens every time I click on a pivot table.

But you can turn it OFF and for this, you just need to click on the “Feild List Button” in the “PivotTable Analyze” tab.

excel pivot table tips tricks to hide unhide field list pane

Once you create a pivot table the next thing which I think you need to do is to name a pivot table.

And for this, you can go to Analyze Tab ➜ Pivot Table ➜ Pivot Table Options and then enter the new name.

excel pivot table tips tricks to change the name of a pivot table

Recently, the option to create a pivot table is added into the Excel’s online App (Limited Options).

It’s as simple as creating a pivot in Excel’s Web App:

In the Insert Tab, click on the “Pivot Table” button from the table group…

excel pivot table tips tricks create a pivot table in excel online app

…and then select the source data range…

excel pivot table tips tricks to select the data to create a pivot table in excel online app

…and the worksheet where you want to insert it…

…and, in the end, click OK.

If you want to automate your pivot table creation process, you can use the VBA code for this.

excel pivot table tips tricks to use vba code

In this guide, I’ve mentioned a simple step by step process to create a pivot table using macro code.

Formatting a Pivot Table like a PRO

As you can use a Pivot Table as a report, it’s important to make some changes in the default formatting.

There are several pre-defined styles in Excel for a pivot table that you can apply with a single click.

In the designed tab, you can find “Pivot Table Style” and when you click on the “More” you can simply select a style which you like.

You can also create a new style, a customized one, you can do this by using the “New PivotTable Style” option.

excel pivot table tips tricks to apply style to a pivot table

Once you have done with your customized style you can simply save it to use it next time, it will be there always.

Go to the pivot table options (right-click on the pivot table and go to pivot table options) and tick mark the “Preserve cell formatting on update”.

The benefit of this option is whenever you update your pivot table you won’t lose the formatting you have.

excel pivot table tips tricks to preserve cell formatting when you update a pivot table

Apart from formatting one which you also need to preserve and that’s “Column Width”.

For this, go to “Pivot Table Options” and untick the “Autofit column width on update” and click OK after that.

excel pivot table tips tricks to disable auto width update when you update a pivot table

When you use more than one item in a pivot table you can simply repeat labels for the top items. It makes it easy to understand the structure of the pivot table.

  • Select the pivot table and go to the “Design tab”.
  • In the design tab, go to Layout ➜ Report Layout ➜ Repeat All Item Labels.
excel pivot table tips tricks to repeat all item labels

In most of the cases, you need to format values after you create a pivot table.

For example, if you want to change the number of decimals from the numbers. All you need to do is select the values column and open the “Format Cell” option.

excel pivot table tips tricks to formatting values

And from this option, you can change the number decimals. From the “Format” option, you can even change other options as well.

One of my favorite thing with formatting is changing “Font Style” for a pivot table.

You can use the format option but the easiest way is to do it from the Home Tab. Select the entire pivot table and then select the font style.

excel pivot table tips tricks to change font style for pivot tables

When you add a pivot table with more than one item field you will get subtotals for the main field.

But sometimes there is no need to show subtotals. In that situation, you can hide them using the following steps:

  • Click on the pivot table and go to the Analyze tab.
  • In the Analyze tab, go to Layout ➜ Subtotals ➜ Do not show subtotals.
excel pivot table tips tricks to hide unhide subtotals

Just like subtotals you can also hide and unhide grand totals and below are the simple steps to do that.

  • Click on the pivot table and go to the Analyze tab.
  • In the Analyze tab, go to Layout ➜ Grand Total ➜ Off for Rows and Columns.
excel pivot table tips tricks to hide unhide grand totals

In a normal pivot table, we have a single format of values in the values column.

But, there are some (few) situations when you need to have different formats in a single pivot table, just like below. For this, you need to use custom formatting.

In Excel, there are predefined color themes that you can use. These themes can be applied to pivot tables as well. Go to the “Page Layout” tab, and click on the “Themes” dropdown.

excel pivot table tips tricks to apply theme

There are more than 32 themes that you can apply with a single click or you can save your current formatting style as a theme.

For every pivot table, you can choose a layout.

In Excel (if you are using 2007 or greater versions) you can have three different layouts. In the design tab, go to the Layout Report ➜ Layout, and select the layout which you want to apply.

excel pivot table tips tricks changing the layout of a pivot table

One of the first things that I do when I create a pivot table is applying “Branded Row and Column”.

You can apply it from the design tab and tick mark the “Banded Column” and “Banded Rows”.

excel pivot table tips tricks to apply 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”.

Just like a normal filter, you can turn on/off filters in a pivot table. In the “Analyze Tab”, you can click the “Field Header” button to turn On or OFF the filters.

excel pivot table tips tricks to turn off on filters

You have selected a cell(s) in a pivot table and you want to filter only those cells, here’s the option that you can use.

After selecting the cells right click and go to “Filter” and after that select “Keep Only Selected Items”.

excel pivot table tips tricks to current selection to the filter

Just like filtering the selected cells you can also hide them. For this, go to “Filter” and after that select “Hide Selected Items”.

excel pivot table tips tricks to hide selection

Apart from normal filters, you use label filters and values filters to filter with a specific value or criteria.

Label Filter:

excel pivot table tips tricks to label filter

Value Filter:

excel pivot table tips tricks to value filter

As I said in the above tip that you can have the Label and Value field, but, you need to activate an option to use both of these filter options altogether.

  • First of all, open the “PivotTable options” and go to the “Total & Filter” tab.
  • In “Total & Filter” Tab, tick mark the “Allow multiple filters per field”.
  • After that, click OK.
excel pivot table tips tricks to allow multiple filters per field

One of my favorite options in filters is to filter “Top 10 Values”. This filter option is useful while creating an instant report.

For this, you need to go to the “Value Filter” and click on the “Top 10” and then click OK.

excel pivot table tips tricks to filter top 10 values

If you want to filter while creating a pivot table, you can do this from the “Pivot Field” window.

To filter values from a column, you can click on the down arrow from the right side and filter the values as you need.

excel pivot table tips tricks to filter fields from the pivot table fields window

One of the best things which I have found to filter data in a pivot table is using a “Slicer”.

To insert a slicer all you need to do is go to “Analyze Tab” and in the “Filter” group click on the “Insert Slicer” button, after that select the field for which you want to insert a slicer and then click OK.

excel pivot table tips tricks to add a slicer

Related: Excel SLICER – A Complete Guide on how to Filter Data with it

Once you insert a slicer you can change its style and format.

  • Select the slicer and go to the Options tab.
  • From the “Slicer Styles” click on the drop-down and select the style you want to apply.
excel pivot table tips tricks to format a slicer

Apart from the styles, you can change the setting from the settings window: Click on the “Slicer Settings” button to open the settings window.

excel pivot table tips tricks to format a slicer and other options

Sometimes when you have multiple pivot tables, it’s hard to control all of them. But if you connect a single slicer with multiple pivot tables, you can control all the pivots with no efforts.

  • First of all, insert a slicer.
  • And, after that, right-click on the slicer and select “Report Connections”.
excel pivot table tips tricks to single slicer for all the pivot tables report connections
  • From the dialog box, select all the pivots and click OK.
excel pivot table tips tricks to single slicer for all the pivot tables select

Now you can simply filter all the pivot tables with a single slicer.

Unlike a slicer, a timeline is a specific filter tool to filter dates and it’s way more powerful than the normal filter.

To insert a slicer all you need to do is go to “Analyze Tab” and in the “Filter” group click on the “Insert Timeline” button and after that select the date column and click OK.

excel pivot table tips tricks to add a timeline

Once you insert a timeline you can change its style and format.

  • Select the timeline and go to the Options tab.
  • From “Timeline Styles” click on the drop-down and select the style you want to apply.
excel pivot table tips tricks to format a timeline filter

Apart from the styles, you can change settings as well.

excel pivot table tips tricks to format a timeline filter and other options

You can use Excel Wildcard Characters in all the filter options where you need to enter the value to filter. Look at the below examples where I have used an asterisk to filter values starting letter A.

excel pivot table tips tricks to wildcard characters with filters

If you have applied filters on multiple fields, you can remove all those filters from Analyze Tab ➜ Actions ➜ Clear ➜ Clear Filter.

excel pivot table tips tricks to clear all filters

Tips to make best out of pivot tables

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

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

Pivot tables are dynamic, so when you add new data or update values into the source data you need to refresh it so that the pivot table gets all the new add values from the source. Refreshing a pivot table is simple:

  • One, right-click on a pivot and select the “Refresh”.
  • Second, go to the “Analyze” tab and click on the “refresh” button.
excel pivot table tips tricks to refresh a pivot table manually

There’s a simple option in Excel which you can activate and make a pivot table get refreshed automatically every time you open the workbook. For this here are the simple steps:

  • First of all, right-click on a pivot table and go to “Pivot Table Options”.
  • After that, go to the “Data” tab and tick mark the “Refresh the data when opening the file”.
excel pivot table tips tricks to tick mark refresh pivot table on opening a file
  • In the end, click OK.

Now every time you open the workbook this pivot table will get updated instantly.

If you want to update your pivot table automatically after a specific interval then this tip is for you.

…here’s how to do it.

  • First of all, while creating a pivot table, in the “Create Pivot Table” window, tick mark “Add this data to the data model”.
excel pivot table tips tricks to add this data to the data model
  • After that, once you create a pivot table, select any of the cells, and go to “Analyze Tab”.
  • In Analyze Tab, Data ➜ Change Data Source ➜ Connection Properties.
excel pivot table tips tricks to click connection properties
  • Now in “Connection Properties”, in the usage tab, tick mark “Refresh Every” and enter minutes.
excel pivot table tips tricks to  add time to refresh a pivot table
  • In the end, click OK.

Now after that specific period which you entered your pivot will automatically be refreshed.

Sometimes, when you have errors in your source data they reflect in the same way in the pivot and this is not a good thing at all.

The better way is to replace those errors with a meaningful value.

Below are the steps to follow:

  • First of all, right click on your pivot table and open pivot table options.
  • Now in “Layout & Format”, tick mark “For error value show” and enter the value in the input box.
excel pivot table tips tricks to enter value for error cells
  • In the end, click OK.

Now for all the errors, you will have the value you have specified.

Let’s say you have a pivot for sales data and there are some cells that are blank.

For a person who is not aware of why these cells are blank can question you about this. So, it’s better to replace it with a meaningful word.

…simple steps you need to follow for this.

  • First of all, “right click” on your pivot table and open pivot table options.
  • Now in “Layout & Format”, tick mark “For empty cells show” and enter the value in the input box.
pivot table tricks enter value for blank cells
  • In the end, click OK.

Now for all the blank cells, you will have the value you have specified.

Let’s say you have a pivot for sales data and there are some cells that are blank.

For a person who is not aware of why these cells are blank can question you about this. So, it’s better to replace it with a meaningful word.

…simple steps you need to follow for this.

  • First of all, “right click” on your pivot table and open pivot table options.
  • Now in “Layout & Format”, tick mark “For empty cells show” and enter the value in the input box.
pivot table tricks enter value for blank cells
  • In the end, click OK.

Now for all the blank cells, you will have the value you have specified.

Now let’s say you have a large pivot table with multiple items.

Here you can insert a blank row after each item so that there would be no clutter in the pivot.

excel pivot table tips tricks to add a blank line after each item

Check out these steps to follow:

  • Select the pivot table and go to the Design tab.
  • In the design tab, go to Layout ➜ Blank Rows ➜ Insert Blank Line after Each Item.
excel pivot table tips tricks to add a blank line after each item from design tab select

The best thing about this option is it gives a clearer view of your report.

When you have a long list of items in your pivot you can arrange all those items in a custom order by just drag and drop.

excel pivot table tricks to drag and drop items

Suppose you have created a pivot table from month wise sales data and you have used products as a report filter.

With the”Show Report Filter Pages” option, you can create multiple worksheets with a pivot table for each product.

Let’s say if you have 10 products in a pivot filter you can create 10 different worksheets with a single click.

Use these steps:

  • Select your pivot and go to the analyze tab.
  • In the analyze tab, go to pivot table ➜ Options ➜ Show Report Filter Pages.
excel pivot table tips tricks to create separate worksheet for each item

Now you have four pivot tables in four separate worksheets.

excel pivot table tips tricks to create seperate worksheet for each item options

When you add value column into the value field it shows SUM or COUNT (sometimes), but, there few other things which you can calculate here:

To open “Value Settings” options select a cell from the value column and right-click.

excel pivot table tips tricks to change values settings for value field

And from the right-click menu, open “Value Field Settings” and then click on

From the “Summarize value field by” select the type of the calculation which you want to show in the pivot.

Let’s say you have a pivot table month wise sale.

Now, you want to insert a running total in your pivot table to show a complete growth of sales in the entire month.

Here are the steps:

  • Right click on it & click “Value Field Setting”.
  • From “Show Values As” drop-down list, select “Running Total In”.
excel pivot table tips tricks to add running total value field setting
  • In the end, click OK.
excel pivot table tips tricks to add running total column

Learn more about adding a running total in a pivot table.

Ranking gives you a better way to compare things with each other…

…and to insert a rank column in a pivot table you can use the following steps:

  • First of all, insert the same data field twice in the pivot.
  • After that for the second field, right click on it and open “Value Field Settings”.
  • Go to “Show Values as” tab and select “Rank Largest To Smallest”.
excel pivot table tips tricks to add ranks options
  • In the end, click OK.
excel pivot table tips tricks to add rank column

…click here to learn more about ranking in a pivot table.

Imagine you have a pivot table for product wise sale.

And now, you want to calculate the percentage share of all products in the total sales.

Steps to use:

  • First of all, insert the same data field twice in the pivot.
  • After that for the second field, right-click on it and open “Value Field Settings”.
  • Go to “Show Values as” tab and select “% of Grand Total”.
excel pivot table tips tricks to create a percentage share options
  • In the end, click OK.
excel pivot table tips tricks to create a percentage share

This also a perfect option to create a quick report.

When you create a pivot table, Excel asks you to add a new worksheet for the pivot table…

…but it also has an option to move an existing pivot table to a new worksheet.

  • For this, go to “Analyze Tab” ➜ Actions ➜ Move Pivot Tables.
excel pivot table tips tricks to move a pivot table to a new worksheet

There is a situation where you need to refer to a cell in a pivot.

But, there could be a problem because when you refer to a cell in a pivot Excel automatically uses GetPivotData function for reference.

The best thing is, you can disable it and here are the steps:

  • Go to File Tab ➜ Options.
  • In options, go to Formulas ➜ Working with Formulas ➜ untick “Use GetPivotData functions for PivotTable reference”.
excel pivot table tips tricks to turn off get pivot data

You can also use a VBA code for this as well:

Sub deactivateGetPivotData()

Application.GenerateGetPivotData = False

End Sub

Check out these ➜ Top 100 Useful Excel VBA Codes + PDF File

Just imagine, you want to create a month wise pivot table but you have dates in your data.

In this situation, you need to add an extra column for months.

But the best way is to create using grouping dates methods in the pivot table using this method you don’t need to add a helper column.

Use the below steps:

  • First of all, you need to insert the date as a row item in your pivot table.
excel pivot table tips tricks to group dates add date column
  • Right click on the pivot table, and select “Group…”.
excel pivot table tips tricks to group dates click group
  • Select “Month” from by section and click OK.
excel pivot table tips tricks to group dates select month

It will group all the dates into months and if you want to learn more about this option here’s the complete guide.

Just like dates, you can group numeric values as well.

The steps are simple.

  • Right click on the pivot table, and select “Group…”.
  • Enter the value to create a range of groups in the “by” and click OK.
excel pivot table tips tricks to group numeric data

…click here to learn how pivot table’s grouping option can help you create a histogram in Excel.

To group columns just like rows, you can use the same steps as rows. But you need to select a column header before that.

When you don’t need groups in your pivot table you can simply ungroup it by right-click and select the “Ungroup”.

To become an advanced pivot table user you should learn to create a calculated field and item in a pivot table.

Let’s say in the below pivot table, you need to create new data by field multiplying the present data field with 10.

excel pivot table tips tricks to insert a custom field

In this situation, instead of creating a separate column in a pivot table you can insert a calculated item.

a complete guide to creating a calculated item and field in a pivot table

Once you add a calculation in a pivot table or you have got a pivot table with a calculated field or item, you can see the list of formulas used in it.

For this, just go to “Analyze Tab” ➜ Calculation ➜ Fields, Items & Sets ➜ List Formulas.

excel pivot table tips tricks to get the lis of formulas used in the pivot table item

You’ll instantly get a new worksheet with a list of formulas used in the pivot table.

If you have duplicate values in your date then you can use a pivot table to get a list of unique values.

  • First of all, you need to insert a pivot table and then add the column where you have duplicate values as a row field.
  • After that, copy that row field from the pivot and paste it as values.
  • Now, the list you have as values is a list of unique values.

The thing which I love about using a pivot table for using to check unique values is it’s a one time setup.

You don’t need to create it again and again.

Let say you have entries in your source data where there are no values or zero values.

You can activate from the field option to “Show items with no data”.

  • First of all, right-click on the field and open the “Field Settings”.
  • Now, go to “Layout & Print” and tick mark “Show items with no data” and click OK.
excel pivot table tips tricks to show items with no data

Boom! all the items where you have no data will show in the pivot table.

This is one of my favorite pivot table options.

With this, you can create a column where it shows the difference of current values from the previous value.

Let say you have a pivot with month values,…

…then, with this option…

…you can add a column of difference value from the previous month, just like below.

Here are the steps:

  • First of all, you need to add the column where you have values, twice in the value field.
excel pivot table tips tricks to insert difference column add value column twice
  • After that, for the second field, open the “Value Setting” and “Show Value As”.
  • Now, from the “Show values as” drop-down select “Difference From” and select “Month” and “(Previous)” from the “Base Item”.
  • In the end, click OK.
excel pivot table tips tricks to insert difference column select previous from options

This will instantly convert the values column into a column with a difference from the previous.

excel pivot table tips tricks to insert difference column

When you double-click on a value cell in a pivot table it shows the data behind that value.

excel pivot table tips tricks to disable show details

It’s a good thing but not all the time you need this to happen and that’s why you can disable it when required.

All you need to do is open the pivot table options and go to “Data Tab” and untick “Enable show details”.

And then click OK.

These are the simple steps to paste a pivot chart into a PowerPoint slide.

  • First of all, select a pivot table and copy it.
  • After that, go to the PowerPoint slide and open the paste special options.
excel pivot table tips tricks to pivot table to powerpoint paste spcial
  • Now from the paste special dialog box, select “Microsoft Excel Chart Object” and click OK.

Insert Image

To make changes to the pivot table you need to double click on the chart.

To add a pivot table into Microsoft Word you need to follow the same steps you did in PowerPoint.

If you have more than one dimension field in a row or column you can expand or collapse the outer fields.

You need to click on the + button to expand and – button to collapse…

excel pivot table tips tricks to expand or collapse field headings

…and to expand or collapse all the groups in one go, you can right-click and choose the option.

If you have more than one dimension field in a row or column you can expand or collapse the outer fields.

excel pivot table tips tricks to hide unhide expand or collapse buttons

There is an option in a pivot table where you can count the number of the cell with the numeric value.

excel pivot table tips tricks to use pivot table in a protected worksheet

For this, all you need to do is open the “Value Option” and select “Count Number” from the “Summary value field by” and then click OK.

Yes, you can sort according to the corresponding values.

  • All you need to do is open the filter and select the “More Sort Option”.
excel-pivot-table-tips-tricks-to-sort-items-according-to-a-corresponding-value-open-filter
  • Then select the “Accessing (A to Z) by:” and select the column for sorting and then click OK.
excel pivot table tips tricks to sort items according to a corresponding value open select

Note:

If you have multiple value columns, you can only use one column for sorting order.

Yes, you can use a custom sorting order for your pivot table.

  • For this, when you open “More Sorting Options”, click on “More Options” and untick the “Sort automatically every time the report is updated”.
  • After that select the sorting order and click OK in the end.

You need to create a new custom sorting order then you can create it from File Tab ➜ Options ➜ Advanced ➜ General ➜ Edit Custom List.

If you enable the “Deferred Layout Update” and drag and move fields between areas after that.

excel pivot table tips tricks to deferred layout

Your pivot table will not update unless you click on the Update button below at the corner of the PivotTable Fields.

It makes it easier for you to check the pivot table and then.

When you insert a value field, the name you get for the field comes something like this “Sum of the Amount” or “Count of Units”.

But sometimes (well, all the time) you need to change this name to the name without “Sum of” or “Count of”.

For this, all you need to do is to remove “Count of” or “Sum of” from the cell and add a space at the end of the name.

Yes, that’s it.

If you want to select an entire pivot table in one go:

Select any of the cells from the pivot table and use the keyboard shortcut Control + A.

Or…

Go to Analyze tab ➜ Select ➜ Entire Pivot Table.

excel pivot table tips tricks to select the entire pivot table at once

If you want to convert a pivot table in values, all you need to do is select the entire pivot table and then:

Use Control + C to copy it and then Paste Special ➜ Values.

When you’re protecting a worksheet where you have a pivot table, make sure to tick mark:

“Use PivotTable & PivotChart”

from the “Allow all the users of this worksheet to:”.

excel pivot table tips tricks to use pivot table in a protected worksheet

If you want to open the “Value Settings” for a particular value column…

…then…

excel pivot table tips tricks to double click to open value field settings

…the best way is to double click on the header of the column.

Make your pivot tables a little more perfect

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.

If you think like this: when you create a pivot table from scratch, Excel creates a pivot cache.

So, more pivot tables you create from scratch more pivot cache Excel will create and your file will need to store more data.

So what’s the point?

Make sure all pivot tables which are from data sources must have the same cache.

But Puneet how could I do this?

Simple, whenever you need to create a second, third, or fourth… just copy and paste the first one and make changes in it.

One more thing which you can do before sending a pivot table to someone is deleting the source data.

Your pivot table will still work fine.

And, if someone needs to have the source data can get it by clicking the grand total of the pivot table.

One more way you can use to share a pivot table with someone is to create a webpage.

Yes, a simple HTML file with a pivot table.

  • For this, all you need to do is to save the workbook as a web page [html].
excel pivot table tips tricks to save a pivot table as a web page
  • In the Publish as Web Page, select the pivot table and click “Publish”.
excel pivot table tips tricks to save a pivot table as a web page as publish

Now you can send this HTML web-page to anyone and he/she can view the pivot table (not editable) even on their mobile phone.

excel pivot table tips tricks to save a pivot table as a web page html file

Let’s say you have a web link for an Excel file, just like the below:

https://excelchamps.com/book1.xlsx

In this workbook, you have the data and with that data, you need to create a pivot table.

For this, the POWER QUERY is the key.

Check this out: Power Query Examples + Tips and Tricks

  • First of all, go to the Data Tab ➜ Get & Transform Data ➜ From Web.
excel pivot table tips tricks tocreat pivot table using power uery by getting data from a web link
  • Now, in the “From Web” dialog box, enter the web address of the workbook and click OK.
excel pivot table tips tricks tocreat pivot table using power uery by getting data from a web link add url
  • After that, select the worksheet and click “Load To”.
excel pivot table tips tricks tocreat pivot table using power uery by getting data from a web link select worksheet
  • Next, select the PivotTable Report and click OK.
excel-pivot-table-tips-tricks-tocreat-pivot-table-using-power-uery-by-getting-data-from-a-web-link-add-url

At this point, you have a blank pivot table that is connected to the workbook from the web address you have entered.

Now you can create a pivot table as you want.

Things you can do in a Pivot Table with CF

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

There are all the CF options available to use with a pivot table.

➜ here is the guide which can help you to learn all the different ways to use CF in pivot tables.

Instead of filtering, you can highlight the top 10 values from a pivot table.

For this, you need to use conditional formatting.

Steps are below:

  • Select any of the cells from the value column from your pivot table.
  • Go to Home tab ➜ Styles ➜ Conditional Formatting.
  • Now in conditional formatting, go to Top/Bottom Rules ➜ Top 10 Items.
excel pivot table tips tricks toapply top 10 conditional formatting option
  • Select the color from the window you have.
excel pivot table tips tricks to-apply top 10 conditional formatting option select color
  • And in the end, click OK.

This option is quite useful while creating quick reports with a pivot table and once you.

You can simply remove conditional formatting from a pivot table using the below steps:

  • First of all, select any of the cells from the pivot table.
  • After that, go to Home Tab ➜ Styles ➜ Conditional Formatting ➜ Clear Rules ➜ “Clear rules from This Pivot Table”.
excel pivot table tips tricks to clear all conditional formatting

If you have more than one pivot table then you need to remove CF one by one.

Using Pivot Charts with Pivot Tables to Visualize your Reports

I’m a big fan of the pivot chart.

If you know how to use a pivot chart properly you can make the 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 and if you want to learn all the stuff about a pivot chart you can learn from this guide.

I’ve shared a simple keyboard shortcut to insert a pivot chart but you also use below steps as well:

  • Select a cell from the pivot table and go to “Analyze tab”.
  • In the “Analyze Tab”, click on the “Pivot Chart”.
excel pivot table tips tricks to insert a pivot chart

It will instantly create a pivot chart from the pivot table you have.

A Pivot table and a Pivot Chart is my favorite way to create a histogram in Excel.

excel pivot table tips tricks to create a histogram using pivot chart

Here’s a step by step guide for this.

When you insert a new pivot chart it comes with some buttons to filter it which sometimes are not really useful.

And if you think like this, you can hide all of them or some of them.

excel pivot table tips tricks a pivot chart with filter buttons

Right-click on the button and select “Hide value Field Button on the Chart” to hide the selected button or click on “Hide all the field button of the Chart” to hide all the buttons.

excel pivot table tips tricks hide filter buttons from pivot chart

When you hide all the buttons from a pivot chart it also hides the filter button from the bottom of the chart but, you can still filter it using the pivot table filter, slicer, or a timeline.

Here are the simple steps to paste a pivot chart into a PowerPoint slide.

  • First of all, select a pivot chart and copy it.
  • After that, go to the PowerPoint slide and open the paste special options.
excel pivot table tips tricks to copy pivot chart to power point slide open paste special
  • Now from the paste special dialog box, select “Microsoft Excel Chart Object” and click OK.
excel pivot table tips tricks to copy pivot chart to power point slide select Microsoft excel chart object

To make changes to the pivot chart you need to double click on it.

Keyboard shortcuts to skyrocket 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.

Alt + N + V

To use this shortcut key make sure you have selected the source data or the active cell is from the source data.

Alt + Shift + Right Arrow

Let say you have a pivot table with months and you want to group the first six or last six months.

All you need to do it select those six cells and use this shortcut key simply.

Alt + Shift + Left Arrow

Just like you can create a group of items, this shortcut helps you ungroup those items from the group.

Ctrl + –

This shortcut key will simply hide the selected cell or cells.

Actually, it doesn’t hide the cell but filters them which you can clear after that from the filters options.

Ctrl + –

To use this shortcut key you need to select a cell from the value field column.

And when you press this shortcut key, it opens the Calculated Field window.

Alt + D and P

In this keyboard shortcut, you need to press the keys subsequently.

Alt + Down Arrow

This key opens the field list.

Alt + F1

To use this keyboard shortcut, you need to select a cell from the pivot table. This key inserts a pivot chart into the existing sheet.

F11

And, if you want to inserts a pivot into a new worksheet then you need to use the above key only.

In the End

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

And with these tips and tricks, 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 the next 10 and so on.

But you need to tell me one thing now: What’s your favorite pivot table tip?

Make sure to share your views with me, 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, and if you liked this article, then please subscribe to our YouTube Channel for Video Tutorials.

Don’t Miss to Read These

36 thoughts on “Pivot Tables in Excel – How To Use Tutorial”

  1. Hi dear friends , I appreciate your hard work in creating these useful information and hope you all the best in life and your business .

    Reply
  2. Hi Puneet, sometimes I hit the keyboard by accident when the focus is on an empty cell, and another empty cell is placed inside the cell, I need to know how to delete the inserted cell without having to copy everything into a new sheet except for the corrupted cell… any help appreciated, this has happened to me so many times.

    Reply
  3. Consider starting with the end in mind so we can see 1st where you are going and how what you are doing might give a report that will mean something to the audience we are providing information to.

    Reply
  4. Why does Microsoft Excel allow you to change data values in a pivot table? Not just headings, but data that is brought into the table from a data source. It seems to me that compromises the data integrity of the pivot.

    Reply
  5. Your e-book link for pivot tables does not work. I got the other 3 books. The zip file that is downloaded contains 3 books/PDFs, not the pivot table tips book. I downloaded 4 zip files, but they contain the same 3 books (tips, macro codes & shortcuts). Will you create one for power query?
    Thanks.

    Reply
  6. Hi.How are you?I am referring your website last one year and found very very useful for us in our daily working.I have question regarding “Pivot table” searching for fields.

    which is not happening in excel 2013 not in 2007 .

    Please guide me.

    Reply
  7. Dear Puneet,
    If I paste the PT as Values by retaining the original style with subtotal field. I.e.(without changing the attribute) currently all subtotal values changes to text. What I am seeking to have an option while pasting the PT with formula. So that data field with PT subtotals should automatically build the formula (vertical and horizontal)

    Reply
  8. Hi – Can I purchase a pdf of this? Of any of your things?…It is often tought to sit here at my desk reading 🙂

    thanks

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

    Reply
  10. 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!!

    Reply

Leave a Comment