Excel.Tips.Net ExcelTips (Menu Interface)

Automatically Sorting as You Enter Information

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Sorting as You Enter Information.

Pat wonders if there is a way to automatically sort every time she adds new data to a worksheet. Pat thinks it would be great, for instance, that when she adds a new name to a list of names that the names are automatically sorted to always be in order.

The only way that this can be done is by using a macro that is triggered whenever something new is entered in the worksheet. You can, for instance, add a macro to the code for a worksheet that is triggered when something in the worksheet changes. (You can view the code window by right-clicking the worksheet tab and choosing View Code from the resulting Context menu.) The following is an example of one such simple macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("A1").Sort Key1:=Range("A2"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
End Sub

The macro assumes that you want to sort on the data in column A and that there is a header in cell A1. If the names are in a different column, just change the cell A2 reference to a different column, such as B2, C2, etc.

Of course, sorting anytime that any change is made can be bothersome. You might want to limit when the sorting is done so that it only occurs when changes are made to a specific portion of your data. The following version of the macro sorts the data only when a change is made in column A.

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

There are some drawbacks to using a macro to automatically sort your data. First, since you are using a macro to sort, the operation is essentially "final." In other words, after the sorting you can't use Ctrl+Z to undo the operation.

A second drawback is that data entry might become a bit disconcerting. For instance, if you use any of the above macros and you start to put names into the worksheet, they will be sorted as soon as you finish what is in column A. If your data uses five columns and you start your entry in row 15, as soon as you get done entering the name into column A (and before you enter data into columns B through E), your data is sorted into the proper order. This means that you will need to find where it was moved in the sort, select the proper cell in column B, and then enter the rest of the data for the record. Of course, the way around this is to add your data in an unnatural order—simply make sure that the name in column A is the very last thing you enter for the record.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9005) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Sorting as You Enter Information.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Sandip    05 Oct 2016, 14:06
pls. help me for following condition.
if column A contains 5 row data, and column B is their values. I want formula that if I press any data which is in column A, the value of that data in column B, should be automatically print in that cell.
Pls. help.
Willy Vanhaelen    24 Sep 2016, 12:31
@Mike S

This macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Or Target.Columns.Count > 1 Then Exit Sub
    Dim tmp As Variant
    tmp = Cells(Target.Row, 1).Formula 'save contents
    On Error GoTo Enable_Events
    Application.EnableEvents = False
    Cells(Target.Row, 1) = "#$"
    Range("A1").Sort Header:=xlYes, _
      Key1:=Range("C1"), Order1:=xlAscending, _
      Key2:=Range("B1"), Order2:=xlAscending
    Cells(Application.Match("#$", Columns(1), 0), 2).Select
    Cells(Selection.Row, 1) = tmp 'restore contents
    Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.

Let me know if something is not working as you expect.
Mike S    24 Sep 2016, 09:27
@ Willy Vanhaelen the list does not change it is an overtime sorting list with persons name and their seniority date that do not change then a date last worked that will be updated weekly. Essentially this one list is used to both select voluntantary and mandatory overtime so sorting by date last worked by itself can lead to an issue with someone being mandated ahead of someone with less seniority. Way more work than it needs to be in my opinion but if it is what I am told to do I am all about making it easier. I just have not played around with macros much so don't know where to start. Thanks for your response and help.
Willy Vanhaelen    23 Sep 2016, 13:15
@Mike S.

It can be done but by adding records, it will be very unreliable.

When you enter data in A, B, and C consecutively this will happen:
1) Entry in A: Ok
2) Entry in B: will trigger the sort but C is still empty so the sort is wrong
3) entry in C: is recorded but you will have to re-enter the date to redo the sort

Not very elegant. If you have the discipline to always enter A, C, B it can work.

If it is a static list where you just want to re-sort when changing the date I can provide you a macro that can do it. Just let me know.
Mike S.    22 Sep 2016, 23:46
Could some one help me design a macro to do the following: column A (names) column B (dates) column C (number) if date changed in B sort whole group by C smallest to largest then B earliest date to latest date automatically
Sreerag    05 Sep 2016, 03:50
Im a newbie to excel so i don't know any technical details. Im using 2007 excel. I want to copy the whole column as i enter data to my main sheet(sheet 1) to the other sheets (named sheet 2,3,4 resp) and I want this to be copied according to the data i enter in Column 'S' of the main sheet. Please help. I have tons of data to be copied that I really get annoyed. So please.

U could also mail me the macro programs.
i would appreciate early replies.
Thank you.
Willy Vanhaelen    26 Aug 2016, 09:55

I don't think that a regular advanced sort as Barry proposes will solve your problem because all rows with an empty cell in column C will end up at the end of the table. What you discribe is in fact a conditional sorting and Excel doesn't provide that.

You can solve it with a help column though. Assuming column E is empty and your data starts at row 2, enter the following formula in E2:
Copy this down as far as needed and sort on this column E.

I hope this solves your problem.
Barry    25 Aug 2016, 16:16

If you do not need to do this too frequently then using Excel in-built sorting functions will do this easily. In Excel 2003 select Data then Sort and then specify the columns you want sorted and whether the sort is ascending or descending, in Excel 2007 or later choose the Data tab, then "Advanced Sort....." and then select the column and sort order.

If you want to do this sort frequently then turn on the macro reorder,assign the macro to a shortcut key,
before doing the above and then turn it off once you've completed the task. Then simply use the shortcut to execute the Sort whenever you like.
James    24 Aug 2016, 14:23
I am a total newbie in excel.

I have two columns I would like to sort:
1)Column C and D
2)Both columns are dates (eg. 15-Dec-16)
3)Column C sometimes will be blank

I would like Column C to sort first and if it is blank, to take into account the date in column D.

Please let me know if anyone can help.

Thank you!
Barry    14 Nov 2015, 05:05

The tip http://excel.tips.net/T003116_Recording_a_Data_Entry_Time.html

should give yo uthe answer you want
Daniel Sales    13 Nov 2015, 06:47
HI There,

I have a spread sheet where I am calling through contacts. i would like to apply a date in the date column and have it automatically update rather than go to "sort & filter" each time.

Can anybody please help?

Many thanks
indra jit ghosh    12 Oct 2015, 02:57
coloumn A-roll
coloumn B- name
column C- rank
i want to do that column d posses rank wise name with automatically changing the valu of c.
how can i do that? please suggest some thing to get rid ths problem.
NILESH    06 Oct 2015, 06:10

Garetth    05 Oct 2015, 20:21

Please help, Im doing a local soccer tournment on my work and I have all the information on excel, but is complicated to update every week the results, goals, points, etc.

So could you help me with some macro to auto sort the positions?


Column A = Gral Position (aka 1,2,3,4,5_
Column B = Team
Column C = wins

So auto sort column C (and B attached to C)

any ideas?

Thank you!!!
Willy Vanhaelen    29 Sep 2015, 10:49

This macro will probably suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Or Target.Columns.Count > 1 Then Exit Sub
    Dim tmp As Variant
    tmp = Cells(Target.Row, 2).Formula 'save contents
    On Error GoTo Enable_Events
    Application.EnableEvents = False
    Cells(Target.Row, 2) = "#$"
    Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    Cells(Application.Match("#$", Columns(2), 0), 1).Select
    Cells(Selection.Row, 2) = tmp 'restore contents
    Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can continue to enter data in the other columns.
Barry    28 Sep 2015, 05:42

What you want can be done, as using the solutions described in this tip and the various comments made.

The critical thing is determining the trigger for the sort. This has to be done in such a way that is not confusing/disconcerting for the User. That is you do not want to, say, enter a value is the bottom of column A and immediately sort then, so that the User then has to find the partial entry to complete the record.

As I have previously suggested the trigger could be on making the entry into the last column K, though obviously if column K entry is not made then the sort will not occur.

A more elegant solution would be to use a Userform for the User to enter the data and when the data is transferred to the table the sort can then be triggered at that point. You can also apply more rigorous data validation rules as well e.g. checking for certain fields to have values. But this is really in the realm of a competent VBA programmer.
maryanne    27 Sep 2015, 09:43

I am new with macro.

I want to customise a function that will automatically sort data alphabetically including the new added data.

SO i have the names in Column A and I have data til row K. I want column A to K to sort automatically every time I enter new data.

I have been trying this formula but cant seem to have it work.
Barry    18 Sep 2015, 06:03

This a classic case for using a pivotable.
Pheb    18 Sep 2015, 03:57
Hi Barry,

My apology for incomplete data. This is what I’m working with, I have a huge data containing of brands, average sales and branches. I need to classify how many brands contains average sales like 1) 100,000 and up 2) 50,000 to 99,999 3)49,000 and below and I need to do it in 53 branches. For now, I use conditional formatting in excel (Highlight Cells Rule) for me to separate data per branch then I copy paste (value only) each branches in a sheet then do a manual sorting. The problem is i really needed to update this monthly. 

Column C - Brands
Column D - 100,000 and up

Column F - Brands
Column G - 50,000 TO 99,999

Column I - Brands
Column J - 49,000 & below

If possible i want to automatically sort 3 columns that contain numbers in descending order(d,g,j)together with their brands (c,f,i). Any help would be appreciated.
Tre    17 Sep 2015, 20:43
Thank you for your help. The code works great after changing the sort order for the right columns.
Barry    17 Sep 2015, 14:18

Your requirement is a lot more complicated. The primary issue is the wookbook with the table to be sorted just has a bunch of formulas which do not in themselves change (even though the value from the linked workbook does), therefore there is no trigger/event to fire off the macro.

You could just fire off the macro when the workbook is opened, and/or every few seconds/minutes/hours if an update is needed whilst the workbook is open. You would use the "On Time" function to trigger the sorting macro, and to reset the On Time function for the next sort interval. The refresh time should be longer than the time taken to refresh the linked data.
Barry    17 Sep 2015, 14:01

Please clarify what columns are within the range you want sorted, and which column you want to sort by (and whether to sort ascending or descending values).
MichaelB    16 Sep 2015, 22:12
I tried taking the top code and modifying it without luck to autosort all at once multiple (8) columns A thru H - which are linked to another workbook that has daily data changes. Can you assist me. Thnx Barry.
Pheb    16 Sep 2015, 05:33


I really need help!I want to automatically sort 3 columns in a excel sheet.
Column 1 - 50 & below
Column 2 - 51 - 100
Column 3 - 100 & up

Please provide a solution, Thank you in advance for your immediate response!
Barry    29 Aug 2015, 14:26

I got this working without any hiccups, but needed a little bit of tweaking of the code that the macro recorder produced.

You can download the file I created using this link:

Barry    29 Aug 2015, 13:36

Can you paste the code into this thread?

Mike    28 Aug 2015, 15:06

I've read your comments to SNZ which should apply to my question as well. I ran the recorder and sorted my data and have the code for that process. However, I still can't seem to get it to work when I put that code into sheet 1.

Mike    28 Aug 2015, 14:37
Thanks! This was very helpful! However, I can't figure out how to make a small change.

How can I use this code to sort based on a change to a cell in a different sheet?

To be more clear, I want to auto sort columns B through L on sheet 2, based on a change to "M4" in sheet 1.
Barry    27 Aug 2015, 05:22
@Nithin Antony

You need to be more specific on what you want.

1. what range do you want to be sorted?
2. what column(s) do you want to trigger the sort?

You need to take note of the comments in the main part of the tips regarding it being disconcerting to the User.
Barry    27 Aug 2015, 05:16

The following code is a variant of what I suggested to Nicolette on 27th March 2015

Please note I have not tested this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        With Worksheets("Sheet1").Sort
              .SortFields.Add Key:=Range("A:A"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlAscending, _
              .SortFields.Add Key:=Range("D:D"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlDescending, _
               .SetRange Range("A:E")
              .Header = xlYes
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
          End With
    ElseIf Not Intersect(Target, Range("L:L")) Is Nothing Then
        With Worksheets("Sheet1").Sort
              .SortFields.Add Key:=Range("K:K"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlAscending, _
              .SortFields.Add Key:=Range("H:H"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlDescending, _
               .SetRange Range("G:L")
              .Header = xlYes
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
          End With
    End If
End Sub

NB: you will need to change the sort order to suit your requirements what you want on each of the lines starting "Order:=" and change the parameter to either xlAscending or xlDescending as required.
Nithin Antony    26 Aug 2015, 06:54
I want to automatically sort 4 columns in a excel sheet.
Column o - A to z
Column D - A to Z
Column j - A to Z
and Column G - Newest to Oldest.

Kindly provide a solution, Thank you!
Tre    25 Aug 2015, 13:58
I could use some help with the code as well.

Columns A-E need custom alphabetical auto-sorting by column A first, then by D, once column E data has changed (alleviates data sorting before you enter all five fields). BUT, I also have a second set of columns... Columns G-L hold another set of data and they need to be custom alphabetical auto-sorted by column K first, then by column H, once column L data has changed (again alleviating auto-sort before all data across the row is entered).

I can see from the examples how the sort code works in general, but I cannot for the life of me, write the code to make even one set of cells auto-sort correctly based on what I have stated here. I can sort them manually this way using the custom sort I enter. But it seems like the custom sort only holds one set of values at a time as I have to re-enter the sort values each time I want to sort one or the other sets of columns.

Is my Excel not working right, or am I doing something wrong? Is there a way to code a macro for each set of columns separately and use their own custom sort values as well?

By the way, thank you very much for your help. I could not seem to find this close of an answer anywhere else.
Anuj Sharma    22 Jun 2015, 00:55
The data which comes daily in my mail in excel sheet. i wants this data in my main excel sheet. all compinies data come in my mail in one excel sheet daily. but i wants one compnies data in my main sheet in one page 2nd compnies data in second page with in my main sheet and third compony data in third page please help me its a urgent for me
Barry    27 Mar 2015, 05:41

The following code should do what you want. The macro should be on the codepage for the sheet containing the data. It hasn't been tested.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        With Worksheets("Expenses").Sort
              .SortFields.Add Key:=Range("D:D"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlAscending, _
              .SortFields.Add Key:=Range("C:C"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlDescending, _
              .SortFields.Add Key:=Range("H:H"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlDescending, _
              .SortFields.Add Key:=Range("F:F"), _
                   SortOn:=xlSortOnValues, _
                   Order:=xlDescending, _
              .SetRange Range("A:K")
              .Header = xlYes
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
          End With
    End If
End Sub

Note: this macro will run even if the value in Column A is changed to the same value i.e. the value doesn't actually change.
Nicolette    26 Mar 2015, 15:56
Hi Barry,

I am having some trouble making this work for my circumstances. I have a huge data table that I'll need sorted automatically by changing anything in column A. The headers are A1:K1, and the data I need sorted automatically, with anything change to column A, is range A2:K1120.

But I'm sorting based on 4 different criteria:
1. Column D titled "Risk Category" - sorted on values, order is a series of risk category titles
2. Column C titled "Credit Line" - sorted on values, largest to smallest
3. Column H titled "YTD Outages" - sorted on values, largest to smallest
4. Column F titled "Payscore" - sorted on values, largest to smallest

How do I manipulate the code you've provided us to accommodate advanced sorting such as this, that is triggered by any change to column A?

Barry    06 Mar 2015, 06:56

I am not clear what precisely you want TBH.

You can just select a single cell in your column of data and then just sort that column. Excel will expand the range to be sorted to the whole table (bounded by empty row and empty column) and make a guess if there is a header row or not.Your data is sorted in date order (assuming dates are in a recognised Excel date format, and so everything is in blocks of data for each month. This won't give you any totals though.

To get totals you could use a pivot table. Pivot tables don't automatically update themselves; this would have to be done manually or have a macro do this for you.
Barry    06 Mar 2015, 06:46

In the VBA code above you can specify the range to be sorted in the line after the "IF" statement. For example:

Range("A12:A18").Sort - however the range will not expand and in particular any related data in the adjacent columns will not be sorted with this range. Using Range("12:18").Sort will select all the rows.

As you are only sorting a selection of the data the chances are that it doesn't include the header, in which case the parameter Header:= xlYes needs to be xlNo.

You will have to have a Sort statement for each worksheet, and specify the range for each sheet. These sort routine could be all in one subroutine which when run would sort every sheet. Depending upon the amount of data this could take some time, so you consider only sorting data if there is a change on the particular sheet concerned.
Barry    06 Mar 2015, 06:29

On Sheet2 use the macro recorder then sort the data as you would normally do, turn off the macro recorder afterwards.

In the Visual Basic Editor open the codepage for Sheet 1 and create a event subroutine for when this worksheet changes. Copy the code from the macro you created (this will be one of the code modules) in the Worksheet_Change subroutine on the codepage for Sheet1.

Every time there is a change on Sheet1 the subroutine will run and sort the data on Sheet2.

Colleen    05 Mar 2015, 14:11
Would like to know how to sort any data by the dates entered to separate into months. Specifically, enter data into column E on a end of the month basis and would like to update on a monthly basis without keeping track of each total.
Adarsh    30 Dec 2014, 12:43
HI I wonder if you guys could help in following EXCEL data sorting problem.

I am working in Research firm,In one of my recent project i need to sort the data in only in specific portions of column (for Eg A12 to A18) whenever its get updated. i dont want to sort above or below of specified portion of the column and also if I sort it should expand its selections.

I am working with more than 80 sheet and different portion in different worksheet need to be sorted whenever it get updated....
Please help me, I thought of recording macro for each sheet is that work ?
SNZ    16 Dec 2014, 12:20
Hi, I have an excel workbook with two sheets. I enter data on sheet1. On sheet2 I calculate scores based on the data entered on sheet 1. I want to sort sheet2 whenever I enter data on sheet 1. How can I do that. I'm a total newbie at excel macros. Any help would be appreciated.
Igor Torrealbas    28 Oct 2014, 05:20
I often receive unsorted data from a laboratory. My reference for sorting is a column (lets call it A1:A160). I want to sort the new values as for this column automatically )they also are in two different workbooks.

Currently I do it by creating a custom list (with the drawback that it has limited space), then sort by this custom list. But everytime that I have to perform this, my reference list will change, so I have to do the whole process once and again.

Would there be a way to let's say "automatically create a custom list, and sort the values in a specified range as for that custom list when they are entered (pasted)"?

Take into consideration that I have never wrote one line of code, but I really want to learn, and this is a case that to start I think ;)

Thanks in Advance
mark     10 Sep 2014, 10:01
what if i want to sort by two columns
column A first then if data is the same
sort by column C

alireza dodangeh    04 Jul 2014, 02:53
It was a good answer

EvanJ    10 Mar 2014, 13:42
What if the data you are entering is on another worksheet? I've got a workbook where I enter data in Sheet1 (column G) and link that data to Sheet2 (column F). I would like to create a Macro that auto-sorts Sheet2 by column F whenever the data in Sheet1 is updated, but everything I've tried so far has failed. Any advice would be much appreciated.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.