Excel Dynamic Arrays for Dashboard Reporting

Everyone reading this has most likely heard of Excel Dashboards.  If you are an Excel programmer, you should have heard of the new Dynamic Array Functions.  Knowing how to properly leverage these functions will greatly simplify your Excel programming.  Here we show how to use a few of the Excel Dynamic Array Functions for Dashboard Reporting.

 

Image of Data Validation Control. Excel Dynamic Array Functions greatly simplify Dashboard Reporting.


Excel Dynamic Array Functions make it very easy to populate your drop-down lists.

 

Excel Dashboards Examples based Excel Dynamic Array Functions for Dashboard Reporting

After working on several projects where we built automated Excel dashboards, without the use of VBA, based on the new dynamic array functions, we thought, this would be a great subject for a post.  We decided to share how simple it is to build an automated, reporting and analysis dashboard in Excel, using these functions.   And how to do it in less time than traditional programming.

Excel Dynamic Array Functions can be used for Dashboard Reporting, as well as for Pivot Tables and Pivot Charts.

In the image below, we combine the use of the Dynamic Array Functions along with the XLookup and other traditional functions.  All dynamic ( The Excel functions only go in the first row, add the # sign ).

The Result:  The result is a dynamic solution that is quick to build, easy to use, and very affordable.

 

 

This image is an example of how we combined the spill functions with the xlookup function.


Here we used the ChooseCols Function to work with an in memory dataset where we added a few calculations, a Filter, and a Sort. This is our base data for the Excel dashboard.

 

Actual Application – 8 Functions in total. The ChooseCols Function is powerful. The result set feeds a pre-filtered Pivot Table and Pivot Chart.

 

Expert’s Note:  The Office 365 Dynamic Array Functions allow you to work in memory, thus not impacting the underlying data set.  Think of it as read-only programming.   For example, the Unique Function gives you a duplicate free result set, but it does not delete the duplicate records in the underlying Excel Table.  Rather they are simply not included in the results.

These new Dynamic Array Functions work much like an Access Select Query, versus an Action Query.

 

 

 

Example #1 Using ChooseCols Function for Dashboard Reports:

We were tasked with building an Excel dashboard to be used for month-end reporting and analysis.  The solution had to function as an automated Excel Template.   The Dashboard template would not be based on an Excel Table.  Instead, the user would type their data into one of the tabs in the file, a named range.  We were not allowed to touch that table; we could not alter that table, we could only reference it.  The data in the table was not as we wanted it, so we chose to use the ChooseCols Function to create our data-set.

The Excel dashboard would contain Pivot Tables, Pivot Charts, and Dynamic Reports.  The use of VBA was prohibited.  So we built a custom dynamic array-based Excel dashboard for their reporting needs.

 

In this example, we had no control of the table, where they entered their data.  We were told not to touch that table.  So we use the ChooseCols Function instead.

 

ChooseCols works great for Excel Dynamic Array Functions for Dashboard Reporting

The solution would be one where the user would manually populate the data range.  The user would then print the report and close the file, done.  That is it, the dashboard self-updates based on the data in the data range.

Challenge:  Their resources for this project were limited.  So they wanted it super simple and basic.

The Result:  See the image below, of the Excel Dashboard based on Dynamic Arrays and Pivot Tables, showing Pivot Charts and Dynamic Reports.

Why Would We do This:   The client often has unique needs, and guidelines, our job as Excel programmers is to use the tools available, to accomplish the task at hand, within said limitations.

 

 

Here we used Excel Dynamic Array Functions to prefilter the data going into the pivot table. Just enter your data, and print.


8 Dynamic Array Functions built this automated Excel dashboard.  Mock data.

 

We did it with 8 Dynamic Array Functions, a few SumIfs and XLookups.

No VBA.   Super simple, super clean.

 

That is a simple example of a project where we needed to be creative, and we definitely needed to leverage the raw power of these new Dynamic Array Functions simplify Excel programming.  With a tight budget, these functions cover a lot of ground.

 

Editor’s Note:  When you add VBA/Macros to a file, you require the user to maintain those going forward, as needed.  It does complicate things.

 

 

In Example #1 we Prefiltered the Data

For this project the new array functions were used in conjunction with traditional functions to programmatically filter their data.  The client would type their data, the dashboard would update, and they would print the reports.  No other effort taken.

 

Enter data, print. The Dynamic Array Functions determined what data was fed to each of the Pivot Tables and associated Pivot Charts, programmatically. The one below would prefilter to 13-weeks.

 

These functions when used by a creative programmer are extremely powerful for automation, with the use of advanced VBA.

Here we show the functions used.  In the image below, that is one complex formulas, using two of the dynamic array functions.  The dates in the file are used to filter the data.

What you see below populates one of the Pivot Tables.  All of those cells are populated via one function in one cell.  This really is the easier way to program Microsoft Excel.

 

 

We often use the new Dynamic Array Functions for trend reporting. We combine them with traditional Excel functions for best results.


This dynamic array populates our 13-week trend report, for this demo, with mock data.

 

 

 

The Dynamic Functions Used in this example:

The complex Functions below are what ultimately populates each of the Pivot Tales and associated Pivot Charts, that populate the dashboard.  Thus, each Pivot Table is pre-filtered and therefore the user does nothing other than print the report.

  1. Pivot Table 1: =FILTER((SORT(CHOOSECOLS(PivotDataToMap,1,2,3,4,5,6),SortColumn,SortOrder)),Downtime_Tracker!$B$3:$B$500>1/1/2020)
    1. Feeds Pivot Chart.
  2. Pivot Table 2: =FILTER(CHOOSECOLS(DowntimeReportData,1,2,3,4,5,6,7,8,9),(WeekNumberColumn=MaxWeekNumber),”-“)
    1. Feeds Pivot Chart.
  3. Pivot Table 3: =FILTER(CHOOSECOLS(DowntimeReportData,1,2,3,4,5,6,7,8,9,10),(TrendReportColumn=”Trend”),”-“)
    1. Feeds Pivot Chart.
  4. Dynamic Report 1: =CHOOSECOLS(TrendDataRange,2,3,4,5,9)
    1. Goes on Dashboard.
  5. Dynamic Report 2: =CHOOSECOLS(LastWeekDataRange,2,3,4,5,9)
    1. Goes on Dashboard.
  6. Criteria Cell: =TAKE(SORT(UNIQUE(WeekNumberColumn),,-1),WeeklyTrendNumber+2)
  7. Criteria Cell: =(SORT(UNIQUE(WeekNumberColumn),,-1))

 

 

 

 

 

Want to discuss Excel Dashboards based on Dynamic Array Functions, give us a call

We do large projects; we do small projects.  We build custom solutions based on your needs and on your budget.  It is easier than you think to get quality help.

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

 

 

 

 

Base your Excel Dashboards on Excel Tables

When building models in Excel, if possible, based them on Excel Tables.  Sure, you can use any range, named or not.  But an Excel Table has many advantages over the Range Method, and only a few potential drawbacks.  Whenever possible, work with Excel Tables.

Excel Tables are a powerful feature in Excel and they should be used whenever optimal.  It is recommended that you learn them.

 

Experts Advice:  When you house data in Excel, do so in an Excel Table.  It will make your life easier, and it will greatly enhance your programing abilities.

 

We strongly recommend that you base your Excel dashboards on Excel tables. They are powerful, and work well with Pivot Tables.


Excel Tables are a powerful, largely dynamic feature in Excel. When you build an Excel report or dashboard, base it on an Excel Table.

 

 

 

 

 

Dynamically Populate Excel Validation Controls for Dashboards

In the past, Validation Controls were often populated via a Dynamic Named Range, based on the Offset/CountA combo function ( See Image below ).  That was one of the most common methods, to make the list dynamic.  And it works great to this day.   But there is an easier way.

You do not need to learn how to write this function, there is an easier way.

 

 

Image of how Data Validations Controls used to be used, to return a dynamic list. Still viable, just too much effort.


Go to method to create a Dynamic Named Range. But it is easier now. You do not need to know how to write that formula

Old Method

 

 

The Easiest way to Populate a Validation Control is with Dynamic Array Functions

If you want to populate a Validation Control with a dynamic list of unique items, sorted, the quickest and easiest way is to leverage the new Dynamic Array Functions Unique and Sort.   There are several other functions you can use to do this.

People often combine 2 or more functions into one complex function.  You can then reference this array with traditional formulas.   What you use will ultimately depend on your needs.

 

Dynamic Array Functions used to Populate Validation Controls, Dynamically

  • Transpose
  • Unique
  • Sort
  • Filter
  • Take
  • VStack
  • Drop
  • Sequence
  • A combination of two or more of these is the norm, very often including Sort.

 

Image of the new way to use a dynamic list in a Data Validation Control, add the # symbol.

New Method The # Symbol tells the function to grow and shrink with the adjacent dataset. You point the function to the first cell in the Dynamic Array, give it a name, and reference that cell, by that name, and add the # sign. Your Xlookup, SumIf, etc., are now dynamic. This is why you do this.

 

 

 

Examples of  Dynamic Array Functions populating drop-down lists:

Use these functions to populate your Lists tab.  Name the first cell of each Spill Range, and reference that range, and end it with the # symbol.

1 =SORT(UNIQUE(tblSampleData[Region]),1,1)

2 =UNIQUE(tblSampleData[Year])

3 =TRANSPOSE(tblSampleData[#Headers])

 

 

Using the Transpose Function to Dynamically Populate a Validation Control

For this example, we wanted to populate a Validation Control with the Headers from the Excel Table.  So, we simply used the Transpose Function to create the Dynamic List.

Next we named the blue cell, where the Transpose Function is written, the name ‘ColumnsList’.

Finally, we created a new Validation Control.  For the Source, we used =ColumnsList#.

=TRANSPOSE(tblSampleData[#Headers])

We will now have a dynamic list, based on the headers in the Excel Table.   We use this feature in the Dynamic Report section of this post.

 

The cell where you type your dynamic array function is the Spill Cell, it is the cell you reference when looking at a dynamic range.


That little # symbol is a huge change in how one programs Excel.

=TRANSPOSE(tblSampleData[#Headers])

 

In this example, the key is to name just the Spill Cell. Then use the # symbol when you reference that cell.

 

 

 

 

 

Creating Sorted, Unique Lists Has Never Been Easier

Using dynamic array functions in your Excel workbooks makes them more dynamic overall.  Simplify the programming, reduce the effort to update the file each time you use it, require less manual effort from the user.  How simple can we make it.

Example:  You can now quickly create a dynamic list, sorted, based on the Unique and Sort functions.  You can filter the list as well, all in one cell, in one function.

 

This is as easy as it gets. Excel should be easy to use; Excel should be easy to program.

 

Why do you need to spend time copying and pasting?  Why do you need to spend time copying down formulas, or worse, changing formulas? What are you doing that you do not need to do?  These functions take away a lot of user effort.

Really, a function to sort your data, live, programmatically via a function, no vba.   Yes.  You can tell the Excel Sort and SortBy Functions how you want your data sorted, on the fly.  The function does the actual sorting, programmatically as your data changes.  This is a game change in itself.

 

 

Example of how dynamic lists can be used. Great for selecting or filtering data. Statistical Functions use unique lists.

 

So what exactly do you do with Dynamic Lists?

Usually you use them to populate Validation Controls. Bu there are certain functions that need them in order to do their work.  These statistical functions are used to summarize data.

Statistical functions love Unique Lists:  Typically, when you use the SumIf, CountIf, AverageIf, functions, etc., you do so against a ‘Unique List’.   How do you get that list?  How do you dynamically get a unique list without using the Offset/CountA combo, or VBA, etc.

If you use the Unique Function for example, you will get a dynamic list, of unique items.  You can sort that list, if desired, using the Sort Function.  You can then write your SumIf Function, looking at the first cell in the Spill Array, and add the # sign.  Your SumIf is now dynamic, and you only need to place it in the first row.  Dynamic is much easier to use and much easier to program.

 

Programmer’s Note:  Validation Controls, Combo Boxes, and ListBoxes love Unique Lists.  Dynamic Unique lists are even better. Unique Records you can use in other functions, even better.

 

 

Example: Excel Dynamic Array Functions make using SumIfs. Just put them in one cell, and they will fill down as needed.

 

 

 

 

Just Populate the First Row of your Report?

Not many people know this, or have thought about this, but you only need to populate the first row of your report with functions, if you are using Dynamic Array Functions.

That is if you base your traditional Excel functions on the first cell of a Spill Range, then you formulas will also be dynamic.  Doing this approach means that you only need to have formulas in the first row of your report or analysis.  Think about it, no longer is there the need to populate hundreds to thousands of cells with formulas; just populate the top row.

See the simple example below.  Here we use four formulas, one in each cell, of the top row, that is it.

 

 

Excel Dynamic Array Functions Simplify Excel Reporting because they make all of your functions dynamic, when you reference a dynamic range.

 

 

That Little # Sign Changes Things – Pay Attention

Not sure if you noticed, but some of our formulas have a # symbol in them.  This is new in Office 365.  It changes how one programs Excel.  If used right, you only have to place formulas in the top row of your report.  So a 10,000 row report would have formulas in only the first row?

Hhhhhmmmmmmm, much like formulas in an Excel Table.  Excel is changing.

 

If used right, you only have to have formulas in the first row, of a 10,000 row report, due to that little # symbol, and Dynamic Array Functions.

 

 

Out of all of the new Excel Dynamic Array Functions, I use Unique the most.

New Method The # Symbol tells the function to grow and shrink with the adjacent dataset. You point the function to the first cell in the Dynamic Array, give it a name, and reference that cell, by that name, and add the # sign. Your Xlookup, SumIf, etc., are now dynamic. This is why you do this.

 

 

Expert Excel Programmers make XLookups and SumIfs Dynamic via # Symbol

Look at the SumIf and CountIf Functions below.  This is how you want to use them. Point them to the first cell of a Spill Range, add the # sign, and you are done.  This is the future method of Excel programming.  Base your functions on Dynamic Arrays, make your traditional functions dynamic.

 

In the Example Above we used Excel Dynamic Array Functions for Dashboard Reporting:

Here, the Region column is the results of the Sort/Unique Dynamic Array Function combo above.   The list pulls the unique list of Regions, from the Excel Table.  It is then sorted.  The list dynamically grows and shrinks, based on the data in the Excel Table.  As such, you do not maintain the list, it is dynamic, in-memory.

Make Sure to Note:  The SumIf and the CountIf functions have a # symbol in them.  They are pointing to cell S14.  S14 is the first cell of the Dynamic Array function.  If you point a function to that cell, to the Spill Cell, and if you add the # sign, you have made that function dynamic.

What does that mean:  That means that the CountIf Function is placed in just one cell.  It will populate the cells below it, as long as there is data.  Think about it, that is amazing.  In the past, you would have to make sure your function went down to the bottom of the referenced data range.  Those days are gone.

 

Experts Note:  Instead of populating hundreds to thousands of cells with a function, you are populating one cell.

 

 

 

 

 

 

 

Use the Filter Function with the Dynamic Validation Controls

Use Slicers with your Pivot Tables and Pivot Charts

Dashboards should be easy to use.  Two things definitely make Dashboards more powerful and easy to use, 1) Validation Controls (Drop-Doan lists), Slicers for Pivot Tables/Charts.

People do not like to use the Pivot Table interface to interact with their data, to filter it.  They prefer to use Table Slicers for the Pivot Tables and Pivot Charts, and to use Validation Controls to Filter their data/reports.

 

Use the Filter Function in Excel Dynamic Array Functions Simplify Excel Reporting

 


Here the Validation Control references a Spill Cell, where the Transpose Function pulls the Header Row from an Excel Table. 100% dynamic.

 

 

We have seen the use of Validation Controls take off in recent years.  Even people with few Excel programming skills know how to use Validation Controls, and many know how to create them.  Doing so dynamically is just the next level.

Here we use the Filter Dynamic Array Function to populate our Dashboards and Reports.  It makes it easier for the user to select their data, and it is visually intuitive.

Filter, Sort, and ChooseCols:  Below we use two Validation Controls to sort the data, programmatically.  We are using the Validation Controls to determine which column to sort by, and which sort order.

 


In this example, we use a Filter on the Region column of the Excel Table to prefiltered the data.  And the user uses the Validation Controls to determine which columns are in the Pivot Table.

 

 

 

 

Slicers make Excel Dashboards a joy to work with.  Use them as much as possible.

 

 

For this example we were able to make a powerful dashboard in Excel, that allowed the user to drill into their details.


Example Dashboard using the Dynamic Array Functions to prefilter, and to populate the Pivot Table and Pivot Charts.

 

 

 

 

 

 

 

ChooseCols Function can be used for Excel Dynamic Array Functions for Dashboard Reporting

What if your client requested you to build a simple to use, dynamic, interactive, Ad Hoc Report, without the use of VBA?   What if they wanted to base the ad hoc report on an Excel table of 200 columns.  They want the user to have ability to pick any number of columns, in any order, to include in their report?

What if they need the solution to be easy to use, so they wanted the user to select the columns for their report, using drop-down lists.  And they wanted the user to be able to sort the results, by any column.  All of this, with no vba.

ChooseCols and the Take Functions work well  with Excel Dynamic Array Functions for Dashboard Reporting.

  • What if the only tools you can use are Excel functions and Validation Controls.

The user should not need to adjust any formulas, or to understand any of the formulas.  Just select their columns, sort, and print the Dashboard.  Simple, but it is what they wanted, it demonstrates how to use these functions, and it worked well for them.

 

 

The new ChooseCols Dynamic Array Function in Excel helps one to extract desired columns from a dataset. You can even change the order of the columns. It has its uses.

change graphic, show validation controls

 

 

It is a one-off solution, but it shows you what can be done.  You can apply aspects of this solution, to other interesting needs.  How can you use these tools.

 

 

 

 

 

 

Referencing Dynamic Arrays is like Writing a Select Query based on a Select Query

Once you create your base Dynamic Array on your Excel Table, and once you add your other functions, you can reference that array, in other arrays.  Build your base data set.  Then reference that data set as needed, applying Filters and such as you go.  Here we filter a preexisting data range, based on the year selected.  Basically, we are treating our Excel datasets like Access Select Query datasets.

 

Build your base dataset using Excel Dynamic Array Functions for Dashboard Reporting. Then Filter that set as needed.

 

 

Programmer’s Note:  You can reference these in memory data sets in all sorts of ways.  Take them whole, as they are, apply functions to determine which columns you return, use functions to filter the rows, even sort the results.  All with one complex function, in one cell.

Once again, just examples of things you can do with Excel Dynamic Array Functions for Dashboard Reporting and Analysis.

 

 

Excel Dynamic Array Functions for Dashboard Reporting

 

 

 

 

 

Conclusion – Excel Dynamic Array Functions for Dashboard Reporting

So in the two simple examples, we used the ChooseCols Function with other functions, Filter, Unique and Sort, to prefilter a result set, to populate a Pivot Table.   The Pivot Table would populate the Pivot Charts.

One of these projects allowed the use of Slicers, the other did not. In both situations we prefiltered the data, before it was fed to the Pivot Tables.

Usually, the Pivot Table is based directly off of the Excel Table, but in this post we wanted to show examples of projects where that was not the case.  We wanted to show simple solutions, as how you can leverage these amazing functions to do more work, in less time, with fewer formulas, and no vba.

What you do with this knowledge is up to you.

 

 

Image of automated Excel dashboard for reporting, one using the new dynamic array functions in Office 365.


One Excel Table, one Pivot Table, one Pivot Chart, one Dynamic Report, Slicers, a few Dynamic Array Functions, and no VBA.

 

 

 

 

 

 

Want to discuss Excel Dashboards based on Dynamic Array Functions, give us a call

We do large projects; we do small projects.  We build custom solutions based on your needs and on your budget.  It is easier than you think to get quality help.

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