Get the Excel Lists Practice Workbook
Every example from the video in one ready-to-use file. Turn text into lists with Ctrl+J, test HAS, HASALL and HASANY, and build the FLATTEN and GROUPBY reports yourself.
Lists are in the Microsoft 365 Beta Channel for now, so you’ll need a Beta build to see them work.
- Press Ctrl+J or go to Insert > List to turn comma-separated text into a list. Filters, COUNTA, SUM and XLOOKUP then work with each item.
- Type curly braces right after the equals sign, like ={SORT(A2)}, to keep a spilling result in one cell.
- HAS, HASALL and HASANY check whether a list contains one, all, or any of your values. “Ann” no longer matches “Anna”.
- FLATTEN splits lists back into rows, so UNIQUE, SORT and GROUPBY can use every item.
- Lists are a Beta Channel preview. PivotTables, charts, Power Query, and data validation drop-downs can’t use them yet.
What Are Lists in Excel?
A list is a set of separate values stored in one Excel cell. It looks like comma-separated text, but each item stays its own value. You can filter by one item, count the items, add them up or look them up.
Until now, a cell like “Ann, Anna, Sam” was one long text string. A “contains Ann” filter pulled in every Anna too. Getting anything useful out of it meant splitting the text into helper columns first.
This changes what an Excel cell can be, and what you can build with it.
| Task | Before | With lists |
|---|---|---|
| Filter by one name | Text filter “contains,” which catches partial matches | The filter shows each item once |
| Count the items in a cell | A LEN and SUBSTITUTE formula | =COUNTA(A2) |
| Check if a value is in a cell | ISNUMBER(SEARCH()) | =HAS(A2, "Ann") |
| Keep several results in one cell | TEXTJOIN, which turns everything into text | ={SORT(A2)} |
| Split items into rows | TEXTSPLIT plus helper columns | =FLATTEN(A2:A20) |
How to Create a List in Excel
Select the cells with your comma-separated text and press Ctrl+J. A small list icon appears in each cell, and every item becomes its own value. Click the icon to see the items.
If you’d rather use the ribbon, go to Insert > List. It does the same thing.
Three things to know:
Ctrl+Jis a toggle. Press it again and the list goes back to plain text.- Match your Excel separator. If your formulas use semicolons, separate the items with semicolons. This depends on your regional settings.
- Edit it like any cell. Double-click or press F2.
Filter by Individual List Items
Once a column holds lists, the filter drop-down changes. Each item shows up once instead of every full text string.
Pick “Ann” and you get every row that includes Ann. No Anna, and no wildcard tricks.
Use COUNTA, SUM and Other Formulas on Lists
The formulas you already know work on lists.
=COUNTA(A2) counts the items inside the list, not the cell. A cell with five names returns 5.
Numbers work too. A cell with “5, 8, 12” used to be text you couldn’t calculate with. As a list, =SUM(B2) adds it up and =COUNT(B2) tells you how many values it holds.
If you reference a list on its own, as in =A2, the items spill into the cells below.
Arrays in Cells: Keep Formula Results in One Cell
Try =SORT(B3) and the sorted items spill down the sheet. To keep them in one cell, type a curly brace right after the equals sign and another at the very end:
={SORT(B3)}Microsoft calls the result an array in a cell. A list is something you type in. An array in a cell is a formula result. Both hold several values in one cell.
This works on any formula that spills, including FILTER and XLOOKUP. It’s especially useful inside Excel tables, where a spilling formula normally runs into #SPILL!.
Don’t confuse these braces with the old Ctrl+Shift+Enter arrays. Those braces sat outside the formula, as in {=SUM(…)}, and Excel added them for you.
The new ones go inside, right after the equals sign, and you type them yourself.
Before this, the usual way to squeeze several results into one cell was TEXTJOIN.
You can see that method in this guide to returning multiple match values in Excel. It works, but the result is plain text.
Return a List with XLOOKUP
XLOOKUP can return a list, and Excel tables can hold lists. Look up a key and return the list column:
=XLOOKUP(D2, TProject[Project name], TProject[Team members])Every item comes back, one per row. Wrap it in SORT to put them in order. Then add the braces to keep the result in one cell:
={SORT(XLOOKUP(D2, TProject[Project name], TProject[Team members]))}
HAS, HASALL and HASANY: Check What’s in a List
Three new functions test what’s inside a list. Each one returns TRUE or FALSE, so they work well inside FILTER or IF.
HAS Function
=HAS(array, value) checks whether one value is in the list:
=HAS(A2, $E$2)Press F4 to lock the lookup cell before you copy the formula down.
HAS matches whole items, not bits of text, so “Ann” doesn’t match “Anna.”
HASALL Function
=HASALL(array, values) returns TRUE only when every value is in the list.
Use it to find rows that include two specific people:
=HASALL(A2, $E$2:$F$2)HASANY Function
=HASANY(array, values) returns TRUE when at least one of the values is in the list:
=HASANY(A2, $E$2:$F$2)The values can come from a range or from another list cell.
FLATTEN: Split Lists into Rows
Referencing a column of lists gives you lists back. FLATTEN breaks them apart so each item gets its own cell:
=FLATTEN(B3:B27)Wrap it in UNIQUE and SORT to get a clean, sorted list of every item:
=SORT(UNIQUE(FLATTEN(B3:B27)))
GROUPBY with Lists
If you’re new to the GROUPBY function, start with that guide.
Lists add two new tricks.
Count How Often Each Item Appears
Use the flattened list column for both the row fields and the values:
=GROUPBY(FLATTEN(A2:A20), FLATTEN(A2:A20), COUNTA, , , 2)It sounds odd, but that’s what gives you one row per item.
The 2 sorts by the count, lowest first, so the least-used items sit at the top.
Return a List for Each Group
This one works the other way. Instead of taking lists apart, it builds them.
Group by one column, use another as the values, and pick UNIQUE as the function:
=GROUPBY(A3:A27, B3:B27, UNIQUE, , 0)Each group appears once, with its items stored as a list in one cell.
The 0 turns off the grand total.
Nested Arrays: Fewer #CALC! Errors
Formulas that return one array per row used to hit #CALC! or cut the result short. With nested arrays, supported formulas return the full result.
If you’ve ever used the TEXTSPLIT function inside BYROW and got #CALC!, this is the fix.
Most nested array calculations need Compatibility Version 3. You set it per workbook under Formulas > Calculation Options > Compatibility Version.
Microsoft warns that some existing formulas return different results in Version 3, so test on a copy.
New Excel Functions for Lists: Quick Reference
| Function | Syntax | Returns |
|---|---|---|
| HAS | HAS(array, value) | TRUE if the value is anywhere in the array |
| HASANY | HASANY(array, values) | TRUE if any of the values are in the array |
| HASALL | HASALL(array, values) | TRUE if all of the values are in the array |
| FLATTEN | FLATTEN(array, [pad_value], [levels]) | The array with nesting removed, spilled into the grid |
pad_valuefills the gaps when rows have different numbers of items.levelssets how many layers of nesting to remove.
Availability and Limitations
Lists, arrays in cells and nested arrays are in preview for Microsoft 365 Insiders in the Beta Channel:
- Windows: Version 2610 (Build 20520.20000) or later
- Mac: Version 16.114 (Build 26092111) or later
Microsoft has only announced Windows and Mac so far. The rollout is gradual, so you might not see it yet even on the right build. Microsoft doesn’t recommend using these features in important workbooks until they’re generally available.
These don’t work with lists yet:
- PivotTables
- Charts
- Power Query
- Data validation drop-downs (a list can’t be the source)
- Find & Replace on individual items
- Conditional formatting, unless you use a formula-based rule
If a report depends on any of these, FLATTEN the lists into regular cells first.
Download the Workbook
Get the Excel Lists Practice Workbook
Try every formula from this post in the ready-to-use file. Free download.
Frequently Asked Questions
How do I put multiple values in one cell in Excel?
Type or paste the values separated by commas, or semicolons depending on your regional settings. Then press Ctrl+J or go to Insert > List. Excel turns the text into a list where each item is its own value. For now this needs the Microsoft 365 Beta Channel.
What new functions work with Excel lists?
Microsoft added four: HAS, HASANY, HASALL and FLATTEN. Existing functions like COUNTA, SUM, SORT, UNIQUE, XLOOKUP and GROUPBY also work with the items inside a list.
What is the difference between a list and an array in a cell?
A list is a set of values you enter into a cell. An array in a cell is a formula result kept in one cell, by typing curly braces right after the equals sign. Both hold several values that other formulas can use.
Why can’t I see lists in Excel?
Lists are a preview feature in the Microsoft 365 Beta Channel. You need Windows Version 2610 (Build 20520.20000) or Mac Version 16.114 (Build 26092111) or later. Even then, Microsoft is rolling it out gradually.
How do I check if a cell contains a specific value in Excel?
If the cell holds a list, use =HAS(A2, “Ann”). It only matches whole items. If the cell holds plain text, use =ISNUMBER(SEARCH(“Ann”, A2)). Be careful, because that also matches “Anna.”
Do Excel lists work with PivotTables, charts or Power Query?
Not yet. None of them can read list values in the preview. Use FLATTEN to split the lists into regular cells first.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
