5 ways to add an order button in Microsoft Excel

Excel logo

You want to add an order button inExcel? The system is an easy way to view the highest or lower values ​​in the data. This is such a common activity in analyzing data that having a button to provide a method with a click to order your data will save you time!

Add an order button with the filters

When you have a tabular data set in Excel, you can add switches to the column header that allow you to order and filter the data. This is the easiest way to add a system button to your data that is always visible and available for use.

Here's how you can add the system options and filter to your data.

1. Select the data to be ordered.
2. Go to the tabData.
3. Click on the commandFilter.

This will add the filters to your data and you can see them on the right side of any column header in your data.

When clicking on one of these, they will reveal the various sorting and filter options. These will allow you to quickly order any field in growing or decreasing order with the commandsOrders from the smallest to the largestorOrder from the largest to the smallest.

Add a system button with a keyboard shortcut

Sorting and filter switches can also be added to the selection of data with a simple combination of shortcuts. Select your data interval and press the keyboard shortcutCtrl+Shift+LTo add the sorting and filter switches. This is an easy way to add or remove the filters since the connection acts as a switch to activate or deactivate them.

Add an order button with VBA

VBA is a coding language available in the desktop version of Excel and will allow you to create personalized solutions. You can create a VBA macro to order your data and assign them to a button in the sheet.

You can then model your button in any way and add personalized text to the button. This is an excellent option when others will use the working folder as it helps to make the system very intuitive for the user. Here's how you can create your system button with VBA.

Press the keyboard shortcutHalt+F11To open the Visual Basic editor.

Go to the menuinsertVisual Basic editor and select the optionModule. This creates a code module where you can add your VBA code that will order your data.

Sub Togglesorterder () Dim Asccell As Boolean Asccell = ActiveSheet.range ("Asccell"). Value2 If Asccell = True then Activesheet.Rage ("Asccell"). Value2 = False with Activeworkbook.Worksheets ("Date"). Listobjects (" Cars "). Sort .Sortfields.ccar .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.cler .Sortfields.Add2 Key: = Range ("Cars [[#All], [Year]]"), Order: = XLDESCENDING. Apply End with End If End Sub

Add the VBA code above your new form. ThereToggleSortOrder()Subroutine allows you to alternate the system in an Excel table calledCarsin the named sheetDatabetween growing and decreasing order of theYearcolumn.

Begins by setting a variable calledascCellon the value from an interval called in the sheet calledascCell. This is a valueTrueorFalseAnd it is used to keep track of the current system and allows the routine to move from the ascending order to the descending order.

You can create the interval called in your sheet by selecting a cell and then typingascCellinname boxand pressingenter.

An educationIf... Then... Elsethen order data based on value fromascCell. Every part of educationIfIt also performs the rewriting in the interval calledascCellto keep track of the system.

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

Add an order button with Office scripts

Office Scripts is a programming language available for Excel desktop and web versions with an M365 company plan. Office scripts will be the way to go if you need a personalized system button that works both in the desktop version and in the Excel web.

Go to the tabAutomateand click on the optionNew scriptto open thecode editorand create a new script.

Function Main (WorkBook: Excelscript.Workbook) {Let Cars = WorkBook.Gettable ("Cars"); Let Asccell = WorkBook.GetactiveWorksheet (). Getrainge ("Ascchell"). Getvalue (); Let Asvalue: Boolean; If (asccell === True) {Asvalue = True; WorkBook.GetactiveWorksheet (). Getrango ("Ascchell"). Setvalue (False); } else {asvalue = false; WorkBook.Getactiveworksheet (). Getrango ("Ascchell"). Setvalue (True); }; Cars.getsort (). Apply ([{Key: 2, ascending: ascvalue}], false); }

Add the code above the code editor and press the buttonSave script.

This code can be used to order a table in an Excel work folder. Start by declaring two variables:carsisascCell. TherecarsVariable memorizes the table of the working folder, while theascCellvariable memorizes a value that indicates whether the sorting operation must be performed in increasing order (true) or decreasing (false).

Check the value of the cell of the interval calledascCellTo determine which order order must be used. This cell is used to keep track of the previously applied system option.

The code will update the Asccell interval every time it is performed. In this way the code can act as a switch between growing and decreasing system.

SelfascCellis set totrue, the data are ordered in increasing order, otherwise they are ordered in decreasing order.

The valueascCellIt is used to set theascValuevariable that determines the system.

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

• key: 2represents which column should be ordered. This uses an index based on zero, so the 2nd will order the third column.
• ascending: ascValueIt is a Boolean value to establish if you want an ascending system (true) or descendant (false).

This code can be performed by the buttonRunin the editorof code, but you can also create a button in the sheet that will perform the code. Click the iconEllipsesin the upper right area of ​​the editorof codeand select the optionAdd buttonfrom the menu. This creates a button floating above the grill and will perform the office script every time it is pressed.

Add a system button with a slicer

Excel has a very useful slicer option to filter the table data. It is an interactive filter object that floats above the grill and allows a user to filter a certain field in the data by clicking on the Slicer elements.

Unfortunately, there is no equivalent object to order data. But you can create an ordering tool of data using the dynamic array functionSORTBY.

In this way it is possible to set Slicer with ascending and descending options that can be selected and obtain the desired system. Here's how you can configure it.

You will first have to set up a second table that will eventually connect to Slicer. This will have 2 columns, one will contain the labels to be viewed as slicer and the other will contain the inputs of the topics required for the functionSORTBY. In the previous table, the columnKind ofcontains labels and columnValuecontains the argumentsSORTBYcorrespondents.

The table will have two lines because the functionSORTBYIt has two possible inputs. Can order in ordergrowing1( ) ordecreasing(-1).

Now you can create slicer for this secondary table. Select the table, go to the cardTable designand click on the commandEnter slicer.

The menu will openEnter slicerwhere you can select the field on which to base the slicer. Select the columnKind ofwhich contains the labelsAscendingisDescendingand press the buttonOKto create the slicer.

This will create an affection in which you can selectAscendingorDescending. This will filter the table to show only the selected option. Now you will need a way to get the value visible from your filtered table. This can be obtained with the functionSUBTOTAL. It will allow you to aggregate a column according to the visible lines.

= Subtotal (104, sort [Value])

The formulaSUBTOTALprevious will obtain the maximum value according to the lines visible in the columnValue. Returns 1 when the table is not filtered or filtered onAscendingand returns -1 when the table is filtered on descriptioning.

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

This resultSUBTOTALIt can be used in the functionSORTBYTo change the system using Slicer! The previous formula will return the tableCarssorted by the columnYearbased on the resultsSUBTOTAL.

Now you can order your data with an affection!

Conclusions

The data system is a fundamental part of any data analysis and having a button for this will make the process much easier. The filters switches offer an integrated button for each column header in the data and these can be added quickly with a shortcut. But the ordering options they provide are not obvious.

Another way to enter an order button for your data is with VBA or Office Scripts. Both will allow you to create code to order your data and these can be added to a button positioned in your sheet.

Author John MacDougall