#37 Extract Unique Values with a CSE Formula

This example is way beyond the scope of this book. There is a secret type of formula in Excel that requires you to press Ctrl+Shift+Enter in order to unlock the powers of the formula.

If you or I needed to get a unique list of values from column B, we would do something such as use an advanced filter or a pivot table or copy the data and use Remove Duplicates. These methods take five seconds and are easy for you and me.

The problem rears its head when you need your manager’s manager to use the spreadsheet. You cannot hope that the VP of Sales is going to master doing a Copy and Remove Duplicates. You need a way to have live formulas that will always be extracting unique lists of values.

The formulas to do this are absolutely insane. But they work. In the figure below, a long formula in D2 figures out how many unique values are in the list. An even longer formula in D5 that is copied down extracts the unique list.

Here is the formula. I won’t try to explain it to you.

But I will do the next best thing. I will introduce you to someone who can explain it to you. Mike Girvin has produced thousands of Excel videos on the YouTube channel ExcelisFun. He has also written a few Excel books, including Ctrl+Shift+Enter, which is a complete guide to these amazing formulas. In the book, Mike explains many formulas in detail so you can understand how they work and write your own. If you are ever about to give up on a formula because it can’t be done, there is a good chance the formulas in Mike’s book will solve it.

Thanks to Mike Girvin, Olga Kryuchkova, and @canalyze_it for suggesting this feature.

While I am promoting Mike Girvin‘s book, I should mention that you should check out is ExcelisFun YouTube channel, where he has thousands of free amazing videos. Mike and I have done a series of fun Dueling Excel videos, where we show various ways to solve problems in Excel.