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.

1. Right-click on the November (2) sheet name at the bottom of the screen and choose Rename.
    Mac Users: hold down the Ctrl key and click the November (2) sheet name. Choose Rename
2. Type “December” and press Enter.
   Note for all users: you can also double-click a sheet tab name to rename it.
3. Click on the December sheet.
4. Click on B1 and change “November” to “December”. Be careful not to click on the “Monthly Budget” image.
5. Make the following data changes:
   • Miscellaneous: $300
   • Bonus: $250 (it’s the holidays!)
   • Freelance: delete amount
6. Save your work.
7. Point your mouse at the December sheet tab at the bottom of the screen.
8. Hold down your left mouse button and then press and hold down the CTRL key ( Mac Users hold down Option key)
9. 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 December sheet.
10. Let go of the mouse button and then the CTRL key. You should now have a December (2) sheet to the right of the December sheet.
11. Rename the December(2) sheet Practice.

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.

1. Point to the Practice sheet and hold down your left mouse button.
2. Notice this time that there is still a black arrow to the left of the Practice sheet, but the piece of paper is blank. It does not have a plus sign (+) because we are moving, instead of copying, the sheet.
3. Left-drag the mouse to the left until the black arrow marker is between the October and November sheets.
4. Release the mouse button.
5. Try moving the Practice sheet back to the right of the December sheet.

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

1. Right-click on the Practice sheet tab at the bottom of the screen.
    Mac Users hold down the Ctrl key and click the Practice sheet tab
2. Click Delete. Figure 6.3 shows the warning message box that will appear on your screen.  Your message box might look slightly different depending on the version of Excel you are using. It is important to note that you cannot Undo once you delete a sheet!
   

   

3. Click Delete.

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.

1. Click on the January sheet to make it active.
2. Hold the SHIFT key down and click on the December sheet.

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:

1. Click in F11 in the January grouped sheet.
2. Enter the formula =SUM(F5:F8).
3. In F12, enter the formula =SUM(C5:C13).
4. In F13, subtract Expenses from Income. In the January sheet, your balance should be $690. HINT: if your answer is negative, you subtracted Income from Expenses.
5. Click on I6. (I6 and I7 are formatted and merged together – this is fine.)
6. Enter a formula that divides Expenses (F12) by Income (F11). Your answer will show as a percentage since this cell has already been formatted to do this. HINT: If you percentage is greater than 100%, you have your numbers reversed.

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.