In Excel, one way to quickly calculate your data by category is to use the Group command. For example, you have a list of boys and girls from a classroom and their scores are as below:
Please follow the steps below to use the group command to quickly calculate the average scores for boys and girls.
Step 1: Sort the data by the column you want to group (e.g., Gender) and click anywhere in the data range;
Step 2: Click the "Data" tab from the ribbon;
Step 3: Click "Subtotal" from the "Outline" group;
Step 4: In the "Subtotal" window, select "Gender" in the first box, and select a function from the drop-down list (e.g., Average) in the second box;
Step 5: Click "OK" at the bottom. You will see the average at the bottom after each category.
Step 6: Click "1" for the first level to display an average for all, and click "2" to have the average by gender, and click "3" to have the list of all children with the averages.