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

September 24, 2018 - by Bill Jelen

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.

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.

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.

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.

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

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.

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

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

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.

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

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.

Title Photo: Farsai C. on Unsplash