Skip to content

Excel: Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro

     

    In this video tutorial I explain how to get data to sort automatically when that data is changed or added to.

    Download the featured file here.

    Method 1: Using a Formula to Auto Sort Data

    Click here to view this part of the video.

    Only use this formula if you don’t use Excel 365.  Excel 365 users have a much simpler solution than the one I am about to explain. When you are using a formula, the data will be sorted in a separate table, not in place. If you need to sort your data in place, then you will need to use the VBA macro method explained in method 3.  To use this formula method, watch the video (recommended as its quite involved) or follow the steps below:

    1. Enter two column headings to the right of your data, RANK and TIE?
    2. House your data in an Excel table by clicking in any cell that contains your data and using the shortcut key CTRL T
    3. Click on OK to confirm
    4. On the Ribbon’s Table Design tab enter a name for the table in the Table Name box (left side).  In the video I called the table Sales_2019.  A table name cannot have a space in it
    5. In the RANK column use the following formula: =RANK.EQ([@[Sales 2019]],[Sales 2019]) where [@[Sales 2019]] is the value you are ranking and[Sales 2019] is the entire column of values that you are ranking against (see the video if you are not sure)
      ** – please see note below
    6. The formula above will return some ranking ties.  To overcome this problem type the following formula in the TIE? column: =IF(COUNTIF($C$2:[@RANK],[@RANK])>1,[@RANK]+1,[@RANK]), where [@RANK] is your first RANK number
    7. Create a separate table with three column headings: RANK, Sales Person & Sales 2020
    8. In the RANK column enter rank numbers starting at one.  Add more rank numbers than you initially need so that when you add new records to the Sales_2019 table they will automatically get sorted
    9. In the Sales Person column, type the following formula:
      =IFERROR(INDEX(Sales_2019[Sales Person],MATCH(F2,Sales_2019[TIE?],0),””) where Sales_2019 is the name you gave to the table in step 2; [Sales Person] is the name of the first column whose values you want to list against their rank; F2 is the number you have entered in the RANK column of the table you created in step 7; and [TIE?] is the name of the TIE column you created in step 6 (it may be easier to watch the video!!!!)
    10. In the Sales 2020 column enter the following formula:
      =IFERROR(INDEX(Sales_2019[Sales 2019],MATCH(F2,Sales_2019[TIE?],0),””) where Sales_2019 is the name you gave to the table in step 2; [Sales 2019] is the name of the second column whose values you want to list against their rank; F2 is the number you have entered in the RANK column of the table you created in step 7; and [TIE?] is the name of the TIE column you created in step 6 (it may be easier to watch the video!!!!)

    ** This formula will work for ranking numeric values, if you want to rank text strings, you need a slightly different formula:
    =COUNTIF([Sales Person],”<“&[@[Sales Person]])

    Method 2: Use a Formula to Auto Sort Data in Excel 365

    Click here to view this part of the video.

    To use this method you will need to use the SORT function which is available in Excel 365.

    1. House your data in an Excel table by clicking in any cell that contains your data and using the shortcut key CTRL T
    2. Click on OK to confirm
    3. On the Ribbon’s Table Design tab enter a name for the table in the Table Name box (left side).  In the video I called the table Sales2021.
    4. In a separate table enter two column headings: Sales Person & Sales 2020
    5. In the first cell in the Sales Person column enter the following formula: =SORT(Sales2o21,1), where Sales2021 is the name of the table housing your data and 1 is the position of the column within the Sales2021 table that you are basing your sort on (ie 1 to sort by name or 2 to sort by sales)

    Method 3: Use a VBA Macro

    Click here to view this part of the video.

    This method has the advantage of being able to sort your data in place.

    Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range

Set SalesTable = ActiveSheet.ListObjects("Sales2022")
Set SortCol = Range("Sales2022[Sales 2022]")

If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End If

End Sub

    To use this method you need to paste the code above into the Visual Basic Editor (VBE).

    1. To open the VBE, right-click on the sheet tab you are working in and click View Code
    2. Paste the code above into the code window

    You will need to make a number of changes to the code to make it work for your data. In the following lines:

    1. Set SalesTable = ActiveSheet.ListObjects(“Sales2022”) – change the name Sales2022 to the name you have given your table
    2. Set SortCol = Range(“Sales2022[Sales 2022]”) – change Sales2022[Sales 2022] change to YourTableName[ColumnToSortOn]
    3. You can change the order of the sort by changing Order:=xlDescending to Order:=xlAscending
    4. Close the VBE. This code will automatically run whenever a change is made to your data.

    Please note, Excel workbooks containing VBA code need to be saved as macro-enabled workbooks.