A Pivot Table is an efficient feature used in Excel to analyze a large amount of data. Instead of using many different formulas, you can use a Pivot Table to calculate it much quicker. You will learn how to create a simple Pivot Table in Excel 2016 or later. First, you need to have entered data in your Excel sheet before you insert a Pivot Table in Excel. Once you create a Pivot Table, you can quickly convert many rows and columns into a structured report. It is a quick way to visualize the answers to the questions you have about your data.
Table of contents
Insert data in Excel
First, you need to have inserted a lot of data into your Excel sheet. Our example data is about a firm which bought products (pen, ruler, eraser) in different colors from four countries. The date, units, and price are also mentioned.
Please make sure that you do not have any empty cells or rows in your data set. Our data set contains 6 columns and 30 rows. In a Pivot Table, the column headings are called fields, and the rows are called records.
- Click on any cell in your data set. You don’t have to select the entire table.
- Then go to the Insert tab.
- Click on the icon PivotTable in the ribbon.
Insert Pivot Table in Excel sheet
A new box called Create PivotTable appears on your sheet.
- First, check if your data set is selected correctly. It will automatically select your data set if you clicked on a cell inside the table.
- Secondly, choose where you want to paste your Pivot Table. It will open in a new worksheet by default, but you can always choose to open it on the existing worksheet.
- If everything is correct, you don’t need to change anything and click the OK button. Then, it automatically directs you to the new worksheet with a blank Pivot Table.
This is how quickly you can insert a blank Pivot Table in Excel. The new worksheet is named sheet 2 and comes before sheet 1, where you have your data written. On the right side, the PivotTable Fields pane opens up. Here, you can create your simple Pivot Table. At the top of the sheet, you can see the PivotTable Tools.
How to create a Pivot Table in Excel
To create a Pivot Table in Excel 2016 or later, you only have to drag and drop fields. Start by going to the PivotTable Fields pane, where you can see the fields and four different areas called; Filters, Columns, Rows, and Values.
Please select one of your fields to get a light green background and move it to the preferred area below. Once you have placed the field into an area, you will see a tick in front of it.
- Product field to the Rows area.
- Color field to the Rows area as well.
- Country field to the Columns area.
- Units to the Values area.
Keep in mind that you can’t drag a field to any area you want, but that is not a problem. When you drag a field to the wrong place, it will show you a red circle with a line.
Now you have created a simple Pivot Table that shows you a summarized data. In our example, you can see how many products the firm has imported from each country. The firm has imported the most products from the UK. You can also see the total units the firm has bought, which is 13800. It shows that the firm has mostly bought rulers.
The products are divided into colors to show more detailed information. You can close it by clicking on the small icon in front of the product’s name, as shown in the below picture.
Once you click on a cell outside your Pivot Table, the PivotTable Fields pane on the right side and the PivotTable Tools at the top disappear. Click on a cell inside your table to make it visible.
Detailed Pivot Table explained
In the Pivot Table, you can see that the firm has bought 4500 pens in total. If you want to understand and get more details about this number, then you can double-click on the cell.
Now it will open a new sheet before the one you already have. It is called sheet 3, and it automatically makes a new table to show how it got the details. If you select the number of units, you can see the sum in the status bar.
The Pivot Table is much more flexible than a standard table. It is because you can select a field and move it to another area, quickly changing the entire Pivot Table. If you don’t want a field, you can also select it and drop it back.
Let’s say that you want to know how many times you bought from the same country. Now you don’t have to use the COUNTIF formula in Excel. In sheet 2, you have to select the Country fields > Drop it in the Rows area > Now select Country again and drop it in the Values area. It creates a Pivot Table that shows you bought products 11 times from the UK and only 4 times from Turkey.
Similarly, you can also do this with the count of colors.
You have learned how to insert a Pivot Table in Excel to get a structured report of a large amount of data. Pivot Tables are more flexible and give you the summarized data. You can get more detailed information if you double-click on a cell in your Pivot Table. This way, you can avoid using complex formulas in Excel by creating a simple Pivot Table.