**Bottom Line:** Learn about the new Dynamic Array functions and formulas that will eventually replace the Ctrl+Shift+Enter array formulas.

**Skill Level:** Beginner

## Download the Excel File

Here's the file I use in the video. Unfortunately, these functions are only available to a portion of users on Microsoft's Office Insiders Program. So you might not have access just yet. The Insiders program is **free** for all Office 365 subscribers and gives you access to early release builds and features.

Dynamic Arrays Example.xlsx (151.5 KB)

## Dynamic Array Functions & Formulas

Microsoft just announced a new feature for Excel that will change the way we work with formulas. The new **dynamic array formulas** allow us to **return multiple results to a range of cells based on one formula**. This is called the **spill range**, and I explain more about it below.

Excel currently has **7 new dynamic array functions**, with more on the way. 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. Plus, existing functions can utilize this same spill range functionality.

## Goodbye Ctrl+Shift+Enter

The goal of this new functionality is to eventually replace array formulas that we input with Ctrl+Shift+Enter (CSE). Don't worry, that will be a long goodbye.

CSE formulas are much more complex, and we usually have to guess at how many cells we need to copy them to. Here's a post and video where I explain more about them.

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).

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.

Let's take a look at how much easier this will be with the new dynamic array function, UNIQUE.

## The UNIQUE Function in Excel

With the new UNIQUE function you'll be able to create a list of unique values (remove duplicate entries) using a very simple formula.

To create a list of unique values, you simply reference the range that contains duplicates in the array argument for UNIQUE.

When the formula is entered, the results will **automatically spill down** into the cells below.

The UNIQUE function has additional optional arguments as well:

- [by_col] – Allows you to compare by rows or columns when the array is multiple columns wide. Default value is False, to compare by rows.
- [occurs_once] – Allows you to only return values that occur once in the array (range). This is a great option. Default value is False, to return all unique values. Set it to True to return values that only occur once.

Here is the help page on the UNIQUE function to learn more about it.

For 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.

## The Spill Range

Get used to the term “spill” for Excel.

The range of cells that contains the results is called the **spill range**. This range can be multiple rows and/or columns, as you'll see in the examples below.

The spill range is brand new functionality in Excel that will make our lives much easier. Previously we had to use Ctrl+Shift+Enter array formulas, and try to guess how many cells to copy it to.

Excel is now going to do all that work for us!

When any cell in the spill range is selected, a blue line appears as a border around the range. What happens if something is blocking the spill range?

## #SPILL Error

If there is already data in the spill range, a #SPILL error will be returned. This indicates that the range where the results need to spill down is not completely blank.

The error box appears and allows you to select the cells that are obstructing the spill range. You can then move or delete those cells, and the formula will automatically re-spill.

## The SORT Function

SORT is another new and very useful function. This outputs a sorted list of the array (range) specified in the function's first argument.

SORT has additional optional arguments for [sort_index], [sort_order], [by_col]. Here is the help page on the SORT function to learn more.

## Spill Reference Notation – Spill Ref

In the example above you'll notice that I used C4# in the array argument for the SORT function.

This is referred to as a **Spill Ref. **It allows us to create a reference to the entire spill range by placing a # (hashtag or pound symbol) after the address of the first cell in the spill range.

There are a few ways to create a spill ref:

- Type or select the first cell in a spill range to create a reference to that cell. Then type the # after it. You will see a bounding box appear around the spill range.
- The other way is to select all the cells in the spill range. The spill ref will automatically be created.
- A quick keyboard shortcut for this is to select the first cell in the spill range, then hit
`Ctrl`+`Shift`+`Down Arrow`to select all the cells. This automatically creates the spill ref as well.

### Uses for Spill Refs

Spill refs are extremely useful. You can use them as the source range for other dynamic array formulas, as I did above with sorting the list of unique values.

You can also use them for regular formulas if you want to do a calculation (SUM, COUNT, etc.) or lookup (VLOOKUP, INDEX, MATCH) on the spill range.

We can even use them for named ranges or data validation (example below). Like I mentioned earlier, spill refs and spill ranges are terms we will use a lot in the future with Excel.

## Combining Dynamic Array Functions

Dynamic array functions can also be combined in the same formula. For example, we can use SORT and UNIQUE in the same formula to return a list of sorted unique values.

This is great for the source of a data validation (drop-down) list.

## Using Dynamic Arrays for Data Validation Lists

These new formulas can also help to simplify Data Validation (or drop-down) lists in cells.

If you are not familiar with Data Validation lists, you can check out my post on the subject here. With this new formula, you can pull out the unique entries from a data set, just as above, and then use that new list as the source of your drop-down list.

In the example below, I used the SORT(UNIQUE()) formula to create a list of uniques from Column B, and output it to Column H. Then I use Column H as the source of my drop-down list for Customer Name.

