316 Unit 2 Excel

Apply Your KnowledgeComplete the following exercises in order, as directed by your teacher.As you work through these projects, you will create four quarterlyworksheets, a summary worksheet for annual data, and a chart sheet.Save all files within the Unit Applications and Projects subfolderwithin your Unit 2 folder.

1. Enter Revenue Data and Formulas in a Worksheeta. Open a new workbook and save it as xxx-Piano1.xls.

b. Enter the data in the following table. Begin to enter data incell A1 and leave row 3 blank. The Total row label shouldappear in cell A10.

First Quarter Revenues

Piano Sales

Organ Sales

Rentals

Service

Lessons

Total

January

61500

27300

3400

17900

8500

February

55000

31300

5200

15100

8700

March

58300

23800

6700

16200

7900

Total

Northstar Piano Sales and Service

c. Using the SUM function, enter formulas in the appropriate cellsto total the columns and rows.

d. To the right of the Total column, add a Percentage column. Inthe Percentage column, enter formulas that display the sales foreach category as a percent of the total sales. Each formula

Applications and Projects Unit 2 317

Figure 1 The formatted worksheet

should contain one relative reference and one absolutereference. Make sure to also enter the formula in cell F10.

e. Save the workbook.

2. Improve the Appearance of the Worksheeta. Change the width of all columns with data to 14, and center the

column labels. Change the height of row 1 to 34.5.

b. Apply the accounting format with two decimal places to therevenue data. Remove the currency symbols from all valuesexcept those in the first row and the Total row. Apply the percentformat with one decimal place to the percentage formulas.

c. Merge and center the labels in cells A1 and A2 over theworksheet. Format the titles in cells A1 and A2 by choosing anappropriate font and font size.

d. Apply the Classic 2 AutoFormat to the range A4:F10 withoutchanging the existing alignment or width/height settings. Applya light yellow fill color to the range A1:F3.

e. Locate and import a clip art image of a piano. Place the image inthe upper-left corner of the worksheet and resize it as necessary.

f. Save the workbook as xxx-Piano2.xls (see Figure 1).

318 Unit 2 Excel

3. Copy, Insert, and Delete Worksheetsa. Delete the Sheet2 and Sheet3 worksheets. Rename the Sheet1

tab as 1st Quarter and apply yellow to the tab. Place three copiesof the worksheet immediately following the 1st Quarterworksheet. Edit the new worksheet tabs so they read 2ndQuarter, 3rd Quarter, and 4th Quarter.

b. In the 2nd Quarter, 3rd Quarter, and 4th Quarter sheets, edit thetitles and column labels so that they reflect the appropriatequarter, and delete the sales data. Note: Be careful not to deleteany of the formulas.

c. Enter new revenue data in the 2nd Quarter, 3rd Quarter, and4th Quarter sheets. (Use numbers in a similar range as those inthe 1st Quarter worksheet.)

d. Save the workbook as xxx-Piano3.xls.

4. Prepare the Summary Worksheeta. Make a copy of the 4th Quarter sheet, and rename the new sheet

tab as Summary. Apply the color purple to the sheet tab. Positionthe Summary sheet so that it is the first sheet in the workbook.

b. On the Summary sheet, edit the subtitle to read AnnualRevenues. Insert a new column just after column B. Edit thecolumn labels over the four revenue columns so that they matchthe sheet tabs (1st Quarter through 4th Quarter).

