Sheets & Books

The typical Excel spreadsheet may not be what you think. The thoughtful folks in MicroSoft came up with the idea that some Excel developers might wish to have several spreadsheets in a single document. They developed the idea that the document you first open is really like a "book" with many pages. Each page is accessible through "tabs" at the bottom of the document. They even let you do arithmetic across the different pages or sheets!

Before we investigate how this is done, you might ask "Why would anyone want to have multiple spreadsheets in a single document?" --- A great question.

Suppose you were a manager and had under your control several different budgets organized against the accounting procedures of your company. Technically, as you spend money, you will want to enter the financial actions with the categories that are allowed. For instance, you may have accounts like Personel, Travel, Equipment, Supplies, etc. Technically, all of your revenue is in a single pot. However, you assign or budget certain amounts to each category and you with to keep up with your categories using different spreadsheets. Your company's cash-flow is a function of what is happening across all of these categories. Excel's "book with sheets" feature may be just what you need.

A school teacher may have different classes. It may be convenient to keep all of the grades in a single document called My Grades and have each of the classes kept on different sheets in the book. When you are computing grades at the end of a grading period you can open one document and quickly switch between classes rather than have to load new documents.

Let's locate the sheets on this small version of an Excel document.

You will notice the default number of sheets is 3 and they are labeled Sheet1, Sheet2, and Sheet3. The "active" sheet is Sheet1 in this illustration.

In order to view the contents of Sheet2, you only have to click on the Sheet2 tab.

The "click on the sheet tab" is the way you can quickly switch from one sheet to another. The Excel developers made each sheet the same size and the Excel procedures are identical regardless of which sheet is currently in view.

You have several options in working with sheets. The list of options available is accessed by right-clicking on a sheet tab.

You may need to add more sheets or remove sheets.

You certainly will want to change the name from the series Sheet1, Sheet2, Sheet3,... to a more descriptive tag like Period1, Period2, Period3,...for the teacher with grades.

And finally, the order of the sheets may be important. You have full control over the arrangement of the sheets from left to right. Lets examine a few of these options.

Insert a new sheet

If you right-click on a sheet and select Insert, you will be allowed to select the Worksheet option to add a new sheet.

When you click on OK, the Excel book will have a new sheet added to the current list of sheets.

Move or Copy

This part is a little tricky. Be sure to keep up with which sheet you right-clicked!

This panel will let you move the sheet relative to the other sheets in your book.

While we have rarely used the Create a Copy feature, this is where you could make a duplicate copy of the selected sheet. That certainly beats copy/paste.

Rename a Sheet

We find this feature to be very useful. If you have 10 sheets in your book, the labels Sheet1, Sheet2, ...., Sheet10 are not very useful in finding a particular sheet. Excel lets you rename a sheet practically anything you wish which will be helpful. You need to follow reasonable guidelines: Use small names and no spaces in the names. The longer the name, the wider the tab and the more screen space a single tab will take up. This will have the effect of hiding some tabs and you will have to access the hidden tabs with the left-right arrow keys buttons. Once you select Rename from the sheet options, you will see the sheet you selected highlighted as in this example.

All you have to do is to enter from the keyboard the new sheet name you wish to use. When you press Enter, the sheet will be renamed.

Although we predict fewer Excel users will want to do arithmetic across a collection of sheets, we would like to examine some of the capabilities you have in "pulling" data from one spreadsheet to another. For purposes of this explanation, let's assume you are viewing Sheet 1 and the other sheets haven't been renamed.

	A	B	C	D	E
1
2
3		Total All Sheets =
4
5

Sheet 1

The specifics in the formula used depends on the exact cell address of the data on Sheet2, Sheet3, and Sheet 4. A typical formula to add numbers on three different sheets might look like:

=Sheet2!C55 + Sheet3!D38 + Sheet4!E48