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

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.

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 (with Easy Steps)


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.

📌 Steps: 

  • We simply can find unique employees through 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 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 in Excel (8 Easy Methods)


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 Summarize Data by Multiple Columns 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 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.

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

Read More: How to Summarize Data in Excel Using Pivot Table (2 Examples)


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. Visit our blog ExcelDemy for more similar Excel-related issues.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo