Hands-On Exercises

Download as a Word document: Hands-On Exercises Ch04 (15.0K)

In this exercise you will have an opportunity to name ranges, use named ranges in a formula, use cell addresses in formulas, and copy/fill formulas.

1. Open the file EXC461.
2. Save the file as "Marina"
3. Select cells A4 through E8. Choose, Insert, Name, Create. In the Create names dialog box, ensure that Top row and Left column are checked and click OK.
4. Open the Define Names dialog box and delete the named range called Department.
5. Select cell C16. Then choose Insert, Name, Paste and paste the range name list in the worksheet.
6. Now select cell F5. Type the formula "=sum(fuel)." Did Excel correctly calculate the amount? On your own, type formulas using the named ranges for each row (except the total row). Calculate the total for each column using the SUM function and the named ranges.
7. In order for the next step to work, you must not use named ranges, but use cell addresses instead. On your own, type formulas in cells B11 through B13 for each statistic. Use the AVERAGE, MIN and MAX functions in your formulas.
8. Copy these formulas across to column E. Did Excel calculate each value correctly?
9. On your own, format the cells where you entered formulas appropriately.
10. Save and Print the worksheet.

Data File: EXC461 (14.0K)

On Your Own: Financial Planners, Inc.

This exercise lets you practice using cell references in formulas, and creating charts. To begin open the file named EXC462, and save it as "Money." Review the contents of the worksheet.

To get started, go to a web site that specializes in stock quotes. Yahoo! Has one. Look up each of the symbols listed to find the current price and the 52-week high. Type the current price and 52 week high into the proper cells on the worksheet. Notice the formula in cell G5 (=$C5*D5). Copy this formula two columns right and four rows down ( Tip : you should have a formula for each stock). Type a formula to calculate the gain or loss for each stock, using standard cell addresses.

This chart uses non-adjacent ranges. Select the stock names as normal (cells A4 through A9), and then hold down the control key to select the labels and values for G4 through I9. Now create a High-Low-Close chart for the selected data. Embed the chart directly below the data.

When you have finished, save and then close the "Money" workbook.

Data File: EXC462 (13.0K)

Hancock Industries: Five year Financial Forecast

In this exercise you will use the TODAY function, natural language formulas, named ranges, and copy and fill operations. ( Note : Cell B23 should have an error message. It will go away as you work through the exercise)

1. Open the file named EXC463.
2. Save the workbook as HancockInd.
3. Select cell F3. Type "=Today()" and click the green check mark in the formula bar.
4. Format cell F3 to display the date as 14-Mar-98.
5. You will now name two ranges from the assumptions table. Select cell B8, then click the Name box and type Cost_Increase (no spaces), and press enter. Next, Select cell B9, click the Name box and type Sales_Growth, press enter. Now select cell B10, click the Name box and type Tax_Rate, press enter. In the next steps you will use these named ranges in formulas.
6. Click on cell C12, type the formula =B12*(1+Sales_Growth), and press enter. In cell C13 enter a formula to calculate the Cost for F-2002, press enter when finished. In cell C14 type the formula =C12-C13 and press enter.
7. Select the Range C12 through C14. Click and drag the fill handle until cell range C12 through F14 is highlighted, and release the mouse button. This copies the formulas across each row. Did this product the correct result? Why?
8. You will now calculate values for Total Expenses using natural language in a function. Ensure that the "Accept Labels In Formulas" option (found under the calculation tab in the menu Tools — Options) is checked. Select cell B21. Enter the function =sum(f-2001) and press enter. Take a moment to manually add the values for the expenses. Is your result the same that Excel calculated?
9. Select Cell B22 and type the formula =B14-B21 and press enter. (Note: the function entered in cell B23 should now have a value of 0. Review the function, but don’t change it.) Now Select cell B24 and type the formula =B22-B23 and press enter.
10. Select cells B22 through B24. Click the fill handle and drag to cell F22. What year will the business become profitable (net income greater that 0)?
11. Save your work, and Preview the worksheet. Does it fit on one page? Click on the Setup button. In the page setup dialog box, click on Landscape orientation, then click the Margins tab and check the box to center horizontally , finally click the OK command button. Print the worksheet.

Data File: EXC463 (15.0K)

In this exercise you will create an embedded pie chart, and print it with the worksheet.

1. Open the file named EXC464.
2. Save the file as "Hardware."
3. Select cell range A4:B9, and then click the chart wizard button on the toolbar.
4. Select Pie Chart type, and then the pie chart with 3-D visual effect sub-type (1 ^st row, 2 ^nd column)
5. Click Next
6. Click Next
7. Enter March Sales-Roscommon Store as the chart title. While still in this dialog box, click on the Data Labels tab. Then select Percentage under "Label Contains".
8. Click next. Make sure the option for to save the chart as an object in Sheet1 is selected, before clicking on finish.
9. Move the chart just below the data area, and resize it so that it fits over cells A11:E30.
10. Click on the Help button. Type in "How do I select nonadjacent ranges?" Click on the Select Ranges options. Read how you select non-adjacent ranges.
11. Use the technique you just read about to select cells A4:A9 and C4:C9 (the department labels, and the amounts for the St. Helen Store.)
12. Create a new pie chart similar to the one for the Roscommon store. Make it the same size, and place it next to the Roscommon Store Pie Chart.
13. Select the cells that contain the data and both charts, and set the print area.
14. Before printing, click the file menu, page setup and switch to landscape orientation. Print the sheet.

Data File: EXC464 (14.0K)