Hands-On Exercises

Download as a Word document: Hands-On Exercises Ch07 (172.0K)

In this exercise you will practice formatting and building multi-sheet workbooks.

Open the file named EX07HE01 as shown in Figure 7-1.

Save the file as "Monthly Sales" to your personal storage location.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch07_Image1ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 7-1

Sunny Side Marina recently purchased two other marinas. They now have locations in Tawas (the original site), Harrisville, and Alpena. The owner wants to use the same basic layout for the monthly sales reports for all stores, and save them in a single workbook.

Copy the contents of sheet one, cells A1 to F9, and paste them on Sheet2 and Sheet3.
SELECT: Cell A1 and
DRAG: to cell F9
CLICK: Copy button on the standard toolbar.
CLICK: Sheet2 tab
CLICK: Paste button on the standard toolbar

On your own, paste again to sheet three.

Change the names of each sheet to reflect town where the store is located.
DOUBLE CLICK: Sheet1 tab
TYPE: Tawas
PRESS: Enter

On your own, change the names of Sheet2 and Sheet3 to Harrisville and Alpena, respectively.

On your own, adjust the column widths on each sheet to properly display the cell contents.

Save the file. Close and exit Excel.

Data File: EX07HE01 (13.0K)

Turtle River Casinos

In this exercise you practice combining and summarizing data that is stored in two individual workbooks.

Make sure there are no workbooks open in the application window. Then do the following:

Open the data file named EX07HE02A and save it as "TRC-1" to your personal storage location.

Open the data file named EX07HE02B and save it as "TRC-2" to your personal storage location.

Open the data file named EX07HE02C and save it as "TRC-Summary" to your personal storage location.

Arrange all the workbooks in the document area:
CHOOSE: Window, Arrange.
SELECT: Tiled option button
CLICK: OK Command button.
Your screen should appear similar to Figure 7-2

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch07_Image2ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 7-2

To calculate the total revenue of the quarterly workbooks:
SELECT: Cell range B5:C9

Use Excel’s "Consolidate" command:
CHOOSE: Data, Consolidate.
Ensure that the function box reads "Sum"

In the Reference text box, enter the data range for the first quarter:
CLICK: Dialog collapse button
CLICK: Title bar of the TRC-1 workbook
SELECT: Cell range B5 to C9
CLICK: Dialog expand button
CLICK: Add command button
On your own, perform the same steps in this sequence for the 2 ^nd quarter.

Before closing the Consolidate dialog box, click the check box to "Create links to source data" which will ensure that the summary workbook is always up to date. When finished click the OK Command button.

Maximize the Summary workbook by double clicking its title bar.

Display the details of the analysis by clicking the Row level 2 button.

Return to viewing the summary information by clicking the Row level 1 button.

Close and save all three workbooks. Exit Excel.

Data File: EX07HE02A (13.0K)

Data File: EX07HE02B (13.0K)

Data File: EX07HE02C (13.0K)