Chapter 2. Managing Workbooks and Worksheets

Some of the more fundamental tasks you’ll undertake in Excel revolve around managing your workbooks and worksheets. A workbook is essentially a container that holds your worksheets. When someone refers to an Excel workbook, they are referring to the entire Excel file. A worksheet is the actual spreadsheet you work in. There can be many worksheets in a workbook—similar to pages in a book.

Excel 2010 includes a new File tab. This tab represents a return to the Excel 2003 look where you had a File menu option that wasn’t a part of Excel 2007, which used a logo similar to the Start Menu icon in Windows Vista and 7. The logo in Excel 2007 caused confusion because most users did not realize it was a menu option. So Microsoft decided to reintroduce the File tab to the Ribbon interface.

The File tab exposes the Backstage view. In the Backstage view, you’ll find every command you need to manage and work with Excel workbooks. The more common tasks found here are opening Excel workbooks, creating new workbooks, and saving workbooks. The Backstage view also provides options to share files, see file information, and access Excel application options.

Excel’s Backstage View

Opening an Excel Workbook

Any time you want to work with an existing Excel workbook, you must open it first. The idea here is simple: You first tell Excel you want to open a file, and then select the file you want, using the Open dialog box.

Click the File tab.

Click the Open command.

Use the Open dialog box to find the file you are trying to open, then double-click the file (or click the Open button).

Excel opens the workbook. Note the file name will be displayed at the top of the workbook.

Closing an Excel Workbook

There may be instances when you want to close the current workbook but still remain working in Excel. In these situations, you’ll find it handy to close only the current workbook. If you have been working in a workbook and try to close it, Excel asks you whether you want to save the workbook before it closes.

Click the Close button (represented by the X) in the document window.

If you’ve made changes to the workbook, Excel prompts you to save your changes. Click Yes if you want to save any changes you have made, or click No if you do not. Excel responds accordingly, then closes the workbook.

Saving a Workbook

You should regularly save your workbooks as you work in them so you don’t lose data. You can save a workbook as many times as you like, so saving often is a good habit to get into. You can also save your workbook under another name if you want to keep track of multiple versions of your workbook.

Click the File tab.

Click the Save As command.

Use the Save As dialog box to locate the directory where you want to store your workbook and then enter the name for the file.

Click the Save button, and Excel saves your workbook.

Switching Between Open Workbooks

You can have multiple workbooks open at the same time and switch between them whenever you want. For example, you might be using two different workbooks to create one report. You can use the Windows Taskbar to quickly move from one workbook to another.

With two or more workbooks already open, click the button on the Windows Taskbar that represents one of your open workbooks (in this case, the Excel2010Charts.xlsx button). This workbook becomes the active workbook.

Click a different workbook button on the Windows Taskbar (for instance, the Excel2010DataLists.xlsx button). This workbook becomes the active workbook.

Switching Between Worksheets

Similar to switching between multiple workbooks, you can switch between the separate worksheets within a single workbook. This allows you to review and edit data stored on separate worksheets within a single workbook.

Click a worksheet tab (in this example, the Sheet1 tab) to see the contents in that worksheet.

Click a different worksheet tab (here, the Sheet2 tab) to see the contents of that worksheet.

Viewing Multiple Workbooks

Instead of constantly switching between workbooks, you can view multiple workbooks onscreen in Excel, and resize their windows as needed. This comes in pretty handy if you are comparing two or more workbooks, or working on multiple workbooks at the same time.

Click the View tab and then select the Arrange All command.

Select how you want the windows arranged (for example, Horizontal).

Click the OK button.

Multiple workbooks are displayed simultaneously. Click on the title bar or in the body of the workbook you want to work in to make it the active worksheet.

Inserting and Deleting Worksheets

By default, Excel automatically provides you with three worksheets when a new workbook is created. As you work with Excel, you will likely find that you’ll need to insert additional worksheets. Conversely, sometimes you’ll need to delete ones you no longer use.

Right-click on the worksheet tab before which you want a new worksheet placed, and then select Insert.

Double-click on the Worksheet icon. A new worksheet appears.

To delete a worksheet, right-click on the worksheet tab you want deleted and then select Delete.

Click the Delete button to confirm that you want the worksheet deleted.

Renaming Worksheets

You should always endeavor to use descriptive names for the worksheets in your Excel file. If you have a workbook that uses multiple worksheets, providing more descriptive names for your worksheets makes working within your workbook far easier than it would be if all your sheets were named Sheet1, Sheet2, and so on.

Double-click the worksheet tab you want to rename. Alternatively, you can right-click the worksheet name and select Rename.

Type the new name and press the Enter key. Excel displays the new name on the worksheet tab.

Coloring Worksheet Tabs

As you add worksheets to your workbook, you will find an increasing need to organize your worksheets. To help with this, Excel allows you to color your worksheet tabs. If you want to indicate something specific about a worksheet tab—for example, if a worksheet contains preliminary data—you can assign it a tab color, such as red.

Right-click on the worksheet tab you want to color.

Click the Tab Color menu option.

Select your preferred color. The color is immediately applied to your worksheet tab.

Moving Worksheets Within a Workbook

When Excel inserts a new worksheet, it always places it in front of the currently selected worksheet. You can, of course, move your worksheet tabs as you start organizing your -workbook.

Right-click the worksheet tab that you want to move.

Select Move or Copy from the shortcut menu. The Move or Copy dialog box opens.

In the list of worksheets, click the name of the worksheet in front of which you want the selected sheet to be moved.

Click the OK button to move the worksheet.

Copying Worksheets Between Workbooks

You might find that a worksheet used in one workbook would be of use in another workbook. Instead of recreating the worksheet, you can simply create a copy of the worksheet in the other workbook.

Right-click the worksheet tab that you want to copy.

Select Move or Copy from the shortcut menu. The Move or Copy dialog box opens.

Click the down arrow next to the To Book field and choose the workbook to which you want the target worksheet copied. Then click the Create a Copy check box.

Click the OK button. At this point, Excel copies the worksheet as directed.

Password Protect a Workbook

You may have instances where your Excel workbooks are so sensitive that only certain users are authorized to see them. In these cases, you can force your workbook to require a password to even open.

With your workbook open, click the File tab and select Save As.

In the Save As dialog box, click the Tools dropdown and select General Options.

Enter an appropriate password in the Password to Open input box, then click the OK button.

Enter the same password in the Confirm Password dialog box, and then press the OK button.

Click the Save button. At this point, your worksheet is protected.

Protecting a Worksheet

When you share files with other users, you might find it useful to protect your worksheets. When your worksheet is protected, you essentially restrict the capability to take certain actions without a password—actions such as inserting or deleting rows and cell data.

Go to the Review tab and select the Protect Sheet command.

Uncheck any action you want to prevent other users from performing, then enter a password in the Password text box.

Click the OK button.

Type the same password in the Confirm Password dialog box, then press the OK button. At this point, your worksheet is protected.

To unprotect a worksheet, go to the Review tab and select the Unprotect Sheet command.

Enter the password.

Click the OK button. At this point, the worksheet is unprotected.