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.
#Get rid of the #value errors for linest in excel for mac how to#
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. The Insiders program is free for all Office 365 subscribers and gives you access to early release builds and features.ĭynamic Arrays Example.xlsx (151.5 KB) Dynamic Array Functions & Formulas Unfortunately, these functions are only available to a portion of users on Microsoft's Office Insiders Program. Bottom Line: Learn about the new Dynamic Array functions and formulas that will eventually replace the Ctrl+Shift+Enter array formulas.
0 kommentar(er)
