Dynamic Arrays in Excel 365 – what is it and how can I use it in Excel?

Dynamic Arrays in Excel 365 – how can I use this in my worksheets?

Dynamic Arrays in Excel 365: Excel iconThis week’s hint and tip is about dynamic arrays in Excel 365 and how you can use it in your worksheets. Some of these features are covered in our Master Class Excel Silver training course but not all of them, so we wanted to create a hint and tip to let you know more about it. We are going to go through it now below.


What is Dynamic Arrays in Excel?

Dynamic Arrays in Excel allow you to see dynamically on a worksheet the results of your dynamic array formulas on a list of data. To see this in operation please open the attached workbook Dynamic Arrays below.

Historically and probably usually you would use the Sort and Filter options from the Data Tab. However in this example we are using the new functions listed below:

    • =SORT
    • =SORTBY
    • =FILTER

This allows you to witness a different way of sorting and filtering data. It enables you to sort or filter on a worksheet whilst still seeing the original data and also the new sorted or filtered list.

** Please note that these functions are relatively new and only apply to the latest versions of Excel 365 **


A video to demonstrate Dynamic Arrays

The video below shows you how to use the dynamic arrays option in Excel through the use of the 3 functions mentioned above. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

Click here to download the example spreadsheet shown in the video to try it out yourself!

We hope you have enjoyed this hint and tip on dynamic arrays in Excel 365. Why not take a look at our previous one on using option buttons in Excel to make a quiz​?