Lists and Arrays: Excel Just Got Bigger. So Did the Job.
For forty years, a cell held one value. One number. One date. One name. And people designed spreadsheets around cells.
That’s changing. This week, Microsoft released lists, arrays in cells, and nested arrays. What are they? They’re ways to put multiple values into a single cell. This isn’t just for display. Excel treats each item as its own value. It can filter them, count them, and calculate with them.
This functionality is available in a beta release, so nothing is changing for your spreadsheets yet. But these features are coming, and you’ll want to be prepared.
Simple examples, big possibilities
Lists. Need to know which days each employee is available? Type “Mon, Tue, Wed, Thu” in a cell and press Ctrl+J. Excel stores four separate days, not one string of text. Filter for Tuesday, and you see everyone available Tuesday. Everyone’s schedule, fully searchable.
I’ve built “multi-select dropdowns” before. It took data validation and a fair amount of VBA to jerry-rig it. And it required jumping through hoops to make sense of the data. Now, it takes a keystroke.
Arrays in cells. Formulas that return a list of results used to need room to spill down the sheet. That meant they were incompatible with Tables. Now those results live in a single cell. Tables are central to Excel, and they just got a whole lot more powerful.
Nested arrays. This is where it gets interesting. An array can now hold other arrays. Picture an orders table. Each order carries its own line items. Each line item carries its own sizes or options. All of it tied together, one row per order.
Think of a filing cabinet. The drawer holds folders. The folders hold pages. Excel can now store data the same way. If that sounds bewildering, don’t worry. I’m still wrapping my mind around it too. My head is spinning with the possibilities.
I’ve been doing this for a while
Here’s the funny part. In some ways, I was already doing this.
I take my clients’ data and logic and encapsulate them in multi-layer lambda formulas. These lambdas manipulate large arrays all the time. They build them, reshape them, and pass them from one step to the next, all inside a single formula. I do this to display what my clients need to see in the way they need to see it. I always spilled the output across a range of cells, never into a single cell. Now I can.
That leaves me with a couple of questions to work through:
- Will I return more arrays to cells? I can return intermediate steps to facilitate auditing my formulas.
- Can I use lambda results as databases? Collapsing a lambda’s output into a single array cell on a worksheet could work like the lambda version of a Power Query data type. I’m not sure it even makes sense to do that, but I’ll have to play with it.
And that’s the point. Even for someone who lives in the world of arrays every day, this changes how I’ll structure my work.
It’s not about Ctrl+J
Anyone can press Ctrl+J. That’s the easy part.
The hard part is knowing when a list belongs in a cell and when it doesn’t. Knowing how the rest of the workbook needs to read that data. Knowing how today’s structure will hold up when the business grows.
That’s not a formula skill. It’s a design skill. And design is where most spreadsheets go wrong.
What does a dedicated developer look like?
At my prior job, I was allowed to be the Excel developer. Not the person who also did Excel. The Excel developer.
It was one of the best things that happened to me there. And to the office.
I got to go deep. When I didn’t know how to do something, I had time to learn, develop, and deploy. Through that process, I learned what worked well . . . and what didn’t. Over time, I built up a suite of spreadsheets with the same look, the same feel, and the same structure. Learn one, and you could find your way around the rest.
That doesn’t happen when every spreadsheet is built by whoever had time that week.
Excel is for everyone . . . mostly
Excel belongs to everyone, and it should. I commend Microsoft for putting real power in the hands of real people. Most spreadsheets should be built by the people who use them.
But not all of them.
The spreadsheets that drive your key processes are different. They’re not one-offs. They’re not just handy tools. They’re mission critical. When they break, the business feels it. Those should be designed by a pro, whether that’s someone in house or a consultant.
Why? Because Excel has outgrown part-time development.
Dynamic arrays. Power Query. LAMBDA. And now lists and arrays. Each one is powerful on its own. Together, they turn Excel into a true programming platform. Unless you’re developing in Excel full time, you won’t have the hours to learn each release, test it against real work, and know how it fits into a well-built spreadsheet.
That’s not a knock on anyone. It’s a matter of time.
The bottom line
Lists and arrays are a gift to every Excel user. But they also lay bare a hidden truth. For forty years, we were allowed to believe that Excel was just a collection of cells. And we built spreadsheets based on that notion. But Excel’s not just a collection of cells. It hasn’t been for decades. And with the introduction of lists and arrays, that truth is undeniable.
So, it’s time to stop building spreadsheets as though cells are what’s important. That is a major shift for most users. And that’s why — now more than ever — it matters who builds the spreadsheet.
Let the people who live in Excel build the spreadsheets your business can’t afford to get wrong. Everyone else gets to use them . . . and get back to their real jobs.
Christopher
“Let the people who live in Excel build the spreadsheets your business can’t afford to get wrong. Everyone else gets to use them . . . and get back to their real jobs.” Well said.