Dynamic Excel 365 Development – Step by Step

Index/Match or XLookup, Concatenate/Concat or TextJoin, GroupBy, Pivot Tables or PivotBy.  Hundreds of cells with formulas, or just one dynamic one that Spills.  How you program Excel has changed; Excel Development Step by Step will help you to get up to date.  Requirement, Excel 365.

It is time to consider forgetting much of what you know about programming custom solutions in Microsoft Excel.  Index/Match, massive amounts of vba code, thousands of cells with formulas, those are a thing of the past.  Excel 365 is dynamic, and here we teach you how to develop dynamic solutions in Excel 365.

Excel programming now focuses on referencing or creating the Spill Cell / Spill Range #

 

– You can make almost any function a dynamic function, a function that Spills –

 


Here we show you the 5 basic steps we take to build a new automated solution in Excel 365. ( Simple Demo ).

 

~ Programming Microsoft Excel has Never Been Easier ~

How you write functions in Excel has changed, it did when the calculation engine changed.   Due to this change, Excel functions are now range-based, i.e. arrays.  Hence, the need to learn new methods of custom Excel development.  Excel just does not work the way it used to.

a.) You can continue to write Index/Match, you can manually transpose your data, and you can write thousands of lines of code.  b). You can do most of the things you have been doing, or you can use the new tools and do it the new way, the dynamic way.

You can use the VLookup or you can use the XLookup.  Both are dynamic, one is just more powerful.  So why not use the most powerful option?

 

5 Basic Steps to Advanced Excel Programming:

That an Intermediate Excel user can easily do

Follow the basic steps below when you develop a new Excel file, and you will find that Excel programming is easier than it has ever been.  At the same time, also more powerful than it has ever been.

 

Think About Excel Differently:  The world is not flat.  Cars don’t use gasoline.  People work from home.  People shop from home.  McDonald’s delivers to your home.  People delivery packages for Prime to make extra money, Excel is now range-based arrays.

 

Create your new Dynamic Excel 365 Workbook in 5 Basic Steps

Of course you will need to do more than what is listed below.  You will want to add Conditional Formatting, Filters, a user interface, etc.  But 80% of the effort can be accomplished ion these 5 steps.

Excel Development Step by Step Guide:

    1. Excel Data RangesCommon, not recommended
      1. Convert ranges to Excel Tables
    2. Excel Tables
      1. With Slicers
    3. Power Query
      1. With Slicers
    4. Pivots
      1. Pivot Tables
      2. Pivot Charts
      3. With Slicers
      4. Power Pivot
    5. Dynamic Array Reports / Functions
      1. Leverage the # in your Excel functions.
      2. Build Interactive reports and analysis.
      3. Build Excel Dashboards.
  • Add VBA as needed.

**   Then add your conditional formatting, hyperlinks, utilities, some traditional functions as needed, and such.  But everything else is in support of the steps above.  You can build 80%+ of the solution in these 5 basic steps.

 

 

 

The new Dynamic Array Functions make reporting in Excel much much easier.


In the image above, we are using onDAF to query a Table that came from Power Query. One cell, one function. No macros.

 

~You can make almost any function a dynamic function, a function that Spills;

One Cell, One Function, Populates a Range ~

 


 

Need to Hire an Excel Consultant, Programmer or Trainer?

If you need help, please contact us, as an Excel Consultancy we provide both programming and training solutions.

877-392-3539

 


 

 

   Excel Development Step by Step   

 

Step 1:  Convert your Data Range to an Excel Table

Converting your Data Range to an Excel Table is easy.  Start there, convert your ranges to Tables, that is the most important step to take.

All of Excel programming should start with the data and Tables.

Data should be in an Excel Table; not in a range, if possible.  Often however they are in a data range instead, such as below.  The first step is to define the data structure, here it is done in a range.  Tables are easier to extract data from than are ranges, named or not, learn to use them.

85% of Excel users do this incorrectly.  They usually spread the data ranges across tabs and even workbooks.  It is the norm.  However, it is not the proper way to program Excel.  If you need to analyze and report on the data, having it across tabs or files makes it much harder to do.

 

Best Practices: Put all Like Data in a Single Excel Table, Vertically.

Then you can Pivot your data as needed, such as below.  Budget, Foreacst, and Actuals are in the same column in Power Query.  We Transposed it here for analysis purposes.


Excel Tables greatly simplify Microsoft Excel Programming.

 

Microsoft Excel is meant to house data, so that it can be manipulated, analyzed, and reported on.  How you store it determines how you access it. Hence, the right design makes a huge impact on the ease of use of your file.

 

Below is what Excel consultants call a “data-range“, as it is a range of data.  85% of Excel users do it this way.  This is not ideal, so when possible, use Excel Tables instead of data-ranges.


Step 1 = Data Ranges to Excel Tables.

 

 

Steps to Convert a Data Range to an Excel Table

  1. First, click a cell in the data-range.  Then click the insert “Table” button on the Insert Ribbon, to convert the Range to an Excel Table.
Excel Development Step by Step Post: Image shows how to convert Excel data range to an Excel Table.

Select a cell in the range, then click Insert/Table.

 

2. When the dialog box will come up, showing the range that makes up the data, if this is correct, hit ok.

3. Then name your Table, and you are good to go.

 

Excel Development Step by Step Post: Image of data range being converted into a Table.


Click yes or cancel when prompted.

 

  •  You will then have a Table, as shown below.

 

Excel Tables Simplify Excel Programming – Learn to use them

 

Excel Development Step by Step Post: Image of Power Query Table as output.


This is what you will end up with.

 

This is how easily you convert a data range to an Excel Table

Tables take a little getting used to, but you too will love them.  Add Slicers and your Table is now interactive.  Combining Excel Tables with Power Query is where the true power is. Writing functions against an Excel Table is easy.

Other Posts on Excel Tables:

 

 


 

Step 2:  Create Excel Tables, with Slicers

All Solutions should start here, with Excel Tables

This is where professional Excel development begins, with the creation of Excel Tables, to house your data.  Think of Tables as a Container, that make accessing and referencing the data easier.  The data in Tables is structured.

Working with Tables does initially take a little getting used to, but in a few weeks you will be a pro.    The time and effort that they can save you, by using Tables, is more than worth any issues you may have.   Use Tables.

 

Once you start using Excel Tables, you will never stop; you will never program Excel the same way again.

 

Added benefit:  With Tables, you can use Slicers to Filter the data.  Oh, so easy to use.  Writing functions against a Table is much easier than against a data range.

 


Step 2 = Load all data to Excel Tabels.

 

 

Excel Tables are part of a Better Solution

You now have an Excel Table and programming Microsoft Excel will now be easier.  It will take a little getting used to, but you will quickly see that this is the way to properly program Excel 365.

 

Need help with Excel Tables?

If you would like to learn more about using Excel Tables, we offer one-on-one training and mentoring services.  Our team of Excel experts and Excel MVPs can help you to leverage this most amazing tool. Please contact us for more details at 877-392-3539.

 

 

Excel Development Step by Step Post: Image of Excel Tables used as data entry forms.

 

 

Excel Expert’s Advice:

  • Your data should be in Excel Tables.
  • You should keep the users out of the Tables.
  • Use Table Forms in Excel to enter new records into a Table.
  • You can also use the same approach, to use Tables as Forms, to recall, edit, and resubmit the record selected.  Small use of VBA.

 


Excel Tables, Power Query, DAFs and VBA is all it took to create this data entry tool in Excel 365.

 

 


 

Step 3:  Load your Excel Table into Power Query

If you have not used Power Query, there has never been a better time.  Power Query Transforms your data. Power Query is actually easy to use, and it is easier to get to the end result in Power Query, than it is in Excel functions.

 

 


The Dynamic Array Functions work well with Excel Tables and Excel Power Query Tables.

 

Transforming data is easier in Power Query than it is in Excel

Much more power, many more options, and like a recorded macro, each time you refresh your data, Power Query will update the Table.

 

How to Load an Excel Table into Power Query

Click any cell in the Table.  Go to the Data Ribbon, and Click “From Table/Range”.

Once you have, the Power Query Editor will open.

Excel Development Step by Step Post: Image of Excel Table being taken into Power Query.

 

Excel Tables and Power Query go hand in hand.

 

  1. Tables go up into Power Query, Power Query comes down into a Table on a Tab, or as a Pivot Table, or just as a Query.
  2. You can do many, if not all of the Transformations directly in Power Query.
  3. Power Query is one of the biggest game changers in Excel programming, back to 2016.
  4. Why would you want to use Power Query?  Most data manipulation efforts are easier to do in Power Query than in a traditional Excel function.

 

