5 Steps to Arrange Sheets in Excel 2013 Ascending Order

Excel is not just about numbers and calculations; it's also a powerful tool for organizing data. If you find yourself working with numerous sheets in an Excel workbook, knowing how to arrange them in order can significantly boost your productivity. Here, we will explore five straightforward steps to arrange sheets in Excel 2013 in ascending order. This skill will help you manage your data more effectively and find information quickly.

Step 1: Understand Your Sheet Names

Before you dive into rearranging sheets, it’s crucial to know exactly what you’re dealing with. Each sheet in your workbook should have a unique and identifiable name:

• Verify Sheet Names: Check each sheet’s name by looking at the tabs at the bottom of the Excel window.
• Ensure Uniqueness: Each sheet name should be unique for clarity.

Step 2: Group Sheets for Reorganization

If your sheets are scattered, grouping them first can make the sorting process smoother:

• Select Sheets: Click the first sheet you want to group while holding the Ctrl key, then click on subsequent sheets you want to move together.

Step 3: Use the Name Box to Sort Sheets

The Name Box, located to the left of the formula bar, can be quite handy for sorting:

• View Sheet Names: Click on the Name Box to see a list of sheet names.
• Reorder: By selecting sheet names from this list, you can easily drag them into the desired ascending order.

📌 Note: This method can be less intuitive if you have many sheets, so consider other methods if you find it cumbersome.

Step 4: Use VBA for Automatic Sorting

For an automated approach, Visual Basic for Applications (VBA) can be used:

• Open VBA: Press Alt + F11 to open the VBA editor.
• Insert Module: Right-click on any of the objects in the Project Explorer, select Insert, then Module.
• Copy and Paste VBA Code: Here’s the code to sort sheets alphabetically:

Sub SortSheets()
    Dim i As Integer, j As Integer
    Dim SheetNames() As String
    Dim SheetNamesSorted() As String
    Dim ws As Worksheet

' Populate the array with sheet names
ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count)
ReDim SheetNamesSorted(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To ActiveWorkbook.Sheets.Count
    SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i

' Sort the array
For i = LBound(SheetNamesSorted) To UBound(SheetNamesSorted)
    SheetNamesSorted(i) = SheetNames(i)
Next i
Call QuickSort(SheetNamesSorted, LBound(SheetNamesSorted), UBound(SheetNamesSorted))

' Rename sheets to match the sorted order
For i = 1 To ActiveWorkbook.Sheets.Count
    ActiveWorkbook.Sheets(i).Name = SheetNamesSorted(i)
Next i

' Reorder sheets based on their new names
For i = 1 To ActiveWorkbook.Sheets.Count
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name = SheetNamesSorted(i) Then
            ws.Move Before:=Sheets(i)
            Exit For
        End If
    Next ws
Next i


End Sub

Sub QuickSort(vArray As Variant, inLow As Long, inHigh As Long)
    Dim pivot   As Variant
    Dim tmpSwap As Variant
    Dim tmpLow  As Long
    Dim tmpHigh As Long

tmpLow = inLow
tmpHigh = inHigh
pivot = vArray((inLow + inHigh) \ 2)
While (tmpLow <= tmpHigh)
    While (vArray(tmpLow) < pivot And tmpLow < inHigh)
        tmpLow = tmpLow + 1
    Wend

    While (pivot < vArray(tmpHigh) And tmpHigh > inLow)
        tmpHigh = tmpHigh - 1
    Wend

    If (tmpLow <= tmpHigh) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHigh)
        vArray(tmpHigh) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHigh = tmpHigh - 1
    End If
Wend

If (inLow < tmpHigh) Then QuickSort vArray, inLow, tmpHigh
If (tmpLow < inHigh) Then QuickSort vArray, tmpLow, inHigh


End Sub
            

• Run the Macro: From the VBA editor, run the SortSheets macro.

Step 5: Manually Arrange Sheets

Sometimes, simple manual sorting might be the fastest option:

• Drag and Drop: Click on the sheet tab you want to move, and drag it to the desired location in the sheet tab list.

By following these five steps, you can efficiently arrange your Excel sheets in ascending order, streamlining your workflow. Remember, organizing your workbook not only makes it easier to find information but also helps in managing large sets of data more effectively. Efficient sheet arrangement can save you time and reduce errors in your data analysis or reporting tasks.

Can I sort sheets in descending order in Excel 2013?

+

Yes, you can modify the VBA code to sort sheets in descending order by changing the sorting criteria.

Will VBA sorting affect existing data?

+

No, the VBA script only reorders the sheet tabs, not affecting the data within the sheets.

What if I need to sort sheets by a date in the name?

+

You would need to modify the VBA code to handle date sorting, which might require additional programming.

Is there a way to sort sheets without VBA?

+

Yes, you can manually rearrange sheets by dragging and dropping them into the desired order.

Can I undo the VBA sorting?

+

You cannot automatically undo the sorting as VBA does not have an “undo” feature for script actions, but you can manually revert the changes or run another VBA script to reverse the sorting.