Modeling Dynamic Excel 365 in 2024 for Business

It is February 2024 and the solutions I am developing right now will for my business clients will most likely be outdated within the year.  Why?  Because the Microsoft Excel Team has been making so many monumental changes in Excel, that Excel 365 is now called “Dynamic Excel“.  Covered in this post: Excel Modeling in 2024.

This is not the version of Excel you were using just two years ago.  The changes to Dynamic Excel are changing how we will program our Excel models in 2024, and beyond.

Question: Are your programming methods based on Excel BDAF or ADAF?   Advice:  For Excel Modeling in 2024, focus on the Dynamic Array Functions, and merge them with traditional funcitons.

At some point, you will need to learn these new functions, new features, and new objects, as the business world slowly implements them.   As you do, please note, that the trick is learning the best methods to integrate the various DAFs, with the traditional functions, such as the SumIfs Function, and embrace the # and Spill Range.

 

Do either of these functions make sense to you?

If not, it is the perfect time to learn how to program Excel 365, ‘dynaand mic Excel‘, and we are here to help.  Programming, Mentoring, Training services for Business.

=FILTER((D10#:R10#),R10#=tblSegment[Segment])

=SORT(LET(x,(UNIQUE(VSTACK(EH5#,EJ5#))),FILTER(x,x<>0)))

 

 


The new FILTER Function is one of the most powerful functions in Excel 365. Definitely in the top 5.

 

 

 

Image of our Company Logo. Excel Modeling in 2024 Blog Post.BDAF = Before Dynamic Array Functions

ADAF = After Dynamic Array Functions.

The changes that began in 2018 are momentous.

=XLOOKUP (InputCell, LookupRange, ResultRange) #

=SORT(LET(x,(UNIQUE(VSTACK(EH5#,EJ5#))),FILTER(x,x<>0)))

 

 

Microsoft Excel has changedExcel programming is now range-based

With the introduction the new dynamic array functions (DAF’s), and this thing called spill behavior, and the new calculation engine, you now program Excel at the range level.

 

Image of Excel file using DAF's.


Excel functions are now “range-based” and not just “cell-based”; they Spill.

 

These functions are so powerful, you can have one formula per row, column, or range.  One formula that “Spills“.   Base your functions on a Spill Range, and add the #.   Dynamic Excel at its finest.

You no longer need to worry about copying down formulas, in 2018 Excel went dynamic.

 

Ongoing Changes to Dynamic Excel Modeling in 2024

The changes to Excel come in waves, and when they are present on your PC will depend on the Excel 365 license you have.

Currently, the items below are available on the Beta version.  The two most exciting are GroupBy and PivotBy.

  1. CheckBox
  2. Stale Values
  3. PercentOf Function
  4. GroupBy Function – Game Changer
  5. PivotBy Function – Game Changer
    1. What will the impact be on the use of Pivot Tables?

We demo each of these below.  

 

The image below is an example of one cell, one dynamic function, that spills.  The BLUE ID cell has the function, the rest of the cells in the range are empty.  =TAKE(FullDataSetQuery#,NumRows+1,NumColumns).

Image of new TAKE Function. Excel Modeling in 2024 Blog Post.


The trick is learning how to get the most out of the new dynamic array functions (DAF’s). Here the user enters the number of rows and columns to return, in the blue cells, and the analysis updates.

 

 

Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today. We will build you a Dynamic Excel Solution

 

 


 

Expert’s Advice – Learn the LET and LAMBDA Functions – Excel Modeling in 2024

If you want to excel in Excel, if you want to be the Office Excel Guru, then learn the LET and LAMBDA Functions.  This is how you standout; do what others cannot.

LET and LAMBDA are the most powerful functions in Excel.  Behind them, the XLookup and SumIfs Functions will do 80%+ of what you need.   If you want to be a true expert, you will want to master both of these new functions.

 

=SORT(LET(x,(UNIQUE(VSTACK(EH5#,EJ5#))),FILTER(x,x<>0)))

 

 


 

Demos of Excel Modeling in 2024Ongoing

As new Functions or Features become available, we will show the more useful ones here.  Changes are constantly happening in dynamic Excel, so you do want to stay up to date.

=XLOOKUP (InputCell, LookupRange, ResultRange) #

For instance, did you notice that there is now a fourth Calculation Mode option?  See, they sneak them in if you are not watching.  What else did you not notice?

 

What else did you not notice?  These changes simplify how you program Microsoft Excel.

 

Transpose, Unique/Remove Duplicates, Sort/SortBy, are now functions!  That is crazy.  No keystrokes, no vba, no crazy combination of formulas, simplicity at its finest.

 

Image of Excel Tables being used as a database replacement solution.


Microsoft Excel has never been more powerful or easier to program or use. Excel 365, Dynamic Excel.

 

Power Query has never been more useful. 

Add columns to the Power Query output, and place calculations in them.  This is power. By combining both tools, you can custom tailor your solutions, based on your needs.

Image of Excel Power Query.


Power Query should be used in most data intensive Excel solutions, right along with Excel Tables.

 

 

Excel Modeling in 2024 – GroupBy Function 

If you are familiar with Microsoft Access programming, you are familiar with the GroupBy Function.  Similar concept.  Once again, we need to point out, that Microsoft Excel is now range-based programming, and not cell-based programming.  Resistance is futile.

 

Excel Modeling in 2024 – PivotBy Function

Some say this will reduce the use of Pivot Tables by as much as 75%.  I tend to agree with them.  I often use the new dynamic array functions to produce interactive reports that rival Pivot Tables in terms of ease-of-use while increasing the user interaction, making the reports ultra dynamic.  No need to refresh.

 

Excel Modeling in 2024 – CheckBox Feature

In the past you would either use Axtive X/Form Controls to place individual checkboxes into cells, or you use Validation Controls with a Yes, No option.  Now you can simply use the new Checkbox in a cell.  This is not above the cell, like the ActiveX Controls, but just like the Validation Control, it is in the cell.   Entering either True or False has never been easier.  Your formulas will love it, as will your users.

 

Excel Modeling in 2024 – PercentOf Demo Function

In the past, you would write a formula to do this, now you can simply use this simple to use function.  A nice enhancement.

 

 

New Calculation Mode – Stale Value Formatting

Did you not notice?

  • There are dozens of changes to Excel that we will not mention here, such as placing an image in a cell, versus over a cell.  We are focused on interactive business solutions, in data-driven models, so our focus is on the power aspects of Excel.

 


 

2024 Custom Dynamic Excel Development Services for Business

Where should we focus our development efforts in 2024, as these new functions and features come online?  The list below is where our team of Excel consultants focus their programming efforts.  As new features are released, we will add them here.

As Excel changes, we will change this list, and we will change how our consulting services can best leverage the new world of Dynamic Excel.

 

  1. Excel Tables
    1. Excel Table Lists
  2. Power Query
    1. Parameters
  3. Data Model
    1. Pivot Tables
    2. Pivot Charts
    3. PowerPivot
    4. Slicers
  4. Dashboards
  5. Dynamic Array Functions
    1. DAF Reports
  6. VBA
    1. With Dynamic Excel you can use less VBA code.
  7. Thoughtful Design, based on best practices.

 

Need Help, Hire Us:  What is the easiest way to learn the best practices, of the new Excel functions and features?  Easy, hire an Excel Mentor at Excel and Access, LLC.

 

 


 

 

Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today. We will build you a custom Excel Solution, based on the new Dynamic Excel.