Table of Contents
Organizing data in a spreadsheet can be a tedious task. Whether you are entering in data from scratch or trying to sort through a lot of information, it can be difficult to make sense of it all. One of the most useful tools in Excel is the Group By function. This allows you to simplify large data sets by grouping them together by a particular value, such as name. In this guide, we’ll provide an overview of how to group by name in Excel, with examples of how this can be used to make data analysis easier.
Often you may want to group the values of some dataset in Excel by name.
For example, suppose we have the following dataset and would like to sum the total sales by name of the employee:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Find the Unique Names
Next, we need to use the =UNIQUE() function to produce a list of unique names in a certain range.
In our example, we’ll type the following formula in cell D2:
=UNIQUE(A2:A12)
This will produce a list of unique names:
Step 3: Calculate the Sum by Name
Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the sales by each employee.
We will type the following formula into cell E2:
=SUMIF($A$2:$A$12, D2, $B$2:$B$12)
From the output we can see:
- The sum of sales made by Andy is 23
- The sum of sales made by Bob is 12
- The sum of sales made by Chad is 28
- The sum of sales made by Doug is 17
Note that in this example we used the SUMIF function to calculate the sum by name.
However, you could instead use the COUNTIF, AVERAGEIF or some other function to instead calculate the count, average or some other aggregate metric grouped by name.