When you add large amounts of data to your Google Sheet, you might have duplicates or repeated values. You might want to count the unique values, pick them out, or even find the occurrences of each unique value.
4 methods to count and display unique values in Google Sheets
- Using COUNTUNIQUE to count the unique values
- Using UNIQUE to display the unique values
- Using Remove duplicates to display the unique values
- Using UNIQUE and COUNTIF to display the occurrences of each unique value
Method 1: Using COUNTUNIQUE to count the unique values
This is a straightforward method to just count the number of unique values in your data.
Step 1: Identify the range of cells you want to count.
To identify the range of cells, make a note of the column name and number of rows. For instance, here the range of cells is from column A, from row 1 to row 10. You have to note down this range to enter into the formula.
Step 2: Enter the formula.
Identify the cell you want to display the count of unique values and enter the formula. The formula to count unique values is =COUNTUNIQUE(A1:A10). There are four parameters in the formula:
- A before the colon represents the start column
- 1 represents the start row
- A after the colon represents the end column
- 10 represents the end row
You can change them according to your range of cells.
Step 3: Press Enter.
After you’ve entered the formula, press Enter to see the formula in action. Here, there are five unique values; Apple, Orange, Banana, Pineapple, Papaya. So, cell C3 should display 5.
Voila, you’ve counted the unique values for your desired range of cells.
Method 2: Using UNIQUE to display the unique values
What if you want to display the unique values instead of just counting them? All you have to do is remove COUNT from the previous formula.
Step 1: Select your desired range of cells.
Just like in the previous method, identify the range of cells you want to apply the formula to. Here, the range of cells would be from A1 to A10.
Step 2: Add the formula and press Enter.
The formula to display the unique values is =UNIQUE(A1:A10). The values inside the brackets represent the start column, start row, end column, end row.
After you’ve added the formula, press Enter. Now, you can see all the unique values in the given range of cells.
Method 3: Using Remove duplicates to display the unique values
This method is ideal if you want to remove the duplicates in place. You can use this method if you don’t want to display them in a separate column.
Step 1: Highlight your range of cells.
Highlight the first cell and drag your cursor to highlight a range of cells. You can see the selected range in the Name box.
Step 2: Click Remove duplicates.
Click on Data on the toolbar. Then, tap on Remove duplicates.
Clicking Remove duplicates will open a dialog box for you to check the range of cells. If it is correct, you can click on the green Remove duplicates button.
You can see another dialog box that displays the number of rows removed and the number of rows intact right now.
Click the green OK button or the X at the top right to close the dialog box. Now, you can see all your duplicates removed.
Method 4: Using UNIQUE and COUNTIF to display the occurrences of each unique value
You can use this method to count the occurrences of each unique value.
Step 1: Identify the range of cells and type the formula.
Note down the range of cells you want to apply the formula. Here, it is A1 to A10. This is a combination of two formulas:
- UNIQUE to display the unique values
- ARRAYFORMULA to display the occurrences of each unique value
You’re combining the two formulas using curly braces.
The formula is ={UNIQUE(A1:A10), ARRAYFORMULA(COUNTIF(A1:A10,UNIQUE(A1:A10)))}
Pick a cell and type in the formula. You can change the range of cells according to your data.
Step 2: Press Enter.
Press Enter after you’ve typed the formula correctly. You can now see the formula in action. It will list down all the unique values and the occurrences of each value.
Wrapping up
You no more have to struggle to remove duplicates or count unique values. You can easily eliminate redundancy using the above methods. You can now count the unique values, pull them out, and remove duplicates. You can also count the occurrences of unique values.