Do you want to add a sort button in Excel?

Sorting is an easy way to see the highest or lowest values in your data. This is such a common task in data analysis that having a button to provide a one-click method to sort your data will save you time!

Excel provides many ways to sort data but not in an easy single-click button solution. But with a bit of ingenuity, you can create your own buttons to sort data.

This post is going to show you how to create buttons to sort your data.

Add a Sort Button with Filter Toggles

When you have a tabular dataset in Excel you are able to add toggles to the column headings that allow you to sort and filter the data.

This is the easiest way to add a sort button to your data that is always visible and available to use.

Here’s how you can add the sort and filter toggles to your data.

1. Select the data which is to be sorted.
2. Go to the Data tab.
3. Click on the Filter command.

This will add the filter toggles to your data and you can see them on the right side of each column heading in your data.

When you click on any of these, they will reveal the various sort and filter options.

These will allow you to quickly sort any field in ascending or descending order with the Sort Smallest to Largest or Sort Largest to Smallest commands.

This will even allow you to

💡 Tip: The sort and filter toggles will even allow you to sort by cell will even allow you to sort by font and cell fill color.

Add a Sort Button with a Keyboard Shortcut

The sort and filter toggles can also be added to your data selection with an easy shortcut combination.

Select your data range and press the Ctrl + Shift + L keyboard shortcut to add the sort and filter toggles.

This is an easy way to add or remove the filters as the shortcut acts as a toggle to turn them on or off.

Add a Sort Button with VBA

VBA is a coding language available in the desktop version of Excel and will allow you to build custom solutions.

You can create a VBA macro to sort your data and assign it to a button in the sheet.

You can then style your button in any way as well as add custom text to the button. This is a great option when others will be using the workbook as it helps to make the sorting very intuitive for the user.

Here’s how you can create your sort button with VBA.

Press the Alt + F11 keyboard shortcut to open the visual basic editor.

Go to the Insert menu of the visual basic editor and select the Module option. This creates a code module where you can add your VBA code that will sort your data.

Sub ToggleSortOrder()

Dim ascCell As Boolean
ascCell = ActiveSheet.Range("ascCell").Value2

If ascCell = True Then
    ActiveSheet.Range("ascCell").Value2 = False
    With ActiveWorkbook.Worksheets("Data").ListObjects("Cars").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("Cars[[#All],[Year]]"), Order:=xlAscending
        .Apply
    End With
Else
    ActiveSheet.Range("ascCell").Value2 = True
    With ActiveWorkbook.Worksheets("Data").ListObjects("Cars").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("Cars[[#All],[Year]]"), Order:=xlDescending
        .Apply
    End With
End If

End Sub

Add the above VBA code to your new module.

The ToggleSortOrder() subroutine allows you to toggle the sort order in an Excel table named Cars in the sheet named Data between ascending and descending order of the Year column.

It begins by setting a variable named ascCell to the value from a named range in the sheet called ascCell. This is a True or False value and it’s used to track the current sort order and allows the routine to toggle between ascending and descending order.

You can create the named range in your sheet by selecting a cell and then typing ascCell into the name box and pressing Enter.

An If... Then... Else statement then sorts the data depending on the value from ascCell. Each part of the If statement also writes back to the ascCell named range to keep track of the sort order.

Now you can create a button in your sheet and set the button to run your VBA code when clicked.

Add a Sort Button with Office Scripts

Office Scripts is a programming language available for both desktop and web versions of Excel with an M365 business plan.

Office Scripts will be the way to go if you need a custom sort button that will work in both the desktop and web versions of Excel.

Go to the Automate tab and click on the New Script option to open the Code Editor and create a new script.

function main(workbook: ExcelScript.Workbook) {

	let cars = workbook.getTable("Cars");
	let ascCell = workbook.getActiveWorksheet().getRange("ascCell").getValue();
	let ascValue: boolean;

	if (ascCell === true) {
		ascValue = true;
		workbook.getActiveWorksheet().getRange("ascCell").setValue(false);
	}
	else {
		ascValue = false;
		workbook.getActiveWorksheet().getRange("ascCell").setValue(true);
	};

	cars.getSort().apply([{ key: 2, ascending: ascValue }], false);
}

Add the above code to the Code Editor and press the Save script button.

This code can be used to sort a table in an Excel workbook. It starts by declaring two variables: cars and ascCell. The cars variable stores the workbook’s table, while the ascCell variable stores a value indicating whether the sort operation should be performed in ascending (true) or descending (false) order.

It checks the value of the named range cell called ascCell to determine which sorting order should be used. This cell is used to track what sorting option has previously been applied.

The code will update the ascCell range each time it’s run. This way the code can act as a toggle between ascending and descending sort order.

If ascCell is set to true, then the data is sorted in ascending order and otherwise it is sorted in descending order.

The ascCell value is used to set the ascValue variable that determines the sorting order.

Finally, it calls the .getSort() method with two parameters. This ensures that the desired sorting operation is performed on the table.

• key: 2 represents which column should be sorted. This uses a zero-based index so the 2 will sort the third column.
• ascending: ascValue is a boolean value for whether we want an ascending (true) or descending (false) sort.

This code can be run from the Run button in the Code Editor, but you can also create a button in the sheet that will run the code.

Click on the Ellipses icon in the top right area of the Code Editor and select the Add button option from the menu.

This creates a button that floats above the grid and will run the Office Script whenever it’s pressed.

💡 Tip: Right-click on the button to edit the text displayed or customize the shape, outline, and fill color. When you right-click on the button it will enable the edit mode and the Shape Format tab will appear in the ribbon.

Add a Sort Button with a Slicer

Excel has a very useful Slicer option for filtering table data.

This is an interactive filter object that floats above the grid and allows a user to filter a given field in the data by clicking items in the slicer.

Unfortunately, there is no equivalent object for sorting your data.

But you can build a data sorting tool using a slicer and the SORTBY dynamic array function.

This way you can set up a slicer with ascending and descending options that can be selected and result in the desired sort order.

Here’s how you can set this up.

You will first need to set up a second table that will eventually connect to a slicer.

This will have 2 columns, one will contain the labels to display in the slicer and the other will contain the required argument inputs for the SORTBY function.

In the above table, the Type column contains the labels and the Value column contains the corresponding SORTBY arguments.

The table will have two rows because the SORTBY function has two possible inputs. It can sort in Ascending order (1) or Descending order (-1).

Now you can create a slicer for this secondary table. Select the table, go to the Table Design tab, and click on the Insert Slicer command.

This will open the Insert Slicers menu where you can select the field on which to base your slicer.

Select the Type column that contains the labels Ascending and Descending and press the OK button to create the slicer.

This will create a slicer where you can select either from Ascending or Descending. This will filter the table to show only the selected option.

Now, you will need a way to get the visible value from your filtered table.

This can be achieved with the SUBTOTAL function. It will allow you to aggregate a column based on the visible rows.

= SUBTOTAL ( 104, Sort[Value] )

The above SUBTOTAL formula will get the maximum value based on the visible rows in the Value column.

This returns 1 when the table is unfiltered or filtered on Ascending and returns -1 when the table is filtered on Descending.

= SORTBY ( Cars, Cars[Year], SUBTOTAL ( 104, Sort[Value] ) )

This SUBTOTAL result can be used in the SORTBY function to change the sort order using a slicer!

The above formula will return the Cars table sorted by the Year column based on the SUBTOTAL results.

Now you can sort your data with a slicer!

Conclusions

Sorting data is a fundamental part of any data analysis, and having a button for this will make the process a lot easier.

Filter toggles offer a built-in button for each column heading in your data, and these can quickly be added with a shortcut. But the sorting options they provide aren’t obvious.

Another way to insert a sort button for your data is with either VBA or Office Scripts. Both of these will allow you to create code to sort your data and these can be added to a button placed in your sheet.

You can also hack the table slicer feature to build your own sorting button thanks to the SORTBY and SUBTOTAL functions.

What is your preferred method to add a sort button in Excel? Let me know in the comments!