How to Summarize a List of Names in Excel (5 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Summarize a List of Names in Excel: 5 Creative Methods

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.

Sample data to show how to summarize a list of names in Excel


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.

📌 Steps:

  • First of all, we will copy the ‘Sales Rep’ column and paste it below ‘Output’.

Copy and Paste of a Column

  • 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.

Remove Duplicates from a Column

  • Here ‘Remove Duplicate’ dialog box will pop up and just click OK.

Remove Duplicates Pop-up

  • The number of duplicated values found and removed will show a pop-up box and there will be shown remaining unique values.

Popup of Number of Duplicate Values and Unique Items

  • The unique employees are below the Output column.

Summarized Names

Read More: How to Summarize Text Data in Excel


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. In that case, we will find unique employees and their repeated values in the column.

📌 Steps: 

  • We simply can find unique employees through the UNIQUE function in Excel that is available in Microsoft 365.

Applying UNIQUE function to summarize list of names in Excel

  • Now we will use the the COUNTIF function to find their repeated presence in the Sales Rep column like in the image below.

Applying COUNTIF function to get the number of occurrences

Read More: How to Summarize Data by Multiple Columns in Excel


3. Summarize Name List with Consolidation Tool

We can simply consolidate the ‘Sales Rep’ and ‘Sales’ columns to see a summarized list in Excel.

📌 Steps:

  • By selecting Consolidate in Data Tools under the Data tab, we can easily summarize a list of names and their corresponding sales volume.

Summarize Names List with Consolidation Tool

  • 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.

Consolidate Pop-up

  • So, the final summarized list will be the picture below.

Read More: How to Make Summary in Excel From Different Sheets


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.

📌 Steps:

  • First, we will use the UNIQUE function. This function will remove any repeated names from the Sales Rep column.

Summarizing Names using UNIQUE function

  • And then SUMIFS will sum up all values against repeated names and show it with its unique name.

Apply SUMIFS function and summarize a list of names in Excel

Read More: How to Create Summary Table in Excel


5. Utilize Excel Pivot Table

We can also use Pivot Table which is so powerful to modify any sort of data set.

📌 Steps:

  • To pivot any dataset, we have to click the Insert tab and then Pivot Table.  

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.

Pivot Table from table or range

  • If we markup both the Sales Rep and Sales columns 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.

Utilizing pivot table to Summarize list of names in Excel and their sales

Read More: How to Summarize Data Without Pivot Table in Excel


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

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.


Related Articles


<< Go Back to Summarize Data In Excel | Data Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo