• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Advanced Formula Environment

You are here: Home / Excel Formulas / Excel Advanced Formula Environment
excel advanced formula environment
December 15, 2022 by Mynda Treacy

Excel’s new Advanced Formula Environment (AFE) is still in its early stages of development, but it’s already very useful, particularly if you’re scared to write your own LAMBDAs, as you’ll see it can write them for you!

You may have seen me use it a few times in tutorials lately, but recently it has undergone an update with some notable improvements, so I’m going to cover it in more detail in this tutorial.

You can get the free AFE add-in from the Office store or via the Insert tab > Get Add-ins, and it’s compatible with Excel 2013 for Desktop onward, as well as Excel for Web and Mac. Once installed, it’ll be available on the Home tab of the ribbon on the far right:

install excel advanced formula environment

It opens in a task pane, which you can detach and resize.

Note: as this tool is still in development, the features and interface you see in this tutorial may be different by the time you come to use it.

Watch the Video

Subscribe YouTube


Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.


Excel Advanced Formula Environment Features

The AFE is split into three main sections: Grid, Names and Modules.

Grid: the grid tab enables you to enter formulas in cells, much like typing a formula in the formula bar, except it automatically wraps the formula onto separate rows for each component and applies colour coding.

excel advanced formula environment grid

Names: The names tab is similar to the Name Manager for defined names. The first section contains Functions you write yourself i.e. LAMBDAs:

excel advanced formula environment names

You can also author new LAMBDAs from here by clicking the + symbol shown below:

writing lambda functions in the excel advanced formula environment

Then enter the formula. Note: here you do not need to wrap the formula in LAMBDA, simply enter your formula in the Function Definition field and click Done:

lambda function details in the excel advanced formula environment

When you view the formula in the Name Manager, you’ll see it has written the LAMBDA for you:

lambda functions shown in excel name manager

You can also reference named constants in the AFE. These appear in the Names section under Formulas and are available in the intelisense. For example, I have a defined name for the VAT rate called VATRate, and I can reference this in my NetVAT formula:

referencing named constants in the excel advanced formula environment

This way should the VAT rate ever change, I only need to edit the defined name VATRate and it will feed through to any functions and formulas using it.

Another option is to generate LAMBDAs from formulas in the grid. You can convert calculations split into steps across several cells into a single LAMBDA. Select the cell containing the final result and then click the icon second from the end:

generate lambda function from formulas in the grid in the excel advanced formula environment

Then enter the range (these are the cells containing the intermediate formulas), the parameters (these are the cells being referenced by the formula), and the output cell (this is the cell containing the formula):

entering the range, cells and output for a lambda function in the excel advanced formula environment

Tip: if the range containing the formulas isn’t contiguous, you can enter the cell references separated by a comma.

Click Preview to see the LAMBDA Excel has written for you (image below). It makes some assumptions about the parameter names, which you can change here.

Tip: select one instance of the name and press F2 to rename all instances:

preview lambda function written in the excel advanced formula environment

Before clicking Create, you can rename the Function if you wish.

It should save automatically and be available in the name manager and grid, but if it doesn’t appear, you can click Save again. This will save it in the Name Manager and you can now use the function in the grid.

Ranges: The ranges area lists any named ranges in the file, however it currently doesn’t include Tables and it cannot display defined names that reference dynamic arrays.

the ranges tab of the names section in the excel advanced formula environment

Formulas: The formulas tab contains formulas you write and define with a name:

the formulas tab of the names section in the excel advanced formula environment

Modules: The Modules tab is used to store collections of named formulas defined using Gist code files from GitHub (cloud icon) or imported from the grid (spreadsheet icon).

the modules tab in the excel advanced formula environment

You can try it out with the Gist file available at this URL: https://gist.github.com/jack-williams/5859d170fcb363dad1620c4d40770527

They can be added to the Workbook module (see image above), or you can create a new module (see image below).

create new module in the excel advanced formula environment

Both are available within the workbook and appear in function auto-complete. Functions in the Workbook module appear in the intellisense list as is, whereas functions added to a new module are prefixed by the function name, separated by a dot operator

new function showing in intellisense

Formula Portability

Formulas saved in the AFE are also in the Name Manager of the file. If you share the file with someone else, then they will have access to those functions in the file, just the same as any other defined names.

Localisation

AFE has some support for localisation of formulas and no support for localisation of app text. Formulas in the AFE must be edited using a comma argument separator, for example =SUM(A2,C2,E2), however the AFE will interact with workbooks using other separators such as semi-colon (;).

When reading or saving a formula, the AFE will automatically translate between formats. Function names can be written using the workbook’s locale, but you can force English function names via the settings (see screenshot below). The AFE will eventually support full formula localisation, rather than requiring comma argument separators.

excel advanced formula environment localisation settings

excel advanced formula environment

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.

5 Excel SUM Function Tricks

Excel SUM Function tricks uncover a load of hidden features that aren’t obvious at first glance. Some are super useful in other functions too.
Category: Excel Formulas
Previous Post:Pro Excel Formula Writing TipsPro Excel Formula Writing Tips
Next Post:Top Excel Functions for Data Analyststop excel functions for data analysts

Reader Interactions

Comments

  1. Kevin NEWNS-SMITH

    December 16, 2022 at 2:45 pm

    Hi Mynda – your instructions for accessing the Advanced Formula Environment is via the Home tab on the ribbon – in my case it is via the Formula tab !!!!

    Reply
    • Mynda Treacy

      December 16, 2022 at 4:11 pm

      Ok. Might be a regional variation, or a recent update.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x