Excel distinct. We can also use the Excel LOOKUP and COUNTIF functions together to get the u...

Excel distinct. We can also use the Excel LOOKUP and COUNTIF functions together to get the unique values from a range or column. You'll learn how to generate distinct lists, which display all Aug 30, 2024 · How to Count Unique Values in Excel (and Distinct Values) Are you tired of manually counting the unique and distinct values in your Excel spreadsheet? Don’t worry; there’s a much easier way! In this guide, we’ll show you how to use Excel’s built-in functions to count unique and distinct values. Let's look at how it works, how to use it, and how to avoid certain pitfalls. In this tutorial, you’ll learn the difference betwee In this lesson, we will explore using the UNIQUE function in Excel 365 to extract unique and distinct values by columns instead of rows. The example below uses INDEX, MATCH and COUNTIF to generate the list. UNIQUE function is purposefully made to get a distinct list of values. If you have a column in Excel for "Country" and listed are USA, USA, France, France, Belgium, Spain, Belgium, and France This section demonstrates how to highlight unique distinct values using Excel's built-in feature "Conditional Formatting". Step 3: Check Unique records only and press OK. Apr 25, 2024 · The results of DISTINCT are affected by the current filter context. Dec 24, 2025 · 8 Ways to Count Unique Values in Microsoft Excel Do you want to count the number of unique values in your Excel data? Knowing the number of unique items in a column when performing data analysis in Excel can provide you with an invaluable overview of your data. In this tutorial, I will show you how to count distinct values as well as Unique Values in an Excel Pivot table. An example of a distinct count is we had five orders in one day, but three orders were placed by the same customer. Nov 21, 2022 · This tutorial demonstrates how to find unique values in Excel and Google Sheets. Mar 21, 2023 · The tutorial demonstrates the most efficient ways to find, filter, select and highlight distinct and unique values in Excel. Feb 16, 2026 · In Excel 2013, and later versions, if you add a pivot table's source data to the workbook's Data Model, it's easy to create a unique count. See examples and tips for various scenarios and data types. chatGPT, BARD, GPT4 etc) to create forum answers is not permitted. Get expert tips, ask questions, and share your love for all things Excel. Extracting values Example 4 This example compares two columns and returns only the unique values between them. By Function. Unique In Excel Formula, Excel Formula For Unique Values, Excel Paste Unique Values And More Example 4 This example compares two columns and returns only the unique values between them. Before the introduction of the UNIQUE function, there wasn’t any function that allowed picking out unique values from a dataset so seamlessly. Jan 24, 2023 · Learn how to use the UNIQUE function in Excel to return a distinct list of values from a range or array. values that appear just once), you can use the UNIQUE function. Apr 19, 2021 · Get distinct values in Excel by By Formula UNIQUE function is purposefully made to get a distinct list of values. Now this may be a very tricky and time consuming task especially when the lists are very long. Jan 12, 2024 · With unique values in Excel, you can automate tasks such as finding the outliers in your data. Aug 4, 2024 · 4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel. g. Advanced Filter to Get Unique Values From Range. It extracts unique values from a range or array, removing duplicates. You can use the Advanced Filter under the Data ribbon to get all the unique values in a column or range. ) Jun 23, 2024 · How to find unique values from multiple columns in Excel. Aug 14, 2024 · Learn how to create a drop-down list with unique values in Excel using Pivot Table, Remove Duplicates, UNIQUE, and Helper Functions!!! Oct 20, 2018 · How can we list duplicate values in Excel? This post will tell you how. Aug 12, 2024 · A wholesome article on how to extract unique values based on criteria in excel with 2 effective methods. Aug 13, 2024 · To extract unique values from more than one range at the same time, you can use the UNIQUE function with the VSTACK function. Example 4 This example compares two columns and returns only the unique values between them. Feb 3, 2025 · Count unique values Count unique values Count unique values (case sensitive) All Excel versions Count unique distinct values within same week, month or year Count unique distinct values that meet multiple criteria Introduction How to count unique distinct items based on a condition and a date condition - Excel 365? Aug 16, 2021 · Figure B How to use the UNIQUE () function in Excel If you’re using Microsoft 365 or one of the 2019 standalone versions of Excel, you can quickly create a dynamic list using the UNIQUE () function. Note: this formula requires Dynamic Array Formulas, available only in Excel 365. In the example shown, the formula in D5 is: =UNIQUE(FILTER(B5:B16,B5:B16<>"")) which outputs the 5 unique values seen in D5:D9. Learn how to quickly get a distinct list by using Advanced Filter, and how to extract unique rows in Excel. Mar 13, 2026 · Discover how to excel count of unique values effortlessly using advanced Excel techniques. So if “UC San Diego” appears 4 times, the output will be . Get distinct values by Advanced Filter. Learn all about Excel Count Distinct values with practical guides, formula insights, and alternative tools. It can handle various data types, including text, numbers, dates, and times. Jan 29, 2026 · The Excel UNIQUE function extracts unique values from a range or array. This tutorial describes multiple ways to extract unique values from a column in Excel. This statistic can give you a useful overview of the data and help you spot errors or inconsistencies. Learn tips on using pivot tables and built-in functions for efficient data analysis. Find out how to exclude empty cells and improve performance for large datasets. Get distinct values by Remove Duplicates. Count Unique Values using UNIQUE and COUNTA. Step 2: Select Advanced. Example 4 This example compares two columns and returns only the unique values between them. UNIQUE is not case-sensitive and works with text, numbers, dates, and other data types. Works on all versions of Excel. Fortunately, we can use a formula to replicate the UNIQUE function. Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells. Watch short videos about unique excel from people around the world. Apr 4, 2025 · The UNIQUE function is a powerful dynamic array function in Excel. If you have Excel 365 or Excel 2021, use the magic UNIQUE function. It is part of Excel’s dynamic array feature, meaning results spill automatically into neighboring cells. Mar 21, 2023 · See how to count unique values in Excel with a formula and get an automatic count of all different values in a pivot table. With an older Count Unique Values using UNIQUE and COUNTA. Oct 17, 2025 · Microsoft Excel's UNIQUE function does exactly as its name suggests—it extracts unique values from an array. It identifies unique entries and removes duplicates, providing a distinct list of values. com Learn how to count distinct values in Excel with the COUNTIF function. Find unique values from multiple columns in Excel in just few seconds. Get the sample file to practice. Jun 16, 2024 · Learn in Excel to get unique values from range using Advanced Filter, INDEX & MATCH, LOOKUP & COUNTIF, UNIQUE, VBA, Remove Duplicates. Jul 25, 2025 · Discover how to use Excel UNIQUE() to instantly remove duplicates and extract distinct values from any list. Need more help? You can always ask an expert in the Excel Tech Community or get support in Communities. This function is especially valuable in consolidating data and identifying distinct entries in a dataset. Unique In Excel Formula, Excel Formula For Unique Values, Excel Paste Unique Values And More Feb 13, 2023 · While working on different lists in excel you need to identify Excel Distinct Values (2016/2019/mac/online) so that you can find out all different/unique/distinct sorts of entries on any list and omitting all the repeated entries. We can also use the Excel INDEX and MATCH functions together to get the unique values from a range or column. Jan 6, 2025 · To count unique values in a set of data, you can use the UNIQUE function together with the COUNTA function. Ideal for mastering unique value counting in Excel. Sometimes the range we want to extract the unique values from might have some empty cells. Learn 5 methods to count unique values in Excel using formulas. Say you have the list of names, shown below, with duplicates. Apr 12, 2023 · See how to get unique values in Excel with the UNIQUE function and dynamic arrays. For example, if you use the formula in the following example to create a measure, the results would change whenever the table was filtered to show only a particular region or a time period. Dec 14, 2024 · Learn how to use the distinct count feature in Excel pivot tables to accurately count unique values and avoid inflated results from duplicates. Feb 1, 2025 · Excel’s UNIQUE function allows users to extract unique values from a dataset, making it easier to identify and remove duplicates. This tutorial provides a comprehensive guide to using UNIQUE with detailed examples. Dec 24, 2025 · 9 Ways to Count Distinct Values in Microsoft Excel Do you want to count distinct values in a list in Excel? When performing any data analysis in Excel you will often want to know the number of distinct items in a column. We can use an array formula to extract a list of unique values from a column. Then you can use the ROWS function to count the number of items in the new range. Jan 23, 2026 · The UNIQUE function extracts distinct values from a range or array by automatically removing duplicates. Jun 7, 2023 · This tutorial explains how to get distinct values in a column in Excel, including several examples. @excelsheetsacademy Count Distinct Values in 10 Seconds Using Excel! 💪🏼 #excel 4. Step 2: Select Remove Duplicates. The result is a dynamic array that automatically updates when source data changes. =UNIQUE(A1:A7) Result. . If you need to extract unique values from non-adjacent columns (such as column A and column C, skipping column B), nest the CHOOSECOLS function (Excel for Microsoft 365 and Excel for the web) inside your formula: =UNIQUE (CHOOSECOLS (T_Signups, 1, 3)). Apr 3, 2025 · To count the number of unique or distinct values among duplicates in Excel or Excel 365 / 2021, here provides some easy and effective formulas for you. This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules. Aug 25, 2022 · To extract a list of distinct values from a set of data (i. It returns a list of distinct values from a specified range or list. Apr 6, 2022 · When counting unique values in Microsoft Excel, use UNIQUE() as a helper function and avoid more complicated expressions. We would like to show you a description here but the site won’t allow us. The Excel UNIQUE function extracts unique values from a range or array, allowing for efficient data cleaning and organization. In the example shown, the formula in E5 is: =SORT(UNIQUE(B5:C15)) which return the 6 unique rows in the range B5:C15, sorted by group. Jul 23, 2025 · This article provides a comprehensive guide on how to count distinct Excel pivot table values, use formulas to count unique Excel entries and learn Excel count distinct value features for better data organization. In the example shown, the formula in F6 is: =SUMPRODUCT (1/COUNTIF (B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE. Step 1: Select the data range. Elevate your spreadsheet skills with us! Grow your business with Enxea: https://www. May 18, 2022 · Now, let’s use this function to create a unique list. This function is helpful for data analysis, simplifies lists, and ensures data accuracy by eliminating repeated values. To get a distinct count, I use: =1/COUNTIF ( {university column}, {university cell ref}) So for example: =1/COUNTIF (F:F, F2) This equation takes the number of times that the university name appears in column F and divides 1 by that number. Download our practice workbook for free, and exercise! Oct 20, 2018 · How can we list duplicate values in Excel? This post will tell you how. We often use this feature to analyze an input dataset that contains several categorical variables. Note: Pivot tables based on the Data Model have a few limitations In this video, I show how to calculate a distinct count with the Excel Data Model, and there are written steps below the video. Not every Excel users get to use this wonderful function. Aug 26, 2022 · To extract a list of unique values from a set of data, ignoring blank cells, you can use the UNIQUE function together with the FILTER function. UNIQUE(array, [by_col], [occurs_once]) Formula of Cell B1. The UNIQUE function then extracts unique values from the filtered result. Forum Microsoft Office Application Help - Excel Help forum Excel Formulas & Functions [SOLVED] Extract and return a distinct and duplicate items list separately from a two list items The use of AI tools (e. Jan 7, 2022 · I'm not sure how to program a formula that allows me to return all the distinct values of column A in sheet 1, and put it in column A of sheet 2 (the summary sheet. The UNIQUE function in Excel extracts unique values from a list. But of all the functions in Excel, there isn’t a straightforward “Distinct Count” unfortunately. 9K Dislike 17 Oct 17, 2025 · Microsoft Excel's UNIQUE function does exactly as its name suggests—it extracts unique values from an array. 3- Press Enter. Formula examples to extract unique values from a range, based on multiple criteria, sort the results alphabetically, and more. The only downside is that UNIQUE() is currently available in certain version of Excel. In the example shown, the formula in F5 is: =COUNTA(UNIQUE(B5:B16)) which returns 7, since there are seven unique colors in B5:B16. Find Unique Values Using Advanced Filter The first option to find and extract unique values in Excel is to use an advanced filter. Here, we will show you how to find them. You'll learn basic syntax, practical applications, and advanced techniques to master this essential Excel function. Apr 29, 2024 · We use the UNIQUE, FILTER, MATCH, and INDEX functions, Excel features as well as VBA Macro code to filter unique in Excel. In the example shown, the formula in D5 is: =UNIQUE(B5:B16,FALSE,TRUE) which outputs the 2 distinct values in the data, "purple", and "gray". Aug 29, 2024 · How to Find Unique Values in Excel (UNIQUE Function) Not that it was impossible to find and sort out unique values in Excel – but yes, it was a task if not a hell of a task 🤕 Especially if it was to be done through functions. Aug 22, 2017 · Excel Array Formula to Extract a List of Unique Values from a Column Update: If you have Office 365 you can use the new UNIQUE Function to extract a distinct or unique list. Note: Create an empty column at right side of your existing column before applying the formula. If you have a column in Excel for "Country" and listed are USA, USA, France, France, Belgium, Spain, Belgium, and France A vibrant community of Excel enthusiasts. The only downside is that UNIQUE () is currently available in certain version of Excel. Aug 21, 2022 · To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. How to create a unique list in Excel Microsoft Excel supports two different dropdowns: Data Validation and a combo box control. Use the LOOKUP and COUNTIF Formula to Get Unique Values From Range. 25. PivotTables in Excel can create a Distinct Count. Insert the INDEX and MATCH Formula to Get Unique Values From Range. In the example shown, the formula in D5 is: =UNIQUE(B5:B16) which outputs the 7 unique values seen in D5:D11. In this lesson, we dive deep into the differences between unique and distinct values in Excel 365 using the powerful UNIQUE function. Dec 23, 2025 · Learn how to count unique values in Excel with proven methods: formulas, PivotTables, and VBA. Elevate your spreadsheet skills with us! Mar 21, 2023 · See how to get unique values in Excel with formulas. VLOOKUP - Return multiple unique distinct values Ahmed Ali asks: How to return multiple values using VLOOKUP in Excel and removing duplicates? I have tried the formula to return multiple values using the index example and worked fine with no duplicate item but how can I list them without the duplicate? Answer: The following array formula is easier to understand than a VLOOKUP formula Aug 29, 2024 · How to Find Unique Values in Excel (UNIQUE Function) Not that it was impossible to find and sort out unique values in Excel – but yes, it was a task if not a hell of a task 🤕 Especially if it was to be done through functions. Jul 6, 2024 · Need to learn how to use the COUNTIFS function to count unique values in Excel? We'll show 4 easy methods with proper illustrations to do it. But luckily enough there exist quite simple methods in excel that can Nov 12, 2025 · To count unique values with one or more conditions, you can use a formula based on UNIQUE, LEN, and FILTER. Here you will learn how to get distinct value in Excel 2013 and above using 3 ways. enxea. They help you to determine the frequency of specific values and can be particularly useful in quality control by ensuring data integrity through the identification of duplicates which, once removed, can lead to more accurate and The terms unique and distinct are sometimes used interchangeably; however, they have different meanings. See examples, arguments, tips and tricks for this new function. Plus, handle conditions, data types, and distinct values. Jul 27, 2024 · Method 5 – Using the UNIQUE Function in Excel The UNIQUE function in Excel is an essential method for finding unique values in a column. Unique values in your Excel spreadsheets can be a goldmine of insights, allowing you to uncover patterns and outliers that could inform your decisions and strategies. Ensure that the first row in the column has a column header. Learn how to apply it to both rows and columns. Result. UNIQUE function Syntax. Jul 28, 2024 · The Excel UNIQUE function identifies and extracts unique values within a given range or list. This section demonstrates how to highlight unique distinct values using Excel's built-in feature "Conditional Formatting". Step 3: Press Remove Duplicates. In the example shown, the formula in cell H5 is: =UNIQUE(VSTACK(range1,range2,range3)) Where range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13) are named ranges. Jun 23, 2024 · How to find unique values from multiple columns in Excel. Extracting values Jul 30, 2021 · To extract a list of unique rows from a set of data, you can use the UNIQUE function together with the SORT function. Sep 22, 2022 · In some applications, like Power BI, you can get a distinct count of unique values in a column in just a click or two. Count the number of unique values by using a filter You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. You can use it to extract both unique and distinct values, as well as compare columns or rows. The image shows you unique distinct values highlighted using Conditional Formatting. It allows you to obtain both strictly unique values and distinct values (where duplicates are removed). See how to apply the 'by column' argument to analyze a Jan 10, 2026 · The example above assumes your columns are adjacent. A vibrant community of Excel enthusiasts. In other words, you'll only see each value once in the result, even if it appears multiple times in the source. How to Select Distinct / Unique Values in Excel Tables A common task when working with data is to obtain unique values or unique combinations of values in selected variables - columns of the table. Mar 21, 2023 · See how to get unique values in Excel with formulas. Excel's UNIQUE function returns a list of unique values in a list or range. You may be interested in How To Reverse Concatenate In Excel (3 ways)? By Formula. Mar 21, 2023 · Learn the difference between unique and distinct values in Excel and how to use formulas, filters, conditional formatting and shortcuts to deal with them. Learn formulas for counting unique names, texts, numbers, case-sensitive unique values, and more. Jul 31, 2024 · You can easily find unique values in Excel and remove duplicates if you study this article on how to get unique values in Excel. In the example shown, the formula in H7 is: =SUM (-- (LEN (UNIQUE (FILTER (B6:B15,C6:C15=H6,"")))>0)) which returns 3, since there are three unique names in B6:B15 associated with Omega. To find unique values in Excel, use the Advanced Filter to extract unique values or filter for unique values. Dec 18, 2020 · To extract a list of unique values from a set of data, you can use the UNIQUE function. How to count the number of unique values in a range that contains duplicate values in Excel. Excel will display the unique values from column A in column B, starting from B1, and will automatically expand to include all unique values. Aug 8, 2024 · There are different ways to get Distinct value in Excel. Sep 16, 2009 · Getting unique values in Excel by using formulas only Asked 16 years, 6 months ago Modified 5 years, 7 months ago Viewed 417k times Feb 1, 2025 · 1. e. sqebcn xnd lslkv jnypj vtueain krxd gyiouyqk bevwmyh gno szjgdd