What Is A Pivot Table In Excel?

Not an Excel expert? No problem, we'll tell you all you need to know

what is a pivot table in excel

Last Updated on

Early Cyber Monday deals are starting to go live on Amazon & Best Buy. Big brands such as Lenovo and Samsung are also starting to offer Black Friday discounts early, with many more to follow.

Pivot tables are particularly useful for anyone who needs to analyze data in Excel frequently. Here we look at pivot tables, and how to make one, showing you how to make the most of this tool by filtering and customizing too. If you don’t know what a pivot table is, or how to make the most of one, you will do by the end of this article.

What Is A Pivot Table 

A pivot table is a data analysis tool that people can create in Excel. Pivot tables are typically used as a way to share information as they can be used to sort, group, reorganize, count, summarize, average or total data that is stored within a table. 

Advanced calculations can be used, as well as columns and rows being flipped into different formats.

They are also a quick way of seeing any inconsistencies in your data or typos, helping your work to be more exact. Mostly, pivot tables are used to find significance in big, detailed, sets of data.

How to Make a Pivot Table in Excel

Step

1

Open Excel

Open Excel and select an existing workbook that has the data for your pivot table.

Step

2

Select Any Cell

Select any cell within the data set you want to extract.

Step

3

In The Tables Group Click On ‘Insert’

In the tables group, click on insert, and on the drop-down menu select ‘PivotTable’.

Step

4

A Create PivotTable

A ‘Create PivotTable’ box will appear.

Check that the table Excel automatically selected is correct, and then select ‘New Worksheet’ at the end of the box before clicking ‘OK’.

Create the Fields

Next, we look at the steps to create the drag fields within your pivot table. These next steps will start when the Pivot Table Fields menu appears on your screen.

You will drag the fields you wish to have in your pivot table from the menu to the areas at the bottom of the menu, indicating where you would like each title to appear on the worksheet.

Step

1

Select A Field Title

Select a field title, such as Product or Amount from the list and drag it down to an area below; being filers, columns, rows, or values. The field titles will be extracted from your initial worksheet.

For example, let’s imagine you selected:

  • Filter: Country
  • Rows: Product
  • Values: Sum of Amount

Sort the Table

Now that all of the information you wanted has been transferred onto your pivot table in your chosen format, you will sort the table. Tables will often be sorted by values, such as smallest to largest or vice versa.

Step

1

Select A Cell

Select a cell in the ‘Sum of Amount’ column.

Step

2

Right-Click On The Selected Cell

Right-click on the selected cell and select Sort.

Then select whether you want the data sorted from smallest to largest or largest to smallest.

Filter the Table

When sorting the fields you will have decided how you want the data to be filtered.

Here we’ll show you how to use the filter option:

Step

1

Find The Filter Button

Find the filter button on the worksheet. In our example it would be beside Country.

Step

2

Click The Filter Button

Click the filter button and from the drop-down menu select how you wish to filter the data – to show one specific section or all of the information together.

Changing the Summary Calculation

The final part of a pivot table that you need to learn is how to change the summary calculation. Excel will add the items by default, to change this.

Step

1

Select A Cell

Select a cell from the ‘Sum of Amount’ column.

Step

2

Right-Click On The Cell

Right-click on the cell and select Value Field Settings.

Step

3

Select The Type Of Calculation

Select the type of calculation you want from the pop-up menu and click ‘OK’.

Final Thoughts  

We hope this guide will help you to make pivot tables that can benefit and speed up your daily business analysis and data sharing.