MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Excel 2020: Formula for Unique or Distinct

September 21, 2020 - by Bill Jelen

Excel Formula for Unique or Distinct. Photo Credit: rawpixel at Unsplash.com

The UNIQUE function will provide either a list of unique or distinct values.

=UNIQUE has three arguments: array, by_col and occurs_once. Only the array is required.

If you ask me for the list of unique values from this list: Apple, Apple, Banana, Cherry, Cherry, I would tell you that the list of unique values is Apple, Banana, Cherry. If you ask 100 accountants, about 92 of them would agree with me. But there is a segment of the population who disagrees and says that the only unique thing in the list is Banana because it is the only item that appears once.

continue reading »

More Reading

Excel Understanding Array Constants. Photo Credit: Dan Gold at Unsplash.com

Excel 2020: Understanding Array Constants »

September 17, 2020 - by Bill Jelen

There are several dynamic array that get better with an array constant. Here is a simple way to understand them. A comma inside an array constant means to move to the next column. A semi-colon means to move to the next row. How do you remember which is which?

Excel Filter with a Formula. Photo Credit: Ben Wicks at Unsplash.com

Excel 2020: Filter with a Formula »

September 16, 2020 - by Bill Jelen

The FILTER function is new as part of the dynamic arrays feature. There are three arguments: array, include, and an optional [if empty].

read more articles »

Supercharge your career with training from MrExcel

Featured Products

Excel Data Cleansing Straight To The Point

Excel Data Cleansing Straight To The Point »

This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy.

The Absolute Guide to Dashboarding & Reporting with Power BI

The Absolute Guide to Dashboarding & Reporting with Power BI »

Learn Power BI Dashboarding Techniques by Watching A Pro!

Microsoft Excel 2019 Inside Out

Microsoft Excel 2019 Inside Out »

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.

Excel Custom Functions Straight to the Point

Excel Custom Functions Straight to the Point »

Excel Custom Functions can be used like any other native functions or user defined functions in Excel. This book shows the process of creating Excel Custom Functions.

Introduction to Financial Modelling

Introduction to Financial Modelling »

With over 50 examples and an extended case study that creates a simple financial model from scratch to highlight the key concepts, this is a "hands on" book, focused on working with Excel more efficiently and effectively.

MrExcel 2020 - Seeing Excel Clearly

MrExcel 2020 - Seeing Excel Clearly »

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.

see more products »