Microsoft Excel 365 Dynamic Array Functions to use w/ Excel Tables

~ Filter ~ Unique ~ ChooseCols ~ Take ~ GroupBy ~ PivotBy ~

 

Use Excel DAFs to Select Data from an Excel Table

If you want to extract data from an Excel Table, these Dynamic Array Functions are the ones to look at.  But which one should you use?     These are not the functions your mother used; these are the new Microsoft Excel Dynamic Array Functions.  Many of the newer functions will get the same result.  So which Microsoft Excel 365 Dynamic Excel Function Should you use to pull data from your Excel Tables?

It depends on what you want to see, and on how dynamic you would like to make it.  For example, using the Filter Function will easily allow you to make the workbook interactive.

The Future of Excel Programming has Arrived

~ GroupBy & PivotBy Functions in Excel 365 ~

 

=GROUPBY(tblSalesDatabyYearGroupBy[[#All],[Staff]],
tblSalesDatabyYearGroupBy[[#All],[QTR1]:[YTD]],
SUM,
GroupByFieldHeaders,
GroupByTotalDepth,
GroupBySortOrder,
tblSalesDatabyYearGroupBy[[#All],[Filter]])

 

 

Selecting One Column, Multiple Columns, or All Columns from a Table

Using these functions, you can reference the entire Table, one column, or multiple columns. That also largely determines which of the functions you ultimately use.  For example, if you plan to take all of the columns, you can use the Take Function, ir simply point to the Table, versus using the ChooseCols function, given you want all columns.

Expert’s Note:  ChooseCols will allow you to select the order of the columns returned, Take will not.  So if you want to use all columns, but in a different order, then you can use ChooseCols.

 

Microsoft Excel 365 Functions to Extract Data from Excel Tables

If you are looking to extract data from an Excel Table, use one of the new Dynamic Array Functions.  Which one to use, well, that is the tricky part, as many of them provide the same end results.  So which one you use, really depends on your data, and what you want to extract.

1. FILTER
2. CHOOSECOLS
3. TAKE
4. UNIQUE
5. GROUPBY
6. PIVOTBY

 

 

• Many of the dynamic Excel 365 functions above, ultimately return the same datasets.  Which to use is dependent on your needs.  In this post, we cover each of them, telling you their advantages and disadvantages.

 

 

 

---

 

Contact the Excel and Power Query Experts at Excel and Access, LLC

877-392-3539

 

---

 

Best Dynamic Array Functions to Reference Excel Data

Since 2018 the Microsoft Excel Team has released a series of new Excel 365 functions.  The new functions are Dynamic Array Functions.  These are not the functions people at work use.  Experts use these functions.

If you want to easily access the data in an Excel Table, use ChooseCols, Filter, or GroupBy.  You will be surprised at just how easy it is to program Excel 365.

In the image below, we used the Fiter Function with the ChooseCols Function. Here the user can specify which columns they want in their report, on the fly. 

Add the use of the drop-down validation control to allow the user to select the year, and you have a nice little demo of how to make Excel easy to use.  The use of the Filter function makes it all possible.

 

Filter, ChooseCols, GroupBy, PivotBy, Unique, etc., the Excel 365 Functions will change how you program Microsoft Excel.  Well, I mean they should.

 

---

 

 

One Cell, One Dynamic Array Function, Populates a Range. 

The data range will grow and shrink with the data.  No need to change your formulas.  What could be easier.

I call this range-based programming.

 

 

 

---

 

 

The Microsoft Excel 365 FILTER Function Loves Excel Tables

The FILTER Function is my favorite Microsoft Excel Function.  It is the favorite function of many Microsoft Excel MVPs.    This is my go-to Excel 365 function.

The Filter function is a great tool when you want to both select and to filter your data all at one time, in one simple to use dynamic array function.    You can select the entire Table, one column, or multiple columns, and you can simultaneously apply multiple user-interactive filters.  Add the Sort function, and you have a powerful combo.

• The user can manipulate the results that they want to see, using drop-down lists, checkboxes, etc.
• Can be applied to an entire Excel Table, or just a few columns.
• Allows for multiple Filters at one time.
• Can read the Excel Checkbox.
• Can read the Excel DropDown Validation Controls.
• Can be used with other Excel functions, such as Sort, or Unique.
• The FILTER Function is the most powerful LOOKUP function in Excel.

 

In the image below, the user is able to determine which year to see data for, as well as being able to choose which column to sort by, and in what sort order.  The Filter Function is used against the year drop-down list.

In this example, the Filter Function is being used against an entire Excel Table.

 

 

In the image below, we use the Filter Function against a Table, but this time, we do not select the entire Table, rather just a few columns.

 

 

In the image below I use Filter with ChooseCols.  This allows the user the ability to use the new Excel Checkbox, to make the file interactive.  Here, what columns do you want to see.  

 

In the image below we have examples of the Filter function using multiple filters, via “and”, “or”.

 

 

The Microsoft Excel 365 FILTER Function is our Favorite Excel Function

Can you see why the Filter Function is our favorite Excel 365 function?  It is one of the most powerful functions, it is a tool for automation.  The Filter function in the right hands, makes Excel dynamic.

 

The Filter Function is also our favorite LOOKUP function.

 

Combine the Filter function with the Sort or SortBy functions, possibly Unique for a user interactive solution.  The Filter Function when used correctly, makes Excel easy to use.

 

Important Note:  Two new Microsoft Excel Dynamic Array Functions have Filter built in.   They are GroupBy and PivotBy.   It just gets better and better.

 

 

---

 

How do you want to see your Excel Table Data

There is more than one way to look at your data.  For example, when it comes to reporting financial information, you often see that in a horizontal format.

That said, store your Excel data, in an Excel Table, with a vertical layout. If your data is in a vertical Excel Table, writing functions is so much easier.

Our recommendation: Store your data in Excel Tables.   Use Power Query &/or the Dynamic Array Functions, such as GroupBy, for Excel based reporting.

 

Note: In the image below, the blue cells have the dynamic array functions.

 

 

---

 

 

The Excel 365 ChooseCols Function is a Data Extraction Powerhouse

The ChooseCols Function is one of the most powerful functions when it comes to extracting the desired columns from an Excel Table.  If I want to select the columns of interest, and if I want to change their order, then I often use the Excel ChooseCols function.

The ChooseCols Function is one of the most versatile functions to extract data from an Excel Table.  Use drop-down lists to allow the user to select which columns, in which order.  Thus allowing the user to create a custom report, on the fly.

Excel Expert’s Note:  You can feed the columns of interest via cell(s) in the spreadsheet, as in the image below.  See the box with numbers in it, those are column numbers.   Notice that the 2nd column is not in the result set, and that the YTD column is the 2nd column in that data set.

 

 

Make the Solution Interactive and Dynamic

In the example below we use the CheckBox to allow the user the ability to set which columns show in their report. We have also provided a dropdown list that the user can use to set the year.  Finally, the use of Slicers allows us to filter the Table and the Pivot Chart.

For Maximum Power, Combine Dynamic Array Functions:  You will often see the ChooseCols Function combined with the Filter ( See image below ), Unique and Sort functions.   Being able to change the order of the columns is powerful, as data sources often come with their own set order.  The ChooseCols function allows us to quickly get the columns we need, and only the columns we need, in the order we want.

Power Query Expert’s Note:  You could just as easily do this in Power Query, if not easier.

 

The order should be 1) Excel Data Tables, 2) Power Query Tables, 3) Dynamic Array Functions, 4) Pivots, and 5) VBA.

 

 

Excel 365 Automation:  It is easy to do, to let the data flow.  Minimize the use of Excel functions.  Let Power Query do the heavy lifting.   Lastly, apply functions and VBA.

 

 

---

 

Excel’s Take Function – Choose Rows and/ or Columns

Take is similar to ChooseCols, but it is not as powerful.  But the TAKE Function does have its use, and I do use it from time to time.

1. The Take Function allows you to choose both rows and columns, or one or the other.
2. Take the Top 5, the Bottom 5, etc.
3. Similar to the Drop Function.

•  You can also not set the number of rows or columns, and you will return the entire dataset.
• =TAKE(tblLookupData_External_Calcs[#All],5,5)

 

 

 

---

 

The New Excel 365 GroupBy Function

Now this is a Power Function!

The GroupBy Function came out in October 2024.   It is revolutionary.  There has never been an Excel function that does so much.

• Sort, built-in.
• Filter, built-in.
• Headers, built-in
• Sub-totals and Grand-totals, top or bottom, built-in.

No, I am not making this up.  This single function, in a single cell, will produce an entire report.  Range-based programming at its finest.

 

 

 

---

 

PivotBy Function Similar to Pivot Table

When you update the data in an Excel Table, the Pivot Table requires a Refresh for the changes to be displayed.  The new PivotBy Function will update when the data updates, no refresh needed.

Will the PivotBy Function replace the Pivot Table.  The PivotBy Function will reduce the use of the Pivot Table, but it will not eliminate it.  I would say it might take 30-40% of the effort, and as such clients will most likely have both the Pivot Table and the PivotBy Function in the same workbook.  Heck, they might even populate the Pivot Table directly from Power Query, as in the image below.

 

 

Pivot Table or PivotBy Function, which to use

Do you need to have the ability to Pivot, or are you just looking for the layout of the data.  Most users do not Pivot their data, they look at it, and they may use Slicers, but that is usually it.  So then, why not use the PivotBy Function?

In the image above, we have a Power Query Table, a Pivot Table, a Pivot Chart, and a PivotBy Function, all based on the Table with the PQ Filter of 2023.  See the similarities?  Then it comes down to, how do you want to use it, then you can decide, Pivot or no Pivot.

 

 

---

 

 

Power Query – The Backbone of Automated Excel Solutions

This is where I start a new project, Power Query based on Excel Tables.  The power of Power Query exceeds the power of Excel’s 365 Dynamic Array Functions.  So why not use Excel Tables to do what Tables do, and store your data.  Next step would be to build Power Queries on those Tables.  You can then build reports directly in Power Query, or you can load the data to a Power Query Table, or even a Pivot Table.  From there, you can use the new Dynamic Array Functions as needed, against the Power Query Table to build any remaining reports and analysis.

In the image below you see a Power Query Table, with two additional columns.  We used the new CheckBox to allow the user to apply a markup, if desired.  Of course we used Filters and a Pivot Chart, for the demo.  This is how Excel is done.  This is an xlsx file.

 

 

 

---

 

Directly Referencing Columns in an Excel Table – No Function Needed

You can extract data from one or more columns, without using an Excel function.   There are times when this is useful.  A great way to quickly get a list of items in a Table.

 

Examples:

• =tblLookupData_External_Calcs[[#All],[YTD]]
• =tblLookupData_External_Calcs[YTD]
• =SUM(tblLookupData_External_Calcs[YTD])

 

• You do not need to use an Excel Function to extract data from an Excel Table.  You can simply reference the column.  It is useful if you want all of the data, in the column.

A great way to get a Dynamic List that you can use else where.

 

 

---

 

Data Flow – Excel Tables to Power Query to Reports

Want to simplify your reporting, point to a filtered Power Query Table, and not to a historical Excel Table.  Only show records for the current period.

 

 

 

---

 

Important Note:  Most DAFs are Intended to be Embedded with other DAFs:

Yes, you can use a single function in Excel, to extract data from an Excel Table.  But with that said, most expert Excel programmers embed two or more Excel Functions into one complex function.

Common Combinations

When it comes to combining dynamic array functions, it is very common to have four or more functions embedded into one.    If you use the GroupBy or PivotBy functions, they have both Sort and Filter built0-n, now that is some power.

1. Filter
2. Sort / SortBy
3. Unique
4. Transpose

You can combine the newer functions with the older functions, you can use the # as well.  Microsoft has made some significant changes to Excel; these changes make Excel easier to use and easier to program.

 

 

---

 

Conclusion – Use Excel DAFs to Select Data from an Excel Table

If you want to extract data from an Excel or Power Query Table, Excel 365’s Dynamic Array Functions are the best ones to use.    The new functions populate a range with data, not just one cell.  The PivotBy and GroupBy Functions are unlike any function Microsoft has written in Excel.  Pointing these powerful functions at an Excel Table greatly simplifies Excel programming.

 

 

Contact us for Excel Programming Help

If you need to hire a professional Excel programmer to build your custom workbooks, give us a call, this is what we do.  We can help you with most things Excel, including Power Query.

 

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