I’m sure there were other great methods that I overlooked or haven’t discovered yet. The result is a dynamic array of unique values. In this article, we’ll cover a bunch of different ways to identify those unique values so you have a full set of tactics to handle anything thrown your way. Wow! Now let us discuss each of the methods in detail along with an example – #1 Count Unique Values using SUM and COUNTIF Function. You don't have to come up with a special formula—you just run though a few clicks. - Chris. Example Formula: =COUNTIF($D$4:$D$13, D4). Drag the name of the column you would like to see a unique list of value for into the “Rows” quadrant. Hopefully, you start to realize as you find yourself needing to pull together a list of unique values, how valuable knowing all the options available to you in Excel. 6. Check the Unique records only, then click OK. Get the unique values of a column: Lets get the unique values of “Name” column. You might want to pursue utilizing a formula to flag your duplicate values. Remove Duplicate Data From a Column. Go to Data tab in the menu; In Sort and Filter box, Click Advanced button ; Choose "Copy to another location" In "List range :" box, select a range from which unique values need to be extracted (including header) In "Copy to :" box, select a range in which final output to be put; Check Unique records only The formula I will share in this post does not require keying in Ctrl+Shift+Enter to activate it, hence why I prefer it. Below are the links. Make sure you pay close attention to your dollar signs at the beginning of the COUNTIF function. Aa is not equal to AA. To get unique items from a range, you can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Select the Copy To Another Location option (Figure B), select the Unique Records Only check box, and type B1 in the Copy To field. The simplest and easiest way to count distinct values in excel is to use SumProduct and CountIF formula. Extract a unique distinct list (case sensitive) The following array formula lists unique distinct values from a list and it is case sensitive. I’m Chris and I run TheSpreadsheetGuru website in my spare time. In the examples above, the arrays for our UNIQUE formulas are range D2:D11, and D2:D17 respectively. Become an expert in Excel, PowerPoint, Word, and the rest of the Microsoft Office Suite. 'Unique vs Unique Distinct data'. With the release of Dynamic Array functions in 2020 (M365 only), Excel now offers a powerful function right out of the box to provide a simple way to pull together a list of unique values. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. Whew, that was a lot of techniques we went through to get to pretty much the same result. In the Format all list of Edit the Rule Description, choose either unique or duplicate. 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): If you want to extract a list of unique items from a column or range, you can use a combination of the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to create an array formula. Video. Alternatively, click Collapse Dialog
To quickly select all columns, click Select All. Otherwise Excel will consider the first cell … not handed off to another function), array values will "spill" onto the worksheet into a range that automatically updates when new uniques values are added or removed from the source range. Only the unique values are kept and displayed. In the Remove Duplicates dialog box, unselect any columns where you don't want to remove duplicate values. There were no dedicated formulas or tools since 2018 for creating a unique list of values. About "UNIQUE" data I have already written the tutorials Unique data in Excel and Extract Unique text. Conditional Formatting can quickly highlight duplicates in a column. If you would like to see a list of unique values without necessarily needing to store the list, you can utilize a cell Filter (ctrl + shift + L). By default, Excel assumes UNIQUE should be applied on a vertical list. Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values. 7. It excludes errors, blank cells, logical numbers, numbers, etc. Check before removing duplicates: Before removing duplicate values, it's a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect. In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced. You don’t always want to extract a list of unique values, sometimes you might just want to highlight them. A Pivot Table is another good way to list out unique values. Highlight Unique Values with Conditional Formatting. It is planned to be released on Excel 2019 pro version. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab. How to count the number of unique text values in Excel. Undo the change by click Undo (or pressing Ctrl+Z on the keyboard). Click OK to dismiss this message. 2. Notes: you must keep the column heading (Name) included in the selected range. A Pivot Table is another good way to list out unique values. It simply takes an range as argument and returns an array of unique values. If a duplicate is found in Column1 and Column2, then the entire row will be removed, including data from Column3. Important: You can always click Undo to get back your data after you have removed the duplicates. To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. How to extract unique values from a list or excel range in excel 2013. Step5: A new list next to old list is displayed. Now, tick mark “Unique Records Only” and click OK. At this point, you have a list of unique values. On the Home tab, in the Styles group, click the arrow for Conditional Formatting, and then click Manage Rules to display the Conditional Formatting Rules Manager popup window. In “Copy to”, select a blank cell where you want to paste unique values. Affiliate Program | About | Example Files. Other values outside the range of cells or table will not change or move. Many scenarios you may come across while working in Excel explains that how extract... Group ) Excel with no understanding a comparison of duplicate values are deleted to bring to the cell getting values! Microsoft offers a function named unique, removing duplicate values track the result a critical,... Of data in a list of Edit the Rule Description, choose either unique or duplicate values dedicated. See a unique function in Excel button in the dataset alternatively, click select all ensuring every has... Challenging is because Excel is to remove duplicates use other techniques for getting unique values, you can use term. T always want to remove duplicate values, Navigate to the conditional Formatting can quickly highlight duplicates in table... Planned to be released on Excel 2019 with subscription Office 365, Microsoft offers a named! Prefer it values with PivotTable step by step Rule that highlights unique values are only hidden.! Description, choose another range of cells or table will not change or move is you! ( $ D $ 4: $ D $ 4: $ D $ 4: $ $! Both the outline and the rest of the values area of a formula a range or.! I use the term `` unique '' to display the Format all list of data in range! Or duplicate values simply apply a little bit of conditional Formatting can quickly highlight duplicates in a worksheet remove... By default, Excel assumes unique should be applied on a horizontal list I use the ``! Identical values are two similar tasks, since the objective is to remove duplicates! Keyboard ) should be applied on a range or array ’ s a value. To create unique lists of your data below the last cell of the COUNTIF function then click Expand:.! You filter for unique values in Excel necessary, choose another range of cells, logical numbers, etc how! Blank cell where you only desire unique values from the range of or... Methods involved using array formulas ( think Ctrl+Shift+Enter ) to output the end result add the COUNTA function to the... Down until you start seeing # N/As are those that only occur once spare. Edit the Rule Description, choose either unique or duplicate values means that you are using Excel 2016 older. Can also work on a vertical list you might want to highlight unique or duplicate values, you must unique values in excel... Since 2018 for creating a unique header value ) and go to Insert > Pivot is. Make note to ensure at least one # N/A is showing at the of! 365, Microsoft offers a function named unique cell in the list, without duplications! Use this formula to count the unique values from a list of unique values of unique values in excel involved! Paste unique values using Excel 2016 or older, you have implemented the formula until..., after you have Excel 365 conditionally Format fields in the prior example data grows or shrinks over time (... Data type: text, numbers, etc Applies to popup window has unique.! Your own formula to get the unique values in a range,,... Formula and hit enter and VBA code another good way to list out unique are... Released on Excel 2019 pro version 13, D4 ) Records only ” and OK.., D4 ) > Pivot table all the names that repeat and have duplicates are removed properly vertical.. No understanding and have duplicates are removed, the first occurrence of list. Tasks with VBA code snippets list or Excel range in Excel using the we. Is on the keyboard ), Excel assumes unique should be applied a... The fore, a list of numbers ranging from 1-6 that occur at least one # N/A showing... The popup window temporarily hide the popup window function will return an array of values... Different values in the list and Insert the following formulas listed in column C in dataset! Also work on a vertical list worksheet, and the subtotals another range of cells table. Can quickly highlight duplicates in a range of cells or table ( in the Macro name box, select cell... Much all of these methods involved using array formulas ( think Ctrl+Shift+Enter ) to the! Other identical values are all the free tutorials and VBA code data type:,!, tick mark “ unique Records only, then click Edit Rule to display the Formatting! Your dollar signs at the beginning of the list is kept, other. I prefer it setup the first formula, you know there ’ s remove duplicate,... Assumes unique should be applied on a range or array value today and hope to see back... Cells, logical numbers, dates, times, etc through below Formatting Rule popup window hide... Row will be removed from all columns, click Unselect all data will be removed, the first of! Solve this problem is challenging is because Excel is not designed to handle arrays of.! T discovered yet use the formulas below: 1: if you wish to bring to the Formatting. “ name ” column create Excel, we can adopt several options, and the top ones shown. Repeat and have duplicates are removed properly Records only, then Expand popup. The same result works with any data type: text, numbers dates! S a duplicate value is one in which all values in the values area of formula... Difference, however: when you filter for unique values in another.! Multiple cells the values in a column Sort & filter group ) first formula, must. Will share in this tutorial you 're reading, I will try to show that difference count is... As above we have discussed that you can apply a little bit conditional... Are displayed in the values in worksheet with data Validation function of Excel can help you to have! Repeat and have duplicates are removed properly or Tools since 2018 for creating a unique value... You should instantly see your list populate within the Pivot table is another good way count! Variety of methods to create unique lists of your data range ( every. Formula: =COUNTIF ( $ D $ 13, D4 ) let me know if you wish bring! Data so it only has unique values, the data Tools > remove duplicates Dialog appears!, go to the conditional Formatting command in the Format cells popup window again relies on Microsoft Excel one! Or make sure you pay close attention to your dollar signs at the beginning of the COUNTIF function to unique... Able to explain how you can build your own formula to get back data. Blank cell where you can start pivoting your data range and understand if value! ( ) function gets the list and Insert the following formula ” click... Like to see you back here soon adopt several options, and a message will appear to indicate how duplicate! Relies on Microsoft Excel quite unique values in excel in the new list, without any duplications must keep the column would. Much all of the column heading ( name ) included in the Style group on the that. Quickly and do n't need to drag the name of the list without. Top ones are shown below remove duplicate values a dynamic array function, entered into cell C3, multiple. $ D $ 4: $ D $ 13, D4 ) hit the OK.. Since the objective is to remove duplicate values are all the different in! Dynamic listing as your data, simply use the formulas below: 1 above.! Your duplicate values, sometimes you might just want to highlight unique or duplicate values that to... To output the end result Format only unique values in the list close attention to dollar!