Changes Continued in Dynamic Excel # in 2023

I am sitting on a train, looking out the window at the Pacific Ocean, as I make my way to Santa Barbara to celebrate the Christmas holiday with family.

Nothing to do for the next 5 hours.  Given that the year is coming to an end, this is the perfect time to write a new Excel post, as it relates to 2023.  So much happened in 2023, and in Dynamic Excel, I will cover it all here.

In 2024 we will create a new post, to cover each and every change of the year.

 

Join the Dynamic Excel Revolution #excelautomation

Everything in Excel is changing.  It is changing fast, so fast, in an entirely new direction.  It is a revolution, a dynamic Excel revolution. Pay attention or be left behind, with old-school skills, that are out of favor in this new Dynamic version of Excel.

Dynamic Excel – Fewer Formulas, fewer macros, increased efficiency.  Join the Dynamic Excel Revolution.

 

Image of an Excel Table being referenced by a few of the dynamic array functions.


Referencing data in Excel Tables has never been more dynamic, or easier. Join the Dynamic Excel Revolution!!! Just point your Excel formula at a Spill Cell, and add the #.

 

Excel Expert’s Note: LinkedIn is the best place to stay abreast of all of the changes in Excel, which are coming almost monthly it seems.  LinkedIn Learning replaces Lynda.Com.  Many of the same experts, just a new platform.  LinkedIn Learning sessions come with Excel workbook example for you to work along with.  Other than one-on-one Excel training, this is the best way to learn Microsoft Excel.

 

Need help, we offer professional consulting services in Excel and Access, and we can assist you with all things Excel.

 

Excel and Access, LLC is an Excel, Access, SQL Sever, and Azure Consultancy.

Banner for one-on-one training page.


One-on-one training with a Microsoft Excel MVP is the best way to learn Microsoft Excel. Bob is the ultimate Excel trainer, and programmer. To really learn Excel, train one-on-one with Bob.

 

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

 


 

 

2023 saw many changes in Microsoft Excel, did you notice?

In 2023 many Excel consultants and trainers spent the year learning what is new in Microsoft Excel.  The best experts also took the time to learn best practices.  Much has changed since 2018, and 2023 was another pivotal year for those making their living in Microsoft Excel.

If you are not “actively” looking for changes in Excel, you will most likely miss them, not knowing that there are functions and features in Excel that you do not know how to use. Log into LinkedIn weekly to stay abreast of all of the changes in Excel.

 

What Does This Mean to you, as an Excel consultant or programmer?

It means a lot, it means the methods you have been using in Excel, say since Excel 1.0 in 1985, are out of date as is your work.  You are doing things the hard way.  Simplify your life, work in Excel 365, and leverage the power of the new calculation engine, with the new dynamic array functions.

 

Biggest Changes to Microsoft Excel Since 2018

  1. New Calculation Engine – Excel goes Dynamic #
  2. Introduction of new Dynamic Array Functions
    1. Spill Array Formulas
    2. Enter the use of the #
      1. Traditional Excel Formulas are now also Dynamic.
    3. Enter real power: LET, LAMBDA, Functions
  3. Changes to Microsoft Excel’s Interface and Features, Remove Duplicates, Checkboxes, Validation-Controls, Navigation, etc.
  4. Changes to Excel Tables
  5. Changes to Power Query
  6. Range-Based Programming, versus Cell-Based Programming.
    1. aka, Spill Cell Programming: Point your formulas to the Spill Range, first cell, so you can apply the #.
      1. This is a game changer.  One dynamic formula per row/column.
  7. Dynamic Array Reports:
    1. Build you reports using the new Dynamic Array Functions.  Once Function, fills a full range, dynamically.
      1. Apply the Filter Function, Sort Functions, etc., to make it even more dynamic.

 

Result:  You will never program the same; the days of cell-programming are gone, range-programming is the new way to write formulas in Excel.   You now write one formula, that goes in one cell, and it returns an array (range) of data.

 


 

 

Microsoft Excel Expert’s Advice: 

Base your Excel solutions on Excel Tables, Power Query and the Dynamic Array Functions.

 

Excel can do so many things, in so many ways; you only need to know say 10% of what Excel can do, to be amazing in Excel.  So what to base your work on?

If you base your Excel solutions the combined Excel Tables with Power Query, you will be able to do some very advanced Excel modeling.

