Excel Nested Arrays: Store Multiple Values in a Single Cell

A brand new capability has dropped in Excel, and it changes something we thought was a hard rule: every cell holds one value. Not anymore. Excel now lets us store multiple values, entire lists, and even full 2D tables, inside a single cell. This feature has a few names: Lists, Arrays in Cells, or Nested Arrays. It opens up some genuinely exciting possibilities. In this tutorial we walk through three exercises that cover the basics, from creating simple single-column lists, to building 2D arrays, to using new functions like FLATTEN, HAS, and VLOOKUP directly against a nested array cell.

Exercise 1: Creating Lists in Cells

Historically, storing multiple values in a single cell meant jamming them together as comma-separated text. Something like “Chen, Patel” in one cell. The problem is that Excel’s calculation engine sees that as one value, not two. It has no idea where Chen ends and Patel begins.

Let’s say we have a journal entry dataset where each row needs to track one or more reviewers:

Column F shows the old approach: comma text. Column G is where we will build proper lists. There are two ways to create a list in a cell.

Method 1: Insert > List

We can click Insert, then List. A small green icon appears in the cell, signaling that we are building a list. We type our values and press Enter. The list icon confirms that Excel now recognizes each item as a distinct value, not a single lump of text.

Method 2: Ctrl+J Shortcut

The faster approach is to select a range of cells that already contain comma-separated text, then press Ctrl+J. Excel converts all selected cells into lists in one shot. Let’s select G11:G18 and hit Ctrl+J:

And bam:

Every cell in column G now sports that list icon. Clicking the icon opens a preview card that shows each value as a distinct item. We can edit list cells directly in the formula bar, or press F2 to toggle into edit mode, just like any other cell.

Now for one of the most immediate benefits: filtering. When we turn on Data filters and click the dropdown on a list column, we see a new option called List Items. This lets us filter by any individual name inside the lists, even when multiple names share a single cell:

We can pick Kim, click OK, and only the rows where Kim is a reviewer appear. That is something the old comma-text approach could never do cleanly. Mission accomplished for Exercise 1.

Exercise 2: Building 2D Arrays in Cells

Lists are single-column: N rows by 1 column. But Excel’s new arrays-in-cells feature goes further. We can store full 2D arrays, meaning rows AND columns, inside a single cell. Think of it as a table living inside one cell.

Consider the following worksheet, which shows a 3×3 grid of numbers in F9:H11:

Wrapping a Range Reference in Curly Braces

Normally, a formula like =F9:H11 would spill all nine values into a 3×3 block of cells. To contain the entire array inside a single cell, we wrap the reference in curly braces. We write the following formula into cell C9:

={F9:H11}

We hit Enter, and bam:

The entire 3×3 array sits in one cell. Clicking the preview card confirms the shape: 3 rows by 3 columns.

Hand-Coding Values with Ctrl+J

We can also type array values directly. Press Ctrl+J to open a cell for multi-line array entry, then use curly braces with commas as column separators and semicolons as row separators. So, we write:

={1,2,3;4,5,6;7,8,9}

The preview card confirms the shape. Commas move across columns, semicolons start new rows. Simple and clean.

Wrapping a Function with Curly Braces

What if we have a formula that normally spills? We can wrap it in curly braces to contain it in one cell. Here is a quick look at the SEQUENCE function before we use it.

Here’s a quick refresher on the SEQUENCE function. It returns an array of sequential numbers based on the rows and columns we specify. The function arguments are:

• rows: the number of rows to return
• columns: the number of columns to return (optional)
• start: the starting number (optional)
• step: the increment between each value (optional)

Normally, =SEQUENCE(3,3) spills nine values across nine cells. To contain the result in a single cell, we wrap it:

={SEQUENCE(3,3)}

The sequence result stays in one cell rather than spilling. The curly braces are the key. Now let’s take this further in Exercise 3.

Exercise 3: Functions That Work with Nested Arrays

Now that we can store arrays in cells, we need functions that know how to work with them. Microsoft shipped several new functions alongside this feature: FLATTEN, HAS, HASANY, and HASALL. And yes, some classic functions like VLOOKUP work against nested array cells too.

First, let’s get an entire Excel Table into a single cell. We have a table named table1. So, we write:

={Table1}

We hit Enter, and the entire table, all rows and columns, lives inside cell B10. Clicking the preview card shows a 10 by 5 array. Now let’s look at what we can do with it.

FLATTEN: Spill a Nested Array Back to the Grid

Before diving in, a quick look at the FLATTEN function. It takes a nested array stored in a cell and spills its contents back out into the standard grid. The function arguments are:

• array: the cell or range containing the nested array to flatten

So, we use the following formula to spill our nested table back into the worksheet:

=FLATTEN(B10)