c. On the Summary sheet, add a formula to total the 2nd Quartercolumn. Check the remaining Total and Percentage formulasand edit them, if necessary. Change the width of columns A–Gin the Summary sheet to 12. If any cells display pound signs(###), adjust the column width to fit the longest entry.

d. In the Summary sheet, change the data to reflect quarterlyrevenues by using 3-D references in formulas to fill in thequarterly totals for each category. Each of these formulas shouldrefer to the appropriate cell in the Total column of thecorresponding quarterly sheet. If necessary, adjust the numberformatting in the Summary sheet to make it consistent with theother sheets.

e. Save the workbook as xxx-Piano4.xls.

5. Create and Format Chartsa. Create a 3-D Pie chart based on the percent of total revenue by

category in the Summary sheet. Place the chart as an object inthe Summary sheet.

Applications and Projects Unit 2 319

Applications and Projects

Figure 2 The stacked column chart displayed on a separate chart sheet

b. Add a title to the pie chart, and then add data labels that showthe values on which each pie slice is based. Move and resize thechart as necessary so that it displays below the worksheet dataon the Summary sheet.

c. On a separate chart sheet, create a stacked column chart with a3-D visual effect that displays the quarterly revenue data bycategory (from the Summary sheet). Add a descriptive charttitle, and display the legend at the bottom of the chart. Ifnecessary, adjust the zoom to a smaller percentage so that youcan view the entire chart onscreen.

d. Move the Chart1 sheet to the end of the workbook, and changethe color of the sheet tab to blue. Save the workbook as xxx-Piano5.xls (see Figure 2).

6. Preview and Print Your Dataa. On the Summary sheet, add an appropriate header and footer.

Change the page orientation to landscape.b. Change the page setup so that the data is centered on the page

horizontally. Adjust the scaling, if necessary, so that the data fitson one page. Preview the worksheet, and then print theSummary sheet only (including the pie chart).

320 Unit 2 Excel

c. On the Summary sheet, simultaneously select the 1st Quarterand 3rd Quarter sales data and set a print area. Preview and printthese nonadjacent areas of the worksheet. Clear the print area.

d. Save the workbook as xxx-Piano6.xls.

7. Save and Preview the Workbook as a Web Pagea. Save the entire workbook as a Web page, with interactivity. Use

the file name xxx-Piano7.htm.

b. Use Web Page Preview to preview the workbook in your browser.Click the tab near the bottom of the window, and display thedifferent worksheets in the workbook.

c. Close the browser window. Close the xxx-Piano6.xls workbookwithout saving it.

What’s Wrong With This Picture?The workbook shown in Figure 3 contains ten mistakes. Some of theerrors are obvious, but others may be more difficult to find. Using theskills you have learned about building and formatting worksheets, tryto find at least seven of these mistakes. On a sheet of paper, identifyeach of the errors you find and describe how you would fix it usingExcel’s tools.

Figure 3 How many errors can you find in this worksheet?

Applications and Projects Unit 2 321

Applications and Projects

Cross-Curriculum ProjectYour Business Communications teacher has challenged you todevelop a worksheet that compares current interest rates andother key provisions for five major credit card companies. Seethe following list as a general guide for the type of informationyou may collect and compare:

• Annual percentage rate (APR)• Grace period for payments• Annual fee, late fee, and over the limit fee• Minimum payment (this may be a formula)Form a team to complete this project. Check with friends and family toget their recommendations on credit card companies to research. UseExcel’s Research tool to locate and insert the appropriate information.You may also conduct research on the Web at www.bankrate.com.

After you finish conducting your research, prepare the basic worksheetand enter the data. Then, do the following:

• Include descriptive worksheet title(s) and column labels and applyattractive fonts. Format the numeric data as appropriate.

• Adjust column widths, row heights, and font sizes.• Apply an AutoFormat, or use Excel’s formatting features to devise

your own format.• Insert one or more clip art images.• Add functions that calculate the minimum and maximum APR.• Create a chart that compares the APR for the five companies. Be

sure to choose a chart type that fits the data, and format the chartto enhance its appearance and readability (include the interestrates as data labels).

• Add a graphic arrow to the chart that draws attention to the creditcard company that looks the most attractive to you and your team.

• Preview the worksheet and chart, and make any necessaryadjustments such as changing the page orientation or centeringthe data on the page. Include your team name in a footer.

When you’re finished, review and discuss the information and makeany necessary changes. Verify that you’ve entered the data correctlyand that all formulas are correct. Perform a spell check and then printthe worksheet.