How to Automatically Sort Data Alphabetically Using an Excel Macro

If you manage a dynamic employee directory or a growing inventory list in Microsoft Excel, manually clicking the “Sort A to Z” button every time you add a new entry is an unnecessary chore. By writing a very brief piece of Visual Basic for Applications (VBA) code, you can create a macro that automatically sorts your data alphabetically the exact moment you type a new word into your spreadsheet.

How to Open the VBA Editor

We need to attach our sorting script directly to the specific worksheet we are working on so it runs continuously in the background.

1. Open your Microsoft Excel workbook.
2. Look at the bottom of the screen where your worksheet tabs are located (e.g., Sheet1).
3. Right-click on the tab of the sheet you want to automate.
4. Select View Code from the context menu. This will open the Microsoft Visual Basic for Applications window.

How to Write the Sorting Macro

In the large white text area that appears, you need to paste a script that tells Excel to monitor column A for changes, and if a change occurs, to re-sort the entire table.

1. Look at the two dropdown menus at the top of the code window. Change the left dropdown from (General) to Worksheet. The right dropdown should automatically change to SelectionChange.
2. Change the right dropdown from SelectionChange to Change. (You can delete the empty SelectionChange block that appeared).
3. Paste the following code into the Worksheet_Change block:

   Private Sub Worksheet_Change(ByVal Target As Range)
       On Error Resume Next
       ' Check if the change happened in Column A
       If Not Intersect(Target, Range("A:A")) Is Nothing Then
           Application.EnableEvents = False
           
           ' Sort the data in columns A to D based on Column A
           Range("A1:D100").Sort Key1:=Range("A2"), _
           Order1:=xlAscending, Header:=xlYes
           
           Application.EnableEvents = True
       End If
   End Sub

4. Close the VBA window by clicking the red X in the top right corner.

Note: You must adjust the Range("A1:D100") in the code to match the actual size of your data table so you do not accidentally leave columns behind during the sort.

How to Save the Automated Workbook

Because your workbook now contains active code, you cannot save it as a standard .xlsx file.

Click File > Save As, and change the file format dropdown to Excel Macro-Enabled Workbook (*.xlsm). From now on, whenever you type a new name or item into Column A and press Enter, your entire table will instantly rearrange itself into perfect alphabetical order.