Excel - Use pivot table to group data by category

By xngo on February 20, 2020

In MS Excel, you can use the powerful Pivot table to get aggregate information from your data.

Assume that you have the following information. The table below shows the price of each fruit sold per day.

Date Fruit Price
2020-01-11 Banana 2.99
2020-01-11 Orange 0.99
2020-01-11 Banana 2.99
2020-02-22 Strawberries 1.99
2020-02-22 Mango 3.66
2020-02-22 Apple 4.56

Total value sold per fruit

If you want to know how the total value sold per fruit, then you have to group by fruit and sum the prices.

  1. Select Fruit and Price columns.
  2. Insert PivotTable.
    MS Excel - Insert - PivotTable
  3. Check the Fruit and Price fields. Ensure that the Values field is set to Sum of Price.
    MS Excel - Insert - PivotTable - Sum of Price

Total fruit sold per day

If you want to know how many fruits were sold per day, then you have to group by date and count the instance of fruit.

  1. Select Date and Fruit columns.
  2. Insert PivotTable.
  3. Check the Date and Fruit fields. Ensure that Date is under Rows and Count of Fruit is under Values.
    MS Excel - Insert - PivotTable - Count of Fruit

Other functions

MS Excel offers other aggregate functions too, such as average, maximum, minimum and etc.

MS Excel - Insert - PivotTable - Value Field Settings MS Excel - Insert - PivotTable - Value Field Settings - Functions

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.