We fill the formula in and bam, the entire table spills from one cell reference into the grid. This can be useful when we need to pass nested data to other formulas that expect a normal range.

HAS: Check Whether a Value Exists in the Array

Since we’re about to use HAS, here’s what it does. It checks whether a specific value exists anywhere inside a nested array and returns TRUE or FALSE. The function arguments are:

• array: the cell containing the nested array to search
• value: the value to look for inside the array

We can use HAS to test whether JE-2409 exists inside our nested table cell. We write the following formula into cell C14:

=HAS(B10,B14)

The formula returns TRUE because JE-2409 is found inside the nested array. Two close relatives are worth knowing: HASANY returns TRUE if any one of multiple lookup values is found, and HASALL returns TRUE only when every lookup value is found.

VLOOKUP: Look Up Data Directly in a Nested Array Cell

For those new to VLOOKUP in this context, it searches the first column of an array for a value and returns a value from a specified column. The function arguments are:

• lookup_value: the value to search for
• table_array: the range or array to search, including a nested array cell
• col_index_num: the column number from which to return a value
• range_lookup: 0 for exact match, 1 for approximate match (optional)

Now, it is time to make things more interesting. Can we use VLOOKUP directly against a nested array cell? Let’s find out. We write the following formula into cell D14:

=VLOOKUP(B14,B10,4,0)

We hit Enter and it works. VLOOKUP finds JE-2409 in the nested array and returns the amount from the 4th column. We just did a lookup into a single cell that holds an entire table. That is pretty cool, and it opens up a very compact way to work with data.

Summary

Excel’s new nested arrays feature, also called Lists or Arrays in Cells, lets us store multiple values inside a single cell. In Exercise 1, we saw how to create single-column lists using Insert > List or the Ctrl+J shortcut, and how filters gain a powerful new List Items option. In Exercise 2, we built 2D arrays by wrapping range references or hand-coded values in curly braces, and we saw how to contain spilling formulas like SEQUENCE in a single cell. In Exercise 3, we stored an entire table in one cell using curly braces around a table name, then used FLATTEN to spill it back to the grid, HAS to test for a value inside the array, and VLOOKUP to retrieve data directly from the nested cell. This new capability is still fresh, and it opens up many interesting possibilities. We are excited to keep exploring it.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

What version of Excel supports nested arrays and Lists?

This feature requires Microsoft 365 (the subscription version of Excel). It is part of a rolling update currently available to beta insiders channel, so if we do not see it yet, checking for Office updates should bring it in. Earlier perpetual versions like Excel 2019 or 2021 do not include this capability.

What is the difference between a List and a nested array in Excel?

A List is the specific term Microsoft uses for a single-column nested array, meaning N rows by 1 column. A nested array (or array in a cell) is the broader concept and can hold any shape, including 2D arrays with multiple rows and columns. Lists are a special case of nested arrays.

How do we convert existing comma-separated text into a List?

We select the cells that contain the comma-separated text and press Ctrl+J. Excel converts each selected cell into a proper List, recognizing the individual values as distinct items rather than one combined string.

What do the curly braces {} do around a formula or range reference?

Wrapping a formula or range reference in curly braces tells Excel to contain the result inside a single cell rather than letting it spill into adjacent cells. For example, ={SEQUENCE(3,3)} keeps all nine results in one cell, while =SEQUENCE(3,3) without braces spills across nine cells.

How do commas and semicolons work when typing array values manually?

In US locale Excel, commas separate values across columns and semicolons separate rows. So {1,2,3;4,5,6} produces two rows and three columns. Other regional locales may use different separators, so it is worth confirming the separators for our region.

What is the FLATTEN function used for?

FLATTEN takes a nested array stored in a single cell and spills its contents back out into the standard grid. This is useful when we need to pass that data to formulas or tools that expect a normal multi-cell range rather than a nested array.

What is the difference between HAS, HASANY, and HASALL?

HAS checks whether one specific value exists anywhere inside a nested array. HASANY accepts multiple lookup values and returns TRUE if at least one of them is found. HASALL uses AND logic and returns TRUE only when every one of the specified values is present in the array.

Can we use XLOOKUP or other modern lookup functions against a nested array cell?

Yes! XLOOKUP and other modern lookup functions work with arrays in cells.

How does filtering on a List column work differently from filtering on regular text?

When we apply AutoFilter to a column containing Lists, the filter dropdown shows a new List Items option. Selecting it lets us filter by any individual value inside the lists, even when multiple values share a single cell. The old comma-text approach could not do this because Excel treated the entire string as one value.

Can we store a formula result, not just static values, in a nested array cell?

Yes. Any formula that returns multiple values can be wrapped in curly braces to contain its result in a single cell. We saw this with ={SEQUENCE(3,3)} and ={Table1}. The formula stays live, so if the underlying data changes, the nested array cell updates automatically.