How to Create a Pivot Table in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

It is easy to use the Recommended PivotTables option for creating pivot tables. But you might prefer to create a Pivot Table in Excel manually. Prior to Excel 2013, manually creating a Pivot Table was your only option.

In this article, I am going to describe to you how to create a Pivot Table in Excel with easy steps.

The above video shows the easy steps of creating a Pivot Table in Excel. We will describe the steps in detail in the following sections.


What Is a Pivot Table in Excel?

A Pivot Table in Excel is a powerful tool that can help you summarize and analyze large amounts of data. It can help you to generate insights into large datasets quickly. Also, you can sort, filter, and calculate data in a more digestible way.

You can quickly categorize and analyze data using a Pivot Table based on several criteria, including dates, categories, and other elements. You can add, count, average, and discover the maximum and minimum values among other calculations.


What Is a Pivot Table Used for in Excel?

The main purpose of a Pivot Table is to summarize and analyze large amounts of data in an organized way. Here, we can list some of the common uses of pivot tables in Excel below:

  • Data Summarization
  • Data Comparison
  • Data Analysis
  • Data Visualization

In practical life, you can compare the total sales of different products, show sales of different products as a percentage of grand total, combine duplicate data, count employee numbers for different departments, insert values to empty rows, and many more.


How to Create a Pivot Table in Excel: 7 Easy Steps

To create a Pivot Table in Excel, we are using a dataset that contains the cost and revenue of different products in different regions. We will generate a region-wise summary of the cost and revenue for the products using a Pivot Table in Excel.

1-dataset for creating pivot table

The following steps will guide you on how to create a Pivot Table in Excel.


Step-01: Inserting Pivot Table in Excel

First of all, you need to insert a Pivot Table in Excel.

  • To insert a Pivot Table, select the data range (B4:H21)Insert PivotTable.

2-insert pivot table using Insert tab

  • As a result, a dialog box named Create PivotTable will appear.
  • Select New Worksheet ➤ OK in the Create PivotTable dialog box.

3-select new worksheet for pivot table

  • A Pivot Table will be inserted into a new worksheet.

4-pivot table in a new sheet


What to Do to Insert Pivot Table in the Existing Sheet?

  • To insert a Pivot Table in the existing sheet, select the data range (B4:H21)Insert PivotTable.
  • Now, in the Create PivotTable box, select Existing Worksheet.
  • After that, select the cell where you want to place the Pivot Table.
  • Then, click OK.

5-create a pivot table in existing sheet

There’s another way of inserting a Pivot Table in the same worksheet.

  • To insert a Pivot Table in the same worksheet, select a cell outside the data range (B4:H21)Insert PivotTable.

6-alternative way of inserting a pivot table in existing sheet

  • In the Create PivotTable box, click on the marked icon.

7-select a table or range in create pivot table box

  • After that, select the table or range you want to use for creating the Pivot Table.
  • Then, click OK to see the Pivot Table in the existing sheet.

8-click OK to insert pivot table


Step-02: Adding Fields to Pivot Table

In this step, I will show you how to add fields to the Pivot Table. Here, I will work on the PivotTable Fields task pane to lay out the Pivot Table.

After inserting Pivot Table, the PivotTable Fields will appear on the right side of the screen. The PivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the field names from the upper part.

9-pivot table fields

You can lay out the Pivot Table in the following ways:

  • Firstly, select and drag the field names (at the top of the PivotTable Fields task pane) to one of the four boxes at the bottom of the task pane.
  • Here, I dragged the Cost and Revenue fields into the Values The Pivot Table will display the total of all the values in the Sum of Cost and Sum of Revenue columns respectively.
  • Secondly, select and drag the field names you want in your Row Labels into the Rows section.
  • Here, I selected the Region and Product Type fields in the Rows section.

10-drag field levels

  • The following figure gives me the desired Pivot Table.
  • From this Pivot Table, you can easily find out the Grand Total of the region-wise Cost and Revenue for different products.

11-pivot table to show region wise sum of cost and revenue

Tip: Typically the PivotTable Fields task pane is docked on the right side of your Excel window. You can drag its title bar to move it anywhere in your Excel window. If you click a cell outside the pivot table, the task pane will temporarily hide.

Step-03: Formatting Numbers in Pivot Table in Excel

