HOW TO SORT TABS IN EXCEL – MOVE, COLOUR CODE & MORE

Save yourself from having a dozen workbooks you don’t need. Learn how to sort tabs in Excel and you will save space and keep your data organised and protected. Hide sensitive information, colour code worksheets and much more.

Sheet Tab in Excel

Microsoft Excel uses workbooks that are made up of worksheets. These are handy for keeping data separate, but still accessible within one workbook.

These worksheets display under individual sheet tabs. The tabs are located at the bottom of your workbook. As you click on each tab, it will take you to another worksheet.

How Many Tabs in an Excel Spreadsheet?

Currently, a new workbook automatically opens with one. You can add more if you wish. The number of tabs coincides with the number of worksheets. The total number of worksheets you can have is only limited by available memory.

Can You Sort Tabs in Excel?

Yes – you will learn how to sort tabs in Excel in several ways:

• Add and copy tabs
• Navigate and select multiple tabs
• Change or Move tabs
• Separate tabs to other worksheets
• Hide and delete tabs
• Rename tabs
• Recolour tabs
• Troubleshooting (retrieve lost tabs and protect tabs).

Let’s get straight into it…

Sort Tabs in Excel – How to Add a Tab

You can do this in several ways:

At the bottom of the worksheet, click the ‘+’ symbol beside the tab.

PC Keyboard shortcut: shift+F11

OR

Right-click on tab → Insert → Worksheet → Ok. 

OR

Home → Insert → Insert Sheet.

If you want more than one tab every time you create a new workbook, you can change the default setting:

File → Options → in the section ‘Creating New Workbooks’ change the number of sheets to be included.

How to Select Tabs

This is handy if you want to edit data on numerous worksheets at once.

Multiple Tabs:

Hold down the ‘ctrl’ key and click on the specific tabs you want to edit or copy.

All Tabs:

Hold down the shift key and click on all the tabs.

OR

Right-click on a tab → Select All Sheets.

To deselect, choose ‘Ungroup Sheets’ or simply click on a singular tab.

How to Copy Tabs

1. Right-click on the first tab → Move or Copy.

2. Here you can select which workbook to add it to (it will highlight your existing workbook by default). You can also select where you want to position it: before the existing tab or after.

3. Tick the box ‘Create a copy’ → Ok.

PC Keyboard shortcut: left click on tab → hold down the ctrl key and drag left.

For every tab simultaneously: hold down the shift key and click on all the tabs → hold down the ctrl key and drag left.

For selected tabs only: hold down the ctrl key and click on the tabs you want to copy → hold down the ctrl key again and drag left.

How to Move a Tab

Follow steps 1 and 2 above. In the ‘Move or Copy’ box, you can select where you’d like to move the tab. E.g. before existing tabs, in the middle or move to the end.

OR

Left-click on a tab and drag.

How to Separate Tabs

You can separate tabs to other workbooks:

Follow steps 1 and 2 for copying tabs → in the ‘Move or Copy’ box, press the drop-down arrow → select any other workbooks you have open or choose ‘new book.’

Navigating Tabs

Once you have 10 or more tabs created, navigation arrows will appear in the bottom left corner. This allows you to scroll back and forth between the tabs.

PC Keyboard shortcut: ctrl+left-click to go to the first/end tab.

If you don’t want to scroll, you can bring up a temporary list of all your tabs. Right-click on the navigation arrows.

Or you can narrow the scroll bar to view more tabs:

1. Place your cursor on the dotted line beside the scroll bar.
2. When it turns into a cross, left-click and drag the bar to the right.

Otherwise, you can purchase Excel add-ins like Kutools for Excel that create vertical lists in the navigation pane, or hyperlinked indexes.

How to Rename a Tab

Double click on the tab name.

OR

Right-click on the tab → Rename → this will highlight the tab name, enabling you to retype.

PC Keyboard shortcut: alt+H+O+R

How to Hide Tabs

Right-click on the tab → Hide.

To unhide, right-click on the tab → Unhide → select from a list of hidden tabs.

How to Delete Tabs

Home → Delete → Delete Sheet.

OR

Right-click on the tab → Delete.

How to Colour Tabs

Adding/changing colour to the tabs is easy. Microsoft Excel has 16 million colours in a workbook, so you have plenty to choose from.

Right-click on the tab → Tab Colour → select a theme colour or ‘More Colours’ to create a custom colour.

Sort Tabs in Excel – Troubleshooting

Retrieving Lost Tabs

There are several different methods you can try.

Method 1

Check the scroll bar or hidden tabs.

Method 2

In your current document, use ‘Save As’ under another name. Then copy the tab from the original document.

For example:

1. If the ‘Sheet 2’ tab is deleted in ‘Workbook 1’, save the document as ‘Workbook 2.’
2. Reopen original document ‘Workbook 1.’
3. Copy the ‘Sheet 2’ tab that was accidentally deleted.
4. In the ‘Move or Copy’ box, select ‘Workbook 2’ from the drop-down list.
5. Tick the ‘Create a Copy’ box.
6. ‘Workbook 2’ will now have all your original tabs.

Method 3

File → Info → Manage Workbook → Recover Unsaved Workbooks → look for the most recent version, open and save a copy.

Method 4

File → Options → Advanced → under display options, make sure ‘Show Sheet Tabs’ is ticked.

Protecting Tabs:

It’s possible to protect individual worksheets/tabs within a workbook, to stop people from making any changes.

Right-click on the tab → Protect Sheet → create a password and confirm it.

To make changes, right-click on tab → Unprotect Sheet → enter password.

Note: passwords are case sensitive and can’t be recovered if you lose them.

Related Articles

How to Create Excel Drop-down List, Autofill & Flash Fill

How to freeze the top row in Excel & Split Window

How to Find Duplicates in Excel

How to Protect Cells in Excel