Modeling Dynamic Excel 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.

 

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 that momentous.

 

 

Microsoft Excel has changed – Excel programming is now range-based, not cell-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.

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.

 

 


 

Demos of Excel Modeling in 2024 – Coming Soon

 

 

Excel Modeling in 2024 – GroupBy Demo – 

Pending.

 

Excel Modeling in 2024 – PivotBy Demo

Pending.

 

Excel Modeling in 2024 – CheckBox Demo

Pending.

 

Excel Modeling in 2024 – PercentOf Demo

Pending.

 

 


 

2024 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
    1. Use
      1. CheckBox
      2. PercentOf
      3. GroupBy
      4. PivotBy
      5. ET
      6. LAMBDA
      7. Next
      8. Next
      9. Etc.
  6. VBA
    1. With Dynamic Excel you can use less VBA code.

 

 


 

 

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.