In this step, I will explain how you can change the Number Format in the Pivot Table.

  • First of all, right-click on any cell of the Pivot Table where you want to change the Number Format.
  • Secondly, select Number Format.

12-changing number format in pivot table

  • After that, a dialog box named Format Cells will appear.
  • Select the Category you want. Here, I selected Currency.
  • Then, click OK.

13-applying currency format in pivot table

  • In the following picture, you can see that the Number Format has changed to Currency.

14-result after applying currency format in pivot table

Note: You have to apply the Number Format separately to each column.

Step-04: Refreshing Values in Pivot Table

Pivot Table does not automatically update when you edit or make a change in the main dataset. To update a Pivot Table, you need to refresh it.

We have used the dataset below to create the Pivot Table. In the dataset, we have changed the value of Cell E7 from 45 to 60.

15-edit dataset used for pivot table

  • To update the Pivot Table, right-click on any cell of the Pivot Table and select Refresh.

16-refresh pivot table

  • As a result, the data in the Pivot Table will be updated.

17-result after refreshing pivot table


Step-05: Sorting Data in Pivot Table

In this fifth step, I will show you how you can sort data in a Pivot Table.

  • Firstly, right-click on any cell from the column where you want to sort data.
  • Then, select Sort and choose the sorting order you want.
  • Here, I selected Sort Smallest to Largest.

18-sort pivot table

  • As a result, the data in the Sum of Cost column inside the Pivot Table will be sorted in the order you selected.

19-result after sorting pivot table


Step-06: Adding Percent of Total in Pivot Table

Here, I will explain how to add Percent of Grand Total in your Pivot Table.

  • Select any cell where you want to add a Percent of Grand Total.
  • Then, select Show Values As % of Grand Total.

20-show result in percentage

  • In the following picture, you can see that Percent of the Grand Total is added to the Pivot Table.

21-result after showing result in percentage

Note: You have to apply ‘Show Value As’ settings separately to each column.

Step-07: Moving Pivot Table to a New Location in Excel

In this step, I will show you how you can move the Pivot Table in Excel to a new location.

  • To do so, select any cell of the Pivot TablePivotTable Analyze Actions Move PivotTable.

22-move pivot table

  • A Move PivotTable dialog box will appear.
  • Select the Location where you want to move the Pivot Table and click on OK.
  • Here, I selected Cell D3 in the Existing Worksheet.

23-select new location for moving pivot table

  • After selecting OK, the Pivot Table will be moved to the desired location.

24-result after moving pivot table


How to Create a Pivot Table in Excel with Dates

In this section, I will explain how to create a Pivot Table in Excel with dates. The dataset contains the dates on which the products were sold. We will try to find the date-wise and month-wise cost of the products. Let’s see the steps.

  • To insert a pivot table with dates, select the data range (B4:H21)Insert PivotTable.

25-creating pivot table with dates

  • Drag Cost in the Values section and Date in the Rows section.

26-selecting fields for pivot table with dates

  • As a result, you will see the date-wise Sum of Cost in the Pivot Table.

27-pivot table with dates

  • To see the month-wise sum of cost, right-click on any cell inside the Pivot Table and select Group from the drop-down menu.

28-group pivot table data

  • After that, select Months in the Grouping box and click OK.

29-select months for grouping pivot table

  • As a result, you will see the month-wise sum of the cost of the products.

30-monthly sum of cost

Note: To ungroup, right-click on any cell inside the Pivot Table and select Ungroup from the drop-down menu.

How to Delete a Pivot Table in Excel

To delete a Pivot Table, you can follow the steps below.

  • Select any cell inside the Pivot Table.
  • After that, select PivotTable Analyze Select Entire PivotTable.
  • Next, click on the Delete key on the keyboard.

31-delete pivot table in excel

Note: If your Pivot Table is on a new worksheet, then you can delete the whole sheet.

Things to Remember

You must remember that if you group any date in your Pivot Table it will be grouped in all the other worksheets of that workbook.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, I explained how to create a Pivot Table in Excel. Here, I explained it in easy steps. I hope this article was clear to you.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

2 Comments
  1. Hi, what an excellent article series!

    Straight and to the point, very easy to follow, practice and understand.

    Many thanks!

    P.S. Why does Excel 2016 show “security warning – external data connection” after downloading the example Excel files?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo