In Excel, a pivot table can help you summarize your data based on different categories. You can gain a “big picture” of your data, and you can also edit your data in a more efficient way. For example, you want to analyze the sales data containing information of salesman, products, quantity, price, item, etc. Use pivot table will help you to quickly find out how different salesmen are performing.
Before creating a pivot table, we need to make sure our dataset is well organized.
- The first row should contain column headings since Excel will use it as the field names in the pivot table.
- Do not leave blank rows or columns in the middle of your dataset, otherwise Excel will consider the empty column/row as the end of the dataset.
Once you have a well-organized dataset, you are ready to make a pivot table.
Here we have the data of products and their price that different salesmen sell.
The dataset now is grouped by salesperson. If we want to get more information about sales by products or by revenue, pivot table would be a useful tool for you.
For example, we could make a pivot table that look like this.
This pivot table is a summary of quantity sold by salesmen (by row) and by products (by column). With this pivot table, we could get a clear idea of how many products of each category that each salesman sells.
The first step to make a pivot table is to select the Insert ribbon and click on “Pivot Table”. On the drop-down menu, select “PivotTable”.
Then the “Create PivotTable” window should pop up and you could select data to make the pivot table. Under “Select a table or range”, select the data we want to use for the pivot table. You could also choose the location you want to put the pivot table. If you want the pivot table to be placed in another sheet, click on the icon on the right of the location area.
The default location is the sheet1, which is the location of the sheet you are currently working in. You can delete the default location and choose your own location to put the pivot table. For example, now we choose to put the table in cell C5 in sheet2. After you select the location, return to the main dialogue box by clicking on the icon on the right again.
Once we have selected data and location to make the pivot table, we can click ok. We will get a blank pivot table.
On the right of the excel window we could see a variety of options we could choose for the pivot table. We start from the top with the pivot table fields. We could choose different fields to add to our pivot table. These fields are all the column titles from the data table we choose. (That is why we want to have clear and informative column titles so it is easier for us to make the pivot table) For example, we choose Salesperson as the row labels, product as the column labels. In order to do this we can just drag the fields to the areas below. Now if we look at the pivot table, we have row and column labels displayed.
The next step would be to choose a value that we want to summarize. The possible values that we could summarize would be unit cost, quantity and sales. In this case, we want to summarize information about quantity sold. Thus we drag “Quantity” down to the Values area and we will get the pivot table that we want.
This table shows the quantity in different products sold by each salesperson. We could also choose to display the percentage instead of the counts. In order to do it, click on the Sum of Quantity in the Values area. In the drop-down menu, select Value Field Settings.
In the pop-up window, click the Show Values As. Then from the drop down menu, choose % of Row Total. (You can also choose other options such as % of Column Total according to which information you want to show in the table.)
Click OK and you will get the pivot table showing percentage of each type of product that a salesperson sold.
You could include more information in the pivot table by adding subcategories. For example, if we want to separate male and female salespersons. We first need to add a column “Gender”.
We use the same method mentioned above to create a blank pivot table that includes the gender data. We first drag Gender into the Row Labels and then Salesperson since the subgroup has to come first. We keep the Column Labels and Values same as in the previous example. This is the pivot table we get with gender as a subgroup.