Interactive Reports in Excel Just Got Easier

Until recently, when you wanted to build an interactive and dynamic Excel report, one that allowed the user to build a quick report, on the fly, you would need to write many formulas, and often you would need to add complex VBA/macros.  But the times are changing.  Microsoft has been on a tear, releasing new powerful Array Functions and new easy-to-use features such as “Remove Duplicates”, allowing Excel experts to take Excel to new levels, with less work.

As we have been saying for years, Microsoft Excel should be easy to use.

The ChooseCols function is one of the new Dynamic Array Functions, these Spill Functions simplify Excel programming and are available in Office 365.

 

Easier Reporting Via Excel’s New Spill Array Functions

Microsoft recently released a series of Spill Array Functions, some of the most powerful and most useful functions in Microsoft Excel today.  These newer array functions are a true game changer for Excel programmers that want an easy-to-program, easy-to-use, point-n-click reporting application. One that requires fewer formulas and little or no VBA/Macros.

With these new functions, you can build an interactive report in minutes, not hours or days. Also a quick and easy way to feed your Pivot Tables and Dashboards.

We will not go over how to use the ChooseCols function here; you can find dozens of YouTube videos on that. Rather, here we will show you how to use this function to quickly program an interactive and dynamic report any Excel user can run. No vba is needed.

 

A little background info on Dynamic Arrays: “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, says Microsoft.”

 

Enter Excel’s new Dynamic Array Spill Functions

For this quick example, we will use an Excel Table to house the data. On the second tab in the file, we will write a single Excel formula, using the ChooseCols Spill Array Function. Doing so will allow us to extract any and all desired columns from the referenced Excel Table, via one function, one cell. The user can put the columns in any order they like, on the fly, no programming experience required.

You can use many of the other new Spill Array Functions with this function. Embedding them into one report-generating query, to make the interactive report even more dynamic:

• ChooseCols Function
• ChooseRows Function
• Take Function
• Unique Function
• Filter Function
• Sort Function
• SortBy Function
• Drop Function
• VStack Function
• HStack Function
• XLookup

 

 

Examples:

 

 

Basic ChooseCols Function Example: = CHOOSECOLS(tblEployeeData,1,2,3,4,5,6,7).

This example is looking at the tblEmployeeData Table in Excel, returning columns 1:7 in sequential order, all rows.

Note: If you are simply going to take the first 7 columns of the table, as in the example above, you can use the Take Function instead of the ChooseCols function, for simplicity.

 

 

 

Advanced ChooseCols Function w/ embedded Dynamic Array Functions Example:

=FILTER(CHOOSECOLS(tblSampleData,FeedChooseCols#),(tblSampleData[Region]=SelectedRegion)*(tblSampleData[Year]=SelectedYear))

This example allows the user to choose exactly which columns to report on, 1 or more, in any order, on the fly. This example also allows the user to Filter the records for Region and Year, using drop-down Validation Controls. No vba needed, one complex function.

 

 

 

---

 

Excel Should be Easy to Use.

As with most things Excel, you can take the extra time to make the file easy to use, by applying best practices to design.  The same can be said to using an Excel function.  You can use it in its simplest form, or you can make the formula dynamic and interactive.  One takes more work to setup; one takes more work to use.

Typically, when the ChooseCols function is used, the column numbers are entered directly into the ChooseCols Function. But doing it this way is not so dynamic, as the user would need to edit the formula to change the columns to be reported on, as well as the order of those columns. If the file is protected, as it should be, then the user is unable to edit the formulas.  Not so dynamic.

Instead, you can designate a set of unprotected cells on the tab so that the user can enter the column numbers of choice on the fly. The ChooseCols Array Function would then look at that dynamic range, and it will use that range to feed the interactive and dynamic report, as displayed in the image below. This makes it much easier for the user to interact with the dynamic report.

This way the user does not need to know anything about Excel programming. The user does not need to even know what the function is, simply enter your column numbers in the designated cells. They just need to know what columns they want in the report, and they can change it on the fly.

 

Expert’s Note: Our main focus in Excel development is the user interface and in making the Excel solution as easy to use as possible. Making Excel easy to use is easy if you hire the right company.

 

Note:

Note: In the past, each cell in a report would have a formula such as the VLookup to populate each column of the report. Each cell would have an editable formula. That is not the case with Spill Functions that operate more like Access Select Queries or SQL Server Views than an Excel formula. Instead, you write one function, simple or complex, in one cell, and the results spill over into the adjacent cells.

To make the report even more dynamic, add Validation Controls that the user can use, to determine which column to sort by, for example. You can use a Validation Control to allow the user to filter the records in the report for say Region. By using the Filter Function you can add multiple criteria that the user can select via drop-down lists.  All of this with one complex function, no VBA needed. (See Image below for formulas).

 

 

 

Setting up the base Excel report:

1. Add an Excel Table to the file to house the report data.
2. Write a ChooseCols Function, referencing the Excel Table by name, on the Report tab.
3. Enter the column numbers, in the order you want them to appear, in the ChooseCols Function, each column number separated by a coma.
4. Hit enter, you have your base report.

 

 

Making the report easier to use.

• • Instead of having the user enter the column numbers directly into the function, enter them into cells on the report sheet.
  • If you want to have a specific number of columns, you can set that in advance.
  • If you want to have the number of columns also to be dynamic, you can use the Filter Function to extract columns from populated cells only.
    • Another Spill Array Function = Filter.
  • You can then add one or more drop-down Validation Controls with choices to be used to Filter the report on the fly. Say by Sales Region or by Product Family, even Year.
  • You can use Sort or SortBy to make sorting the report dynamic as well, once again, no VBA is needed.
  • You can continue to add filters, to add other functions, and if needed, even add some VBA.  You can make an easy to use, interactive Excel report in no time.

 

 

Function Used in the example above:

=SORT(FILTER(CHOOSECOLS(PatientDataSortRange,PatientReportColumnOrder_Filtered#),_

PatientTableStatusColumn=PatientReportFilter_1),PatientReportSort_1,PatientReportSort_2)

 

The way Reporting is done in Excel just changed.

In about 15 minutes you can build a truly interactive and dynamic report in Excel by using one Excel Table. Add one or more of Excel’s newest and most powerful Array Functions to date, Excel Spill Array Functions.

The result of using the new ChooseCols Array Function, the user can specify any number of columns, in any order, from the base table, to be reported on, in one formula.

The user can then Filter and Sort the report on the fly, without the need of VLookups, Index/Match, SumIfs, Sum, Count, VBA, etc. So, one complex Spill function will do the trick.

Expert’s Comments:  This is a simple example, but it does show you just how powerful the new Spill Functions are. It shows just how they are changing the way that Microsoft Excel works. Microsoft Excel is becoming more database-like in how it functions.  If you think about it, these newer functions mimic how Access queries work.  For example, VStack is just like an Access Union Query.

 

 

Other Posts on how to use the new Excel Spill Array Functions for Business.

 

Microsoft Excel Spill Array Functions for Business – what to do with them.

1. Related Post: 

2. Related Post:  Using VStack Function to Consolidate Excel Data

3. Related Post:  Using Unique Function in Validation Controls – Users Love Drop Down Lists

4. Related Post:  Using Transpose Function in Excel – Transposing Excel Then and Now

 

 

 

 

Contact Us

Want to see how these functions can assist you and your organization? How about a quick demo? Show us the challenges you are having with your excel files, and we can show you how to get the most out of your Excel files. Free consultations, guaranteed work, friendly service, local programmers.