How to sort tabs in excel
Computer software, ms excel

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

Save yourself from having a dozen workbooks you don’t need. By using Excel tabs, you can save space and keep your data sorted, neat 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 the 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.

excel tabs located at bottom of 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 coincide 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…

How to Add a Tab

You can do this several ways:

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

PC Keyboard shortcut: shift+F11

how to add an excel tab

OR

Right click on tab → Insert → Worksheet → OK. 

OR

Home → Insert → Insert Sheet.

Insert an excel 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 change default number of tabs in excel

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 select all tabs in excel

How to Copy Tabs

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

how to copy an excel tab

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 existing tab or after.

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

how to create a copy of an excel tab

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 step 1 and 2 above. In the ‘Move or Copy’ box, you can select where you’d like to move the tab to. 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.’

how to add excel tabs to a separate workbook

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.

tab navigation arrows

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

how to navigate excel tabs

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

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

excel scrollbar

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 tab name, enabling you to retype.

how to rename excel tab

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 hide an excel tab

How to Delete Tabs

Home → Delete → Delete Sheet.

how to delete excel worksheet

OR

Right click on the tab → Delete.

how to delete excel tab

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.

how to add color tabs in excel

Troubleshooting

To view more videos, click here.

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 accidently 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.

copy excel tab to new workbook

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.

show sheet tabs in excel

Protecting Tabs:

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

Right click on the tab → select ‘Protect Sheet’ → create a password and confirm it.

To make changes, right click on tab → select ‘Unprotect Sheet’ and enter password.

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

how to protect an excel tab

please like, share, comment, subscribe

Advertisements

3 thoughts on “HOW TO SORT TABS IN EXCEL – MOVE, COLOUR CODE & MORE”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s