Excel 2024 In Practice - Ch 3 Guided Project 3-3

Guided Project 3-3 - SIMnet Project 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 and apply a chart style.

3.Select the Revenue by Department sheet, select cells A4:F4, press Ctrl, and select cells A13:F13.

4.Click the Insert Pie or Doughnut Chart button [Insert tab, Charts group] and choose 3-D Pie.

5.Click the Quick Styles button [Chart Design tab, Chart Styles group] and select Style 3.

Size and position a chart object.

6.Drag the chart object so its top-left corner is at cell H4.

7.Point to the bottom right selection handle to display the resize arrow.

8.Drag the pointer to reach cell Q19.

Explode a pie slice.

9.Double-click the pie to open its Format Data Series task pane.

10.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.)

11.Click the Series Options button in the Format Data Point task pane.

12.Set the point explosion percentage at 10%.

13.Display and format data labels.

14.Click 41% in the San Diego slice to open the Format Data Labels pane.

15.Verify that the Label Options group is selected in the pane and click the Label Options button.

16.Click Label Options to expand the group if it is collapsed.

17.Select the Category Name box.

18.Confirm that the Percentage and the Show Leader Lines boxes are selected.

19.Click the Separator drop-down list and choose (space) to insert a blank space between the city name and the percentage (Figure 3-81).

A screenshot of Microsoft Excel illustrates a 3-D pie chart and the Format Data Labels pane.The pie chart is titled Golf and shows the data for five cities. The data is as follows: San Diego, shaded in blue, occupies 41 percent. Atlanta, shaded in teal, occupies 36 percent. St. Louis, shaded in purple, occupies 9 percent. Boston, shaded in orange, occupies 7 percent. Chicago, shaded in green, occupies 7 percent. Data labels display category names and percentages connected to slices with leader lines, and these labels are selected and highlighted. On the right, the Format Data Labels pane provides the Label Options and Text Options groups. The Label Options group has expanded, including Label Contains and Label Position. The Label Contains category contains checkboxes for Value From Cells, Series Name, Category Name, Value, Percentage, Show Leader Lines, and Legend key. The Category Name, Show Leader Lines, and Percentage are selected. Below this is a Separator option with (space) selected from a drop-down arrow, and a Reset Label Text button is shown. The Label Position contains radio buttons for Center, Inside End, Outside End, and Best Fit. The Centre option is selected.

Figure 3-81 Format Data Labels task pane

20.Click the Fill & Line button, expand the Fill group, and select No Fill. This removes the black background box for each label.

21.Click the Text Fill button [Format tab, WordArt Styles group] and choose Black, Text 1 (second column).

22.Click as many times as needed to select only the San Diego data label.

23.Point to display a move pointer and drag the label as shown in Figure 3-82 .

Data labels are positioned by dragging each one

Figure 3-82 Data labels positioned

24.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.

25.Select the legend and delete it.

26.Select the chart object and apply a Black, Text 1 outline that is ¼ pt thick (weight).

27.Click a worksheet cell and close the task pane if it is open.

28.Create a funnel chart sheet.

29.Select cells A22:B26, click the Quick Analysis button, and choose Charts.

30.Choose More Charts to open the Insert Chart dialog box.

31.Click Funnel in the Recommended Charts list (Figure 3-83).

A screenshot of Microsoft Excel shows a funnel chart preview.Two tabs, Recommended Charts and All Charts, are visible at the top of the Insert Chart dialog box. The Recommended Charts tab is selected, and a left sidebar displays the chart previews, including a column chart, a pie chart, a horizontal bar chart, and a funnel chart. The funnel chart is selected and enlarged in the right pane, showing progressively decreasing values for five categories. The funnel chart includes the following: Boston at $57,910, San Diego at $31,925, Atlanta at $30,100, Chicago at $29,425, and St. Louis at $26,250. The chart title is a blank placeholder. Below the funnel chart, a description reads, A funnel chart represents stages in a process showing progressively decreasing proportions. Two buttons, OK and Cancel, are located at the bottom right of the dialog box. The OK button is selected.

Figure 3-83 Funnel chart suggested based on the data

32.Click OK. Notice that the data is arranged in largest-to-smallest value order.

33.Move the chart object to its own sheet named City Totals.

34.Apply a style and a special effect.

35.Click the Quick Styles button [Chart Design tab, Chart Styles group] and select Style 3.

36.Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Series 1. A funnel chart has one data series.

37.Click the Shape Effects button [Format tab, Shape Styles group].

38.Point to Shadow and choose Offset: Bottom Right in the Outer group.

39.Edit data labels and the chart title.

40.Click the Chart Elements drop-down list [Format tab, Current Selection group] and select Series 1 Data Labels.

41.Click the Bold button [Home tab, Font group].

42.Change the font size to 16.

43.Change the Font Color to Light Gray, Background 2 (third column).

44.Select the Chart Title box.

Triple-click to select all the placeholder text. You can also click and drag, or click once and press Ctrl+A.

45.Type Total Sales by City.

46.Select the chart object and apply a Black, Text 1 outline that is ¼ pt thick (weight).

47.Add Alt Text to a chart for increased accessibility.

48.Right-click the border of the funnel chart and select View Alt Text.

49.Click the entry box to place the insertion point.

50.Type This funnel chart displays totals in descending value order. You can select and recolor individual data points. including the last period. NOTE: To ensure accurate grading, type the alt text as shown here. Only include one space between the sentences.

51.Close the Alt Text pane.

52.Insert sparklines in a worksheet.

53.Click the First Period worksheet tab.

54.Select cells B19:E19 as the data range.

55.Click the Column Sparkline button [Insert tab, Sparklines group].

56.Select cell $B$20 in the Location Range box and click OK. This places a single sparkline for the totals.

57.Select cells B20:E20 and merge and center them.

58.Set the height of row 20 to 84.

59.Format sparklines in worksheet.

60.Select the sparkline and click the Sparkline Axis button [Sparkline tab, Group group].

61.Choose Custom Value in the Vertical Axis Minimum Value Options group.

62.Type 10.0 as the minimum value to better scale the data and click OK.

63.Click the Quick Styles button [Sparkline tab, Style group].

64.Choose Brown, Sparkline Style Accent 2, Darker 50% (first row).

65.Apply Outside Borders and Light Gray, Background 2 fill to the merged cell [Home tab].

66.Set the page orientation to landscape.

67.Click cell A1.

68.Save and close the workbook (Figure 3-84).

Excel 3-3 completed

Figure 3-84 Excel 3-3 completed

69.Upload and save your project file.

70.Submit file for grading.