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

Get FREE Advanced Excel Exercises with Solutions!

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.


Insert Pivot Table in Excel: 7 Easy Steps

Let’s assume we have an Excel large worksheet that contains 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 the Insert tab 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


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.

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 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 at the top of the list.

Insert Pivot Table Excel


Step 5: Filter the Pivot Table in Excel

  • We can also use Filters 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.

Download Practice Workbook

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


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 leave a comment below. Have a great day!!!


Further Readings


<< Go Back to How to Create Pivot Table in Excel | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo