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

Download Practice Workbook

Organize Sheets.xlsm

Example 1 – Apply Shortcuts to Highlight Sheet

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

Select intended cells to apply shortcut

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

=D5<6000

New Formatting Rule window

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