To do this we can just use a spill ref to reference the spill range (H4#). By adding the hashtag to the end, we are letting Excel know that we want the whole spill range, not just cell H4.

The amazing part is that the **spill range automatically updates** as items are added to column B. Everything is dynamic, meaning we never need to do maintenance on our ranges or update our formulas. **The spill ref always includes everything in the automatically updating spill range.**

## Filter Function

FILTER is another great function coming to Excel. With the Filter function we can use an entire table as the data source, and filter it down by one or multiple criteria.

For example, in the image below, I've filtered the data set on the left to just the information that applies to the customer Stater Bros (cell I1). As I show in the video, the criteria cell(s) can be drop-downs to make for quick interactive reports.

The goal of most array formulas is to do multiple calculations and return multiple results to a range of cells. This FILTER function really demonstrates how a lot can be done with just one simple formula. And again, since these these arrays are dynamic, the results will automatically be updated (re-spilled) any time changes are made to the source range or its precedents.

## Dynamic Array Formulas Are Coming!

As I mentioned, these functions are not yet available to the general public. The current availability is limited to a portion of users on Microsoft's Office Insiders Program (Insider channel). The program is free for Office 365 subscribers. There is no set release date to all Office 365 users yet, but hopefully that will be soon.

As of now, there are 7 new dynamic array functions:

- Filter – allows you to filter a range of data based on criteria you define.
- RandArray – returns an array of random numbers between 0 and 1.
- Sequence – allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
- Single – returns a single value at the intersection of a cell's row or column.
- Sort – sorts the contents of a range or array.
- SortBy – sorts based on the values in a corresponding range or array.
- Unique – returns a list of unique values in a list or range.

You can click any of the links above to read the Microsoft help article about each function.

## Existing Functions Can Spill

You might be wondering about other existing array functions in Excel. Well, those functions will spill too. Here is an image of the model I used in my solution on the Dynamic Histogram Chart.

That solutions uses the FREQUENCY function. In older versions of Excel we have to enter that formula with Ctrl+Shift+Enter and first select a range of cells for the output. I had to add extra rows to allow up to 10 bins.

However, with the new spill functionality that setup can actually be simplified. I don't need all the extra rows with the max number starting in cell C16. I could also use a spill ref for E11# to reference the dynamic range in a named range for the source of my chart. Currently we can't use spill refs directly in charts, but I'm guessing that will be fixed.

So, the spill range is NOT limited to the new set of functions. We are really getting two new features here: **dynamic array functions** and **spill ranges**.

## Free eBook on Dynamic Arrays

Another great resource to learn all about these functions before they release is a new eBook by Bill Jelen. It's called *Excel Dynamic Arrays: Straight to the Point* and it's completely **free to download** right now for a limited time.

Click here to download the eBook as a PDF file

## Conclusion

I know, I know, what a jerk right? I get to sit here and tease you about these new features that you probably can't use yet.

Dynamic arrays are coming soon though. And I hope you're as excited as I am for this update.

If you have been using Google Sheets, then you know this isn't really new technology here. However, Excel's implementation of the spill range and spill refs (A4#) is different (at the time of this writing). It opens up a whole new world of possibility and simplicity with Excel formulas and other features.

You've probably heard me say, “there are always a million ways to solve the same problem in Excel…” This new feature probably multiplies that number by another million, leaving us with a lot of new things to explore and learn in Excel. 🙂

I explain how to get these new features in the coming soon section above.

Are you excited for this? Please leave a comment below and let us know. Thank you!

Thanks a lot for your neat presentation. Just to inform you that link for downloading the eBook “Excel Dynamic Arrays” is not working. unfortunately I could not download it.

I would be so thankful if you can share it with me in PDF format.

Best regards

It is good they keep on developing and making things easier for us.

And again your video is so informative and easy to understand.

I hope this functions are not only for the last Office version because at work we use 2013.

Would be a pity not to be able to use them.

Thanks Jon

Another very informative update.

Thanks Jon.

Thanks,very useful.

Hello Jon,

Thanks for keeping us up to date and for all the free training emails you send out. The training you provide in these are easy to follow so I’m sure your courses would be just as good. I’ll pay for a course soon when I get some spare time.

cheers

Jeff

Jon, thank you for sharing and clearly explaining these amazing tools. Excel is awesome!

Hi Jon,

Thank you for the Dynamic Array Formula information and an explanation that made transitioning into the detail of the Bill Jelen e-book much easier.

After understanding this material, I was able to convert a multi-condition SUMIFS() formula into a UNIQUE(SORT(FILTER(…)…)…) structure with the same 6 conditions. The resulting data produced was pretty much the same with either function, but with the dynamic array, adding a new vendor occurs automatically, whereas the SUMIFS() required some additional manipulation.

Thanks for a great introduction to and explanation of these new functions.

Don

Hello,

Thanks for great tips. I understand this is not available in Excel 2016. When will it be available in MS Office Excel 2016 and general public? These are some great features. I am not able or allowed to download Office 365 at my work. Love your page and following all of these tutorials.

Thanks,

Melisa

Argh I want this NOW

Excelent tool!!! And thank you a lot for share it…

Can’t wait to have it on Spanish version!

Hi, Jon…

Do you know if there is available this new function in Spanish version?

If so, can you provide me? I have search on web with no success…

Thank you in advance.

Cesar

Good Afternoon,

Nice functions, it will help to do more in less.

Thanks

Excellent, cant wait to master this one…. thank you

As always, very informative and timely. I could use these tools yesterday but I am patient to wait until tomorrow. Great job as always Jon.

Haha! Thanks Don! I’ll let Microsoft know you’re only willing to wait until tomorrow 😉

For the data validation lists, could you use the Unique formula in a Named Range to create the dynamic range that way, removing the need for the extra column?

If so does it need the # notation?

Great post as usual Jon, easy to read and very informative.

Hopefully these formulas come out sooner rather than later

Hey Terence,

Great question! I just tested and it is returning an error when using the named range in the data validation source. This could just be a bug though. I’ll follow up with Microsoft to see.

You can use the UNIQUE function in a named range though. Referencing the named range in a cell does cause it to spill, which is really useful. However, I’m not sure if the spill has to occur somewhere for the data validation and/or other tools to read the list.

It looks like it does right now, but if we could just used named ranges to hold the spill ranges then that could potentially prevent a lot of spill errors.

Thanks again!

It’s great

Thanks

Such an awesome feature! I know it’s too early to say, but maybe they will apply this functionality to existing functions in the future such as LINEST, TREND, etc.?

Hey Ryan,

Great to see you here. I forgot to mention it in the original post, but the spill functionality does work on existing functions too. I just added a paragraph to the bottom of the post that shows an image of the FREQUENCY function being spilled.

It will work for TREND as well. Microsoft already has the help page updated for TREND and shows the spill range in the image there. The same should be true for LINEST. Haven’t tested yet.

Thanks!

Very cool! Another question for you then. Are CSEs disappearing completely or only for functions that spill? For example, many Excel users use SUMPRODUCT(Rng1 * Rng2) to multiply two ranges together to get their desired result when really SUM(Rng1 * Rng2) will give the same result. The former function is often just used since the latter requires CSE. Will SUM(Rng1 * Rng2) now work without CSE or will it still return a #VALUE error?

Hi,

I am using MS Office 2016. There is no UNIQUE formula in Excel.

How can I use the UNIQUE funtion

Hi Abdul,

As I mention in the article, you will need to be on the Insides Fast program, which is free, to get this pre-release build that includes the new functions. It is currently limited to a random portion of insiders, so Microsoft can test and gather feedback. Hopefully it will be available to everyone on Office 365 in the coming months. I will definitely announce it in the Excel Campus newsletter when the functionality arrives for everyone.

Hi Jon,

are these functions only available for O365 or it will be incorporated in other Excel 2019 versions?

Thanks

Abid

Thanks Jon. I’m doing you’re VBA course and am thoroughly enjoying it. I have some experience with VBA and find your course excellent. It starts off straight into looking at code then comes back to details of VBE and recording macros a little later – a great approach compared to other courses,/books which require the student to wade through tedious and boring detail of the VBE and the macro recorder before beginning any coding. As you say more than once – begin coding immediately.

Hi Rick,

Thanks so much for the nice feedback. I’m happy to hear you are enjoying the course. I forgot to mention it in the article, but this new array and spill functionality is being added to VBA as well. We will be able to reference and modify spill ranges in VBA. This opens up a whole new world of automation for formulas and arrays with VBA.

Thanks again! 🙂

The Dynamic Array Function is amazing! thank you very much John!

Yes! Glad I’m not the only one that thinks so. Thanks Jian! 🙂

Sounds like this is going to be really cool. I’m already thinking of a couple of ways I can use these in my work.

Yes! Can’t wait for everyone to get their hands on this. It’s just fun to play with and start to think about how it changes some of the solutions and techniques we use today. Thanks Don!

Thanks Jon for sharing this awesome features. It has been nice learning through you. Thanks again.

Thanks Akinwale! 🙂

Thanks Jon for an insight into the future. Do you know if maxifs is likely to be a candidate in the next releases?

Hi Gerard,

Great question! MAXIFS already exists for Office 365 subscribers. It was released earlier this year, if I remember correctly. Here is the help page for MAXIFS.

This new spill functionality will work with all existing functions that would produce multiple results. I hope that helps.

Jon,

Do you plan to add this into any of your current courses, such as The Ultimate Lookup Formulas Course when it’s reopen or any of the other courses you offer?

Tom

Hi Tom,

Yes, absolutely. There are so many uses for this with lookup formulas. We can use the spill ref notation (A4#) for the lookup_value argument in VLOOKUP to instantly return multiple results (spill the results). This is great for grabbing a list of unique values from a data set, then returning other columns from the data as well.

That’s just one of many examples, and I will definitely have more training on this in the future.

Thanks again, Tom! 🙂

Jon are the arrays still volatile and so using up resources?

Hi Allister,

Great question! The arrays are not volatile. The functions are going to be dependent on other ranges, and only recalc when those ranges change. This is the same behavior as other non-volatile functions.

There are limitations with volatile functions. For example, you might not be able to use a volatile function inside a function/formula that spills. In Bill Jelen’s book he mentions an example of using RANDBETWEEN within UNIQUE. This will return an error, as it would be an endless loop.

I just added a section to the article that explains how the spill range functionality also works on existing functions like FREQUENCY and TREND that would normally produce multiple results. So, it’s not just limited to the new functions.

I believe the calc engine in Excel has been further optimized for performance with this new functionality. In a lot of scenarios we will be able to greatly reduce the number of formulas needed to produce results. The CSE formulas exist in every cell within the range they occupy, and each do a calculation. Usually a complex calculation. That is not necessarily the case with this new functionality. So you could see performance improvements. Again, a lot depends on the model, so I don’t want to make a blanket statement that this will solve all your problems with calc times/performance…

I hope that helps. 🙂

Finally !

Thanks for sharing Jon

Great Stuff! Anxious to get these.

Thanks Tim! Hopefully the wait won’t be too long.

Excellent – Thank You!!

Thanks Dana! 🙂

Very COOL stuff Jon. I hope it will soon be available for Excel 2016 users as well.

Thanks very much for sharing. )BTW, I’m thinking of going over to Office 365 in the near future anyway!)

Hi Edil,

Yes, these features will be available to Office 365 subscribers when they are released. I’m not sure if/when they will be released on the perpetual versions of Excel 2016/2019.

Thanks!

Jon,

Thanks for sharing this great news!! I have several applications right now where I can put these to work once they are released. This is truly an exciting new enhancement to avoid the cumbersome array formulas, I always try to avoid. I hope to hear more about these soon.

Best Regards,

Wayne

Hi Wayne,

I’m happy to hear you will be using these new features. I’m excited too and will create new articles and videos as enhancements are made, and we continue to find new solutions for dynamic arrays and spill ranges. Fun stuff! 🙂

Unfortunately, I have Excel 2013 at work so it sounds like I won’t be able to use these new formulas until we upgrade to 2016. Is that correct?

Yes, that is correct. You will likely need to be on Office 365, which means you would be downloading and installing the latest version of Excel 2016. There is a perpetual (non-subscription) version of Office/Excel 2016 as well. I don’t believe that will get this update right away, and not sure about 2019 yet.

The best bet to get these features, when they are released, is to be on Office 365.

Will these functions be available only for 365 users? Or, will the older versions of Excel get them through updates?

Hi Jeff,

I’m not 100% sure on the answer to this. However, I’m guessing they will only be available to Office 365 users. At least to start.

There are actually a lot of changes behind the scenes that have to happen for this functionality to work with the calc engine in Excel. It’s probably a bigger structural change than we realize.

Therefore, I doubt older versions will get this functionality. I could be wrong, but guessing it’s going to be a new feature going forward. Just like older versions of Excel didn’t get Slicers when they were introduced.

I can’t wait to use this. I create unique, sorted lists all the time. I almost always use pivot tables with one field. I love these new formulas. Thanks for sharing this and whetting our appetites.

Awesome! This will be a big help with those tasks. Thanks Jeff! 🙂

Would I be right in saying that these new functions are only available in Office 365, they are certainly not available in Excel 2016.

Mike.

Hi Mike,

The versions of Excel get a bit confusing. If you are on an Office 365 subscription then you are technically downloading and installing the desktop version of Excel 2016.

However, there is also a stand alone (perpetual) version of Office or Excel 2016 that you can just pay a one-time fee for. This is not a subscription.

This is where it gets confusing. However, these features will make it to Office 365 first. I’m not sure if they will make it to the perpetual version of 2016 and/or when they will be available on Office 2019 perpetual.

I hope that helps.

Jon, thanks for all the info and for help everyone to be better Excel users (sorry, my english isn’t good, i live in Buenos Aires but i trying every day learn a lite more english and Excel…)

Thanks Pablo! I really appreciate your support. 🙂

Hope it doesn’t take too long to get these out to the general public. Unique will definitely be put to use by me as soon as it is released. Thanks for the heads up on this and all the other help you provide this community.

It’s great to hear you will be putting these new functions to good use. Hopefully they will be released in the next few months. Thanks Jerry! 🙂