There are lots of types, a person wishes to summarize a list of names in Excel. Due to space limitations, we can not discuss all of that here right now. But we will try our best to show some ways to summarize names that can be human or business entities or any kind of names. We have discussed a number of straightforward methods in this post to summarize a list of names in Excel. So, be with us and adhere to the process.
Download Practice Workbook
You can download the practice workbook from the following download button.
5 Methods to Summarize a List of Names in Excel
With the data from a store, we attempt to summarize a list of names in Excel. Date-wise data on the sales value of sales representatives will be our focus. Given below is a sample table.
1. Use ‘Remove Duplicates’ Command
As distinct sales representatives of a store were present on different days, we can not get a clear picture of their individual sales performances. So, in this case, we are going to summarize their names to see the employees’ names in that particular store without any repetition.
- First of all, we will copy the ‘Sales Rep’ column and paste it below ‘Output’.
- To remove repeated names from the column we have to go to Remove Duplicates that will be found in Data Tools under the Data tab.
- Here ‘Remove Duplicate’ dialog box will pop up and just click OK.
- The number of duplicated values found and removed will show a pop-up box and there will be shown remaining unique values.
- The unique employees are below the Output column.
2. Summarize a List of Names with Number of Occurrences
All employee’s schedules are not evenly distributed in the given time period of the sample dataset. Now we want to know their attendance during that period. For that case, we will find unique employees and their repeated values in the column.
- We simply can find unique employees through UNIQUE function in Excel that is available in Microsoft 365.
- Now we will use the COUNTIF function to find their repeated presence in the Sales Rep column like in the image below.
3. Summarize Name List with Consolidation Tool
We can simply consolidate the ‘Sales Rep’ and ‘Sales’ columns to see a summarized list in Excel.
- By selecting Consolidate in Data Tools under the Data tab, we can easily summarize a list of names and their corresponding sales volume.
- Then a Consolidate pop-up will appear here. In this box, we will select both columns that we want to summarize and now we will mark the Left Column under Use Labels in, after that just click OK.
- So, the final summarized list will be the picture below.
4. Apply UNIQUE and SUMIFS Functions
We also can solve the problem by using two powerful functions in Excel. And they are UNIQUE and SUMIFS functions.
- First, we will use the UNIQUE function. This function will remove any repeated names from the Sales Rep column.
- And then SUMIFS will sum up all values against repeated names and show it with its unique name.
5. Utilize Excel Pivot Table
We can also use Pivot Table which is so powerful to modify any sort of data set.
- To pivot any dataset, we have to click the Insert tab and then Pivot Table.
- In the ‘Pivot Table from table or range’ pop up we will just click OK to open up a New Worksheet for pivot Table.
- If we markup both Sales Rep and Sales column and values as a sum up a form, then the below pivot table will appear that is good to go for analyzing data for a list of names in Excel.
These processes and steps will do basic jobs to summarize a list of names in Excel. Additionally, you may download the workbook and use it for your own practice. Please let me know in the comment section if you have any queries, concerns, or suggestions. Visit our blog ExcelDemy for more similar Excel-related issues.