Excel Scroll and Sort Table

Dashboards have limited space which is why an Excel scroll and sort table is super handy.

Scroll and sort tables allow you to embed an extract of data from a larger table in your Dashboard. Your user can scroll to see all the records and toggle which column to sort by and set the sort order, as shown below:

Creating an Excel Scroll and Sort Table

Before we had the luxury of Dynamic Arrays, creating an Excel scroll and sort table was a complex task. It required multiple tables, formulas and helper columns, which I cover in detail in my Excel Dashboard course. But in this tutorial I want to show you how simple it is to create a scroll and sort table using the new Dynamic Array functions and functionality.

To be clear, the techniques in this tutorial will only work in Office 365 where you have the new Dynamic Array functions.

Note: At the time of writing Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. We don’t have an ETA for when they will be available to all Office 365 users yet. And to be clear, Excel 2019 does not come with Dynamic Arrays. The only way to get them is with Office 365 …or wait until Excel 2022 (?) comes out.

Source Data

My 30 rows of source data is stored in an Excel Table called ‘Table1’:

Formatting your data in an Excel Table isn’t strictly necessary, but it makes it quick and easy to write my formulas using the Table’s Structured Reference.

Excel Scroll and Sort Table Form Controls

The sort column and sort order are controlled using Radio Buttons and I used a Scroll Bar for the scrolling of the table.

Radio Buttons and Scroll Bars are Form Controls that allow the user to interact with your worksheet. Note: the up/down triangles are Symbols inserted in the cell.

Form Controls are found on the Developer tab:

Click here for instructions if you don’t see the Developer tab.

The selection made in a Form Control is returned to a cell that you specify by right-clicking on the Form Control > Format Control > Cell Link:

You can see the Form Control selection results in the image below where the Sort Order button is displayed in cell I3, the Sort By is in cell I4 and the scroll bar position is in cell I5 (these results can also be hidden on another sheet. I've included them beside the table so you can see them in context):

When you place the Radio Buttons inside a Group Box, like I’ve done above, each button is given a number. For example, the first 'Order' Radio Button is selected and cell I3 contains 1. Likewise, the second 'Sort By' Radio Button is selected and cell I4 contains 2.

The Scroll Bar shows position 0 because it is at the top of the scroll bar.

Tip: the whole Radio Button frame must be inside the Group Box for it to form part of the group. If the outer edge of the Radio Button is outside the Group Box, it won’t be included.

Excel Scroll and Sort Table Formula

The table contains a single formula that detects the selections made in the Form Controls and returns an extract of the source data accordingly. It uses the INDEX, SORT, CHOOSE and SEQUENCE functions:

=INDEX( SORT(Table1, I4, CHOOSE(I3,1,-1)), SEQUENCE(10,1,I5+1,1), {1,2,3,4,5,6})

In English it reads:

SORT Table1 by the 'Sort By' column number displayed in cell I4 (which is the 'Sort By' Radio Button output), use CHOOSE* to return 1 for ascending and -1 for descending based on the order specified in cell I3 (which is the 'Order' Radio Button output), INDEX the table returned by SORT,  returning 10 rows starting at the row number in cell I5+1 (which is the Scroll Bar output) and increment by 1, and return columns 1 through 6.

And because it’s a dynamic array formula it spills the results to create a table 10 rows high and 6 columns wide:

*The SORT function requires 1 for ascending order and -1 for descending order. The Radio buttons return 1 for ascending and 2 for descending, so we use CHOOSE to return the correct value for the SORT function. Alternatively you could use SWITCH(I3,1,1,2,-1) to achieve the same result.

Conditional Formatting

The final touch is to add Conditional Formatting to highlight the selected ‘Sort by’ column.

Download Workbook

Remember, only Office 365 users with Dynamic Arrays will be able to use this workbook.

Related Tutorials

I realise that you may not have access to Office 365 yet, but there are many other ways you can use Form Controls in earlier versions of Excel. Below are a few tutorials you might like to check out:

	Form Controls
	Toggle Conditional Formatting On and Off using Form Controls
	Interactive Excel Charts
	Excel Chart Highlighting

Please Share

If you liked this please share this tutorial with your friends and colleagues.