Site icon Professor Excel

Sort Excel Sheets: 3 Simple Methods (+Download)

Sort Worksheets in your Excel Workbook: 3 Simple Methods.

Sort Worksheets in your Excel Workbook: 3 Simple Methods.

Especially for large Excel workbooks with many sheets it’s crucial to organize the content. You should—for example—sort worksheets in a logic sequence. One way to sort sheets would be in an alphabetical order. This article introduces three simple methods for sorting Excel worksheets.

Method 1. Sort sheets manually

Often, sorting worksheets manually is more efficient than any large VBA/ add-in solution.

Yes, it sounds stupid. But there is no built-in function in Excel for sorting worksheets. Because of that, in many cases it’s really the fastest method to sort sheets per drag-and-drop with the mouse. You can also use the “move worksheets” function (right-click on the sheet name), but according to my experience this function takes much longer than just using dragging the sheets around using the mouse.

Maybe a small piece of advice at this point. If you press and hold the Ctrl key on the keyboard while dragging worksheets around, you create a copy of the worksheet instead of moving it.

Method 2. Use a simple VBA macro

Sort worksheets in Excel using a VBA macro.

You’ve probably skipped the first method because you have too many worksheets or you are looking for a recurring solution. Again, Excel doesn’t offer a built-in function for sorting worksheets. So you could just copy and paste one of the following VBA macros. Follow these steps:

  1. Insert a new VBA module,
  2. paste one of the two source codes snippets
  3. and press start.

If you need assistance with inserting a VBA macro, please refer to this article.

Macro 1. Sort sheets alphabetically in ascending order.

Sub sortAscending()
    Dim i, n, k As Double

    'Count the number of worksheets and store the number in variable "n"
    n = Application.Sheets.Count
    
    'Do the following look for each worksheet again
    For i = 1 To n
    
        'Loop through all worksheets until the second last one (later you use the .move after function)
        For k = 1 To n - 1
            
            'If the name is larger than the following worksheet, change the sequence of these two worksheets.
            'In order to enable a proper comparison, change all characters to lower case (UCase = Upper case works
            'the same way.
            If LCase(Sheets(k).Name) > LCase(Sheets(k + 1).Name) Then Sheets(k).Move after:=Sheets(k + 1)
        Next
    Next

End Sub

Macro 2. Sort sheets alphabetically in descending order.

Sub sortDescending()
    Dim i, n, k As Double

    'Count the number of worksheets and store the number in variable "n"
    n = Application.Sheets.Count
    
    'Do the following loop for each worksheet again
    For i = 1 To n
    
        'Loop through all worksheets until the second last one (later you use the .move after function)
        For k = 1 To n - 1
            
            'If the name is smaller than the following worksheet, change the sequence of these two worksheets.
            'In order to enable a proper comparison, change all characters to lower case (UCase = Upper case works
            'the same way.
            If LCase(Sheets(k).Name) < LCase(Sheets(k + 1).Name) Then Sheets(k).Move after:=Sheets(k + 1)
        Next
    Next

End Sub

Please feel free to download the VBA code in an Excel file. Click here for starting the download.

Method 3. Use an Excel add-in to sort sheets

Sort worksheets using the Excel add-in “Professor Excel Tools“.

You don’t want to struggle with a VBA macro? Or you need further options for fine-tuning the sorting? There are some Excel add-ins available for sorting worksheets. Also our Excel add-in “Professor Excel Tools” provides such function.

Follow these steps:

  1. Select the worksheets you want to sort.
  2. Click on “Sort Sheets” on the Professor Excel ribbon.
  3. Fine-tune the options. For example sort all worksheets or just the selected worksheets. Or group them by tab color. Press “Start”.

That’s it.

You can try “Professor Excel Tools” for free. Click here and the download start immediately.


This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


Exit mobile version