Upon completion of Lesson 8, you will be able to:
- Identify the purpose of charting worksheet data.
- Identify the types of charts you can create.
- Create a chart embedded in a worksheet.
- Move a chart to a chart sheet.
- Update a data source.
- Select chart elements.
- Choose a chart layout and style.
- Create a 3-D chart.
- Display or hide chart elements.
- Format and modify a chart.
- Edit and format chart text.
- Change the chart type.
Project 8-1
- Open the Populations Data File. Rename the workbook as Populations of Large Cities followed by your initials.
- Select the data in the range A5:B14, and then insert a column chart using the Stacked Column Chart.
- Move the chart to a chart sheet named Column Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Enter the chart title as World’s 10 Largest Cities.
- Enter the vertical axis title as Population in Millions.
- Remove the Legend.
- Change the slant for the horizontal labels to 90 degrees.
- Resize the chart so that the horizontal labels are completely viewable.
- Close the workbook.
Project 8-2
- Open the Running Data File. Rename the workbook as Running Times followed by your initials.
- Using the data in the range A5:B14, insert a line chart in the worksheet.
- In the range A5:A14, delete the word Week, leaving only the week number.
- Add a horizontal axis title below the axis with the text Week.
- Add a vertical axis with the text Time in Minutes.
- Do not include a chart title. Do not include a legend in the chart.
- Resize and move the chart to fill the range C4:H18.
- Close the workbook.
Project 8-3
- Open the McDonalds Data File. Rename the workbook as McDonalds Restaurants followed by your initials.
- Using the data in the range A3:B5, create a pie chart using the 3-D Pie Chart.
- Enter the chart title Total Restaurants.
- Show the legend above the chart.
- Move the chart to a chart sheet named Pie Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Format the font sizes of the chart title to 28 points, the slice percentages to 18 points, and the legend to 12 points.
- Close the workbook.
Project 8-4
- Open the Family Data File. Rename the workbook as Family Expenses followed by your initials.
- Using the data in the range A6:B13, create a pie chart using the 3-D Pie Chart.
- Move the chart to a chart sheet named 3-D Pie Chart. Move the chart sheet to be the first sheet listed in the workbook.
- The chart should include a chart title and data labels with percentages, but does not include a legend.
- Change the chart title to Where Our Money Goes.
- Change the font size of the chart title to 24 points.
- Change the font size of the data labels to 14 points.
- Based on the chart, in what area(s) does the family spend the most?
- Close the workbook.
Project 8-5
- Open the Study Data File. Rename the workbook as Study and Grades followed by your initials.
- Using the data in the range B4:C21, create a scatter chart with only markers.
- Move the chart to a chart sheet named Scatter Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Add the following chart title above the chart: Relationship Between Exam Grades and Study Time.
- Add the following horizontal axis title below the axis: Hours of Study.
- Add the following vertical axis title: Exam Grades.
- Change the font size of the chart title to 20 points.
- Change the font size of the axis titles to 14 points.
- Delete the legend.
- Format the vertical axis so its minimum value is fixed at 50.
- What relationship, if any, does the chart show between exam grades and study time?
- Close the workbook.
Project 8-6
- Open the Concession Data File. Rename the workbook as Concession Sales followed by your initials.
- Using the data in the range A4:E9, create a column chart.
- Move the chart to a chart sheet named Column Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Change the chart title to Concession Sales. Change the font size of the chart title to 24 points.
- Add the following vertical axis title: Sales in Dollars. Change the font size of the axis title to 14 points.
- Change the font size of the horizontal and vertical axis labels to 13 points and make them bold.
- Slant the horizontal axis labels to 30 degrees.
- Move the legend above the chart.
- Change the font size of the legend to 10 points.
- Which product has decreased in sales over the last four games? Which product has increased in sales over the last four games?
- Close the workbook.
Project 8-7
- Open the Triangle Data File. Rename the workbook as Triangle Growth followed by your initials.
- Using the data in the range A5:F7, create a line chart.
- Change the chart so that row 5 is used as labels.
- Move the chart to a chart sheet named Line Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Change the chart title to Triangle Software Revenue and Income.
- Change the vertical axis title to (Dollars in Thousands).
- Show the legend at the top of the chart.
- Have the company’s sales decreased, increased, or remained stable?
- Click the Line Chart tab to make a duplicate. Rename the copied chart sheet Clustered Column Chart.
- Change the chart type to a column chart.
- Close the workbook.
Project 8-8
- Open the Chico Data File. Rename the workbook as Chico Temperatures followed by your initials.
- Using the data in the range A3:M5, create a line chart.
- Move the chart to a chart sheet named Line Chart. Move the chart sheet to be the first sheet listed in the workbook.
- Change the chart title to Temperatures for Chico, California.
- Change the vertical axis title to Temperatures in Fahrenheit.
- Click the Customization tab. Make the following changes:
- Change the high series label to orange.
- Change the low series label to black.
- Close the Chart Editor dialog box.
- Close the workbook.
Activity 8-1
- You recently opened a store that buys and sells used CDs. As a small business owner, you are responsible for budgets and inventory. Initially, you tracked the inventory and budget data by hand in a paper notebook. Now that the business is growing, this method has become too cumbersome. You decide to transfer the data into an electronic format.Create a new workbook and save it as Sounds Good followed by your initials. Create and format one worksheet to track inventory and one worksheet to track the budget. Both worksheets should contain the name of your store—Sounds Good CDs—and a title describing the data.For the inventory worksheet, include (a) the title of the CD or DVD, (b) the artist, (c) the quantity of each, and (d) the cost per item. Enter the data shown in Figure 8-17 in the worksheet. Rename the worksheet as Inventory.Figure 8-17
Title Artist Quantity Budgeted Nerve Net Brian Eno 4 $6.95 Thursday Afternoon Brian Eno 2 $7.95 Geometry Robert Rich 3 $5.95 On This Planet Steve Roach 3 $8.95 Possible Planet Steve Roach 5 $6.95 The budget worksheet records the expected income and expenses for the month. Include rows for (a) sales revenue, (b) purchases of used CDs, (c) rent expense, (d) utilities expense, (e) tax expense, and (f) net income. Include columns for (a) budgeted amounts and (b) actual amounts. Then, enter the data shown in Figure 8-18.
Figure 8-18
Actual Budgeted Sales Revenue $12,875 $11,950 Purchases of CDs 5,500 4,800 Rent 575 575 Utilities 350 350 Taxes 817 667 Net Income For the Actual Net Income, enter a formula that subtracts the purchases and expenses from revenue. For the Budgeted Net Income, enter a formula that subtracts the purchases and expenses from revenue. Rename the worksheet as Budget.
Using the data you entered in the Budget worksheet, create a chart that compares the actual and budgeted values in each category. Use an appropriate chart type. Choose which chart elements to display, where they should be located, and how the chart is formatted. Put the chart in its own sheet and move the chart sheet show it is the first sheet in the list in the workbook.
Name the workbook as Sounds Good-(your initials).