UNIQUE function - quick way to find unique values in Excel
Vinayak Jadhav
?? Certified Microsoft Power BI Data Analyst Associate?? Certified Microsoft Office Specialist? Non IT Professional Driving Digital Transformation ?? Generate your several Income sources
The introduction of the UNIQUE function in Excel 365 has changed everything! What used to be a rocket science becomes as easy as ABC. Now, you don't need to be a formula expert to get unique values from a range, based on one or multiple criteria, and arrange the results in alphabetical order. All is done with simple formulas that everyone can read and adjust for your own needs.
Excel UNIQUE function
The UNIQUE function in Excel returns a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc.
The function is categorized under Dynamic Arrays Functions. The result is a dynamic array that automatically spills into the neighboring cells vertically or horizontally.
The syntax of the Excel UNIQUE function is as follows:
UNIQUE(array, [by_col], [exactly_once])
Where:
Array (required) - the range or array from which to return unique values.
By_col (optional) - a logical value indicating how to compare data:
Exactly once (optional) - a logical value that defines what values are considered unique:
领英推荐
Basic UNIQUE formula in Excel
Below is an Excel unique values formula in its simplest form.
The goal is to extract a list of unique names from the range B2:B10. For this, we enter the following formula in D2:
=UNIQUE(B2:B10)
Please notice that the 2nd and 3rd arguments are omitted because the defaults work perfectly in our case - we are comparing the rows against each other and wish to return all the different names in the range.
When you press the Enter key to complete the formula, Excel will output the first found name in D2 spilling the other names into the cells below. As the result, you have all the unique values in a column:
In case your data is across the columns from B2 to I2, set the 2nd argument to TRUE to compare the columns against each other:
=UNIQUE(B2:I2,TRUE)
Type the above formula in B4, press Enter, and the results will spill horizontally into the cells to the right. Thus, you'll get the unique values in a row:
Follow me for emerging tech, leadership and growth topics | World Champion turned Cyberpreneur | Co-Founder & CEO, TRUSTBYTES
8 个月Unlock the power of data analytics to drive game-changing insights and make informed decisions.?
Computer science Student | Software developer | Passionate about Innovation,AI ,and Digital Marketing
8 个月Congratulations on the 16th edition of "Mastering Data Analytics"! Always looking forward to your insights and expertise. Keep up the great work!Vinayak Jadhav
Web Designer & Developer | Digital Marketing | BNI Member| Lead Generation Websites | Accelerating Business Growth with Strategic Online Solutions | Philadelphia, PA
8 个月Exploring unique functions in Excel for data analytics is crucial! Your newsletter seems like a valuable resource for mastering these skills. Looking forward to more insights.
19K Followers l Founder l Keynote Speaker | Author | Women Leadership in Industry - Digital Marketing l Most Influential Digital Marketer | AI in Marketing l Corporate & AI Governance l ESG l Digital Transformation
8 个月That's exciting! I'm always interested in learning more about data analytics. What are some of the topics you'll be covering in this edition of "Mastering Data Analytics"? Looking forward to reading it!
Owner, Director | Cyber Security Graduate, Microsoft Certified Systems Engineer, Expert in Apple Computing
8 个月The UNIQUE function is a valuable addition to Excel's formula repertoire, and its applications are numerous, including data cleaning, data visualization, and data summarization.