ASM Arman
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.
Step 1: Insert a Pivot Table in Excel Worksheet
- First, select all the cells with the data including the column header.
- Then, we will go to Insert Ribbon and click on the PivotTable.
- Now, select From Table/Range from the drop-down list.
- 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.
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.
- The image below shows the fields we have dragged in the first step of creating the pivot table.
- And the below image shows the pivot table that Excel creates for us.
- 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.
- Select the operation that you want to perform and click OK. We have selected Count.
- 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).
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.
- 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.
Similar Readings
- How to Edit a Pivot Table in Excel (5 Methods)
- Update Pivot Table Range (5 Suitable Methods)
- Create Table in Excel Using Shortcut (8 Methods)
- How to Make a Table in Excel (With Customization)
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.
- 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
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
- Now you will get a two-dimensional 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
- In the Tools group, click PivotChart.
- Now, select a chart type from the window that will appear. We have selected the default Column
- Click OK.
- Now, you will get the pivot chart visually representing the information more conveniently.
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!!!