Excel  Consultants Advice:  Leverage Power Query to Simplify the Excel programming process

Much of what is done in Excel, can more easily be done in Power Query.  Sure, not everything, but many many things, far easier, far better.

 

Excel Development Step by Step Post: Image of data manipulation in Power Query

 

Expert’s Extra Tip:   You can add columns to Power Query Table output, and place calculations in it. When you refresh the Power Query Table, the data will update, but the calculations will remain in place. The new column is not in the Power Query Editor.   If you desire, you can upload this into Power Query.

 

Excel Development Step by Step Post: Image of Power Query in Excel.


Power Query simplifies Excel.

Note the Blue Column, Column S is not in the Power Query Editor, and it has a function in it.

 

Power Query Simplifies the User Experience

In the image below, you will see one Excel Table, that is looking at a range of cells.  Each of those cells has a path in it.  The Table has a Validation Control (Drop-Down List) that shows the items from the range below.    All the user needs to do is to select which file/folder that want to upload into Power Query, then hit RefreshAll and their work is done.  No vba needed.  No programming experience needed.

The VBA in this file is used to move data from one Excel Table, and to append it to a historical Excel Table.  This file does not use VBA with Power Query.

Excel Development Step by Step Post: Image of Power Query Variables, in mini–Excel Tables.


Place variables in Excel Tables, and link into Power Query, to be used as variables.

Easiest way to allow a user to select what data is imported into Excel, via drop-down lists. What could be easier. No VBA needed.

 

 

Excel Development Step by Step Power Query Programmer’s Note:

you can just as easily use an external data source, to pull a Table into Power Query.  For this simple demo, the Table is in the same file.  But often the data is external, from a variety of data formats.  If you like, you can bring everything in from a file, all at once, no matter how many files.

If you need help with Power Query programming, we have a team of Top Excel Consultants to do the programming for you.

 

 


 

Step 4:  Excel Pivot Tables via Power Query

You can quickly create an Excel Pivot Table, from within Power Query.   No need to load the Pivot Table to a worksheet, instead, load it as a Query and feed your Pivots.

Excel Dashboards are usually based on Pivot Tables, Pivot Charts, Slicers and Dynamic Reports.

One of the output options of Power Query are Excel Pivot Tables.  Thus, you do not need to store the data in Table or Sheet.  The Pivot Table simply reads the dynamic Power query data source, thus reducing file size.

Alternatively, you can save the Power Query output as an Excel Table, and you could base the Pivot Table on that, thus allowing you to add new columns to the Power Query output, add your calculations, and that will be the data source of the Pivot Table.

 


Step 4 = Power Query data into an Excel Pivot Table.

Basing your Pivot Tables on Power Query simplifies the Excel programming process, but it also makes the file smaller, and possibly more stable.

 

Excel Pivots are the main component of Excel Dashboards.

Pivot Tables, Pivot Charts, Slicers and Dynamic Array Function based Reports.

If you build your Excel Dashboards on Power Query data, you can make your solution easy to use, while still having all the power of traditional Microsoft Excel, plus the added power of Power Query.

 

Excel Development Step by Step Post: Image of Excel 365 dashboard


Dashboards have never been more powerful nor more meaningful than they are in dynamic Excel.

 

 

But Wait!:  Excel PivotBy Function a Pivot Table Alternative

Microsoft Excel is in a constant state of flux.  So much so, that as I write this, I impatiently wait for my clients to have access to this new function.   I have the version that contains it, by my clients do not, so I must wait to use it.

 

The PIVOTBY function allows you to group, aggregate, sort, and filter data based on the row and column fields that you specify. A column-oriented array or range that contains the values which are used to group rows and generate row headers. The array or range may contain multiple columns.

 

Image of PivotBy Function on Microsoft website.


PivotBy and GroupBy will greatly impact the use of traditional Pivot Tables.

No need to Refresh your Pivot Tables if you use the dynamic array function.

 

 


 

Step 5:   Dynamic Array Function Reports ( DAFR )

This is where you will really notice the difference between the old Excel and the new Dynamic Excel available in Office 365.  You can write an entire report with one function in one cell.  The days of cell-based programming in Excel are long gone, the new focus is on range-level programming.  One function, one cell, spills as needed.

Dynamic Array Functions Simplify Excel Programming Post

