Google Sheets: FLATTEN, HAS & Lists

Key insights

• New functions: Microsoft added FLATTEN, HAS, HASANY, and HASALL so Excel can handle nested or multi-value arrays directly in formulas and produce in-cell lists.
  
• FLATTEN behavior: FLATTEN(array, [pad_value], [levels]) removes levels of nesting, spills a simpler range to the sheet, and can use pad_value to keep rows aligned instead of uneven output.
  
• Membership checks: HAS returns TRUE when a single value appears in an array; HASANY returns TRUE if any of several values appear; HASALL returns TRUE only when all listed values are present.
  
• Practical benefits: These functions cut the need for manual reshaping or Power Query for many tasks, let you write shorter formulas, and speed up workflows that use nested lists or spilled results.
  
• When to use: Use lists and FLATTEN for reshaping nested outputs and use HAS variants for quick membership tests; avoid them if you need full, documented behavior for complex edge cases until availability is confirmed.
  
• Quick tips: Reference list items with INDEX, combine FLATTEN and HAS to filter or test values, and prefer these functions when you want cleaner, in-cell array handling instead of extra cleanup steps.
  

Keywords

FLATTEN function, HAS function, lists in cells, Google Sheets FLATTEN, Excel FLATTEN function, how to use HAS function, lists inside cells guide, spreadsheet functions FLATTEN HAS