Let’s concise them into a Pivot Table here.
- Go to the Insert tab > Pivot Tables.
You’ll see the Insert PivotTables dialog box on your screen as follows:
- Create a reference to the cells containing the relevant data.
We will navigate to the sheet ‘Data’ in our workbook and select the cells that contain data.
We have converted our data into an Excel table so Excel automatically recognizes it as Table1.
Do not forget to include the headers in the selection.
- Choose the option for New Worksheet or Existing Worksheet.
We will choose New Worksheet to have the Pivot table created on a new sheet.
- Click Okay.
There comes the Pivot Table pane to the right of your sheet 💭
It has two parts. The first part (as above) has all the fields (columns) of your source data listed.
And here’s the second part.
This part includes four boxes where you can specify how each field is to be shown in the Pivot Table. You can choose to have any field organized as a row or as a column, as a filter, or as a value 🎯
- Drag the filed Products from the list of fields to the box for Rows.
Excel organized all the products as rows.
- Drag the field Amount from the list of fields to the box for Values.
And this is what happens:
Excel adds a column for Values. The column Amount in our source data contained the sales amount of each transaction.
By adding it as values, Excel has summarized the sales of each product and listed them against each of the products 💰
But what if you don’t need the sum of sales of each product, but their count?
- Right-click on any number from the column Sum of Amounts.
- From the context menu, select Summarize Values By.
- Click on any operation that you want to be performed. For example, we want the Count of sales so we are selecting Count 🔢
The results change as follows:
The column Sum of Amounts becomes Count of Amounts. For each product, we now have the Count of sales transactions.
No, it doesn’t stop here.
- Drag the field for Customer Type to the box for columns.
And this is what happens:
Excel adds columns for each Customer Type. And the sales of each product are now split into customer types 📊
Let’s add another field to see how you can further drill down into details using a Pivot Table.
- Drag and drop the field for Months to the box for Rows.
Excel adds a breakup of months under each product.
So now you can see a summary of sales of each product, for each month and by each customer type. Too convenient and clean ✔
You can make so many more variations to your Pivot Table by pivoting between rows and columns. No matter how vast your data is, Pivot Tables know how to knit it all together.