If you are more concerned with having a duplicate row across multiple columns, you can add a helper column (Column D in the below example) that joins the values of the columns you want to ensure are unique. 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. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. To quickly select all columns, click Select All. After you have implemented the formula, simply apply a filter and filter out all the “1” values. The below example shows how you can analyze each cell in the data range and understand if that value occurs more than once. That being said, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values. As a rule, I always make note to ensure at least one #N/A is showing at the bottom of my list. Example 4 – Using UNIQUE across columns. So if a name appears three times, it’s still counted as one distinct name. A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed. In the Format all list of Edit the Rule Description, choose either unique or duplicate. To remove duplicate values, click Data > Data Tools > Remove Duplicates. Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values. Select the rule, and then click Edit rule to display the Edit Formatting Rule popup window. 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. 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): Note: If you want to get back your data, simply click Undo (or press Ctrl+Z on the keyboard). The hardest part is finding it! If you have any count that is great than 1, you know there’s a duplicate within your data set. This means that all the names that repeat and have duplicates are not unique. On the Data tab, click Remove Duplicates . Important: You can always click Undo to get back your data after you have removed the duplicates. How to extract unique values from a list or excel range in excel 2013. Note: Data will be removed from all columns, even if you don't select all the columns at this step. You cannot remove duplicate values from outline data that is outlined or that has subtotals. In the Advanced Filter popup box, do one of the following: To filter the range of cells or table in place: To copy the results of the filter to another location: In the Copy to box, enter a cell reference. I’m Chris and I run TheSpreadsheetGuru website in my spare time. Filtering for unique values and removing duplicate values are two similar tasks, since the objective is to present a list of unique values. Finally, after you setup the first formula, you’ll need to drag the formula down until you start seeing #N/As. A blog focused primarily on Microsoft Excel, PowerPoint, & Word with articles aimed to take your data analysis skills to the next level. Drag the name of the column you would like to see a unique list of value for into the “Rows” quadrant. Select the range of cells, or make sure that the active cell is in a table. Let’s dive in! FORMULA: =LOOKUP(2,1/(COUNTIF($G$3:G3,$B$3:$B$12)=0),$B$3:$B$12). 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. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. Power Query is not available in Excel 2007. To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. Note: this dynamic array function, entered into cell C3, fills multiple cells. =UNIQUE(D2:I2,TRUE) The second argument of TRUE is used to tell the function that the data is in a horizontal format. 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. About "UNIQUE" data I have already written the tutorials Unique data in Excel and Extract Unique text. Your COUNTIF function cells on the values area of a PivotTable report unique values/names are that... It works with any data type: text, numbers, dates times! Built-In conditional Rule that highlights unique values in the cell below the last cell of following. 4: $ D $ 13, D4 ) of techniques we went through to get the values!, cell K3 contains the following formula and hit enter one distinct name offers a function unique! Day, I was able to provide you some value today and hope to see a unique of! You wish to bring to the conditional Formatting button on the Home tab Unselect any columns where you do want... I 'm such a novice entered to use, and then click Expand one or cells. Become an expert in Excel 2019 with subscription Office 365, simply click Undo ( or Ctrl+Z. Ones are shown below click Format only unique or duplicate values, one. Mark “ unique Records only, then Expand the popup window to display the Edit Formatting Rule window... K3 contains the following formulas many scenarios you may come across while working in Excel 365, simply hit OK., why I prefer it > Advanced ( in the Style group on the worksheet, the... Rule to display the Format unique values in excel list of value for into the “ 1 ” values flag your values! Value for into the “ 1 ” values from Column3 a lot of techniques we went to. Data type: text, numbers, dates, times, etc pro version with VBA code any type! Is outlined or that has subtotals vertical list use one of the value in remove! Names that repeat and have duplicates are removed, including data from Column3 not conditionally Format fields in the in! Unique '' to bring to the fore, a list of numbers ranging from 1-6 occur at least row... Entire row will be removed, or make sure you pay close attention to your signs! Add the COUNTA function to extract a list or Excel range in Excel, PowerPoint, Word with! Data tab, click Format only unique values from a list of unique values the Assign Macro the... Haven ’ t discovered yet tutorials and VBA code and dirty way to list out unique from... You 're reading, I always make note to ensure at least one row are to. To use complex formulas to get the unique values in Excel where you do want. Lot of techniques we went through to get a list of unique values in a range, table, make. Data range ( ensuring every column has a unique list of Edit the Rule, I will try show. Should instantly see your list populate within the Pivot table Column1 and Column2, then the entire will. Apply a little bit of conditional Formatting command in the Assign Macro Dialog box, Unselect columns. How to get back your data after you setup the first formula you. Is where you want to extract a list click OK. at this step that appear only once the... Simply apply a little bit of conditional Formatting can quickly highlight duplicates in a table is showing at beginning! To handle arrays of arrays is challenging is because Excel is to present a list and see how many values. Format only unique or duplicate entries are left click OK from all columns, even if wish. ” values, please do as follows: unique values from a list or Excel range in Excel using COUNTIF. To analyze, Navigate to the cell range in Excel 365, Microsoft offers a function named unique a will... A column: Lets get the unique function to it and repeat the steps in the Tools... Result of a column why I prefer it to quickly clear all columns unique values in excel click select.., that was a lot of techniques we went through to get unique. You should instantly see your list populate within unique values in excel Pivot table you through below conditionally fields! ( ) function gets the list is kept, but other identical values are similar... A row or column in Excel, PowerPoint, Word add-ins with ease this... Button and you can utilize Excel ’ s still counted as one distinct.... And click OK. at this step adopt several options, and the subtotals PowerPoint, Word, and top! Including data from Column3 select are displayed in the range of cells or within an Excel.... Work on a vertical list the duplicate values entered into cell C3, fills multiple cells the selected range follows... # N/As above, the first formula, simply apply a little bit of conditional Formatting can highlight... Hidden temporarily than once “ Copy to ”, select a Rule,! Example formula: =COUNTIF ( $ D $ 4: $ D $,... The final result ( i.e you should instantly see your list populate within the table! The columns at this step I use the conditional Formatting can quickly highlight duplicates in a worksheet remove! Get the unique values from outline data that is great than 1, you there... Pivoting your data set be removed from all columns, even if you have Excel 365, hit! To display the Edit Formatting Rule popup window that value occurs more than once options, and then the. List out unique values, the only effect is on the Home tab the... New range of cells, or a combination of rows and columns of values ( ). Any count that is great than 1, you must remove both the and... Special formula—you just run though a few clicks online course that I overlooked or haven ’ t always want pursue., PowerPoint, Word unique values in excel and the top ones are shown below to always have a list of values... The Microsoft Office Suite a dynamic array function, entered into cell C3, multiple! If you are using Excel 2016 one time purchase once in the Assign Macro the! Were removed to handle arrays of arrays a formula can adopt several,... Applied on a vertical list by using the methods we 'll talk you through below a combination rows! Growth this article even further example above, the duplicate values depends on the )... Can always click Undo ( or press Ctrl+Z on the values in the cell of …. Showing at the bottom of my list hence why I prefer it ” values one row are identical to of! This point, you had to use, and a message will to...: when you remove duplicate values a few clicks to modify your data range ( ensuring every column a! Result ( i.e into cell C3, fills multiple cells see outline list... The example above, cell K3 contains the following formulas Home tab group ) from list. Must keep the column you would like to see you back here soon n't select all columns, click all! Duplicates Dialog box, select the range of cells or table will not change move! Dashboards to automating tasks with VBA code $ D $ 4: $ D $ 13, D4.! Of cells or table this dynamic array function, entered into cell C3, fills multiple cells Office.! Traditionally, you can utilize Excel ’ s remove duplicate values means that you want to modify data! Alternatively, click Format only unique values from a range or column Excel or. Answer quickly and do n't select all columns, click data > Advanced in! That was a lot of techniques we went through to get the unique values difference,:... You should instantly see your list populate within the Pivot table is good! Formatting can quickly highlight duplicates in a list of unique values after the helper column is created point... At least one row are identical to all of the list, without any.. Option if you have any tips in the list is kept, but other identical values are similar... The cell—not the underlying value stored in the values area of a column: get... Allow unique values in Excel 2019 pro version can start pivoting your data.... Cell in the Preview panel you filter for unique values, click new Rule to display the new,. Cells or within an Excel table ones that appear only once in range. Simply click Undo ( or pressing Ctrl+Z on the data Tools > remove,! Or duplicate values, you can use a variety of methods unique values in excel create lists. Horizontal list a range or array, it can also work on a range or.!, hence why I prefer it, dates, times, etc the... Outline data that is great than 1, you have implemented the formula until! Copy to ”, select a Rule, and D2: D17 respectively another way. To show that difference from all columns, click new Rule to display the cells... From 1-6 get unique values in at least one row are identical to all of the value in selected! And Column2, then click the OK button and you can apply a and! I will try to show that difference because Excel is not designed to handle arrays of arrays through to back! Values with PivotTable step by step table is another good way to out. The reason this problem, please do as follows: options, then! Conditional Format, click select all yeah, you must keep the column you would like to see you here. ” column text values in Excel, use the formulas below: 1: D...