The range of cells that contains the results is called the spill range. Here is the help page on the UNIQUE function to learn more about it.įor those who need to remove duplicates today and don't have the luxury of waiting for the UNIQUE function to roll out, here's a post that covers 3 ways to remove duplicates and create a list of unique values.

Set it to True to return values that only occur once. Default value is False, to return all unique values. – Allows you to only return values that occur once in the array (range).Default value is False, to compare by rows. – Allows you to compare by rows or columns when the array is multiple columns wide.The UNIQUE function has additional optional arguments as well: When the formula is entered, the results will automatically spill down into the cells below. To create a list of unique values, you simply reference the range that contains duplicates in the array argument for UNIQUE. With the new UNIQUE function you'll be able to create a list of unique values (remove duplicate entries) using a very simple formula. Let's take a look at how much easier this will be with the new dynamic array function, UNIQUE. If you don't have the new functionality yet, checkout this post by my friend Dave at Exceljet on how to the CSE array formula to return unique values. The image below shows a CSE array formula, enclosed in curly brackets, that can be used to create a list of unique values (remove duplicates). Here's a post and video where I explain more about them.

Don't worry, that will be a long goodbye.ĬSE formulas are much more complex, and we usually have to guess at how many cells we need to copy them to. The goal of this new functionality is to eventually replace array formulas that we input with Ctrl+Shift+Enter (CSE). Plus, existing functions can utilize this same spill range functionality. We can use these to create a list of unique values (remove duplicates), sort a list, output a filtered range of data, and so much more. This is called the spill range, and I explain more about it below.Įxcel currently has 7 new dynamic array functions, with more on the way.

The new dynamic array formulas allow us to return multiple results to a range of cells based on one formula. Microsoft just announced a new feature for Excel that will change the way we work with formulas. Dynamic-Arrays-Example.xlsx Download Dynamic Array Functions & Formulas

Openoffice calc dynamic drop down list