How to quickly change order of tabs to your need in Excel?

When working with complex Excel workbooks, especially those containing dozens or even hundreds of worksheets, managing and navigating through the tabs efficiently becomes essential for productivity. The default order of worksheet tabs may not always suit the workflow or presentation needs, so being able to reorder tabs as desired helps keep related data together and streamlines editing or review tasks. For example, you may want to group summary and data entry sheets at the front of the workbook, or arrange monthly reports in chronological order. The screenshots below illustrate a typical situation where rearranging sheet tabs improves clarity and workflow efficiency. This article introduces several quick and practical methods to change the order of worksheet tabs within a workbook.

Change order of tabs by dragging

Change order of tabs using Navigation pane

Change order of tabs by Sort Sheets function

Change order of tabs using VBA macro (custom order by name or rule)

Change order of tabs by dragging

Excel provides a straightforward way to move tabs: you can manually drag a sheet tab to any position along the workbook's tab bar. This method is intuitive, making it suitable for quick adjustments or when only a few worksheets need to be moved.

To reorder a sheet, click on the tab with your left mouse button and hold it down. Drag the selected tab left or right along the sheet bar to the new position you want, indicated by a small black triangle. Once the tab is located where you would like, release the mouse button to drop it into place.

You can repeat this method for each sheet you want to reposition. This is best for smaller workbooks or when you need to quickly move only a handful of sheets. However, for larger workbooks or complex arrangements, it may become time-consuming and difficult to manage accuracy, especially if tab names are hidden off-screen or require careful placement.

Change order of tabs using Navigation pane of Kutools

In workbooks with numerous sheets, some tab names might not be immediately visible due to limited horizontal space. This can make manual rearrangement in the status bar cumbersome. To address this, Kutools for Excel offers a Navigation pane, allowing you to see all worksheet names in a vertically scrollable list. This approach not only helps when many tabs are present, but also provides a clearer overview, making selection and movement easier and more accurate.

1. Click Kutools > Navigation to open the Navigation pane. Inside the pane, click the Workbook & Sheet button to switch to the Workbook & Sheet view. See the screenshot below:

Once opened, you'll see that all worksheet names are listed vertically in the pane for easy access, even if they were previously hidden in the tab bar due to space constraints:

2. To reorder, simply click and hold the worksheet name, then drag it to the desired position in the list. When the highlight shifts to the right spot, release the mouse button to confirm the change. Each movement instantly updates the sheet order within the workbook itself, maintaining easy consistency. See the demonstration below:

You can repeat this process for as many tabs as needed. The Navigation pane is particularly suitable for large workbooks or users who frequently modify tab order, and is less prone to misplacement errors compared to using the status bar.

Change order of tabs by Sort Sheets function

When you need to reorder a large number of worksheets—particularly in extensive reporting workbooks—manual dragging becomes inefficient and may increase the risk of mistakes. The Sort Sheets function in Kutools for Excel is designed for these situations, allowing you to reorganize sheet tabs automatically based on different criteria such as alphabetical order, number/alphabet mix, tab color, or even reverse the sheet order. This eliminates repetitive dragging and helps ensure consistent, error-free arrangement for organizational or presentation needs.

1. Go to Kutools Plus > Worksheet > Sort Sheets. The Sort Sheets dialog box will appear as shown:

2. In the Sort Sheets dialog, select your desired sorting method from the available list (alphabetically, by color, reverse, etc.). The New sheet tabs order pane will instantly preview the result, giving you an immediate look at the prospective tab arrangement before applying changes:

3. When satisfied, click OK to apply the new sheet order to your workbook. The changes occur instantly and can significantly reduce the time needed for workbook organization.

Change order of tabs using VBA macro (custom order by name or rule)

For scenarios where worksheet tabs need to follow a specific, custom-defined order that cannot be accomplished by alphabetical, color-based, or simple manual movement—such as matching a report sequence, conforming to a predefined workflow, or following project-specific naming convention—a VBA macro provides an effective and flexible solution.

With VBA, you can automate the reordering of sheets precisely to match a custom list of names or programmatically set order based on your own rules. This is especially handy in large files or standardized templates where tab position is critical to structure and usability.

1. Open the VBA editor by clicking Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module and copy the following code into the new module:

Sub ReorderWorksheetsCustom()
    Dim wsNames As Variant
    Dim i As Integer
    Dim ws As Worksheet
    
    On Error Resume Next
    
    ' Define your desired worksheet name order here (case-sensitive)
    wsNames = Array("Summary", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Data")
    
    Application.ScreenUpdating = False
    
    For i = UBound(wsNames) To 0 Step -1
        Set ws = Worksheets(wsNames(i))
        If Not ws Is Nothing Then
            ws.Move Before:=Worksheets(1)
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

2. To run the code, click the Run button in the toolbar. The VBA macro will reorder the worksheets in your workbook according to the sequence listed in the wsNames array, placing each in order from rightmost to leftmost (i.e., the first name in the array will appear as the first tab in the workbook).

Related Articles:

• How to quickly batch import multiple csv/text/xml files in Excel?
• How to quickly count the first instance only of values in Excel?
• How to quickly find the largest value but smaller than X in Excel?