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.

Format Cells Window

The cells will be highlighted.

Output of applying shortcuts to organize sheets by highlighting cells in Excel

Example 2 – Change Sheet Tab Color in Excel

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

organize sheets by Changing Sheet Tab Color in Excel

How to Sort Worksheets Alphabetically

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

Before Sorting Alphabetically

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

Code to organize sheets in alphabetical order

The worksheets will get sorted alphabetically.

Organize sheets by Sorting Alphabetically in Excel

How to Group Worksheets in Excel

Example 1 – Group Selected Sheets

Hold down the Ctrl key and select the intended sheets.

Select intended sheets to group

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

Run VBA code to Group Selected Sheets

The selected sheets will be grouped.

Output of grouping selected sheets

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

Run VBA code to group all sheets

All the sheets will be grouped.

Output of grouping all sheets

How to Ungroup Worksheets in Excel

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

Apply no color to sheet tab

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

Ungroup Sheets

How to Delete Sheets in Excel

Example 1 – Delete a Sheet

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

Delete a sheet

Example 2 – Delete Selected Sheets

Right-click on selected sheets >> choose Delete.

Delete selected sheets

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

<< Go Back to Excel Worksheets | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!