51

New Excel Features: Dynamic Array Formulas & Spill Ranges

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

Skill Level: Beginner

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

Here's the file I use in the video. These functions are now available to users on Microsoft's Office Insiders Program.  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.

How to Create Dyanmic Array Formula Unique Function

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

Old Ctrl Shift Enter Array Formula to List Unique Values..

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.

Dynamic Array Formulas Unique Function Spill Range

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 Output Results of Dynamic Array Formula

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.

Error Box for Spill Error in Excel - Select Obstructing Cells

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.

Dynamic Array Reference in Formulas with Cell Reference Hashtag - Sort Function..

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.

Dynamic Array Formulas Sort and Unique Functions..

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.

Dynamic Data Validation List based on Dynamic Array Formula of Unique Values..

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.

Dynamic Array Formulas Filter Function..

Click to Enlarge

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!

The current availability is limited to users on Microsoft's Office Insiders Program (Insider channel).  The program is free for Office 365 subscribers.

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.

Existing Array Functions Spill in Excel

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.

Excel Dynamic Arrays eBook by Bill Jelen Free Download

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 51 comments
Melisa - November 20, 2018

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

Reply
Cameron - October 31, 2018

Argh I want this NOW

Reply
cmnavedo - October 5, 2018

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

Can’t wait to have it on Spanish version!

Reply
cmnavedo - October 5, 2018

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

Reply
Argenis Chaffardet - October 5, 2018

Good Afternoon,

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

Thanks

Reply
Mark - October 5, 2018

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

Reply
Don Hollerbach - October 5, 2018

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.

Reply
    Jon Acampora - October 5, 2018

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

    Reply
Terence - October 5, 2018

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

Reply
    Jon Acampora - October 5, 2018

    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!

    Reply
Bakr Saleh - October 5, 2018

It’s great
Thanks

Reply
Ryan - October 4, 2018

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

Reply
    Jon Acampora - October 5, 2018

    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!

    Reply
      Ryan - October 5, 2018

      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?

      Reply
Abdul Jabbar P - October 4, 2018

Hi,
I am using MS Office 2016. There is no UNIQUE formula in Excel.
How can I use the UNIQUE funtion

Reply
    Jon Acampora - October 5, 2018

    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.

    Reply
Rick Symons - October 4, 2018

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.

Reply
    Jon Acampora - October 5, 2018

    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! 🙂

    Reply
Jian - October 4, 2018

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

Reply
    Jon Acampora - October 5, 2018

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

    Reply
Don Wilcox - October 4, 2018

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.

Reply
    Jon Acampora - October 5, 2018

    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!

    Reply
Akinwale Jesudeyi - October 4, 2018

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

Reply
GerardB - October 4, 2018

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

Reply
    Jon Acampora - October 5, 2018

    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.

    Reply
Tom - October 4, 2018

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

Reply
    Jon Acampora - October 5, 2018

    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! 🙂

    Reply
Allister - October 4, 2018

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

Reply
    Jon Acampora - October 5, 2018

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

    Reply
Cristian Guay - October 4, 2018

Finally !
Thanks for sharing Jon

Reply
Tim Hoogenboom - October 4, 2018

Great Stuff! Anxious to get these.

Reply
    Jon Acampora - October 4, 2018

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

    Reply
Dana Clemmer - October 4, 2018

Excellent – Thank You!!

Reply
Edil - October 4, 2018

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

Reply
    Jon Acampora - October 4, 2018

    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!

    Reply
R. Wayne Hyde - October 4, 2018

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

Reply
    Jon Acampora - October 4, 2018

    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! 🙂

    Reply
Jeff - October 4, 2018

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?

Reply
    Jon Acampora - October 4, 2018

    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.

    Reply
Jeff - October 4, 2018

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

Reply
    Jon Acampora - October 4, 2018

    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.

    Reply
Jeff - October 4, 2018

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.

Reply
    Jon Acampora - October 4, 2018

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

    Reply
Mike Bennett - October 4, 2018

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.

Reply
    Jon Acampora - October 4, 2018

    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.

    Reply
Pablo Laneve - October 4, 2018

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

Reply
    Jon Acampora - October 4, 2018

    Thanks Pablo! I really appreciate your support. 🙂

    Reply
JerryN - October 4, 2018

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.

Reply
    Jon Acampora - October 4, 2018

    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! 🙂

    Reply

Leave a Reply: