Cascade formatting Excel formula

Question

I use Excel and Smartsheet a lot and make some very complex nested formulas. Because these are written in sheets rather than in any kind of IDE, there is little or no formatting to them for legibility, so I copy them into Notepad++ for debugging or working on them.

I have a user defined language in Notepad++ that works for Excel Formulae, and others for Smartsheet, Google Sheets etc.
I want to use these to fold formula sections up within functions (to quickly narrow down to the section that needs debugging). However, although the fold option works to hide whole lines, this doesn't work within a line so I need a plugin or something that will convert formulas from a long line to split them out per function and argument (indented properly of course!) I have seen auto-formatters for SQL that do this effectively (I'm a big fan of freeformatter), I'm looking for a way to do it in Notepad++ on different languages.

For example:

Input:

=IF(B2, IF(C2="Yes", "Result 1", "Result 2"), IF(D2>3, "Result4", "Result5"))

Output:

=IF(
    B2,
    IF(
        C2="Yes",
        "Result 1",
        "Result 2"
    ),
    IF(
        D2>3,
        "Result4",
        "Result5"
    )
)

Answer 1

Please install the Excel Labs add-in and use the Advanced Formula Environment to view and edit your formulas. It is based on the Monaco Editor used by VS Code and has all the benefits of that.

I have a short introduction to the Advanced Formula Environment on my blog here.

The Excel Labs add-in can be loaded from the Add-Ins store via the Add-Ins button on the home tab of the Excel Ribbon.

As you can see, complex formulae can be folded.

I have also compiled and published a list of keyboard shortcuts, should you find it helpful.

https://www.linkedin.com/posts/owenhprice_keyboard-shortcuts-for-the-advanced-formula-activity-7094683958608633856-ibEN/