You might find yourself in a situation where you need to count the unique values in an MS Excel spreadsheet. This can be useful, especially when you have tons of data and there’s no way you can count them manually.
6 Methods to count unique values in MS Excel:
- Using the UNIQUE function
- Using the COUNTA function
- Using a pivot table
- Using SUM and COUNTIF function
- Using SUMPRODUCT and COUNTIF function
- Using the Remove Duplicates option
Method #1: Using the UNIQUE Function
In this method, you take the unique values and place them in a separate column. You then count the values in that column. This method essentially removes repetition and gives you distinct values.
Step #1: Open your Excel workbook
If you have an existing workbook in which you need to count the unique values, open that.
Otherwise, open a new Excel workbook and populate it with the data required.
Step #2: Select a cell to enter the function
Your unique values will be transferred to another column. So, select a cell in a new column by clicking on it.
This is where you’ll enter the function and your unique values will appear there. In our example, it’s C1.
You can check for the cell’s name in the Name Box, in the top left corner above the spreadsheet.
Step #3: Type in the UNIQUE function
Type in the below into the selected cell.
The syntax is =UNIQUE(range_of_cells).
Type =UNIQUE() and place the cursor in between the two brackets.
Step #4: Select the range of cells
Select the cells with the data to be checked for the unique values.
You can do this by dragging your cursor across the range of cells. As you do this, the range of cells will be populated into the UNIQUE function.
Step #5: Press Enter
Once you are done selecting the range, press Enter.
You can see the function in action now.
Step #6: Select the column with the unique values
Now, select the column with the unique cells by clicking on the alphabetic Column Name.
Make sure there’s nothing else in that column.
In this instance, it’s the C column. So, we select it by clicking on the letter C.
Step #7: Check the status bar for the count
You can find the status bar at the bottom corner of your workbook. The status bar will display the number of entries in the selected column, which gives you the number of unique values.
Method #2: Using the COUNTA Function
This is similar to the previous method. However, instead of checking the status bar for the count, you use the COUNTA function. This method removes any duplicates and gives you the number of distinct values.
Step #1: Open your Excel workbook
If you have an existing workbook in which you need to count the unique values, open that.
Otherwise, open a new Excel workbook and populate it with the data required.
Step #2: Select a cell to enter the function
Select a cell in a new column by clicking on it.
You can check for the cell’s name in the Name Box, in the top left corner above the spreadsheet.
In our example, it’s E1.
Step #3: Type in the function
You’ll now enter the UNIQUE function inside the COUNTA function. The UNIQUE function gets all the unique values and the COUNTA function will return the count. Instead of COUNTA, you can use COUNT, but it only counts the numerical values. COUNTA checks for both text and numerical values.
The syntax is =COUNTA(UNIQUE(range_of_cells)).
Type =COUNTA(UNIQUE()) and place the cursor in between the innermost brackets.
Step #4: Select the range of cells
Now, select the range of cells with the values to be checked.
You can select the range of cells by clicking, holding, and dragging your cursor across them.
Once you select the range of cells, check the UNIQUE function. It will be populated with the range.
Step #5: Press Enter
Once you’re done, press Enter on your keyboard to see the function in action.
You’ll see the count of your unique values.
Method #3: Using a Pivot Table
This method removes any value that’s been repeated and returns values that only appear once.
Step #1: Open your Excel workbook
If you have an existing workbook in which you need to count the unique values, open that.
Otherwise, open a new Excel workbook and populate it with the data required.
Step #2: Insert the PivotTable
Select a cell in a new column by clicking on it.
You can check for the cell’s name in the Name Box, in the top left corner above the spreadsheet.
In our example, it’s C1.
This is where the pivot table will show up.
Now, click on Insert in the main menu bar to change the ribbon.
Select the PivotTable icon.
Step #3: Select the range of cells
The Create PivotTable dialog box will open up.
Place your cursor in the Table/Range field, where after you will see your worksheet again.
Select the range of cells to create the table by dragging your cursor across the range of cells.
Once you press Enter on your keyboard, the Create PivotTable dialog box will return, and show the selected range in the Table/Range field.
No need to change the location, so keep Existing Worksheet selected.
Click on the OK button.
On the extreme right of your workbook, check the checkbox which corresponds to the Header you used in your dataset.
In our example, it is ‘Fruits’.
You can now see the table in your workbook.
Step #4: Add the value to the range of cells
Go back to the left panel.
Click on the drop-down icon next to the Header.
Select the Add to Values option.
This will add the count of each distinct value in the cell next to the distinct value.
See our example pivot table now.
A new column has been added with the heading ‘Count of Fruits’ and it shows the number of each unique value.
Step #5: Filter to get the values that only appear once
Click on the dropdown icon next to the Header in the pivot table.
Select Value Filters, which will open another submenu.
Select Equals.
You’ll now see a Custom Filter dialog box.
Enter 1 in the field and click on the OK button.
Now, your pivot table will only show unique values or values that appeared only once in the data.
Method #4: Using SUM and COUNTIF Function
This method checks for distinct values and eliminates duplicates.
Step #1: Select a cell to show the count
Select a cell in a new column by clicking on it.
You can check for the cell’s name in the Name Box, in the top left corner above the spreadsheet.
In our example, it’s C1.
This is where the count of distinct values will be displayed.
Step #2: Type in the function
The formula here is =SUM(1/COUNTIF(A2:A10, A2:A10)).
Where A2:A10 will be the range of cells of your data.
Step #3: Press Enter
Press Enter to see the count of distinct values after the elimination of duplicates.
Method #5: Using SUMPRODUCT and COUNTIF Function
This method is similar to the previous one. It eliminates duplicates and checks for distinct values.
Step #1: Select a cell to display the count
Select a cell in a new column by clicking on it.
You can check for the cell’s name in the Name Box, in the top left corner above the spreadsheet.
In our example, it’s E1.
This is where the count of distinct values will be displayed.
Step #2: Type in the function
The formula is =SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10)).
For A2:A10. Replace the range of cells depending on your data.
Step #3: Press Enter
Once you’re done, press Enter on your keyboard.
This would display the count.
Method #6: Using the Remove Duplicates Option
This method removes the duplicates in place. So, if you want data with duplicates, make a copy of your data before using this method.
Step #1: Open your Excel workbook
If you have an existing workbook in which you need to count the unique values, open that.
Otherwise, open a new Excel workbook and populate it with the data required.
Step #2: Select the range of cells
Select the range of cells by clicking, holding, and dragging your cursor across them.
Step #3: Go to the Data tab and select Remove Duplicates.
Click on the Data tab in the top menu bar to change the ribbon.
Select the Remove Duplicates icon.
You’ll now see a Remove Duplicates dialog box.
If your column has a header, check the header checkbox.
Click the OK button once you’re done.
Step #4: Click OK on the prompt
You’ll now see a warning prompt. The prompt will tell you the number of unique values and the number of duplicates. Click on the OK button.
Now, your column will be free of duplicates.
Conclusion
Counting unique values doesn’t have to be complex. MS Excel allows you to do that in several ways. You can use any of the six methods mentioned above to count the unique values in your Excel workbook in seconds.