Skip to main content
AmigoFish

Everything You Knew About Excel Cells Just Changed

From Leila Gharani Ā· Ā· 9 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.

If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.

Watch on YouTube

Not rated yet

Description

Excel cells can now hold lists. Press Ctrl+J and use HAS, HASALL, HASANY and FLATTEN. šŸ“„ Download the practice file: https://www.xelplus.com/excel-lists-in-cells/?utm_source=yt-video&utm_medium=vid-description&utm_campaign=download-lists-in-cells&utm_content=excel-lists-in-cells#download

šŸŽ“ Modern Excel Functions: https://www.xelplus.com/course/new-excel-functions-course/?utm_source=yt-video&utm_medium=vid-description&utm_campaign=modern-functions&utm_content=excel-lists-in-cells (500,000+ enrolled in our courses)

Note: lists in cells are in preview for Microsoft 365 Insiders on the Beta Channel (Windows and Mac). They're rolling out gradually.

For 40 years, a cell held one value. Now it can hold a list. I take a project tracker where the whole team sits in one cell, turn it into lists with Ctrl+J, and filter so "Ann" no longer matches "Anna". Formulas you already know, like COUNTA, SUM, SORT and XLOOKUP, work with lists too.

Then I use the four new functions. HAS, HASALL and HASANY check who's on which project. FLATTEN splits lists into rows. GROUPBY builds a report of projects per person and per department.

āœ… Good to know: Ctrl+J is a toggle. Press it again to turn a list back into text. You can also go to Insert, List. Separate items with your Excel separator: a comma or a semicolon, depending on your regional settings. To keep a formula result in one cell, wrap it in curly braces: ={SORT(B2)}. Microsoft may still change the feature before release, so don't build important workbooks on it yet.

ā±ļø Chapters: 00:00 One cell, one value: the old problem 00:30 Convert text to lists with Ctrl+J 00:51 Filter by list items 01:05 COUNTA counts the items in a list 01:28 Turn a list back into text 01:57 Lists of numbers with SUM and COUNT 02:23 Create a list from Insert, List 03:00 Sort a list and keep it in one cell 03:29 List separator: comma or semicolon 03:46 XLOOKUP returns the whole team 04:17 Sort the result inside one cell 04:46 HAS: check if a list contains a value 05:30 HASALL: check for all values 05:52 HASANY: check for any value 06:09 FLATTEN: split lists into rows 06:51 GROUPBY: count projects per person 07:55 GROUPBY with UNIQUE: projects per department

šŸŽ¬ Related videos: GROUPBY and PIVOTBY explained: https://youtu.be/mCJzhCxEQlM GROUPBY tricks for better reports: https://youtu.be/yhppTEj5wqs BYROW and BYCOL with practical examples: https://youtu.be/YqaEJiwJj2I

āž”ļø Join this channel: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

šŸ‘‰ Get one practical Excel tip each week in our newsletter: https://link.xelplus.com/yt-d-newsletter

🚩 Connect with us: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/in/leilagharani

#excel #exceltips #excelfunctions

Back to Leila Gharani Ā· Report a problem