6.1 Multiple Sheet Basics

Learning Objectives

Throughout our textbook, we have worked with Excel files that have included multiple sheets. Depending on the version of Excel you are using, a new Excel file may start with one or several sheets. In this chapter, we will be working with a personal budget file that contains income and expenses for an entire year. Our file contains a sheet for each month of the year, as well as, a Summary sheet that will add all twelve monthly sheets of data together. To begin with, we’ll get comfortable with moving through the sheets, organizing them, and making sure that all twelve monthly sheets are consistent.

Figure 6.1 shows the January sheet in the Personal Budget file along with all the sheet tabs along the bottom of the window.

Navigating Through A Multiple Sheet File

Download Data file: CH6 Data

1. Open the data file CH6 Data and save the file to your computer as CH6 Personal Budget. Notice that the file has an Expenses Summary sheet at the far left followed by monthly sheets.

2. Click on the different sheets at the bottom of the screen to move through the sheets. Notice that the Expenses Summary sheet is formatted differently from the monthly sheets. Notice also that all the monthly sheets are identical in layout and format.

3. Take a second look at the months at the end of the year. Not all the data has been entered for September through November… and there is no sheet for December. We will enter the remaining data and add an additional sheet for December.

4. Add the following data in the September, October, and November sheets:

Copying A Sheet

1. To make a December sheet, we are going to copy the November sheet.
2. Point your mouse at the November sheet tab at the bottom of the screen.
3. Hold down your left mouse button and then press and hold down the CTRL key.
    Mac Users: hold down your left button while pointing at the November sheet tab and then press and hold down the Option (alt) key 
4. At this point, you will see a black down-pointing arrow to the left of the November sheet tab and your mouse cursor will become a small piece of paper with a plus sign on it.
5. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of the November sheet tab.
6. Let go of the mouse button and then the CTRL key. You should now have a November (2) sheet to the right of the November sheet as shown in Figure 6.2.

Next, we’ll update the November (2) sheet to turn it into our December sheet.

Skill Refresher

Copying a Sheet

Moving And Deleting Sheets

Sometimes your sheets do not end up in the right order, and you need to move them in order to fix this. Let’s try moving our “Practice” sheet to see how this is done.

Since our Practice sheet is not a sheet we will need in our Budget file, we’ll go ahead and delete it now.

Grouping And Ungrouping Sheets

Take a look at the monthly sheets again. Notice that there is a place in each of these sheets in the range F11:F13 to calculate three pieces of Summary data: Income, Expenses, and Balance; but there aren’t any formulas in these cells. There is also a place for the % of Income Spent (in cell I5), but we will need to add a formula in I6:I7 to calculate this. If we entered these formulas individually in each of the 12 month sheets, it would take a long time! Because this task would be very repetitive, it would also be fairly likely that we would make some mistakes along the way entering the same formulas over and over again. By grouping all the month sheets together, we can enter each of the formulas once and have them appear in all the sheets.

Now all 12 sheets should be selected. You can tell this in two ways: the sheet tabs that have been selected are now bold at the bottom of your screen. Also, notice in the Title bar at the top of the screen the word [Group] or – Group (depending on your version of Excel) added to the end of the title. You can see both of these in Figure 6.4.

IT IS IMPORTANT TO REMEMBER THAT ANY CHANGES WE MAKE TO THE JANUARY SHEET WILL BE MADE TO ALL THE SHEETS!! This is a very good thing when we want to make changes to all the sheets at once, but we need to be sure to ungroup them when we’re done making these changes. Let’s go ahead and add the formulas to all twelve of the sheets at once:

Notice that a data bar was set up in I5 to visually show the income spent. Do you remember how to do this from earlier in our textbook? Your January sheet should now look like Figure 6.5.

7. Now that we are done making changes to all the monthly sheets at once, we need to ungroup them. Right-click on one of the grouped sheets and choose Ungroup Sheets.
 Mac Users hold down Ctrl key click on one of the grouped sheets and choose Ungroup Sheets.

Notice the sheets tabs are no longer bold and the word [Group] is no longer in the title bar.

8. Click on several of the month sheets to see that all the formulas have been added.

9. Click on the December sheet. Your sheet should now look like Figure 6.6.

1. Take a look at the Notes in the September sheet. It says that the rent was raised in September, so we need to cancel our Gym Membership and show $0 for the Gym amount in October, November, and December.
2. Group the October, November, and December sheets. If you do this successfully, these three sheet names should be bold and the word [Group] or – Group will appear in the Title bar.
3. Click on C13 and change the amount to $0. Press Enter.
4. Ungroup the sheets. The balances should be: October $605, November $530, and December $430.

Skill Refresher

To Group Sheets:
Click on the leftmost sheet you want to group; then hold the SHIFT key down and click on the rightmost sheet you want to group.  Same for Excel for Mac.

To Ungroup Sheets:
Right-click on one of the grouped sheets and choose Ungroup Sheets.

 Mac Users hold down Ctrl key click on one of the grouped sheets and choose Ungroup Sheets.

Key Takeaways