Skip to main content

How to auto sort column by value in Excel?

Author: Kelly Last Modified: 2025-04-16

For example, you have a purchase table as below screenshot shown. Now you want the Price column to be sorted automatically when you enter new numbers/prices in this column, how could you solve it? Here I introduce a VBA macro to help you auto sort a specific column by value in Excel.

Auto sort column by value with VBA

A screenshot showing a sample purchase table in Excel with a price column to be sorted


Auto sort column by value with VBA

This VBA macro will sort all data in a specific column automatically as soon as you enter new data or change value in the column in Excel.

1. Right click current sheet name in the "Sheet Tab bar", and then click the "View Code" from the right-clicking menu.
A screenshot showing the right-click menu with View Code option selected in the sheet tab bar

2. In the opening "Microsoft Visual Basic for Application" dialog box, paste the following VBA macro code into the opening window.
A screenshot of the Microsoft Visual Basic for Applications window with VBA code pasted for auto sorting

VBA: Auto Sort Column in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Note:
1) In the above vba code, "B:B" means it will auto-sort Column B, B1 is the first cell in Column B, "B2" is the second cell in Column B, and you can change them based on your needs.
2) The snippet "Header:=xlYes" in the 5th row tells Excel that the range you will sort has a header, so that the first row of the range will not be included when sorting. If there is no header, please change it to "Header:=xlNo"; and change "Key1:=Range("B2")" in the 4th row to "Key1:=Range("B1")".

3. Then go back to the worksheet, when you enter a new number in the Price column or modify any existing prices, the Price column will be automatically sorted in ascending order.
A screenshot showing an Excel table with the price column sorted automatically after entering a new value
Note: When you enter a new number in the Price column, you must enter the number in the first blank cell below the original numbers. If there are blank cells between the new entered number and original numbers as well as blank cells between original numbers, this column won't be sorted automatically.


Demo: Auto sort column by value with VBA in Excel

 
Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Easily sort by frequency of occurrences in Excel

Kutools for Excel's "Advanced Sort" feature allows you to effortlessly sort data by various criteria such as text length, last name, absolute value, frequency, and more.

A screenshot of Kutools for Excel 'Advanced Sort' feature with sorting options visible

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!