Organize Sheets in Excel (Sort, Group, Ungroup Worksheets)

Download Practice Workbook

Example 1 – Apply Shortcuts to Highlight Sheet

Select range B5:D9 >> press Alt+H+L+N.

In the pop-up window, choose Use a formula as Rule Type >> insert the below formula >> click on Format.

Go to Fill >> choose the desired color >> click on OK >> click OK again in New Formatting Rule.

The cells will be highlighted.

Example 2 – Change Sheet Tab Color in Excel

Right-click on the intended sheet tab >> click on Tab Color >> choose the desired color.

How to Sort Worksheets Alphabetically

To sort worksheets by name or alphabetically, press Alt+F11 to Open VBE.

Choose Insert >> click on Module >> enter the code below and Run.

Sub SortTabsAlphabetically()

Dim ws As Worksheet
Dim i As Integer, j As Integer
Dim temp As Worksheet

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count - 1

For j = i + 1 To Worksheets.Count

If UCase(Worksheets(j).Name) < UCase(Worksheets(i).Name) Then

Worksheets(j).Move before:=Worksheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True

ThisWorkbook.Sheets("Sort Sheet Alphabetically").Activate

End Sub

The worksheets will get sorted alphabetically.

How to Group Worksheets in Excel

Example 1 – Group Selected Sheets

Hold down the Ctrl key and select the intended sheets.

Click on Insert >> choose Module >> add the code below and Run.

Sub GroupSelectedSheets()

Dim ws As Worksheet

If ActiveWindow.SelectedSheets.Count < 2 Then

MsgBox "Please select two or more sheets to group.", vbInformation

Exit Sub

End If

For Each ws In ActiveWindow.SelectedSheets

ws.Tab.Color = RGB(0, 0, 255)

Next ws

End Sub

The selected sheets will be grouped.

Example 2 – Group All Sheets

Open VBE >> select Insert >> click on Module >> insert the following code and Run.

Sub GroupAllSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Select False

Next ws

For Each ws In ActiveWindow.SelectedSheets

ws.Tab.Color = RGB(0, 0, 255)

Next ws

End Sub

All the sheets will be grouped.

How to Ungroup Worksheets in Excel

Right-click on grouped sheets >> click on Tab Color >> choose No Color.

Right-click on group sheets again >> Choose Ungroup Sheets.

How to Delete Sheets in Excel

Example 1 – Delete a Sheet

Right-click on the intended sheet >> choose Delete.

Example 2 – Delete Selected Sheets

Right-click on selected sheets >> choose Delete.

Things to Remember

• Make sure there are no hidden sheets when grouping all sheets.
• Apply the No-Color theme to grouped sheets before ungrouping.

Organize Sheets in Excel: Knowledge Hub

• How to Arrange Excel Sheet in Ascending Order
• How to Sort Excel Sheet by Name 
• How to Reverse the Order of Worksheets in Excel 

<< Go Back to Excel Worksheets | Learn Excel