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. This feature isn’t covered in our courses, 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​?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy