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.

Automatically Sorting as You Enter Information

Written by Allen Wyatt (last updated August 14, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003


2

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, _
      Orientation:=xlTopToBottom
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, _
          Orientation:=xlTopToBottom
    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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Strikethrough Shortcut Key

One common type of formatting is strikethrough, which is normally applied from the Font dialog box. There is no built-in ...

Discover More

Spell Checking Only Checking Grammar

Word has a built-in spelling and grammar checker that can help reduce errors in your prose. It may be a bit confusing if ...

Discover More

Creating Dependent Cells

Making the values in two cells mirror each other may seem like a desirable thing to do. It can be done, as discussed in ...

Discover More

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!

More ExcelTips (menu)

Sorting by Colors

Need to sort your data based on the color of the cell? Excel doesn't include this capability, but you can still do the ...

Discover More

Sorting Data Containing Merged Cells

When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause ...

Discover More

Creating a Sort Order

Excel is very flexible in how it can sort your data. You can even create your own custom sort order that is helpful when ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 4?

2024-10-23 13:14:59

Michael

Matilda,

You need to use a PivotTable. Hopefully this long YouTube vid link works for you, otherwise just do a new search:-

https://www.google.com/search?q=pivot+table+different+sorts+in+same+column&oq=pivot+table+different+sorts+in+same+column+&gs_lcrp=EgZjaHJvbWUyBggAEEUYOTIHCAEQIRigATIHCAIQIRigATIHCAMQIRigATIHCAQQIRigATIHCAUQIRifBTIHCAYQIRifBTIHCAcQIRifBTIHCAgQIRifBTIHCAkQIRifBdIBCjMwOTg3ajBqMTWoAgiwAgE&sourceid=chrome&ie=UTF-8#fpstate=ive&vld=cid:badbed3f,vid:sDhjmB8a-x0,st:9

Michael


2024-10-16 11:49:06

Matilda

Hi,

I have used the above code to auto-sort all of column C but I need it so it sorts within the 4 'groupings' in column B rather than the whole colunm.

So column B has the types, BP from B8 - B82, SPON from B83 - B90, TP from B91 - B165 and TPAD from B166 - B175. We will be entering the company names into column C which will need sorting. So I need a code or codes that it only auto-sorts within their ranges.

I hope I'm making sense.
Matilda


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.