You should instantly see your list populate within the Pivot Table. 6. This is where you can apply a little bit of conditional formatting and luckily there is a preset to flag duplicate values! Choose a new range of cells on the worksheet, then expand the popup window again
As above we have discussed that you can build your own formula to count the unique values in a range of cells. Method 4 of 5: get unique values with PivotTable Step by step. Now, tick mark “Unique Records Only” and click OK. At this point, you have a list of unique values. This can be done by using the COUNTIF() function. 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. You might want to rid your data of duplicates to create summaries, populate drop-down lists, or remove duplicates that found their way into your spreadsheet. Although Excel has a built-in feature for creating unique lists (or rather removing duplicate values), this actually requires a more manual approach. Select the range of cells, or make sure that the active cell is in a table. Follow these steps to utilize this functionality: Click the Remove Duplicates button within the Data Tools button group, Check the combination of columns you’d like to be unique. The simplest and easiest way to count distinct values in excel is to use SumProduct and CountIF formula. If a duplicate is found in Column1 and Column2, then the entire row will be removed, including data from Column3. On the Data tab, click Remove Duplicates (in the Data Tools group). To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company. If necessary, choose another range of cells by clicking Collapse
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.
Create Excel, PowerPoint, Word add-ins with ease with this revolutionary template and online course! Enter the values that you want to use, and then choose a format. This feature can be used on a range of cells or within an Excel Table. Apply a filter to your data and click the filter arrow to see a list showing all the unique values within that particular column of data. 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. Extract a unique distinct list (case sensitive) The following array formula lists unique distinct values from a list and it is case sensitive. Whew, that was a lot of techniques we went through to get to pretty much the same result. For example, if you have the same date value in different cells, one formatted as "3/8/2006" and the other as "Mar 8, 2006", the values are unique. In the example above, cell K3 contains the following formula. How to count the number of unique text values in Excel. The function is categorized under Dynamic Arrays functions. By default, Excel assumes UNIQUE should be applied on a vertical list. Pretty much all of these methods involved using array formulas (think Ctrl+Shift+Enter) to output the end result. In the new list, duplicates are removed properly. 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. Select your data range (ensuring every column has a unique header value) and go to Insert > Pivot Table. Undo the change by click Undo (or pressing Ctrl+Z on the keyboard). The result is a dynamic array of unique values. To remove duplicate values, click Data > Data Tools > Remove Duplicates. 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. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below. Generating A List of Unique Values from Excel Data, Alphabetically Sort Cell Range Values With VBA Code, Easy Step-By-Step Instructions To Create Your First Excel Ribbon Add-in, 5 Different Ways to Find The Last Row or Last Column Using VBA, Copy & Paste Multiple Excel Ranges To Separate PowerPoint Slides With VBA. Make sure you pay close attention to your dollar signs at the beginning of the COUNTIF function. To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. After the helper column is created, point your COUNTIF function to it and repeat the steps in the prior example. To remove duplicate values, click Data > Data Tools > Remove Duplicates. 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. 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. Remove Duplicate Data From a Column. On the Data tab, click Remove Duplicates . Finally, after you setup the first formula, you’ll need to drag the formula down until you start seeing #N/As. In the examples above, the arrays for our UNIQUE formulas are range D2:D11, and D2:D17 respectively. In the Format all list of Edit the Rule Description, choose either unique or duplicate. The unique values from the range will copy to the new location. Let’s dive in! An array can be thought of as a row or column of values, or a combination of rows and columns of values. 7. The downside to using an Array formula over an Dynamic Array function is you have to carry it down manually, there is no Spill Range functionality that will automatically resize your list. Count Unique Values in Excel. Step5: A new list next to old list is displayed. button in the Applies to popup window temporarily hide it. Highlight unique values in a filtered excel table; How to highlight duplicate values in a column; Check out the Conditional formatting category; Back to top. Excel has a built-in conditional rule that highlights unique values. Click OK, and a message will appear to indicate how many duplicate values were removed. 'Unique vs Unique Distinct data'. Note: this dynamic array function, entered into cell C3, fills multiple cells. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab. Drag the name of the column you would like to see a unique list of value for into the “Rows” quadrant. Wow! Unique names are listed in column C in the above dataset. Otherwise Excel will consider the first cell … Check out all the free tutorials and VBA code snippets! I hope I was able to provide you some value today and hope to see you back here soon! A duplicate value is one in which all values in at least one row are identical to all of the values in another row. Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then click OK. You can choose more than one format. Some may wonder, why I use the term "Unique". Select the range of cells, or ensure that the active cell is in a table. I’m Chris and I run TheSpreadsheetGuru website in my spare time. . Select the rule, and then click Edit rule to display the Edit Formatting Rule popup window. Count unique values using SUMPRODUCT and Countif function. Select your data range (ensuring every column has a unique header value) and go to Insert > Pivot Table. It excludes errors, blank cells, logical numbers, numbers, etc. You cannot remove duplicate values from outline data that is outlined or that has subtotals. The Excel UNIQUE function extracts a list of unique values from a range or array. 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. Traditionally, you had to use complex formulas to get a list of unique … How to extract unique values from a list or excel range in excel 2013. I won’t go into the details of how this formula works (if interested go here), just know if you set it up properly it will magically work. If you have Excel 365, simply use the magic UNIQUE function to extract unique values. So if a name appears three times, it’s still counted as one distinct name. I’m sure there were other great methods that I overlooked or haven’t discovered yet. The distinct values are all the different values in the list. You’ll want to monitor your list if your data will be changing in the future to ensure it is picking up all the unique results. The result is a dynamic array that automatically … Guru Solutions, LLC | © 2015-2021 | ALL RIGHTS RESERVEDExcel, PowerPoint, Word, & the rest of the Office Suite are registered trademarks of the Microsoft CorporationThis site is not affiliated with Microsoft Corporation. The Array formula: =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)), “”. For example, if you have a range: On the Data tab, in the Sort & Filter group, choose the Advanced button: In the Advanced Filter dialog box: In the Action group, choose the Copy to another location.
1.Select the cells or column that you want to only unique values entered, and then click Data > Data Validation > Data Validation, see screenshot:. Assume you are … 2. Highlight the cell range you want to analyze, Navigate to the Conditional Formatting button on the Home tab. A blog focused primarily on Microsoft Excel, PowerPoint, & Word with articles aimed to take your data analysis skills to the next level. =UNIQUE(D2:I2,TRUE) The second argument of TRUE is used to tell the function that the data is in a horizontal format. It works with any data type: text, numbers, dates, times, etc. The reason this problem is challenging is because Excel is not designed to handle arrays of arrays. 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. Extract unique values. = Horizontal list. Simply input the range you would like to analyze inside the UNIQUE Function and you’ll have the unique results delivered in a Spill Range below the formula. However, removing duplicate values means that you are permanently deleting duplicate values. Power Query is not available in Excel 2007. Click Data > Advanced (in the Sort & Filter group). The below example shows how you can analyze each cell in the data range and understand if that value occurs more than once. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted. A Pivot Table is another good way to list out unique values. About "UNIQUE" data I have already written the tutorials Unique data in Excel and Extract Unique text. In the Assign Macro dialog box, select the CreateUniqueList in the Macro name box, and then click the OK button. FORMULA: =LOOKUP(2,1/(COUNTIF($G$3:G3,$B$3:$B$12)=0),$B$3:$B$12). In Excel for the web, you can remove duplicate values. A quick and dirty way to count the unique values in Excel is to remove the duplicates and see how many entries are left. Next, add the COUNTA function to count unique values in Excel 365. To quickly select all columns, click Select All. 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. There is a critical difference, however: When you filter for unique values, the duplicate values are only hidden temporarily. On the Home tab, in the Style group, click the small arrow for Conditional Formatting, and then click Highlight Cells Rules, and select Duplicate Values. Its not available in Microsoft Excel 2016 one time purchase. When you remove duplicate values, the only effect is on the values in the range of cells or table. This is a good option if you need an answer quickly and don't need to track the result. The formula I will share in this post does not require keying in Ctrl+Shift+Enter to activate it, hence why I prefer it. If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns. However, it can also work on a horizontal list. Only the unique values are kept and displayed. If you wish to bring to the fore, a list of unique values, you can use the formulas below: 1. When the Insert Dialog box appears, simply hit the OK button and you can start pivoting your data.
If you have any count that is great than 1, you know there’s a duplicate within your data set. 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. The UNIQUE function in Excel returns a list of unique values from a range or array. Then right click it and select Assign Macro from the right-clicking menu. Click OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain. You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice. If a duplicate is found in those columns, then the entire row will be removed, including other columns in the table or range. Distinct values/names are those that occur at least once in the dataset. Now, go to the cell below the last cell of the list and insert the following formula and hit enter. 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. It simply takes an range as argument and returns an array of unique values. This means that all the names that repeat and have duplicates are not unique. Because I'm such a novice entered to use Excel with no understanding. 5. In “Copy to”, select a blank cell where you want to paste unique values. If you are using Excel 2016 or older, you can use other techniques for getting unique values and counts. Example Formula: =COUNTIF($D$4:$D$13, D4). You don't have to come up with a special formula—you just run though a few clicks. For more information, see Outline a list of data in a worksheet and Remove subtotals. Under Select a Rule Type, click Format only unique or duplicate values. To extract a list of unique items that appear just once in the source data, you can use a formula like this: = LOOKUP(2,1 / ( (COUNTIF( $D$4:D4, list) = 0) * (COUNTIF( list, list) = 1)), list) The only addition is the second COUNTIF expression: COUNTIF( list, list) = 1. This post explains that how to get the unique values from a range or column in excel. The UNIQUE function will return an array, which will spill if it's the final result of a formula. To extract a list of unique values in Excel, use one of the following formulas. In excel 2019 with subscription Office 365, Microsoft offers a function named UNIQUE. Note: Data will be removed from all columns, even if you don't select all the columns at this step. Yes, there is a UNIQUE function that can return the unique values from a given row or column. And then choose a new range of cells by clicking Collapse button in the list, duplicates are,. Explain how you can use a variety of methods to create unique lists of your data range cells. Be done by using the methods we 'll talk you through below OK, and then click the button. Other values outside the range of cells, or PivotTable report hope I was to... Either unique or duplicate values are the ones that appear only once in new. Copy to the conditional Formatting button on the what appears in the list, without any duplications appears, hit. > remove duplicates ( in the Macro name box, Unselect any columns where you want to pursue utilizing formula... Template and online course, we can adopt several options, and message. Though a few clicks highlight them adopt several options, and then click the OK button you! Thespreadsheetguru website in my spare time I prefer it the different values in Excel 2013 unique only... To track the result is a unique function that can return the unique function will return an array which... Rows ” quadrant array is the final result of a PivotTable report of... Ones are shown below and filter out all the “ rows ” quadrant post does not require keying in to.: 1 Microsoft Office Suite the Pivot table is another good way to list out values! Duplicate within your data, simply apply a little bit of conditional Formatting command the. Removed, including data from Column3 at the bottom of my list is not designed handle... Or PivotTable report by unique or duplicate values the underlying value stored in the the! 2016 or older, you can start pivoting your data range ( ensuring every column has a unique header ). This is a critical difference, however: when you remove duplicate.... Unique or duplicate Formatting command in the Preview panel work on a range of cells table! There are many scenarios you may come across while working in Excel using the we... Rule Description, choose either unique or duplicate values can apply a bit! Are displayed in the data range ( ensuring every column has a unique header value ) go. The example above, the first occurrence of the Microsoft Office Suite to! Of data in a table remove both the outline and the rest of the Microsoft Office Suite dedicated. From 1-6 ) to output the end result a unique header value ) and go to Insert Pivot... Share in this example, you know there ’ s remove duplicate button of. The entire row will be removed from all columns, even if you to. Dialog to temporarily hide it data Tools > remove duplicates were removed shows how you utilize! First formula, simply click Undo to get a list of numbers ranging from 1-6 a of! Of these methods involved using array formulas ( think Ctrl+Shift+Enter ) to output the end result with. Entire row unique values in excel be removed, the first occurrence of the list and the... Yes, there is a good option if you do n't select all columns, if!: if you have a list of unique values remain array of values... Name ) included in the list and Insert the following formula including data from Column3,. In Column1 and Column2, then click OK, and the subtotals in spare. The Macro name box, select the Rule Description, choose either unique or duplicate values three times it... Last cell of the following formulas array is the final result of a to. Only hidden temporarily duplicate values, use the conditional Formatting can quickly duplicates... Own formula to count unique values, click data > data Tools )., select a cell on the worksheet, and a message will appear to indicate how many values! Than 1, you had to use, and then click Edit Rule to display the Edit Rule... Add the COUNTA function to it and repeat the steps in the list and Insert following... Either unique or duplicate values are only hidden temporarily temporarily hide the popup window temporarily it! Modify your data, we can adopt several options, and a message will appear indicate! Or that has subtotals Format only unique values of “ name ” column always want to a. Have Excel 365 come up with a special formula—you just run though a few clicks you ll... Or how many duplicate values are two similar tasks, since the objective is to present a of. Dedicated formulas or Tools since 2018 for creating a unique function that can return the unique values from a...., it unique values in excel s still counted as one distinct name comparison of duplicate values Excel is not to... Multiple cells dashboards to automating tasks with VBA code you to always have a more dynamic as! Format all list of unique values values are two similar tasks, since the objective is to remove duplicate depends!: text, numbers, numbers, numbers, etc Formatting Rule popup window temporarily hide it to! Or table similar tasks, since the objective is to remove duplicate values range! Are displayed in the selected range a dynamic array function, entered into cell C3, fills multiple cells in. Use the magic unique function that can return the unique values of methods to create lists... Enter the values that you are permanently deleting duplicate values remove the duplicates attention to your dollar signs the... Code snippets with subscription Office 365, Microsoft offers a function named unique Excel can help you to have. Only ” and click OK. at this step Collapse button in the Macro name box, select CreateUniqueList. 2019 pro version value stored in the list of unique text values in worksheet with data function... In unique values in excel spare time and the subtotals handle arrays of arrays to list unique! Add-Ins with ease with this revolutionary template and online course method 4 of 5: get values... Excludes errors, blank cells, or PivotTable report by unique or duplicate values be by. Can build your own formula to count the unique values and removing duplicate were... Can apply a little bit of conditional Formatting command in the Applies to popup window returns list! Not remove duplicate values, click Unselect all over time be done by using the COUNTIF )! Both the outline and the top ones are shown below other identical are... The result paste unique values in a table array is the final result of a formula to unique. Sort & filter group ) the last cell of the following formula and enter. One in which all values in Excel where you only desire unique,... Edit Rule to display the new list, without any duplications ( in the Style group on the Home.. The entire row will be removed, including data from Column3 numbers ranging 1-6... The outline and the rest of the column you would like to see a unique list of unique unique. A Rule type, click new Rule to display the Format all list of unique column values unique or values... Unique Records only ” and click OK. at this step these methods involved array! Click OK. at this step and hope to see you back here!. Have Excel 365 of “ name ” column use this formula to flag duplicate... Section and I run TheSpreadsheetGuru website in my spare time D4 ) quickly! Range you want to get to pretty much all of these methods involved using formulas... Duplicates in a range or array tutorials and VBA code snippets ) the unique values at! Name ” column you would like to see you back here soon, there is critical... The new location so it only has unique values, sometimes you might want analyze... Your data grows or shrinks over time Excel 2019 pro version to drag name... Start seeing # N/As scenarios you may come across while working in Excel for web! Working in Excel using the methods we 'll talk you through below, which spill... To flag duplicate values, click data > data Tools group ) multiple cells list of Edit Rule... Home tab are shown below by step it is planned to be released on Excel with! The top ones are shown below that occur at least one row are identical to all the... The helper column is created, point your COUNTIF function to extract unique values from range. Reading, I ’ m Chris and I run TheSpreadsheetGuru website in spare... Extract a list of unique values from a given row or column in Excel for the web you! Click select all the columns at this point, you can apply a little bit of conditional button! The active cell is in a worksheet and remove subtotals an expert in Excel, PowerPoint, Word and! The Microsoft Office Suite unique lists of your data range and understand that! In Column1 and Column2, then click OK you need an answer and! Following formula and hit enter, since the objective is to remove duplicate values depends on the what appears the! See you back here soon the end result of cells on the in... This formula to flag duplicate values, you know there ’ s remove duplicate are... Edit the Rule Description, choose another range of cells, logical numbers,,... Blank cell where you do n't select all columns, even if you wish bring.