Working with
Multiple Excel Worksheets

Sure you can work with individual worksheets in Excel.  But, better yet, is to learn how to make worksheets reference other worksheets so they become related or dependent.  Another worksaver involves selecting several worksheets before formatting or entering data then subsequent actions affect all. (If this sounds like Greek to you, the Excel Basics and Excel Beyond Basics workshops are two ways to learn about Excel.)

Entering  and Formatting Data on Multiple Worksheets

If you have a new Excel workbook open and select sheets 1, 2 and 3 as follows (Tip: click on Sheet 1 tab, then Control+click on others as desired),

then after typing onto one of those sheets, the same information goes onto the other two as well.  An example:  wanting to create an electronic grade book, these names typed onto sheet 1 also appear on sheets 2 and 3 in cells A3:A19.

Formatting changes can also be quickly applied to several sheets.   The column title "Students" will look like this on all three sheets if bolding, font type, and font size are applied with sheets 1, 2, and 3 selected:

Linking Spreadsheets

Continuing with the gradebook example, say you've entered information (gradually throughout the term) and created formulas on two of the worksheets ending with this:

Sheet 1 (renamed Homework):

Sheet 2 (renamed Quizzes):

Well, you could create a summary sheet that includes links back to these two sheets (along with maybe Exam data) for calculating a final grade.  First you'll see the end result, then an explanation of how the summary sheet  (named Exams & Summary) links or depends on the Homework and Quizzes sheets.

The result:

Explanation:

On Exams & Summary sheet student Mary Asan in row 3 has a Homework score in cell B3 of 91.5.   That score was not typed into the cell.  Instead that entry looks to whatever is on the Homework worksheet in cell K3.  And here are the steps that were followed:.  

1. On the Exams & Summary worksheet select cell B3. Then type an = sign.

2. Then change to a different worksheet:  the Homework worksheet.   On it, click in cell K3. 

3. The formula is being built.  And it's now done!  So press Enter and you'll return to the Exams & Summary worksheet with the result.   Selecting cell B3, you'll see this on the formula bar: 

Mary Asan's Quizzes score was retrieved to the Exams & Summary worksheet in the same way:

1. On the Exam & Summary worksheet select cell C3.  Then type an = sign.

2. Next change to the Quizzes worksheet.  On it, select cell J3.  

3. The formula is done; press Enter to return to the Exams & Summary worksheet.  Selecting cell C3, you'll see this in the formula bar: 

To see the formulas for the entire Exams and Summary worksheet, go here:  Formulas.
Get the file (Excel 97 format) explained above. Or here (Excel 5.0/95 format).

For more information on If statements, you might want to attend the Excel Statistical-Logical workshop.

Last updated 11/24/03 To sign up for workshops, write or call Peggy at 6836. URL: http://training.hanover.edu/online/Excel/multiple.htm