How to auto sort column by value in Excel?
When working with lists or tables in Excel, it's common to want your data to always appear sorted, especially as new information is added. For instance, imagine you manage a purchase table as illustrated in the screenshot below, where prices of items are regularly updated or new entries are added. In such situations, you may notice that Excel does not automatically reorder the Price column when you input new values — the data remains in its original position until you manually sort it again. This can easily lead to confusion or errors, particularly when you need to quickly analyze or present always-sorted information. Knowing how to auto sort a column according to its value as soon as there are changes or additions can therefore save significant time and maintain the accuracy of your workflow.
This guide provides practical methods to help you automatically sort a specific column by value as you update your worksheet. Below, you’ll find both a VBA macro solution as well as a dynamic formula solution for newer versions of Excel. Each method is suited to particular scenarios, and details on setup, application tips, parameter explanations, and troubleshooting are provided to ensure you can implement the most appropriate approach effectively.
Auto sort column by value with VBA
Auto sort column by value with Excel Formula (Dynamic Arrays)
Auto sort column by value with VBA
This VBA macro automatically sorts all data in a specific column each time you enter a new value or modify existing data within that column in your worksheet. This approach is ideal if you’re using an older version of Excel, or if you want data within a table to be perpetually sorted in-place without using additional columns.
Typical scenarios where this method is helpful:
- When you want the data in your table or column to immediately reflect an updated order after any change, without requiring manual sorting.
- If you manage collaborative worksheets where several users frequently update the same range of data and need the view to remain consistently sorted.
Things to know before you start:
- VBA solutions make direct changes to your worksheet. It’s a good idea to save your file before beginning.
- The macro will only work if macros are enabled in your Excel environment.
- Macros will be attached to the specific worksheet and may need adapting if your table layout changes.
1. Right click the current sheet name in the Sheet tab bar, and then click "View Code" from the context menu.
2. In the opened "Microsoft Visual Basic for Applications" window, paste the following VBA macro code into the code window for your target worksheet.
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 SubNotes:
1) In the above VBA code, "B:B" refers to Column B, "B1" is the cell in the first row of Column B, and "B2" is the cell just below the header. You can modify these references to match the column you want to auto-sort in your data.
2) The parameter Header:=xlYes in the fifth row specifies that your data range includes a header row, ensuring the header is not sorted with the column values. If your data range does not have a header, change this to Header:=xlNo and also adjust Key1:=Range("B2") to Key1:=Range("B1").
3) The macro is triggered whenever a cell value changes within the specified column.
3. Return to your worksheet. Now, whenever you add a new number in the Price column or update an existing value, the column will immediately be resorted in ascending order.
Tip: Always enter new values immediately after the last used cell in the column. If there are blank cells among your data, the sort will not operate as expected.
Auto sort column by value with Excel Formula (Dynamic Arrays)
For users of Excel 365 or Excel 2021 and later, dynamic array functions like SORT and SORTBY provide an efficient, code-free way to generate a sorted list and keep it automatically updated as your data changes. This solution does not require VBA, is natively supported in recent versions of Excel, and is suitable for scenarios where you prefer to keep your original data unchanged and instead work with a sorted copy of your data in a separate range or worksheet.
When should I use this approach?
- If you need a real-time sorted version of your data in a different location (e.g., for reports, dashboards, or printing) while preserving the original raw data.
- When you’re using Excel 365, Excel 2021, or later versions that support dynamic arrays.
- This method displays the sorted data in a new area and does not re-order your original data. If you require in-place reordering, consider the VBA solution above.
- Dynamic arrays are not available in Excel 2019 or earlier.
1. Decide where you want to display the sorted list. For example, if your original table is in A1:C10 (with headers in row 1) and you want to show the sorted list starting in cell E1, select E1.
2. Enter the following formula into cell E1:
=SORT(A2:C10,2,1)In the above formula:
- A2:C10 is your original data range (excluding headers). Adjust this to match your data range.
- 2 means you are sorting by the second column — for example, if your Price is in the second column of the selected range.
- 1 specifies ascending order. Use -1 if you want descending order.
3. Press Enter. The formula will output a dynamic, sorted copy of your data. If you add, remove, or edit items in your original range (e.g., new prices or products), the sorted list updates immediately — no manual intervention is needed.
Notes:
- If more data is entered below the original area, the reference in the formula (e.g.,
A2:C10) must be expanded accordingly. - For continuously changing data ranges, consider using Excel tables (Insert > Table), and refer to the table name in your SORT formula for automatic range updates.
- If using a single column (e.g., B2:B10), the formula simplifies to
=SORT(B2:B10,1,1).
Demo: Auto sort column by value with VBA in Excel
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.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in