The Ultimate Guide to Pivot Tables in Excel

In this lesson, you will learn how to use Pivot Table in Excel.

How to insert the Pivot Table?

I have prepared sample sales data for an imaginary company.

pivot table data

The table contains columns such as:

  • Date
  • Product
  • Category
  • Customer
  • CRM Customer Group
  • Region
  • State
  • City
  • Sales Manager
  • Sales Rep
  • Price
  • Quantity
  • Sales

To insert a pivot table, go to the Insert tab and click the Pivot Table button. You can also use the keyboard shortcut ALT + N + V.

create pivot table

To create a pivot table, select the range of cells you want to analyze. Excel will automatically identify the correct range in most cases. Click OK to create the empty pivot table on a new worksheet.

blank pivot table

Populate Pivot Table With Data

To populate a pivot table with data, drag and drop the column labels into the four areas on the right side of the screen. You can experiment with different combinations to see how the pivot table changes.

Here are the four areas:

  • Rows
  • Columns
  • Values
  • Filters

The possibilities for creating different tables based on the same data set are endless. The only limitation is your imagination.

pivot table fields

Here are some common rules to follow when creating a pivot table:

  • Filters: Use this area to filter the data in your pivot table. For example, you could filter by region, date, or customer.
  • Rows: This area shows the values of the columns you drag into it. For example, if you drag the “Region” column into the “Rows” area, your pivot table will show the sales for each region.
  • Columns: This area shows the values of the columns you drag into it. For example, if you drag the “Product” column into the “Columns” area, your pivot table will show the sales for each product.
  • Values: This area shows the calculations that are performed on the data in your pivot table. For example, you could calculate the total sales or the average sales.
See also  How to Use Data Validation in Excel

The more you use pivot tables, the more familiar you will become with the different areas and how to use them.

You can put two different columns in the same area. This will create a more detailed pivot table, but it may also make it more difficult to read.

Let’s see an example: I created a basic report of sales products for customers.

Sum of Sales of Products

Excel calculated many useful numbers in the pivot table. You can see the sales of each product for each customer. The pivot table also shows the total sales for each row and column.

Adding more categories of products to the pivot table will make it more detailed, but it may also make it more difficult to read.

Sum of Sales of Products and Category

Let’s see how to create better reports.

Formatting Pivot Table

Right-click on the data values and choose “Number Format” to format your pivot table.

Number format

I’ll choose the Currency format to show the values as sales revenues. I’ll reduce the decimal places to 0 to save space.

Format cells Currency

Number formatting makes the report easier to read.

Pivot Table number formatting

Sort Pivot Table

To improve visibility even further, sort the pivot table by clicking on the bottom row and selecting “Sort Largest to Smallest”.

Sort Largest to Smallest column

Sort the data in the last column in descending order.

Sort Largest to Smallest rows

This is how the report looks now.

pivot table sorted largest to smallest

The pivot table is now easier to read because the sales are sorted in descending order. This makes it easier to see the top-performing products and regions.

You can also sort the pivot table by other criteria, such as date or customer.

See also  Understanding OLE Action in Excel

Keeping your pivot table in a logical order makes it look more professional and helps you to communicate your findings more effectively.

Pivot table filtering

A pivot table can be a very complex report, so you may need to filter the data. To do this, you can add filters to the pivot table.

In this example, I added filters for Region, State, and City.

pivot table filter

The filter appears at the top of the pivot table. I filtered the data to show only sales from the Central region in Kansas.

Pivot Table Filter Central Kansas

The pivot table shows only sales from the Central region in Kansas. The data is sorted in descending order.

Slicer

Another way to filter a pivot table is to use a slicer.

A slicer is a tool that allows you to quickly filter the data in a pivot table by one or more fields.

To insert a slicer, click on a cell in the pivot table and then go to the Analyze tab.

Insert Slicer

You will see a window with the options for inserting slicers.

Insert Slicers Product

Choose any product you want. I will click on “Product” to see the list of products.

Product Slicer

The slicer shows me some useful information even before I select anything. I can see that one product is grayed out, which means that there is no data for that product. I can select a product to see the filtered data quickly.

I can click on each product in the slicer to see how the pivot table data changes. The slicer filters the table very quickly.

To select more than one product, hold down the Ctrl key and click on the other products.

See also  How to Insert Smartart in Excel

Slicer two products

You can add more slicers to filter the data even further. To do this, click on a cell in the pivot table and then use the Insert Slicer button again.

I added another slicer to filter by customer.

Pivot Table two Slicers

My data is filtered the way I want it. If you don’t like the slicer, you can remove it by right-clicking it and selecting “Remove”.

Remove Slicer

You can also keep the slicer and remove its filter. To do this, click the Clear Filter button in the slicer.

Updating (refreshing pivot table)

Pivot tables do not automatically update when the source data changes. To update a pivot table, right-click it and select “Refresh”.

Pivot Table refresh

Updating the data in a pivot table takes just a second. The formatting will remain the same.