Excel now supports multiple values in a single cell

Project trackers, surveys, inventory lists, and customer databases often hold interrelated information that needs to be grouped together—like multiple owners, tags, locations, or responses.

In the past, if you wanted to organise this information in Excel, you had to spread it across extra rows and columns, or cram everything into hard-to-manage text strings. But now, with the Insiders Beta, you can use lists, arrays in cells, and nested arrays to keep related information together without compromising on filtering, calculations, or analysis.

Let’s take a closer look at how these new features can be applied to project tracking.

You can create a list with Insert > List or Ctrl+J, then type or paste items separated by commas or semicolons, according to your regional settings. Clicking the icon in the cell reveals the individual values.

A typical project can have several owners, workstreams, and milestones. Traditionally, you would have to use separate columns, helper tables, or pile text like “Carlos, Henrietta, Jacob” into a single cell.

Now, with lists, you can store these values in one cell while still being able to work with each item separately. This means project owners remain associated with their projects but are also easily accessible for filtering and calculations.

On the left, using comma-separated text limits filtering to complete text entries only. In contrast, lists on the right allow you to filter by each individual list item.

You can keep the output of any spilling formula in a single cell by wrapping the formula body with braces { }.

Maintaining connections among project owners is just the start.

Projects often include other sets of related information like milestone dates, deployment sites, or open risks. Many times, this data is generated through formulas rather than entered manually.

Initially, formulas that delivered multiple results would spread those results over various cells. However, with arrays in cells, these results can stay together in one cell while remaining accessible for calculations and analysis.

The example shows how TEXTSPLIT functions with and without nested arrays. Without nested arrays, Excel returns only the first item for each row. With nested arrays, the results spill over, one array per row.

Sometimes, the information linked to a project has its own structure.

For example, a website launch may encompass different workstreams for design, development, and testing, each with its own set of milestones. Nested arrays help keep these relationships intact, ensuring milestone groups are tied to their respective workstreams instead of merging everything into one list.

This approach provides a more intuitive way to organise and engage with complex data in Excel.

Additionally, we’ve introduced new functions designed to help you search, validate, and reorganise your data:

• If you need a simple list of all milestones throughout each workstream, try using FLATTEN to merge nested results.
• Got a specific owner in mind? Use HAS to find their projects.
• Looking for any projects involving a leadership team member? Use HASANY.
• Need to ensure all required stakeholders are included before a project launch? Use HASALL.

These capabilities significantly enhance the types of information that Excel can handle, making it easier to represent real-world data while keeping related information organised. Whether you’re tracking projects, analysing survey results, managing certifications, or sorting product data, you can maintain connections among related data without losing the ability to filter, calculate, and analyse.

For more comprehensive details about lists, arrays in cells, nested arrays, and the new functions, check out this resource: Put multiple values in one cell with lists and arrays in Excel | Microsoft Community Hub

If you’re keen to access these exciting tools now, consider joining Insiders Beta here >

Frequently Asked Questions

What are lists and arrays in Excel?

Lists and arrays are powerful tools in Excel that allow you to store related information, like project owners or milestones, within a single cell while still being able to access and manipulate that data individually.

How can I create a list in Excel?

To create a list, simply go to Insert and select List or press Ctrl + J. Then, type or paste the items you want, separated by commas or semicolons based on your settings.

Can I filter lists in Excel?

Yes, you can filter lists in Excel. By using the new list functionality, you can filter by individual items rather than just by entire text entries.

What are some new functions available in Excel’s Insiders Beta?

Some of the new functions include FLATTEN, HAS, HASANY, and HASALL, which help in searching and reorganising your data more efficiently.