How to Manage Multiple Worksheets in Excel?

Microsoft Office applications are lifesaver for many of us in day-to-day life. Regardless of the education, professional and ethnic background, applications like Excel stand out for decades. Though it is simple to use Excel worksheets to create lists, charts and tables, learning few tips and tricks can save you considerable time. In our earlier article, we have explained about improving productivity in Excel and in this article let us explore tip to manage multiple spreadsheets in Excel workbook.

Working with Multiple Worksheets in Excel

Whenever you open an Excel application, it will open a new workbook with a name like “Book1”. Inside a single workbook, you can have multiple sheets for different purposes. You can refer sheets as spreadsheets or worksheets. People work with multiple sheets to prepare a larger output that has inputs spread across sheets. In this kind of scenario, it is essential to effectively manage multiple worksheets in Excel book to get things done quickly.

How to Manage Multiple Spreadsheets in Excel?

Here are few tips and tricks we think can help you in managing spreadsheets:

Coloring sheets
Hide and unhide sheets
Protect worksheet with password
Quick reference from another sheet
Navigating between sheets
Limit number of sheets
Naming worksheets properly
Copy and move worksheets

1. Coloring Worksheets in Excel

By default, all worksheets in a book have same white background color. When you have multiple worksheets, applying different background colors to sheets can help you to remember the type of content you have on each worksheet. You can also use the sheet colors for various other purposes like keeping all completed task in a green sheet while pending tasks under yellow or red sheet.

When you are inside a workbook, right click on the sheet.
Hover over “Tab Color” and choose the color from the visual color picker tool.

You will see a light background change on the sheet when you working on it. However, when you move to next sheet, you can see the full background color for other sheets. You can remove the applied color by again right clicking and going to “Tab Color > no Color” option.

2. Hide and Unhide Sheets

Probably you already know, you can hide or unhide workbooks from “View > Hide / Unhide” menu. Similar to workbooks, you can also hide or unhide the worksheets to increase the viewability of important sheets on your book.

Right click on the sheet and select “Hide” option to hide a sheet.
Use “Control” key to select multiple sheets one by one. Alternatively, use “Shift” key to select multiple sheets as a block and hide them at once.
Similarly, right click and choose “Unhide” to select the sheet you want to unhide and show.

When you select multiple sheets, Excel will combine them in single group. You can ungroup from the right click menu or click on any sheet tab.

3. Protect Worksheet with Password

When you do not want anyone to change the content on your sheet, the good idea is to protect or lock the content with a password. The person viewing the sheet will not know the restriction unless he or she tries to edit the cell data. Go to “Review” tab and choose “Protect Sheet” option. Excel will show you a pop-up where you can type a password and select the actions user can do with that sheet.

After saving your workbook, if you or anyone try to modify the cell data in a protected cell will be prompted with the below message to enter the password.

Remember not to have sensitive data on your sheet though you protect, someone can simply delete the entire sheet.

4. Quick Reference from Another Sheet

When you lot of numbers, you may do mathematical calculations from cells referenced across sheets. Though you can go and select the cell for referring, the easy way is to use the text reference. Let us say, you have data in cells B2 and B3 of sheet 1. You can use the below formula, if you want to sum up these two cell data and show in a cell of sheet 2.

=Sheet1!B2+Sheet1!B3

You can click on any cell an apply formula with Sheetx!Cellnumber format for cross reference.

5. Navigating Between Sheets

You can remove the pain of navigating across multiple worksheets by using keyboard shortcuts.

Control + PageUp to move in left direction
Control + PageDown move in right direction

6. Limit Number of Sheets

Excel does not restrict the number of sheets in a workbook. It depends on the available memory size on your computer at that point of time. We recommend, you to keep the number of sheets within a limit when working with multiple sheets. Otherwise, you may need to end up in using the small arrows located in lower left corner.

You can still use Control + Page Up/Down keys to navigate to next/previous sheet. Alternatively, you can use the following shortcuts.

Right click on one of the small arrows to select all a sheet from the pop-up.
Control + Click on left arrow to navigate to first sheet.
Control + Click on right arrow to navigate to last sheet.

Note that Excel will remember where you are when closing a workbook. Next time when you open, it will go to the same sheet when you were at the time of closing.

7. Naming Worksheets Properly

Excel by default name the worksheets like Sheet1, Sheet 2, etc. This does not make sense when you have multiple sheets with content. You can double click on a sheet or select “Rename” from the right-click context menu to change the name. Having descriptive names will help you to easily navigate and remember the order.

8. Copy, Move and Delete Multiple Worksheets

You can manage moving, deleting and copying worksheets by right clicking and choosing the appropriate option. Use control or shift keys to select multiple sheets and delete, move or duplicate them at once.

Remember, in order to move you should drag and drop the selected sheet(s) in the required position. Excel will show you single or multiple document icon when you drag. The right click menu only allows you to move the selected sheet(s) to the end. When you want to copy or duplicate the entire worksheet, ensure to select “Create a copy” checkbox under “Move or Copy” pop-up.

How to Manage Multiple Worksheets in Excel?

Working with Multiple Worksheets in Excel