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.
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