Как сделать distinct в excel
In this tutorial, you will learn how to count unique values in Excel with formulas, and how to get an automatic count of distinct values in a pivot table. We will also discuss a number of formula examples for counting unique names, texts, numbers, cased-sensitive unique values, and more.
When working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. And sometimes, you may want to count only the distinct (different) values.
If you have been visiting this blog on a regular basic, you already know the Excel formula to count duplicates. And today, we are going to explore different ways to count unique values in Excel. But for the sake of clarity, let's define the terms first.
- Unique values - these are the values that appear in the list only once.
- Distinct values - these are all different values in the list, i.e. unique values plus 1 st occurrences of duplicate values.
The following screenshot demonstrates the difference:
And now, let's see how you can count unique and distinct values in Excel using formulas and PivotTable features.
How to count unique values in Excel
Here's a common task that all Excel users have to perform once in a while. You have a list of data and you need to find out the number of unique values in that list. How do you do that? Easier than you may think :) Below you will find a few formulas to count unique values of different types.
Count unique values in a column
Supposing you have a column of names in your Excel worksheet, and you need to count unique names in that column. The solution is to use the SUM function in combination with IF and COUNTIF:
Note. This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it. Once you do this, Excel will automatically enclose the formula in like in the screenshot below. In no case should you type the curly braces manually, that won't work.
In this example, we are counting unique names in range A2:A10, so our formula takes the following shape:
Further on in this tutorial, we are going to discuss a handful of other formulas to count unique values of different types. And because all those formulas are variations of the basic Excel unique values formula, it makes sense to break down the above formula, so you can fully understand how it works and tweak it for your data. If someone is not interested in technicalities, you can skip right to the next formula example.
How the Excel count unique values formula works
As you see, 3 different functions are used in our unique values formula - SUM, IF and COUNTIF. Looking from the inside out, here's what each function does:
-
The COUNTIF function counts how many times each individual value appears in the specified range.
Tip. To see what a specific part of your Excel unique values formula evaluates to, select that part in the formula bar and press the F9 key.
Count unique text values in Excel
If your Excel list contains both numerical and text values, and you want to count only unique text values, add the ISTEXT function to the array formula discussed above:
As you know, the Excel ISTEXT function returns TRUE if an evaluated value is text, FALSE otherwise. Since the asterisk (*) works as the AND operator in array formulas, the IF function returns 1 only if a value is both text and unique, 0 otherwise. And after the SUM function adds up all 1's, you will get a count of unique text values in the specified range.
Don't forget to press Ctrl + Shift + Enter to correctly enter the array formula, and you will get a result similar to this:
As you can see in the screenshot above, the formula returns the total number of unique text values, excluding blank cells, numbers, logical values of TRUE and FALSE, and errors.
Count unique numeric values in Excel
To count unique numbers in a list of data, utilize an array formula like we've just used for counting unique text values, with the only difference that you embed ISNUMBER instead of ISTEXT in your unique values formula:
Count case-sensitive unique values in Excel
If your table contains case-sensitive data, the easiest way to count unique values would be creating a helper column with the following array formula to identify duplicate and unique items:
And then, use a simple COUNTIF function to count unique values:
Count distinct values in Excel (unique and 1 st duplicate occurrences)
To get a count of distinct values in a list, use the following formula:
Remember, it's an array formula, and therefore you should press the Ctrl + Shift + Enter shortcut instead of the usual Enter keystroke.
Alternatively, you can use the SUMPRODUCT function and complete the formula in the usual way by pressing the Enter key:
For example, to count the distinct values in range A2:A10, you can go with either:
How the Excel distinct formula works
As you already know, we use the COUNTIF function to find out how many times each individual value appears in the specified range. In the above example, the result of the COUNTIF function is the following array: .
After that, a number of division operations are performed, where each value of the array is used as a divisor with 1 as the dividend. This turns all duplicates values into fractional numbers corresponding to the number of duplicate occurrences. For example, if a value appears 2 times in the list, it generates 2 items in the array with a value of 0.5 (1/2=0.5). And if a value appears 3 times, it produces 3 items in the array with a value of 0.3(3). In our example, the result of 1/COUNTIF(A2:A10,A2:A10)) is the array .
Doesn't make much sense so far? That's because we haven't applied the SUM / SUMPRODUCT function yet. When one of these functions adds up the values in the array, the sum of all fractional numbers for each individual item always yields 1, no matter how many occurrences of that item exist in the list. And because all unique values appear in the array as 1's (1/1=1), the final result returned by the formula is the total number of all different values in the list.
Formulas to count distinct values of different types
As is the case with counting unique values in Excel, you can use variations of the basic Excel count distinct formula to handle specific value types such as numbers, text, and case-sensitive values.
Please remember that all of the below formulas are array formulas and require pressing Ctrl + Shift + Enter .
Count distinct values ignoring empty cells
For example, to count distinct values in range A2:A10, use the following array formula:
=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
Formula to count distinct text values
To count distinct text values in a column, we'll be using the same approach that we've just used to exclude empty cells.
As you can easily guess, we will simply embed the ISTEXT function into our Excel count distinct formula:
And here's a real-life formula example:
Formula to count distinct numbers
To count distinct numeric values (numbers, dates and times), use the ISNUMBER function:
For example, to count all different numbers in range A2:A10, use the following formula:
Count case-sensitive distinct values in Excel
Similarly to counting case-sensitive unique values, the easiest way to count case-sensitive distinct values is to add a helper column with the array formula that identifies unique values including first duplicate occurrences. The formula is basically the same as the one we used to count case-sensitive unique values, with one small change in a cell reference that makes a great difference:
As you remember, all array formulas in Excel require pressing Ctrl + Shift + Enter .
After the above formula is finished, you can count "distinct" values with a usual COUNTIF formula like this:
If there is no way you can add a helper column to your worksheet, you can use the following complex array formula to count case-sensitive distinct values without creating an additional column:
=SUM(IFERROR(1/IF($A$2:$A$10<>"", FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))
Count unique and distinct rows in Excel
Counting unique / distinct rows in Excel is akin to counting unique and distinct values, with the only difference that you use the COUNTIFS function instead of COUNTIF, which lets you specify several columns to check for unique values.
For example, to count unique or distinct names based on the values in columns A (First Name) and B (Last Name), use one of the following formulas:
Formula to count unique rows:
Formula to count distinct rows:
Naturally, you are not limited to counting unique rows based only on two columns, the Excel COUNTIFS function can process up to 127 range/criteria pairs.
Count distinct values in Excel using a PivotTable
The latest versions of Excel 2013 and Excel 2016 have a special feature that allows counting distinct values automatically in a pivot table. The following screenshot gives an idea of how the Excel Distinct Count looks like:
To create a pivot table with the distinct count for a certain column, perform the following steps.
- Select the data to be included in a pivot table, switch to the Insert tab, Tables group, and click the PivotTable button.
- In the Create PivotTable dialog, choose whether to place your pivot table in a new or existing worksheet, and be sure to select the Add this data to the Data Model checkbox.
- When your pivot table opens, arrange the Rows, Columns and Values areas the way you want. If you don't have much experience with Excel pivot tables, the following detailed guidelines may prove helpful: Creating a PivotTable in Excel.
- Move the field whose distinct count you want to calculate (Item field in this example) to the Values area, click on it, and select Field Value Settings… from the drop-down menu:
- The Value Field Settings dialog window will open, you scroll down to Distinct Count, which is the very last option in the list, select it and click OK.
You can also give a custom name to your Distinct Count if you want to.
Done! The newly created pivot table will display the distinct count like shown in the very first screenshot in this section.
Tip. After updating your source data, remember to update the PivotTable to bring the distinct count up to date. To refresh a pivot table, just click the Refresh button on the Analyze tab, in the Data group.
This is how you count distinct and unique values in Excel. If someone wants to have a closer look at the formulas discussed in this tutorial, you are welcome to download the sample Excel Count Unique workbook.
I thank you for reading and hope to see you again next week. In the next article, we are going to discuss various ways to find, filter, extract and highlights unique values in Excel. Please stay tuned!
If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that?
In SQL Server I would do something like
7 Answers 7
Simpler than you might think:
- Click the Data Ribbon Menu
- Select the Advanced Button in the Sort & Filter section
- Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only
If copying data to another sheet, you must initiate the Advanced dialog from the sheet you want to copy to, otherwise you get a You can only copy filtered data to the active sheet error.
Excel 2007 and 2010:
Use the Remove Duplicates menu option under the Data header.
Excel 2003:
Easy way:
- Make sure your data has a header
- Data --> Filter --> Advanced Filter
- Check Unique Records Only
- Select Copy to another location
- Click OK
Write a macro with the following code:
That gives you your distinct list. You may want to copy your list to another sheet first.
Thanks! This is a proof that Stack Exchange is way better than any other source for technical information. All other google results are useless, and unranked. Also I wonder how experts-exchange survive
Or (a simple crude way):
and copy down. It just copies the first occurrence of each value across (in the row that it occurs in).
As with all Excel formulas, you may need to use semicolons ( ; ) instead of commas ( , ) based on your system’s regional settings.
I use two main methods to get distinct values - the first is the invaluable Remove Duplicates tool as suggested by Nathan DeWitt.
The downside to Remove Duplicates is that you lose data. Despite the power of Ctrl + z you may not wish to do this for various reasons - eg you may have filters currently selected that make it quite messy to remove duplicates to get your distinct list of values.
The other method, not mentioned above is to use a Pivot Table.
Let's say this is your table and you want to get the distinct list of Platforms.
Insert a Pivot Table with the table as your data source.
Now select the Platform(s) column as your Row Labels .
Voila! A list of distinct values.
If you wish to tidy this up, you can switch off Field Headers and set Grand Totals to be Off for Rows and Columns .
I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no function =COUNTDISTINCT(A2:A100) so what can I do instead? (Excel 2003)
I can't exactly use answers to this similar question because I don't want to modify the table or the filtering. I need an addition in the worksheet, not a modification.
7 Answers 7
will do it without having to use an array formula.
@RBarryYoung, SUMPRODUCT works like an Array function without being one. While it can do much more than what it's being used for here, here it is just checking every instance of a column, and coming up with a scaled value for each distinct value based on how many iterations there are in the column. So if there were two instances of 'a', it would add 1/2 and 1/2 to get 1. So for every distinct value, you'll add one to the sum.
This is the part that I'm having trouble with: "coming up with a scaled value for each distinct value based on how many iterations there are in the column." If I understand this, then SUMPRODUCT is effectively executing a loop like for each [i] in A2:A100 DO:< sum += (([i]<>"")/COUNTIF(A2:A100,[i])) > , right? I am having a little trouble understanding how it knows to enumerate both the conditional and the 2nd COUNTIF parameter, but not the 1st COUNTIF parameter? Is this DWIM-magic documented/explained anywhere by MS? It would be nice to know how to write these intentionally.
Yes, you got it, it iterates an array for what would normally be a single value, multiplying all of its parameters (which in this case is just 1), then adding the iterations. SUMPRODUCTs can get very complex, but they are one of the most useful functions in Excel, it can do literal miracles.
This fails if the input contains blank entries. Easy fix as demoed on youtube is this: =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")) . It ensures that the COUNTIF never returns a 0 for blank lines, which would result in a DIV/0 error.
I found a solution here which seems to be an incredible roundabout way to solve it. But hey, it works.
and then press Ctrl + Shift + Enter . Pressing only Enter will give the wrong result.
Nope, that's the best way. I'm not sure why there's an IF there. You can just use =SUM(1/COUNTIF(A2:A100,A2:A100)) array entered
@dkusleika The IF seems to be there to prevent a division by zero error. As mentioned on the page linked to above, this is needed if the range contains blank cells, otherwise you can leave the IF out.
This article shows this for text values:
and this for numeric values:
This article shows similar formulas, but also shows a method using filters.
This is the final part of the Excel Unique Values series that shows how to get a list of distinct / unique values in column using a formula, and how to tweak that formula for different datasets. You will also learn how to quickly get a distinct list using Excel's Advanced Filter, and how to extract unique rows with Duplicate Remover.
In a couple of recent articles, we discussed different methods to count and find unique values in Excel. If you had a chance to read those tutorials, you already know how to get a unique or distinct list by identifying, filtering, and copying. But that's a bit long, and by far not the only, way to extract unique values in Excel. You can do it much faster by using a special formula, and in a moment I will show you this and a couple of other techniques.
Tip. To quickly get unique values in the latest version of Excel 365 that supports dynamic arrays, use the UNIQUE function as explained in the above linked tutorial.
How to get unique values in Excel
To avoid any confusion, first off, let's agree on what we call unique values in Excel. Unique values are the values that exist in a list only once. For example:
To extract a list of unique values in Excel, use one of the following formulas.
Array unique values formula (completed by pressing Ctrl + Shift + Enter ):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)<>1), 0)), "")
Regular unique values formula (completed by pressing Enter):
=IFERROR(INDEX($A$2:$A$10, MATCH(0,INDEX(COUNTIF($B$1:B1, $A$2:$A$10)+(COUNTIF($A$2:$A$10, $A$2:$A$10)<>1),0,0), 0)), "")
In the above formulas, the following references are used:
- A2:A10 - the source list.
- B1 - the top cell of the unique list minus 1. In this example, we start the unique list in B2, and therefore we supply B1 to the formula (B2-1=B1). If your unique list begins, say, in cell C3, then change $B$1:B1 to $C$2:C2.
Note. Because the formula references the cell above the first cell of the unique list, which is usually the column header (B1 in this example), make sure your header has a unique name that does not appear anywhere else in the column.
In this example, we are extracting unique names from column A (more precisely from range A2:A20), and the following screenshot demonstrates the array formula in action:
The detailed explanation of the formula's logic is provided in a separate section, and here's how to use the formula to extract unique values in your Excel worksheets:
- Tweak one of the formulas according to your dataset.
- Enter the formula in the first cell of the unique list (B2 in this example).
- If you are using the array formula, press Ctrl + Shift + Enter . If you've opted for the regular formula, press the Enter key as usual. as far as needed by dragging the fill handle. Since both unique values formulas are we encapsulated in the IFERROR function, you can copy the formula up to the end of your table, and it won't clutter your data with any errors no matter how few unique values have been extracted.
How to get distinct values in Excel (unique + 1 st duplicate occurrences)
As you may have already guessed from the heading of this section, distinct values in Excel are all different values in a list, i.e. unique values and first instances of duplicate values. For example:
To get a distinct list in Excel, use the following formulas.
Array distinct formula (requires pressing Ctrl + Shift + Enter ):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")
Regular distinct formula:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")
- A2:A10 is the source list.
- B1 is the cell above the first cell of the distinct list. In this example, the distinct list begins in cell B2 (it's the first cell where you enter the formula), so you reference B1.
Extract distinct values in a column ignoring blank cells
If your source list contains any blank cells, the distinct formula we've just discussed would return a zero for each empty row, which might be a problem. To fix this, improve the formula a bit further:
Array formula to extract distinct values excluding blanks:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") + IF($A$2:$A$10="",1,0), 0)), "")
Get a list of distinct text values ignoring numbers and blanks
In a similar manner, you can get a list of distinct values excluding empty cells and cells with numbers:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), "")
As a quick reminder, in the above formulas, A2:A10 is the source list, and B1 is cell right above the first cell of the distinct list.
The following screenshot shows the result of both formulas:
How to extract case-sensitive distinct values in Excel
When working with case-sensitive data such as passwords, user names or file names, you may need to get a list of case-sensitive distinct values. For this, use the following array formula, where A2:A10 is the source list, and B1 is the cell above the first cell of the distinct list:
Array formula to get case-sensitive distinct values (requires pressing Ctrl + Shift + Enter )
=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), "")
How the unique / distinct formula works
This section is written especially for those curious and thoughtful Excel users who not only want to know the formula but fully understand its nuts and bolts.
It goes without saying that the formulas to extract unique and distinct values in Excel are neither trivial nor straightforward. But having a closer look, you may notice that all the formulas are based on the same approach - using INDEX/MATCH in combination with COUNTIF, or COUNTIF + IF functions.
For our in-depth analysis, let's use the array formula that extracts a list of distinct values because all other formulas discussed in this tutorial are improvements or variations of this basic one:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")
And now, let's break down the core part of our distinct formula:
-
COUNTIF(range, criteria) returns the number of cells within a range that meet a specified condition.
In this example, COUNTIF($B$1:B1, $A$2:$A$10) returns an array of 1's and 0's based on whether any of the values of the source list ($A$2:$A$10) appears somewhere in the distinct list ($B$1:B1). If the value is found, the formula returns 1, otherwise - 0.
In particular, in cell B2, COUNTIF($B$1:B1, $A$2:$A$10) becomes:
MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)
In this example, INDEX($A$2:$A$10, 1)
and returns "Ronnie".
When the formula is copied down the column, the distinct list ($B$1:B1) expands because the second cell reference (B1) is a relative reference that changes according to the relative position of the cell where the formula moves.
So, when copied to cell B3, COUNTIF($B$1:B1, $A$2:$A$10) changes to COUNTIF($B$1:B2, $A$2:$A$10), and becomes:
because one "Ronnie" is found in range $B$1:B2.
And then, MATCH(0,0;0;0;0;0;0;0;0>,0) returns 2, because 2 is the relative position of the first 0 in the array.
Tip. For better understanding of the formula's logic, you can select different parts of the formula in the formula bar and press F9 to see what a selected part evaluates to:
If you still have difficulties figuring out the formula, you can check out the following tutorial for the detailed explanation of how the INDEX/MATCH liaison works: INDEX & MATCH as a better alternative to Excel VLOOKUP.
As already mentioned, the other formulas discussed in this tutorial are based on the same logic, with just a few modifications:
Unique values formula - contains one more COUNTIF function that excludes from the unique list all items that appear in the source list more than once: COUNTIF($A$2:$A$10, $A$2:$A$10)<>1 .
Distinct values formula ignoring blanks - here you add an IF function that prevents blank cells from being added to the distinct list: IF($A$2:$A$13="",1,0) .
Distinct text values formula ignoring numbers - you use the ISTEXT function to check whether a value is text, and the IF function to dismiss all other value types, including blank cells: IF(ISTEXT($A$2:$A$13)=FALSE,1,0) .
Extract distinct values from a column with Excel's Advanced Filter
If you don't want to waste time on figuring out the arcane twists of the distinct value formulas, you can quickly get a list of distinct values by using the Advanced Filter. The detailed steps follow below.
- Select the column of data from which you want to extract distinct values.
- Switch to the Data tab >Sort & Filter group, and click the Advanced button:
- In the Advanced Filter dialog box, select the following options:
- Check Copy to another location radio button.
- In the List range box, verify that the source range is displayed correctly.
- In the Copy to box, enter the topmost cell of the destination range. Please keep in mind that you can copy the filtered data only to the active sheet.
- Select the Unique records only
Please pay attention that although the Advanced Filter's option is named "Unique records only", it extracts distinct values, i.e. unique values and 1 st occurrences of duplicate values.
Extract unique and distinct rows with Duplicate Remover
In the final part of this tutorial, let me show you our own solution to find and extract distinct and unique values in Excel sheets. This solution combines the versatility of Excel formulas and simplicity of the advanced filter. In addition, it provides a couple of unique features such as:
- Find and extract unique / distinct rows based on values in one or more columns.
- Find, highlight, and copy unique values to any other location, in the same or different workbook.
And now, let's see the Duplicate Remover tool in action.
Supposing you have a summary table created by consolidating data from several other tables. Obviously, that summary table contains a lot of duplicate rows and your task is to extract unique rows that appear in the table only once, or distinct rows including unique and 1 st duplicate occurrences. Either way, with the Duplicate Remover add-in the job is done in 5 quick steps.
In this example, we aim to extract unique rows that appear in the source table only once, so we select the Unique option:
Tip. As you can see in the above screenshot, there are also 2 options for duplicate values, just keep it in mind if you need to dedupe some other worksheet.
- Highlight unique values
- Select unique values
- Identify in a status column
- Copy to another location
Because we are extracting unique rows, select Copy to another location, and then specify where exactly you want to copy them - active sheet (select the Custom location option, and specify the top cell of the destination range), new worksheet or new workbook.
Liked this quick and simple way to get a list of unique values or rows in Excel? If so, I encourage you to download an evaluation version below and give it a try. Duplicate Remover as well as all other time-saving tools that we have are included with Ultimate Suite for Excel.
In last week's tutorial, we explored different ways to count unique values in Excel. But occasionally you may want to view only unique or distinct values in a column - not how many, but the actual values. Before moving further, let's make sure we are on the same page with the terms. So, what are distinct and what are unique values in Excel?
- Unique values are the items that appear in a dataset only once.
- Distinct values are all different items in a list, i.e. unique values and 1 st occurrences of duplicate values.
And now, let's investigate the most efficient techniques to deal with unique and distinct values in your Excel sheets.
How to find unique /distinct values in Excel
The easiest way to identify unique and distinct values in Excel is by using the IF function together with COUNTIF. There can be a few variations of the formula depending on the type of values that you want to find, as demonstrated in the following examples.
Find unique values in a column
To find distinct or unique values in a list, use one of the following formulas, where A2 is the first and A10 is the last cell with data.
How to find unique values in Excel:
=IF(COUNTIF($A$2:$A$10, $A2)=1, "Unique", "")
How to get distinct values in Excel:
=IF(COUNTIF($A$2:$A2, $A2)=1, "Distinct", "")
In the distinct formula, there is just one small deviation in the second cell reference, which however makes a big difference:
Tip. If you'd like to search for unique values between 2 columns, i.e. find values that are present in one column but absent in another, then use the formula explained in How to compare 2 columns for differences.
Find unique / distinct rows in Excel
In a similar manner, you can find unique rows in your Excel table based on values in 2 or more columns. In this case, you need to use the COUNTIFS function instead of COUNTIF to evaluate the values in several columns (up to 127 range/criteria pairs can be evaluated in a single formula).
For example, to find unique or distinct names in the list, use the following formulas:
Formula to get unique rows:
=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, "Unique row", "")
Formula to find distinct rows:
=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, "Distinct row", "")
Find case-sensitive unique / distinct values in Excel
If you are working with a data set where case matters, you'd need a bit more trickier array formula.
Finding case-sensitive unique values:
Finding case-sensitive distinct values:
Since both are array formulas, be sure to press Ctrl + Shift + Enter to complete them correctly.
When the unique or distinct values are found, you can easily filter, select and copy them as demonstrated below.
How to filter unique and distinct values in Excel
To view only unique or distinct values in the list, filter them out by performing the following steps.
How to select distinct / unique values
If you have a relatively small list of unique / distinct values, you can simply select it in the usual way using the mouse. If the filtered list contains hundreds or thousands of rows, you can use one of the following time-saving shortcuts.
To quickly select the unique or distinct list including column headers, filter unique values, click on any cell in the unique list, and then press Ctrl + A .
To select distinct or unique values without column headers, filter unique values, select the first cell with data, and press Ctrl + Shift + End to extend the selection to the last cell.
Tip. In some rare cases, mostly on very large workbooks, the above shortcuts may select both visible and invisible cells. To fix this, press either Ctrl + A or Ctrl + Shift + End first, and then press Alt + ; to select only visible cells, ignoring hidden rows.
If you have difficulties with remembering that many shortcuts, use this visual way: select the entire unique / distinct list, then go to the Home tab > Find & Select > Go to Special, and select Visible cells only.
Copy unique or distinct values to another location
To copy a list of unique values to another location, just do the following:
- Select the filtered values using the mouse or the above mentioned shortcuts.
- Press Ctrl + C to copy the selected values.
- Select the top-left cell in the destination range (it can be on the same or different sheet), and press Ctrl + V to paste the values.
How to highlight unique and distinct values in Excel
Whenever you need to highlight anything in Excel based on a certain condition, head right to the Conditional Formatting feature. More detailed information and examples follow below.
Highlight unique values in a column (built-in rule)
The fastest and easiest way to highlight unique values in Excel is to apply the inbuilt conditional formatting rule:
Tip. If you are not happy with any of the predefined formats, click Custom Format. (the last item in the drop-down list) and set the fill and/or font color to your liking.
As you see, highlighting unique values in Excel is the easiest task one could imagine. However, Excel's built-in rule works only for the items that appear in the list only once. If you need to highlight distinct values - unique and 1 st duplicate occurrences - you will have to create your own rule based on a formula. You would also need to create a custom rule to highlight unique rows based on the values in one or more columns.
Highlight unique and distinct values in Excel (custom rule)
To highlight unique or distinct values in a column, select the data without a column header (you don't want the header to get highlighted, do you?), and create a conditional formatting rule with one of the following formulas.
Highlight unique values
To highlight the values that appear in a list just once, use the following formula:
Where A2 is the first and A10 is the last cell of the applied range.
Highlight distinct values
To highlight all different values in a column, i.e. unique values and 1 st duplicate occurrences, go with the following formula:
Where A2 is the top-most cell of the range.
How to create a formula based rule
To create a conditional formatting rule based on a formula, do the following:
- Go to the Home tab >Styles group, and click Conditional Formatting >New rule > Use a formula to determine which cells to format.
- Enter your formula on the Format values where this formula is true box.
- Click the Format. button and choose the fill color and/or font color you want.
- Finally, click the OK button to apply the rule.
For more detailed steps with screenshots, please see the following tutorial: How to create an Excel conditional formatting rules based on another cell value.
The below screenshot demonstrates both rules in action:
Highlight entire rows based on unique / distinct values in one column
To highlight entire rows based on unique values in a specific column, use the formulas for unique and distinct values we used in the previous example, but apply your rule to the whole table rather than to a single column.
The following screenshot demonstrates the rule that highlights rows based on distinct numbers in column A:
How to highlight unique rows in Excel
If you want to highlight rows based on the values in 2 or more columns, use the COUNTIFS function that allows specifying several criteria in a single formula.
Highlight unique rows
Highlight distinct rows (unique + 1 st duplicate occurrences)
This is how you can find, filter and highlight distinct or unique values in Excel. To consolidate your knowledge, you can download the sample Find Unique Values workbook and reverse-engineer the formulas for better understanding.
Fast and easy way to find and highlight unique values in Excel
As you have just seen, Microsoft Excel provides quite a lot of useful features that can help you identify and highlight unique values in your worksheets. However, all those solutions can hardly be called intuitive and easy-to-use because they requires memorizing a handful of different formulas. Of course, it's no big deal for Excel professionals :) For those Excel users who want to save their time and effort, let me demonstrate a quick and straightforward way to find unique values in Excel.
In this final section of our today's tutorial, we are going to use our Duplicate Remover add-in for Excel. Please don't be confused by the tool's name. Apart from duplicate records, the add-in can perfectly handle unique and distinct entries, and you will make sure of it in a moment.
The wizard will run and the entire table will get selected automatically. So, just click Next to proceed to the next step.
Tip. When using the add-in for the first time, it makes sense to check the Create a backup copy box, just in case.
- Unique
- Unique +1 st occurrences (distinct)
Tip. If your table has headers, be sure to select the My table has headers box. And if your table may have empty cells, make sure the Skip empty cells option is checked. Both options reside in the upper part of the dialog window and are usually selected by default.
- Highlight unique values with color
- Select unique values
- Identify in a status column
- Copy to another location
Click the Finish button, and get the result in seconds:
This is how you can find, select and highlight unique values in Excel using our Duplicate Remover add-in. It just couldn't be simpler, right?
If finding duplicate and unique values in Excel is a common part of your daily work, just try this dedupe tool and you will be amazed with the results! Duplicate Remover as well as our other time-saving tools are included with Ultimate Suite for Excel.
Читайте также: