MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News


September 24, 2018 - by Bill Jelen

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News

Today at the Ignite Conference in Orlando Florida, Microsoft debuted a major change to the calculation engine. Every function is now treated as an array formula and there is no more need to press Ctrl + Shift + Enter. This will lead to a rapid increase in the number of functions introduced in Office 365.

Seven amazing new functions debut in Insider Fast this week. I will be covering these new formulas every day this week, but if you would like to read ahead:

  • This article covers the new =A2:A20 formula, the SPILL error, and the new SINGLE function required in place of Implicit Intersection
  • Tuesday will cover SORT and SORTBY
  • Wednesday will cover FILTER
  • Thursday will cover UNIQUE
  • Friday will cover SEQUENCE and RANDARRAY functions

Taken as a whole, these functions in essence turn every Excel function into an array function. Kudos to Joe McDaid and his team at Microsoft. Joe is the same guy who brought us TEXTJOIN, MAXIFS and MINIFS. [Joe - if you are reading this, I am hurt that you didn't bring us ROMANIFS yet, but I am still waiting...]

Let's start with the basic array formula. Go to cell E3. Type =A2:C10 as shown here. In the past, you would have to wrap that formula in an aggregation function and maybe use Ctrl + Shift + Enter.

One cell, pointing to many cells
One cell, pointing to many cells
There will be no need for Ctrl+Shift+Enter anymore

But simply press Enter. Excel returns 27 answers. The answers spill into the adjacent cells! Look at the formula in the formula bar... there aren't any curly braces. No one had to press Ctrl + Shift + Enter.

One formula brought all of these answers.
One formula brought all of these answers.

Cell E4 contains the value "Central" but it does not contain a formula. The formula appears in the formula bar, but it is greyed out.

We are in a whole new world here, Captain.
We are in a whole new world here, Captain.

Just to prove that E4 does not contain a formula, here are some queries typed into the Immediate Pane in VBA. There is a value in E4 but no formula.


VBA is reporting no formula in E4.
VBA is reporting no formula in E4.

But, similar to Schrodinger's Cat, if you select D1:H20 and use Go To Special, Formulas, then E3:G12 are shown as formula cells. A whole new world indeed.

Wait! This is Going to Break Stuff!

Yes. I like the way you are thinking. Let's try to break it. Put some stuff in the way. Enter one of the formulas. What will happen???

How will Excel deal with this?
How will Excel deal with this?

Check it out... quite possibly the first published photo of the new #SPILL! error. This is Excel's way of saying that it can't return all the results, so it won't return any of the results. The little on-grid UL to the left of the cell gives you a choice to select the obstructing cells so perhaps you can cut them and paste elsewhere.

We've got a #SPILL! error in Aisle E...
We've got a #SPILL! error in Aisle E...

As soon as you clear out the obstructing cells, the results appear.

Add more data and the formula returns more cells.
Add more data and the formula returns more cells.

As soon as you clear out the obstructing cells, the results appear. Also - check out what else just happened in the previous figure. I typed a new row in the table in columns A:C. The formula expanded to include the extra row.

Smitty Smith has a great article on all of the different SPILL errors.

Who Would You Nominate as the Cliff Claven of Excel?

I know a lot of Excel gurus. Who is the person who knows every single picayune fact about Excel? Is it Charley Kyd? Bob Umlas? Jordan Goldmeier? Well, whoever it is, by now they are surely bellowing from the back of the room... "What about Implicit Intersection!!!!"

Implicit Intersection is about as common as flying pigs

Oh, wow. Implicit Intersection is about as common as flying pigs, but someone had to bring up Implicit Intersection. Have you *ever* seen anyone actually use Implicit Intersection in the wild? Do we really need to talk about this?

Without boring you to tears, if you enter =C$2:C$10 any where in rows 2 through 10, the formula would return only the value from that row. I've seen one person use this in real life and she had no idea what she was doing. I've seen Szilvia Juhasz talk about it. Mike Girvin mentioned it in a video. Bob Umlas has written about it. Hmmm. Szilivia once called me the Jeremy Piven of Excel, so today, right here at 38,000 feet, I will nominate her as the Cliff Claven of Excel. So Szilvia... I hear you want to know how implicit intersection is going to work in this brave new world.

If you really desperately *need* implicit intersection, wrap your formula in the SINGLE function as shown here.

Use the SINGLE function when you need Implicit Intersection.
Use the SINGLE function when you need Implicit Intersection.

Here is the crazy amazing back-compat story: If you use SINGLE in Office 365 and open in Excel 2013, Joe McDaid will rewrite the formula as implicit intersection. And if you open an Excel 2013 function that has implicit intersection, Joe McDaid will rewrite that formula to use SINGLE.

Watch Video

Download Excel File

To download the excel file: excel-modern-array-formulas-debut-in-office-365.xlsx

This is the first of five (or more!) articles about these new Excel Dynamic Array Functions. This article lays the groundwork. Follow the link to read tomorrow's article on SORT and SORTBY.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Thou shalt make it clear where people need to input data."

Title Photo: Farsai C. on Unsplash


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.