How to use HAS, HASANY, and FLATTEN formulas?

Sakthi Akilesh 0 Reputation points
2026-10-20T06:41:05.2166667+00:00

Hi everyone,

I'm new to the community. I'm trying to use the new HAS, HASANY, and FLATTEN functions in Excel, but they don't appear to be available on my end.

I first contacted Microsoft Excel support, and they directed me here. We first thought I was on an older version of the app. However, I've since updated and reinstalled Excel, and I still can't use these functions.

My setup:

  • Excel for Mac, Version: 16.114 (26101123)
  • Microsoft 365 subscription: Personal

Has anyone else run into this? Is there a setting I need to turn on, or are these functions not yet available for my version? Any help would be appreciated.

Thanks in advance!

Microsoft 365 and Office | Excel | Other | MacOS
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Thomas4-N 23,140 Reputation points Microsoft External Staff Moderator
    2026-10-23T09:54:16.9+00:00

    Hello Sakthi Akilesh,

    Your Excel build is new enough, but these functions are currently Beta Channel preview features, not standard production features.

    Microsoft lists the minimum Mac version as 16.114 (Build 26092111). Your build 26101123 is newer, so the build itself is not the issue.

    Please check:

    1. Open Excel > Help > Check for Updates.
    2. In Microsoft AutoUpdate, confirm that the update channel is Beta Channel.
    3. Open the workbook and go to Formulas > Calculation Options.
    4. Select Compatibility Version 3, which is required for most nested-array calculations.
    5. Restart Excel and test the functions again.

    The functions use these formats:

    • HAS(array, value)
    • HASANY(array, values)
    • HASALL(array, values)
    • FLATTEN(array, [pad_value], [levels])

    If they still do not appear, Microsoft states that these preview features are being released gradually. An eligible Beta build therefore does not guarantee immediate access. Reinstalling the same production version will not enable them.

    Microsoft’s announcement:

    Put multiple values in one cell with lists and arrays in Excel

    Was this answer helpful?

  2. AI answer

    2026-10-20T06:42:07.0066667+00:00

    HAS, HASANY, and FLATTEN are not enabled in the production Excel for Mac 16.114 build; to try them now, install Excel through Microsoft’s Microsoft 365 Insider/Beta channel rather than reinstalling the same production release.^1^

    Switch to an Insider build

    Since updating and reinstalling production Excel did not help, check the installation source and channel:

    1. In Excel, select Help > Check for Updates.
    2. In Microsoft AutoUpdate, select Advanced.
    3. Under Update Channel, select the available Insider/Beta option.
    4. Accept the Insider agreement, select Check for Updates, and install all offered updates.
    5. Restart Excel and type =HAS, =HASANY, or =FLATTEN in a blank cell. If the function appears in formula autocomplete, it has reached your account and build.

    Microsoft documents Insider as the early-access route for preview features; displayed channel names can vary by account, build, and enrollment status.^2^

    If no Insider/Beta option appears

    Your Excel installation may be from the Mac App Store. App Store builds use the production channel and cannot receive Insider builds; Insider versions must be installed directly from Microsoft.^3^

    To identify the installation:

    1. Open Finder > Applications.
    2. Control-click Microsoft Excel.app, then select Show Package Contents.
    3. Open Contents.
    4. If _MASReceipt exists, it is the Mac App Store edition.^4^

    In that case, remove the App Store installation and install Microsoft 365 directly from Microsoft before enrolling in Insider. Back up unsaved local workbooks first. Your Microsoft 365 Personal subscription is eligible to activate the directly downloaded apps.

    What version 16.114 indicates

    Version 16.114 (Build 26101123) is the October 14, 2026 production release. Its published Excel entry contains quality and performance improvements but does not list these functions as a production feature.^5^

    Therefore:

    • There is no workbook preference that turns these functions on in production 16.114.
    • Reinstalling 16.114 will not add them.
    • Even on Beta, feature rollout can be staged, so installation of the newest Beta build does not guarantee immediate availability.
    • If the functions remain absent after installing Beta, use Help > Feedback in Excel and wait for the feature to reach that build/account.
    • Avoid sharing a workbook that depends on these functions with production-channel users until general availability; unavailable functions can appear with _xlfn. or return #NAME?.^6^

    The available Microsoft material does not yet provide authoritative production documentation for the final syntax, so examples from preview articles may change before general availability.

    References

    1. Excel Lists in Cells: HAS, HASALL, HASANY & FLATTEN
    2. Update Office for Mac automatically | Microsoft Support
    3. Microsoft Office and the Mac App Store | Microsoft Support
    4. Deploy Office for Mac from the Mac App Store
    5. Release notes for Office for Mac
    6. Excel issues on Mac (macOS Sequoia) - Microsoft Q&A
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.