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 Data ribbon and click on “Pivot Table”. On the drop-down menu, select “Create Manual PivotTable”.
Then the “Create PivotTable” window should pop up and you could select data to make the pivot table. Under “Use a table or a range in this workbook”, select the data we want to use for the pivot table. You could also choose the location you want to put the pivot table. You can either choose to place the pivot table in a new worksheet or in the existing worksheet. Here we put the pivot table in the blank area of the existing worksheet.
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 "i" icon next to the Sum of Quantity in the Values area. In the pop-up window, select Options.
Under the "Show Data As", click the drop-up icon next to "Normal". Then from the drop down menu, choose % of row. (You can also choose other options such as % of column 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.