If you need help with Power Query Programming, please give us a call at 877-392-3539.

  1. Base your data model on Excel Tables, with just the base elements.
  2. Upload the Table into Power Query.
  3. In Power Query, manipulate your data.
  4. Download Power Query Table to Excel sheet.
  5. Make a change to the Blue Table, hit RefreshAll, and the Green Table will reflect the changes in the Blue Table.
    1. In Dynamic Excel, you can use fewer formulas, less vba, and you can expand the automation capacities.
    2. In dynamic Excel you focus on the use of Spill Cells / Spill Ranges, and the #.

 

Images of Excel Tables and Power Query.


How you program Excel is changing: less vba, fewer formulas, more automation. Excel Table into Power Query ( Blue ), Excel Table out of Power Query ( Green ). Power Query is a data transformation tool, use it.

Join the Dynamic Excel Revolution!

 

 


 

Examples of how formulas are now written in Dynamic Excel 365.

  1. =SORT(FILTER(tblDetails,tblDetails[Year]=DynamicReportYearFilter),DynamicReportSortColumn,DynamicReportSortOrder)
  2. =SUM(H23:INDEX(H23#,CurrentMonth))
  3. =VSTACK(CHOOSECOLS(TableHeaderRow,D6#),FILTER(CHOOSECOLS(tblSampleData,D6#),(tblSampleData[Region]=SelectedRegion_2)))

 

Image of functions in the formula bar in Excel workbook.


One cell, one complex function, populates an entire range. Range-based programming in dynamic Excel 365 is the way to go.

=IFERROR(FILTER(CHOOSECOLS(tblServiceOrders,SEQUENCE(ColumnsToReturn,,1,1)), CHOOSECOLS(tblServiceOrders,SelectedCriteriaColumnsToReturn)=SelectedRepairStatus),”No Records”)

 

 


 

Hashtag # Programming in Dynamic Excel 365

One cell, one Function, populates an entire range. This is “range-based programming“.

Now experts focus on the use of the # to simplify their programming. It is the biggest change to Excel since the calculation engine was rebuilt.  Leverage spilled array behavior in your workbooks.

Add the # to the end of a VLookup or SumIfs, in a formula, in one cell, and see what happens.

 

Important Note:  If you only learn one new thing in Excel, learn this!

 

Image from Microsoft Website on Dynamic Array Functions.

If you do not know what this means, then you are programming the old way, and your work is dated and less efficient than the new Dynamic Excel released in 2018, updated in 2023.

In 2024: Focus on creating Spill Cells / Spill Ranges, and using the # in your functions.

 

 

Image of Excel file, showing the introduction of the Hashtag converts the legacy functions and makes them dynamic. #

Enter the # – a Game Changer. Once you learn to leverage the #, you will never program the same.

Excel Expert’s Note: The Validation Control Drop-Down list now automatically provides a Unique List. Or you can reference a Spill Cell with #, and the list will be dynamic, as in the image above.

 

 


 

Excel and Access, LLC’s Advice for Excel Programming in 2024 

Simple, focus on programming’s best practices, based on Excel’s most powerful and useful functions and features.  Learn to use these well and build powerful yet stable solutions for your clients.

Build Dynamic, Automated, Interactive Dashboard Solutions in Microsoft Excel.  Focus on the most advanced functions and features of Excel:

  1. Power Query
  2. Excel Tables
  3. Data Model
    1. Pivot Tables
    2. Pivot Charts
    3. PowerPivot
  4. Dynamic Array Functions ( DAFs )
  5. VBA / Macros

 

By combing Power Query with Excel Tables and the new dynamic array functions, you can do so much more, with much less effort than you could before Excel went dynamic.  You will write fewer formulas, you will populate fewer cells with formulas, and you will write less vba/macros.

 

Image of Excel Table alongside Power Query.

Excel Tables and Power Query are two sides of the same coin. They work best when used together. They simplify Excel programming. Add the # to your functions, and you have a powerful, dynamic, version of Excel.

 

 


 

Staying Current Since Microsoft Excel Went Dynamic in 2018 via LinkedIn

Much has changed since then, and things will continue to change.  So now is the time to catchup, and to stay current on the changes the Microsoft Excel Team is making to Excel 365.

Are you up to date on everything Excel?  If not, no worries, most people do not realize that Microsoft Excel is now dynamic, a huge shift in the use of Excel, based on the calculation engine.  If you want to stay on top of the changes in excel, we suggest you join the Excel Community on LinkedIn.

The Excel experts on LinkedIn will tell you all that you need to know, to take advantage to the changes in Excel.

Automating solutions in Microsoft Excel has never been easier, as Excel is now dynamic due to the new calculation engine and the new dynamic array functions.

 

Image of LinkedIn experts on LinkedIn.Com.


LinkedIn Learning has some big names, individuals with amazing talent, teaching the masses all they need to know about Excel. Many of these same experts have their own websites, where you can pay for online based training. (Jon, Leila, Mark, Mynda, all have incredible materials available. check them out.)

 

 


 

 

It is time to rethink how we work in Excel; Work Smarter as Excel Goes Dynamic!

 

Dynamic Excel” is not the same Excel that your mother used

If you are not using Excel 365, along with the Dynamic Array Functions, you are programming in the past.  Your work is dated, and it is not optimal.  Excel is no longer cell-based, in 2018 MS Excel became range-based programming.

You no longer have to use Index-Match, legacy Arrays, or the VLookup.  Instead, use the XLookup, as well as the new Dynamic Array Functions, and make SURE to use # and to leverage Spill Cells / Spill Range.   *

 

Don’t copy your formulas down or across.  Think differently; “there is no box”.  Bend the rules programming.

 

  1. Unique, Filter, Sort, SortBy
  2. TextJoin, TextBefore, TextAfter
  3. ChooseCols, ChooseRows, Take, Drop, Transpose
  4. Sequence, HStack, VStack, ToCols, ToRows
  5. Let, Lambda
  6. GroupBy, PivotBy
  7. XMatch, XLookup
  8. PercentOf
  • Just an example of the new Dynamic Array Functions available in Excel 365 ( Dynamic Excel )

 

Excel LogoExpert’s Note:  Now you think, how can I introduce a Spill Cell / Spill Range so that I can leverage the dynamic power of the #

The green shaded cells in the image below are Spill Cells. All you do is put a formula, say a SumIf, in the first row of the column.  When you write the function, point it at the Spill Cell, at the top of the Spill Range.  Make sure to use the # in your function.

Now the functions will grow and shrink, along with the data referenced via the Spill Cell.  This is how Excel is done in 2024 and beyond.

This is Range Based Programming, not cell based programming.

 

 

Image of dynamic excel functions in use. example.


By introducing a Spill Cell, you can point most functions at that cell, add the #, and now that function is also dynamic. No need to copy formulas down or across.

 

Expert’s Note:  With the new engine, with the new dynamic array functions, with the new tools such as Remove Duplicates, you can do in seconds that which used to take hours, and this is dynamic, so you never have to do it again.

 

Everything eventually changes:  Prior to the changes in the calculation engine, Excel was “Cell-Based“, meaning you program, cell-by-cell.  But in Excel 365, you now program at the “Range Level“, not the cell level.

 

 


 

 

Change how you think about your Methods of Excel Programming

Think in terms of ranges, not individual cells.  Functions now produce result sets, much like Access, no longer simply filling one cell at a time.

Excel is becoming Access-Like with the latest round of dynamic array functions, such as GroupBy, PivotBy, SortBy, etc.

Instead of saying, what function, or functions, should I put in that cell, to return say xyz,  Now you think, how do I introduce a Spill Cell, so that I can create a complete result set, with one function, in one cell, one that is 100% dynamic.

To keep up, you must change how you think about Excel.  There is no way around it, learn now and remain an expert, or learn it later to catchup.   The changes are amazingly easy to use.

 

Animated image from Microsoft on Spill Ranges.


From the Microsoft website, they show how powerful dynamic Excel is. The Functions in this demo are looking at a Spill Cell, see the use of the #.

 


 

The Excel and Access Experts Reaction to the Changes in Microsoft Excel in 2023

2023 was a pivotal year here at Excel and Access, LLC in terms of what we do for our clients.  This year our team of Excel consultants and trainers dug in to learn best programming practices when it comes to the latest version of the new and improved “Dynamic Excel”.

 

Initially it was hard finding out anything at all on these changes:

Given that many of the changes have gone unnoticed by the vast majority of Excel users, to learn what this new version of Excel has to offer, and what this version was capable of.  We searched for what limited resources were available.  Microsoft’s website was not even up to date, as the changes were happening so quickly.  Most of the info we could find was on a few websites, then over time, the LinkedIn Excel Community started to release posts and videos on the changes in Microsoft Excel.     In early 2022, it was hard to find any information at all.

 

LinkedIn’s Excel Community Filled the Gap

Best source of info:  the LinkedIn based Microsoft Excel experts, like Jon, Oz, Mynda, Leila, and Mark, as well as the rest of the crew, really stepped in to help spread the word.  Many are Excel MVP’s, many are not, but all are passionate about Excel. They can teach you all that you need to know about Excel Tables, Power Query, Pivots, the Data Model, and the Dynamic Array Functions.  Learn from the Microsoft Excel subject matter experts.

Excel is changing at an increasing rate, LI is your best source to stay on top of the changes, and how best to use them.

 

Personally: I first watch Trump Excel’s videos on the XLookup. Great coverage early in the game.  Then watched Oz on Power Query, mostly on LinkedIn Learning.   After that I took the private courses offered by Jon (Excel Campus) and Mark (Excel Off the Grid) on Power Query and on Excel Tables.  I also watched their LinkedIn materials.  Finally, Mynda and Leila taught me much as well.   To me Leila is the face of Excel.

 

Exceptional TalentAmazing Instructors.  There is no other word for it, this is how you quickly learn to program in Microsoft Excel.  Just make sure to practice, practice, practice.

 

Image from LinkedIn Learning.


LinkedIn Learning is the second best way to learn how to program in Microsoft Excel.

Our one-on-one Excel training with an Excel MVP is the Best way to learn how to program Excel. Period.

 

 


 

Excel has always made changes between versions, but nothing like this.

Historically speaking, the Microsoft Excel Team has always made changes to MS Excel, each time a new version was released. Which would be every few years.  And while the purchased editions of Excel do have periodic updates via downloads, the creation of Excel 365 changed the landscape drastically.  Excel is now being updated all the time, most users do not even notice.

 

Excel Expert’s Advice:  Now you may receive changes to Excel every few months, and many of these ae significant changes. Office 365 is now mandatory, of you want to have the latest and greatest from Microsoft.  We recommend that most users use Excel 365.

 

This is Revolutionary – Excel 365 is now in a Constant State of Flux

If you were unaware of the changes to Excel, most people are unaware, and if you were to open a workbook that uses these new DAF Functions, you would be confused, they would make zero sense to you.  Choosecols, Take, Drop, Filter, SortBy, GroupBy, PivotBy, LAMBDA, & LET.  Let what? 

Microsoft Excel is not a database, but Excel is becoming more Access-like.  Is this a database or a workbook?  These new functions are database functions ( GroupBy, SortBy, VStack, PivotBy, etc. ).  

I recently had the CEO of a large healthcare client say to me, “our finance department cannot do what you can do, they do not know any of this, that is why we contracted with you“.

With these tools, I can do what their entire finance department cannot.  The tools, properly used, are the difference.  They don’t know what they don’t know.  It is time to rethink how we work in Excel.  It is time to be informed and to stay informed.

Stay informed or be left behind. 

 

 

It is the tools; they make the difference.  Learn them or someone else may take your job.  Or learn them because they simplify your programming effort.  DAFs greatly decrease the number of formulas you write.  One function per range, not one function per cell.  No need to copy them down, or across, as they are now dynamic.

 

Even worse possibility let’s say you call yourself an Excel expert, you make your way to your client’s office, and while there, he asks for help on a spreadsheet his entry level associate analyst developed.   Your client says, he is out of the office, and I desperately need to update this file.  You look it over and realize, you have no understanding of what you are seeing.  XLookup?  Sequence?  LET? Choosecols, Filter, what? Spill Error?  These are not the Excel functions your mother used to use.

You instantly look the lesser.  From expert to intermediate in the blink of an eye, at least in your clients’ eyes.

How did this happen?  Simple, you did not know Excel changed, and now you need to play catchup.  But don’t stress, the MVPs and other Excel experts will help you to understand what you are missing.  With some hard work, in three to six months, you will know all you need to know.

 

Image of Excel report crated in Power Query.


This report was 100% created via Power Query, referencing multiple CSV files. Neither macros nor formulas were used to create the report.

 

If you need to hire a firm to assist you, give us a call at 877-392-3539 and we will help you with all of your Excel files.

 

Contact us Image

 


 

 

Enter the Microsoft Excel MVPs

The Microsoft Certified, Excel Subject Matter experts

In the beginning it was almost impossible to get much information on anything, even on the Microsoft website.  Then the Microsoft Excel MVPs stepped in, and not only told us, but they also showed us, all there is to know about this new version of Excel.  You can take their paid courses, or you can learn for free via YouTube videos.  Either way, you will not find a better source of information on the changes in Excel.

The LinkedIn Excel Community will help you to learn all you need to learn in Excel.  LinkedIn Learning, well worth the price, and you get a tax deduction.

What a community.  What a resource.  Where would we be without them. Let the Microsoft Excel Subject Matter experts guide you.

If you are looking for one-on-one Excel MVP based training, we recommend Bob.

 

We Reached out to Top Excel Experts

Bob has been an MVP since 1993 – the First Year of the MVP Program

I remember being in a Zoom session with the Legendary Microsoft Excel MPV, Bob, and he showed me this new function called Unique.  He showed me quickly ( A few seconds ), and then moved on.  I was like, what, what was that?  After that call, I started my research and I have been at it since, that is now two years ago.

Bob Umlas

Since then, I no longer approach Excel in quite the same way; I now program custom Excel solutions, at the range level. Much like an Access database.  Ultimately learning best-practices on the new tools that the Microsoft Excel Team provided.

Our goal at Excel and Access, LLC was to fully understand these changes, and what they will mean to our clients, and their use of Excel, as we implement these changes into their custom solutions.  We had many conversations with many a programmer, we watched many a video, we took many a course to see how they are benefiting from the recent changes in Excel.  It was in these conversations that we were able to fully understand the changes and how we can best utilize them.

 

Our clients love working with Bob.  He gets some amazing reviews.  He knows Excel like no other.  If you want to learn Excel, work with Bob.

 

 


 

The # can be used in any Excel Function, almost

Then I saw something odd in a video on Excel functions and making them dynamic.   I forget who it was, but he was not an Excel MVP.   The second time I saw someone use it, and that was Leila.  Instantly reached out to Bob, to get more info.  Bob, Leila, what don’t they know, at the expert level?

So what caught my eye:  There was this # in the function.  Instead of a range, they used the # tag, referencing a single cell a Spill Range / Spill Cell.  What was that, and what does it mean?  It ended up being one of the most exciting things I have ever seen in Excel, I can really use that.  I have never programmed the same.

And it does not only work with the new DAF’s, it works with almost all of the Excel functions (Vlookup, SumIfs, Index, etc.)

 

Image of Excel Function


If you use the # in your SumIfs, you will not need to copy the formula down, or across, as the data range grows.

 

 


 

Microsoft says this about Dynamic Array Functions:

Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling.

Formulas that can return arrays of variable size are called dynamic array formulas. Formulas that are currently returning arrays that are successfully spilling can be referred to as  spilled array formulas.

Image from Microsoft website on DAFs

 

Add # to a Function to Create Dynamic Excel Functions

I remember the first time I saw a formula that said something similar to SalesData#, as one of the functions arguments.

That was definitely a game changer, stopped me in my tracks.  What the heck was going on.  I Googled and found nothing.  I asked a few experts, nothing.  Then I saw a video by Leila, and there it was, front and center.

I played with this one a lot, and I continue to push it today, to see how far I can take this.  In my mind Spill Ranges allow me to do more, with less.

Excel is now dynamic, goal, learn best-practice in terms of using the # within the custom Excel trackers and solutions that I and the team, build.

 

Image of # being used in Excel Validation Control.


Use the # with your Validation Control.

 

One of the Most useful changes in Excel is the introduction of the # .

With the introduction of the hash tag, at the end of a reference, in a function, you no longer need to copy down/across your formulas.  That is HUGE!!!!!  One of the most important changes to Excel, right after the design of the new Excel calculation engine.  Excel is now dynamic, thus easier to automate.

  • Vlookup, SumIfs, Concatenate, A1#+B1#, they all work with the #, basically making them dynamic as well.
  • You can add the # or you can select all of the criteria, at once, for the criteria, say in a VLookup.
  • Yes, in the new Dynamic Excel, you can select multiple criteria at once, select a criteria range, and you will return multiple values, such as with the VLookup or SumIfs.
    • You should be writing your formulas differently now.
  • Both will fill more than one cell, the Spill Cell and Spill Range.

 

 

Most overlooked change, even “Missed“.

– as stated above, the older functions such as the Vlookup, SumIf, and Concat will all now work as semi-dynamic functions.  Take the VLookup for example, you can point at a range of cells, as the criteria for the search; you do not need to select a single cell to reference and to lookup, and it will return more than one cell.  Those days of cell-to-cell programming are largely gone, if you take the opportunity to learn all about the new Dynamic Excel.

 

Check this out:  In Excel, you can now select a range as the criteria for the function ( Example, VLOOKUP), and the single function will populate a row or column, one cell, one function, no need to use the #.

 

=VLOOKUP(O14:O18,DataTable,SelectedColumn)

=FILTER(tblDetails,tblDetails[Year]=2020)

=IFERROR(P8#/(O8#+P8#),0)

 

10 Excel Functions merged as one. One cell, one function, returns range, dynamically: This is the new Excel.

=SORT(HSTACK(UNIQUE(tblCaseLoad[Therapist_ID]),XLOOKUP(T7#,L:L,M:N),XLOOKUP(U7#,M:M,N:N),SUMIF(L:L,T7#,O:O),SUMIF(M:M,U7#,P:P),IFERROR(SUMIF(M:M,U7#,Q:Q),”See data”),X7#/SUM(X7#)),F2,G2)

 

 

Image of Excel feeding Power Query variables.


Power Query should be used to transform your data. Allow users to select files, run the update process, all without the use of VBA, if so desired.

 

 

 


 

 

Our Favorite Changes in Dynamic Excel, the new Dynamic Array Functions:

No worry, these are not the legacy Arrays that you are afraid of; these are so easy to use. Reporting has never been easier

  1. Filter
  2. Unique
  3. Sort, SortBy
  4. Sequence
  5. ChooseCols, ChooseRows
  6. Take
  7. Drop
  8. LET / LAMBDA
  9. ToCol, ToRow, etc.
  10. TextBefore, TextAfter, TextJoin, etc.
  11. VStack, HStack
  12. GroupBy
  13. PivotBy
  14. PercentOf
  15. More to come in the weeks ahead

 

  • Best used of DAFs is to combine 3 or more of them into one complex function, giving results much like an Access database.  One cell, one complex function, spills as needed.
    • Choose the Data Source
    • Pull Unique Records
    • Choose the Columns Desired
    • Set Column Order
    • Filter the rows by Multiple Criteria
    • SortBy
    • GroupBy
    • PivotBy

 

 

Advanced Excel Functions really do not exist

The VLookup, SumIfs, Let, Sequence, etc., these are not hard to learn, these are not ‘advanced’, rather just more intermediate functions, just a lot more powerful.

  • There is no such thing as an advanced functions in Excel; functions may be intermediate, but not advanced.
    • Combining two or more DAFs into one complex function, is an ‘advanced function’.   And that is where the power is.

Dealing with large datasets, with more columns than you need, with rows you want to filter, the ChooseCols, Take, Filter, Unique, functions will make it easy, and you can forget about the need to use VBA.

 

Image of simple Pivot Table, referencing Power Query data, brought in via a CSV.


Create Pivot Tables in Power Query, based on external data. Use the Data Model to combine Budgets, Actuals, and Forecasts into one Table.

 


 

 

Want even more power – Power Query takes Excel to the next level!

Want to make it even easier, then instead of using the DAFs to manipulate entire datasets, use Power Query.  We will cover more on the combined use of Power Query, Excel Tables, DAF, Pivots, vba and the Data Model in 2024.

Image of Power Query.


Power Query does a better job of slicing data than does Excel formulas. Use Power Query to transform your data into Excel. Use Excel to present the data. Power Query is like a database inside of Microsoft Excel.

 

 

 


 

 

If you are looking to learn all about the new Dynamic Excel, we recommend you start by watching the LinkedIn Learning or You Tube videos by the Excel subject matter experts below. Then expand to see what the other LinkedIn based Excel experts have to say, so many of them.

 

 

Where to learn all about the changes in Excel – Top Excel Experts

Along the way we found some very good sources to learn, just what has changed, what you need to know, and most importantly, how to use them, in relation to best practices.

If these concepts are new to you, such as the XLOOKUP, Unique, Filter, etc., I recommend that you start with Trump Excel, then follow that up with Oz. You will get a solid foundation from the two of them.  Both explain things very well.  I highly recommend each.

Microsoft Excel’s Subject Matter Experts, Certified by Microsoft

If you work in a business setting, Jon is a great instructor.  Jon leverages his financial background to teach you all that Excel can do in a business setting.

When it comes to Power Query, one word covers it, Mark.  Mark has what I consider to be the highest level of difficulty in his courses.  If you want to learn, I mean really learn Power Query, then Mark is a must.

But I would watch Oz and Jon first, to get you ready for what Mark will reveal.  Mark literally changes how you work in Power Query, with the tools he offers, like his fxApproximateMatchFunction.  Joining Excel Off the Grid is a good idea.

Leila and Mynda are exceptional instructors, on my top 5 resources for learning all about the changes in Excel, as they happen.  The sheer volume of quality material that this small group of MVPs puts out is staggering.  Try to watch them all.  If you want to learn, LinkedIn is ground zero.

Mynda has been at this for a longtime, and their site is exceptional.  MyOnlineTrainingHub, huge resource. One you should bookmark.

 

 

To Learn Dynamic Excel I Recommend you do the following:

In the past, to learn about Excel, to get help, you would visit one or more of the Excel forums, such as VBAExpress.Com, or ExpertsExchange.Com.

  1. Watch the Trump Excel and Oz YouTube and LinkedIn Learning videos and courses.
    1. Oz covers Power Query very well. Easy to learn following his teachings.
      1. Practice, practice, practice.
    2. Watch the Jon courses on Tables, Pivots, DAF, and Power Query.
      1. Build models of your own, trying to what he is showing you.
        1. This will take some time.
        2. Watch the other videos that you find.
          1. Practice, practice, practice.
        3. Finally, join a few LinkedIn Excel groups, so you can ask question, speak directly with other Excel experts. LinkedIn is the best place to keep abreast of the changes in Excel.
          1. Learning how to advance your Excel programming skills has never been easier.
            1. Join the Dynamic Excel Revolution #

 

Image of Excel workbook used in our one-on-one training sessions.


If you want to be good in Excel, you need to Practice, Practice, Practice. This is an example workbook of how we help you to practice.

 


 

Join the Revolution, the Dynamic Excel Revolution – #excelautomation

Connect with us, Excelandaccess.com, and a growing group of Dynamic Excel, DAF, PQ, Pivot, VBA solution providers on LinkedIn.  Learn the latest; learn what is available in the Beta program, and test these long before all other 365 users get their hands on it.

See what complex solutions this group has come up with, with his highly unique method of programming Excel, range-based programming.

 

 


 

 

Most Recent Changes in Excel 365:

  • Validation Controls – Dropdown lists.
  • Views
  • Checkboxes
  • Navigation Pane
  • GroupBy
  • PivotBy
  • Are all available to those in the early release, help test, versions of Excel 365, like the Beta program.
  • Minimal changes?  No.
  • Checkboxes for example, while super simple and basic, are long over needed.  Check = True, Unchecked = False.
  • A function that sorts, long overdue.
  • These are significant changes.

 

And the Validation Control, now always provides a unique list of items, and there is no longer the need to use Unique or Filter to create the Unique List.  Add sort, and wow, that is useful to the user experience; make a selection based on a dynamic list.

 

 


 

Next year, 2024, we plan to …….. ( Stay tuned )

 

Tools for 2024 Excel Programming Solutions:

  1. Dynamic Array Functions.
  2. Use of #.
  3. Excel Tables.
    1. Excel Tables in place of forms for data entry.
  4. Power Query.
  5. Data Model
    1. Pivot Tables
    2. Pivot Charts
    3. Slicers
  6. VBA/Macros
  7. Validation Controls, Conditional Formatting, etc.
  8. Best practices

 

A lot changed in 2024 and more will change in 2024 as Dynamic Excel pushes the boundaries even further.

If you make your living, working in Microsoft Excel, staying abreast of the changes has never been more important.  Since 2018 the Microsoft Excel Team has made so many significant changes to Excel, that professional Excel consultants now program Excel differently than they did before Excel went dynamic, before the dynamic array functions.  Keeping abreast of all of this is a challenge.

But luckily for us, the Excel community on LinkedIn is strong, full of passionate Excel experts, willing to share what they know.  LinkedIn .Com, the best place to learn about all of the changes in Excel 365.

 

 


 

 

 

Contact Us Banner

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

 

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

Contact us for a Free Consultation Today.

 

 

Contact us for a Free Consultation Today. Operators are standing by to take your call.   Get a Smarter Solution.

We use Zoom to discuss your needs live. There is no cost or obligation for this call.  We simply want to discuss your needs with you as soon as possible.