How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)

Pivot Tables in Excel are a great way to summarize, analyze, explore, and present summary data. A pivot table allows you to extract the significance from a large, detailed data set. In this tutorial, we will learn how to insert a pivot table in Excel in 7 easy steps to quickly summarize the information of a large worksheet. Even if you are new to the world of Excel, it will be very easy for you to insert a pivot table in Excel after reading this tutorial.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


7 Easy Steps to Insert Pivot Table in Excel

Let’s assume we have an Excel large worksheet that contains the information about various fruits and vegetables that a country has imported to three different countries in Europe. We have the Order ID, Product Name, Product Category, Exported Amount, Importer Country, and the Export Date. The image below shows the Excel worksheet we are going to use to create a pivot table.

Insert Pivot Table in Excel


Step 1: Insert a Pivot Table in Excel Worksheet

  • First, select all the cells with the data including the column header.

Select all the Data Rows in the Worksheet

  • Then, we will go to Insert Ribbon and click on the PivotTable.

Insert Pivot Table

  • Now, select From Table/Range from the drop-down list.

Select From Table/Range

  • A dialog box will appear like the following image. Excel will automatically select the data for you. For the new pivot table, the default location will be a New Worksheet.
  • Click OK.

Insert Pivot Table in Excel

Read more: How to Insert Table in Excel


Step 2: Drag the Fields in the PivotTable Fields 

  • Now a new sheet will appear. It will have a box on the right side of the sheet titled PivotTable Fields. You will find it on the left side of the sheet if you are working with Excel 2007/2010.
  • Drag and drop fields from your data like the image below.
    • Product field to the Rows area.
    • Amount field to the Values area.
    • Country field to the Filters area.

Drag the Fields in the PivotTable Fields

  • The image below shows the fields we have dragged in the first step of creating the pivot table.

Drag the Fields in the PivotTable Fields

  • And the below image shows the pivot table that Excel creates for us.

Excel Creates a Pivot Table

  • Excel assumes you want to sum the values of the Data field (the field in the Values box). So, it will sum the values in the Amount column as we have put it in the Values The pivot table will sum up the amount of each product individually as we can see in the image above.

Read more: How to Insert or Delete Rows and Columns from Excel Table


Step 3: Change the Operation to Perform 

  • The default operation that Excel performs in the pivot table is SUM. But you can perform a different calculation such as Count or Average. To do so, click the Values field and select Value Field Settings.

Change the Operation to Perform

  • Select the operation that you want to perform and click OK. We have selected Count.

Select the Operation to Perform in Pivot Table

  • Excel will now count the appearance of every product in our data range. We have the information about each product being exported to three different countries (Canada, France, Spain). So, each product appears in the data range 3 times. As we have 14 different products, the total count or Grand Total will be 42 (14 X 3).

Pivot Table Based on Count


Step 4: Sort the Pivot Table in Excel

  • We can see from our pivot table that the exporter country exports Blueberry the most. But the pivot table arranges the product in alphabetically ascending order. But we can sort it in ascending order based on the total amount being exported. To do that, we will right-click any cell inside the Sum of Amount
  • Select Sort from the window that will appear. You will see different types of sorting.
  • Select Sort Largest to Smallest.

Sort the Pivot Table

  • Following that, you will see that products have been rearranged in ascending order based on the total exported amount with Blueberry on the top of the list.

Insert Pivot Table Excel


Similar Readings


Step 5: Filter the Pivot Table in Excel

  • We can also use the Filter to gain insight from our data. Remember that, we have used the Country column to filter our data for the pivot table. Now, if we want to find out which product the country has exported most to Canada, we can find it easily using the Filter. Click the small down-arrow on the right side of the Country. A window will appear. Select Canada from the list.
  • Click OK.

Filter the Pivot Table

  • Now, you will find out the amount of each product that has been exported to Canada. You will also see that Canada is shown just beside the Country

Amount of Each Product Exported to Canada


Step 6: Two-dimensional Pivot Table in Excel

  • First, select a field and drag it to the Rows area and also the Columns area. In this way, you can create a two-dimensional pivot table. First, insert a pivot table.
  • Next, to get the total amount exported to each country, of each product, Drag and drop fields from your data like the image below.
    • Country field to the Rows
    • Product field to the Columns
    • Amount field to the Values
    • Category field to the Filters

Two-dimensional Pivot Table

  • Now you will get a two-dimensional pivot table.

2D Pivot Table


Step 7: Create a Pivot Chart in Excel

  • You can create and insert a pivot chart to visually summarize the information to get insight into the information more easily. To insert a pivot chart, click on any cell on the pivot table.
  • Now, click on the PivotTable Analyze

Create a Pivot Chart

  • In the Tools group, click PivotChart.

Click on PivortChart

  • Now, select a chart type from the window that will appear. We have selected the default Column
  • Click OK.

Create a Pivot Chart

  • Now, you will get the pivot chart visually representing the information more conveniently.

Pivot Chat Visualizing the Information


Things to Remember

  • The pivot table will always sort the information in alphabetically ascending order by default. You have to use the sort option to reorder the information as per your need.
  • Select New Worksheet when you are creating a pivot table. If you select Existing Worksheet, a pivot table will be created in your existing sheet that contains the data. There is a substantial risk of data being distorted if we create the pivot table in our existing worksheet.

Conclusion

In this article, we have learned to create and insert a pivot table and a pivot chart in Excel. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Further Readings

Tags:

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo