I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts a data set using array formulas and explain in detail how the formulas work. Why would you want to sort a table using array formulas when Excel has tools built-in that works great? Formulas are dynamic and change instantly when new values are added.
If you have built an interactive worksheet or Dashboard you may have form controls, active-x controls, drop-down Lists or something else connected to your data. The Excel user may not be happy to manually sort values using the features Excel provides.
It may be tedious and time-consuming to sort values each time conditions change or data is added even if built-in tools is used.
The sorted table to the right is created with an array formula using the data in the table to the left. It is sorted by the first column and then by the second column and lastly the third column. The order is from A to Z.
The second techniques is to manually sort the data set using the built in tools Excel provides.
The last technique I will demonstrate is to use a macro that sorts values based on certain conditions like if a value is changed or a worksheet is activated etc, it is event code that makes this possible. They are not placed on the same module as regular macros but in worksheet or workbook modules which I will also describe in detail in this article.
Sort using array formulas
The following formula adapts to as many as there are rows in the Excel Table making this feasible for any size of data set. If you have huge amounts of data I recommend using the VBA macro instead, it will be much faster.
Step 2 - Sort Col C from A to Z with COUNTIF function
This part of the formula also creates an array containing numbers representing the sort order of each item in column C, however, it also divides the numbers with 10, 100 or 1000 etc based on the number of rows in the data set.
The ROWS function returns the number of rows in a cell range or a structured reference meaning a cell reference pointing to an Excel Table.
This example demonstrates a button that is linked to a macro, click the button to start the macro. I will also show you how to execute the macro if a cell on the same worksheet has been changed using event code.
'Allows you to write shorter code by referring to an object only once instead of using it with each property.
'Clear previous sort conditions
'Apply sorting from A to Z to cell range B3:B8
.SortFields.Add2 Key:=Range("B3:B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'Apply sorting from A to Z to cell range C3:C8
.SortFields.Add2 Key:=Range("C3:C8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'Apply sorting from A to Z to cell range D3:D8
.SortFields.Add2 Key:=Range("D3:D8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'Set range to B2:D8
'Column names exists
.Header = xlYes
'No case sensitivity
.MatchCase = False
'Sort orientation xlTopToBottom vs xlLeftToRight
.Orientation = xlTopToBottom
'Apply sorting to range
Click and drag on the worksheet to create the button. You can always later resize the button.
A dialog box appears asking for a macro to be assigned. Select the macro you want to use and click OK.
Event code allows you to run the sort macro automatically based on specific things that can happen, for instance, activating the worksheet, selecting a cell or in this case, if a cell value has changed.
'Event code that is executed when a cell value in worksheet VBA changes
Private Sub Worksheet_Change(ByVal Target As Range)
'Start macro named macro1