Pivot Table is a tool used to summarize and reorganize selected rows and columns of data in a spreadsheet or database table to obtain the desired output without changing the spreadsheet.
To make it easier to understand the use of pivot tables, I downloaded a data set from Excel Easy which I will use to demonstrate in this tutorial. The data set consists of 6 column fields (Order ID, Product, Category, Amount, Date and Country) and more than 200 rows.
From the data set we are going to create pivot table to summarize the total amount of every product for each country.
Insert a Pivot Table
To insert a pivot table follow this step.
Step 1. Select any cell inside the data set, then go to “Insert” tab and click “PivotTable” on the Tables group then select “PivotTable” in the dropdown.
Step 2. Create PivotTable dialog box will appear which also selected all the data around the cell you selected. Click ok to continue.
Step 3. Now the PivotTable field list will appear, here you will control how your, PivotTable will appear by dragging the field to your desired location. In this example, we want to summarize the total amount of every product for each country, so we will drag the Product field in the Column Labels (1), Country to Row Labels (2) and the Amount to the Values (3). We will also add the category field in the report filter to easily choose if we want to show only fruits or vegetables on the table.
Step 4. The output for our PivotTable that we created will look like on the screenshot below, it’s clean and summarized.
Filter
You can also filter the field in the PivotTable. For example, if you want only to show banana product in each country, just select banana in the product filter.
And this will be the output.
Sort
To show which country that has the highest amount of banana, we will use the sort pivot table.
Step 1. Select any cell in the Banana column to activate the pivot table tools. On the Data tab, sort and filter group, click the Sort Largest to Smallest button (ZA)
Step 2. And now you can see that the United States has highest amount.