Range-Based Programming: One-Cell, One-Function.  Add the # to make it Dynamic.

 

Step by Step Excel 365 Development Post: Image of DAFs on Excel Power Query.


Step 5 – Create interactive reports using the dynamic array functions.

 

Step by Step Excel 365 Development Post: Image of Example of dynamic array functions to create reports in Excel.


DAF Report – One cell, one function, populates a range.

 

 

Excel Development Step by Step Post: Image of Dynamic Array Functions Report.


DAFR are rapidly changing how Microsoft Excel is programmed. Add the #, and things will never be the same.  Excel is now easier to program as well s being easier to use.

 

 

Excel Development Step by Step Post: Image of Dynamic Array Report.


For this demo report, the user can select any column, from the data source, and it will be in the report. ( See Green Row, Drop-Down choices)

 

Excel based Reporting has Changed!

Dynamic Array Functions allow you to build an interactive report.  They are live, no need to refresh.  Typically they will populate two or more cells, not just the one with the function in it.

Soon you will be able to create an entire report with the GroupBy Function, or the PivotBy Function.  It is amazing what the Excel Team at Microsoft has been able to accomplish.  This is simply the most powerful and yet easy to use version of Excel, since Excel for the Mac in 1985.    What a tool.

 

 


 

Excel has changed, are you staying current?

The only way there is to see just how old and outdated your Excel 365 solutions are to watch LinkedIn and see what the experts such as Excel and Access LLC are doing.  Once you see what we do, you will see that your solutions are not easy to use, nor program.

When you use Excel 365, you only need to put the formula in the first row, just as in an Excel Table.  Just base your functions on a Spill Cell or Spill Range, and add the #.  No need to worry about the formula making it to the bottom of the data -range, that is what the # is for.

 

Are you using the # in your Excel Functions?

If not, what are you doing?   Are you manually copying down formulas as the data grows?

=XLOOKUP(B8,A1:A5,C1:C5)#

=T7#+U7#

=SUMIFS(tblLookupData[YTD],tblLookupData[Year],$U$5,tblLookupData[Staff],T6#)

=INDEX($F$6:$K$9,MATCH(X6#,$E$6:$E$9),2)

=INDEX($F$6:$K$9,XMATCH(AB16#,$E$6:$E$9),$AD$5)

=FILTER(FILTER(tblLookupData[[Staff]:[YTD]],tblLookupData[Year]=2020),{1,0,1,1,1,1,1})

 


Excel Development Step by Step – Extras

Dynamic Excel Utility Functions:

Many of the new dynamic carry functions are what I call “Utility Functions“, in that you typically use them with other functions, to manipulate the result set.  The Utility Functions are unlike ChooseCols or GroupBy, which are looking at the entire Table, to bring back an array of results.

Common Uses:  Populating a Validation Control.

  1. Unique
  2. Filter
  3. Sequence
  4. Sort
  5. SortBy
  6. Transpose
  7. Take
  8. Drop
  9. Let
  • Bold are most used, above.

Examples of Excel’s Utility Functions at work:

1 =SORT(UNIQUE(FILTER(Regions,Regions>0)))

2 =TRANSPOSE(VLOOKUP($U6,NamedRangeExample,SEQUENCE(5,1,3,1),FALSE))

3 =FILTER(TAKE(rangeEmployeeData,,),AgeColumn>AgeSelection)

4 =SORT(DROP(CHOOSECOLS(DynamicNamedRange,1,2,3,4,5,6,7),1),$D$2,$G$2)

 

 

 


 

 

Excel Development Step by Step – Summary

Microsoft Excel Programming has Changed.

The changes are so significant, that leading Excel consultants are changing the way that they program Excel.  Excel programming is now range-based, not cell-based.  This is the new dynamic Excel.  Excel programming has never been easier.

You can now create an ultra-compact, powerful, yet easy to use solution in a fraction of the time older programming methods take.   Excel 365 is the price of admission.  Power Query, Excel Tables, those are your tools to produce your Dashboards and Dynamic Reports.

The changes to Excel 365 are a game changer.  If you are not using the new functions along with Tables and Power Query, ask yourself why.  Why work harder.

 

 

Free Consultations: Let’s Discuss Excel Development Step by Step Programming and Training Needs

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

 

Image of Contact Us Banner


We are here to help, please give us a call to discuss your needs.