Ch 3 Guided Project 3-3

Blue Lake Sporting Goods has locations in major cities. For this project, you create a pie chart and a bar chart to display each store’s contribution to golf-related sales and how total sales evolved for the period. You create a line chart to illustrate the break-even sales needed for a new item.

[Student Learning Outcomes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.7, 3.8]

File Needed: BlueLake-03.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-BlueLake-03.xlsx

Skills Covered in This Project

• Create, size, and position a pie chart object.
• Apply a chart style.
• Explode a pie slice.
• Delete the legend from a chart.
• Reposition data labels in a chart.
• Create a clustered bar chart object.
• Format data labels and data series.
• Add Alt Text to a chart.
• Insert and format sparklines in a worksheet.

 This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead.

Steps to complete This Project

Mark the steps as checked when you complete them.

1. Open the BlueLake-03 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Create a pie chart object.
   1. Select the Revenue by Department sheet, select cells A4:F4, press Ctrl, and select cells A13:F13.
   2. Click the Insert Pie or Doughnut Chart button [Insert tab, Charts group] and choose 3-D Pie.
3. Apply a chart style.
   1. Select the chart object if necessary.
   2. Click the More button [Chart Design tab, Chart Styles group].
   3. Select Style 3.
4. Size and position a chart object.
   1. Point to the chart object border to display the move pointer.
   2. Drag the chart object so its top-left corner is at cell H4.
   3. Point to the bottom right selection handle to display the resize arrow.
   4. Drag the pointer to reach cell Q19.
5. Explode a pie slice.
   1. Double-click the pie to open its Format Data Series task pane.
   2. Click the San Diego slice to update the pane to the Format Data Point task pane. (Rest the pointer on a slice to see its identifying ScreenTip or refer to the legend.)
   3. Click the Series Options button in the Format Data Point task pane.
   4. Set the pie explosion percentage at 10%.
   5. Close the task pane and click the chart object border to deselect the San Diego slice.
6. Display and format data labels.
   1. Click the Chart Elements quick button, click the Data Labels arrow to open its submenu, and choose More Options.
   2. Verify that the Label Options group is selected in the Format Data Labels pane.
   3. Click Label Options to expand the group if it is collapsed.
   4. Select the Category Name box.
   5. Confirm that the Percentage and the Show Leader Lines boxes are selected (Figure 3-80).

1. 1. Click the Separator drop-down list and choose (space).
   2. Click the Fill & Line button, expand the Fill group, and select No Fill. This removes the black background box for each label.
   3. Close the task pane.
2. Delete the legend and recolor and position data labels.
   1. Select the legend.
   2. Confirm your selection in the Chart Elements drop-down list [Format tab, Current Selection group].
   3. Press Delete to remove the legend.
   4. Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Series “Golf” Data Labels. All of the city names and percentages are selected.
   5. Click the Text Fill button [Format tab, WordArt Styles group] and choose Black, Text 1 (second column).
   6. Click as many times as needed to select only the San Diego data label.
   7. Point to display a move pointer and drag the label as shown in Figure 3-81.

1. 1. Select each data label and position it as shown in the figure. If you accidentally move another element, press Ctrl+Z to undo the error.
   2. Click a worksheet cell.
2. Create a clustered bar chart object to simulate a funnel chart.
   1. Select cells A22:B26.
   2. Click the Quick Analysis button and choose Charts.
   3. Click Clustered bar in the list (Figure 3-82).

1. Size and position a clustered bar chart object.
   1. Point to the chart object border to display the move pointer.
   2. Drag the bar chart object so its top-left corner is at cell C21.
   3. Point to the bottom right selection handle to display the resize arrow.
   4. Drag the pointer to reach cell L40 (Figure 3-82).
2. Apply a chart style.
   1. Confirm that the bar chart object is selected.
   2. Click the More button [Chart Design tab, Chart Styles group].
   3. Select Style 3.
3. Format data labels and edit the chart title.
   1. Select one of the data labels in any bar (the dollar amount) and change the font size to 16.
   2. Select the Chart Title box.
   3. Triple-click to select all the placeholder text. You can also click and drag, or click once and press Ctrl+A.
   4. Type Total Sales by City.
4. Remove the horizontal axis, reverse the category order, and apply a special effect.
   1. Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Horizontal (Value) Axis to select the values along the bottom of the plot area.
   2. Press Delete to remove the values.
   3. Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Series 1.
   4. Click the Shape Effects button [Format tab, Shape Styles group].
   5. Point to Shadow and choose Offset: Bottom Right in the Outer group.
   6. Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Vertical (Category) Axis to select the city names.
   7. Click the Format Selection button [Format tab, Current Selection group] to open the Format Axis pane.
   8. Click the Axis Options button in the Axis Options group.
   9. Select the Categories in reverse order box and close the task pane.
5. Add Alt Text to a chart for increased accessibility.
   1. Right-click the border of the bar chart and select Edit Alt Text.
       Right click the border of the bar chart and select View Alt Text.
   2. Click the entry box to place the insertion point.
   3. Type This is a bar chart which displays totals in descending order. You can select an individual data point to apply a fill color. including the period. NOTE: To ensure accurate grading, type the alt text as shown here. Only include one space between the sentences.
   4. Close the Alt Text pane.
   5. Select cell A1.
6. Insert sparklines in a worksheet.
   1. Click the First Period worksheet tab.
   2. Select cells B19:E19 as the data range.
   3. Click the Line Sparkline button [Insert tab, Sparklines group].
   4. Select cell $I$19 in the Location Range box and click OK. This places a single sparkline for the totals.
7. Format sparklines in worksheet.
   1. Click the Format button [Home tab, Cells group] and change the Row Height to 35.
   2. Click the Format button [Home tab, Cells group] and set the Column Width to 40.
   3. Select the Markers box in the Show group in the Sparkline tab.
   4. Click the More button [Sparkline tab, Style group].
   5. Choose Brown, Sparkline Style Accent 6, Darker 50% (first row, last column).
   6. Apply Outside Borders to cell I19.
   7. Click the row 4 heading and set the row height to 35.
   8. Click cell A1.
   9. Set the page orientation to landscape.
8. Save and close the workbook (Figure 3-83).