How to Auto Sort in Excel When Data Is Entered (4 Easy Ways)

Consider the following dataset with 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Product, Storage (units), and Sorted Data. Let’s sort through the dataset.

auto sort when data is entered in excel

Method 1 – Using the SORT Function for Ascending Sorting


  • Go to cell E5 and insert the following formula:

sort function to auto-sort in excel when data is entered in ascending order

  • Press Enter to insert the data after sorting them according to the set criteria.
  • If you now change the Storage units value in the first table for any product, this will automatically sort it in the second table.

Read More: How to Auto Sort Multiple Columns in Excel

Method 2 – Sorting in Descending Order


  • Double-click on cell E5 and enter the below formula:

sort function to auto-sort in descending order in excel when data is entered

  • Press the Enter key, and you should get the data in descending order.
  • If you change any value in the main data, Excel will automatically sort it again.

Method 3 – Auto Sorting Multiple Columns


  • Double-click on cell E5 and insert the formula below:

  • Press the Enter key to sort out the columns of the main data table even if we change any values.

Read More: Excel Auto Sort When Data Changes

Method 4 – Applying VLOOKUP Function

Let’s sort the table according to a customized “ranking” score given to each product.


  • Create a new helped column in B, pushing the data a column to the right.
  • Do the same with the “Sorted” table, creating a new column F.
  • Navigate to cell B5 and insert the formula below:

  • Press Enter.
  • Manually write the rankings in the second table in ascending order as in the image below.

  • Then, type in this formula in cell G5:

  • Press the Enter key.
  • Insert the following formula in cell H5:

  • Press Enter.

Things to Remember

  • The SORT function is only available in Microsoft 365.
  • The result from this function is a dynamic array and can’t change the individual values.
  • The first argument of the SORT function has to be a range of cells.
  • If no criteria are specified, this function will sort in ascending order by default.
  • The SORT function will give a #VALUE error if the sort_index is out of range.

Download Practice Workbook

You can download the practice workbook from here.

Related Articles

<< Go Back to Auto Sort in Excel | Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

  1. Reply
    Kanhaiyalal Newaskar. Sep 5, 2021 at 11:14 AM

    Dear Sir, 5th Sept.2021.

    Very clearly and nicely you showed this article. Very easy to understand.
    Thanking you for the efforts taken.
    Awaiting eagerly to receive such notes and articles in future too.
    I remain.

    Kanhaiyalal Newaskar

  2. Thank you, Asikul, I learned a new trick in Excel today. Just keep filling my Excel tool box.

  3. Reply
    Eric Charles Baumgardner Oct 28, 2021 at 1:12 AM

    How do I insert my own workbook value into the code? I am having issues when I paste new data into the tab as the code no longer works once I do that.

      In order to enter your own workbook value into the code, you need to change the ranges inside the code. Check the following screenshot. Thanks!

  4. Please I need the cracked version of the Excel 365 version to download. I tried every means to get that version but not able to get from it. Kindly help me with a link to a site that have some some for me to download for my window 10 64bit laptop. Thank you. Your formula is working perfectly.

  5. i’m getting a Compile Error.
    Saying Syntax error.

    • Dear SWAROOP,
      A syntax error occurs due to misspelling or missing code. Mostly, due to not specifying the condition and not putting closing brackets. You can check the VBA editor which will help you identify the problems. You can try applying the code in a new worksheet to find your solution.

      In addition, if you are applying VBA code with your own workbook data, don’t forget to change the ranges. Check the below screenshot. Thanks!

  6. Hi, i found your answer very helpful but I have a small problem here as I have my headers in the first two rows. so inorder to work I need the first 2 rows to stay same and the remaining rows have to autosort using this vba script. Is it possible?

    • Hello Swaroop,
      can you please share your Excel file and explain the question clearly?
      Because we didn’t sort our first two rows in this example also.

  7. For option 3 Run a VBA Code to Auto Sort When Data Is entered. I’m not a very Excel expert but I did the steps as mentioned an nothing happened. I think that im missing some steps after pasting the code into the VB and save or run the macro. I close the MVB for apps window and then nothing happens

    would you mind to help

    • Reply Avatar photo
      Naimul Hasan Arif Nov 10, 2022 at 1:57 PM

      I will be glad if I could help you even a little.
      The most important think here is to be careful about the code range. Input the range in the code according to your dataset.
      For more simplification, you can try the following code where the code runs in range B3:B12 and sorts the values in the Ascending order.

      Private Sub AutoSort(ByVal Target As Range)
      If Not Intersect(Target, Range(“B:B”)) Is Nothing Then
      Range(“B3:B12”).Sort Key1:=Range(“B3”), Order1:=xlAscending, Header:=xlNo
      End If
      End Sub

      I hope it’ll run perfectly.

  8. Thank you for the information. I was wondering if it is possible to sort two columns based on the second column without creating a new “table”? I have a list of names and a column that has a countif function. I want to sort by the countif function as it updates.

    • Hello DERRELL,
      Thank you for your question. I will be glad if I can help you even a little.
      First of all, YES, you can sort two columns based on the second column without creating a new table.
      If you want to know the process in detail, kindly read through the following article.
      I hope this will solve your issue. If you still face problems, please feel free to comment again or send your workbook through e-mail so that our team can check the issue.

Leave a reply

Advanced Excel Exercises with Solutions PDF