Homework answers / question archive / The file will be renamed automatically to include your name

The file will be renamed automatically to include your name

MS Excel

Share With

---

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.

If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.

Add Sparklines to the data and apply a Quick Style.

 

On the Population Data worksheet, select cells B4:F13.

 

On the Insert tab, in the Sparklines group, click the Column button.

 

In the Create Sparklines dialog, verify that B4:F13 is the Data Range and specify G4:G13 as the Location Range. Click OK.

 

On the Sparkline tab, in the Style group, apply the Blue, Sparkline Style Colorful #2 style. Click the More button to expand the gallery, then click the second style in the last row.

Alternate Instruction for Microsoft 365 Apps icon On the Sparkline Tools Design tab, in the Style group, apply the Blue, Sparkline Style Colorful #2 style. Click the More button to expand the gallery, then click the second style in the last row.

Change the Sparklines to lines with markers for all data points and highlight the high point marker in a different color.

 

On the Sparkline tab, in the Type group, click the Line button.

Alternate Instruction for Microsoft 365 Apps icon On the Sparkline Tools Design tab, in the Type group, click the Line button.

 

On the Sparkline tab, in the Show group, click the Markers check box.

Alternate Instruction for Microsoft 365 Apps icon On the Sparkline Tools Design tab, in the Show group, click the Markers check box.

 

On the Sparkline tab, in the Style group, click the Marker Color button, point to High Point, and select Purple (the last color in the row of standard colors).

Alternate Instruction for Microsoft 365 Apps icon On the Sparkline Tools Design tab, in the Style group, click the Marker Color button, point to High Point, and select Purple (the last color in the row of standard colors).

Create a column chart to represent the population data for Houston and then add a second series to represent the overall population of the United States.

 

Select cells A3:F3. Press Ctrl and click and drag to select cells A7:F7.

 

On the Insert tab, in the Charts group, click the Column or Bar Chart button, and choose Clustered Column (the first chart type under 2-D Column).

 

Click and drag the chart to reposition it immediately below the data.

 

On the Chart Design tab, in the Data group, click the Select Data button.

Alternate Instruction for Microsoft 365 Apps icon On the Chart Tools Design tab, in the Data group, click the Select Data button.

 

In the Legend Entries (Series) box, click the Add button.

 

Click cell A15 to add the cell reference to the Series name box.

 

Click in the Series values box and delete the default entry. Click and drag to select cells B15:F15.

 

Click OK.

 

Click OK again to close the Select Data Source dialog.

Change the chart type to a combination chart with a secondary axis for the national population data.

 

On the Chart Tools Design tab, in the Type group, click the Change Chart Type button.

Alternate Instruction for Microsoft 365 Apps icon On the Chart Design tab, in the Type group, click the Change Chart Type button.

 

In the list of chart types at the left side of the All Charts tab, click Combo.

 

Excel automatically suggests a line chart for the Total US population series, but the scale difference between the two series makes the Houston data unreadable. Click the Secondary Axis check box next to Total US population series.

 

Click OK.

Add a legend below the chart and format it by applying a style.

 

Click the Chart Elements button that appears near the upper right corner of the chart. Point to Legend and click the arrow that appears at the right. Click Bottom.

 

Click the legend to select it. On the Chart Format tab, in the Shape Styles group, click the More button to expand the gallery. Select the Subtle Effect - Green, Accent 1 style (the second style in the fourth row in the Theme Styles section).

Alternate Instruction for Microsoft 365 Apps icon Click the legend to select it. On the Chart Tools Format tab, in the Shape Styles group, click the More button to expand the gallery. Select the Subtle Effect - Green, Accent 1 style (the second style in the fourth row in the Theme Styles section).

Change the fill color of a data point to make it stand out.

 

Select the data point for 1990 for the series Houston, TX by clicking the 1990 column once to select the series and then clicking it a second time to select just that data point.

 

On the ChartFormat tab, in the Shape Styles group, click the Shape Fill button and select Red, Accent 2 (the fifth color from the right in the first row of theme colors).

Alternate Instruction for Microsoft 365 Apps icon On the Chart Tools Format tab, in the Shape Styles group, click the Shape Fill button and select Red, Accent 2 (the fifth color from the right in the first row of theme colors).

Save this chart as a new chart template.

 

Right-click an empty area of the chart and select Save as Template....

 

In the Save Chart Template dialog, type Population Combo Chart in the File name box.

 

Click Save.

Add a callout to the chart, apply a style to it, and add text.

 

On the Chart Format tab, in the Insert Shapes group, click the More button to expand the gallery.

Alternate Instruction for Microsoft 365 Apps icon On the Chart Tools Format tab, in the Insert Shapes group, click the More button to expand the gallery.

 

Select Callout: Line (the fifth shape from the left in the Callouts section).

 

Click anywhere in the chart to insert the shape.

 

Click and drag the shape to reposition it so it is pointing to the highlighted data point as shown in Figure EX 7.64.

 

On the Shape Format tab, in the Shape Styles group, click the More button to expand the gallery. Select the Subtle Effect - Green, Accent 1 style (the second style in the fourth row).

Alternate Instruction for Microsoft 365 Apps icon On the Drawing Tools Format tab, in the Shape Styles group, click the More button to expand the gallery. Select the Subtle Effect - Green, Accent 1 style (the second style in the fourth row).

 

Type: What happened here? and then click anywhere outside the callout.

 

If necessary, make the shape slightly larger so all the text is visible.

Create a new chart from the template you saved.

 

Select cells A3:F4. Press Ctrl and click and drag to select cells A7:F7.

 

On the Insert tab, in the Charts group, click the dialog launcher to open the Insert Chart dialog.

 

In the Insert Chart dialog, click the All Charts tab.

 

Click Templates. If necessary, select the Population Combo Chart template.

 

Click OK.

 

If necessary, move the chart so it is positioned to the right of the first chart as shown in Figure EX 7.64.

Two Combo charts aligned with eachother on the worksheet

FIGURE EX 7.64

Change the outline color of a data series.

 

Select the New York, NY data series by clicking any column in the second chart.

 

On the Chart Format tab, in the Shape Styles group, click the Shape Outline button, and select Black, Text 1 (the second color from the left in the first row of theme colors).

Alternate Instruction for Microsoft 365 Apps icon On the Chart Tools Format tab, in the Shape Styles group, click the Shape Outline button, and select Black, Text 1 (the second color from the left in the first row of theme colors).

Create a clustered column chart for population growth by region. Move the chart to its own chart sheet named Regional Trends.

 

Go to the Census Data by State worksheet and select cells A5:L8.

 

On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button and select Clustered Column (the first chart under the 2-D Column section).

 

If necessary, change the x axis labels to match the column headings. On the Chart Design tab, in the Data group, click the Select Data button. In the Horizontal (Category) Axis Labels box, click the Edit button, and then click and drag to select cells B3:L3. Click OK. Click OK to accept the changes to the chart.

Alternate Instruction for Microsoft 365 Apps icon If necessary, change the x axis labels to match the column headings. On the Chart Tools Design tab, in the Data group, click the Select Data button. In the Horizontal (Category) Axis Labels box, click the Edit button, and then click and drag to select cells B3:L3. Click OK. Click OK to accept the changes to the chart.

 

On the Chart Design tab, in the Location group, click the Move Chart button.

Alternate Instruction for Microsoft 365 Apps icon On the Chart Tools Design tab, in the Location group, click the Move Chart button.

 

Click the New Sheet radio button. Type Regional Trends in the box and click OK.

Edit and format the chart title.

 

Click the chart title and type: Population Trends by Region

 

If necessary, select the text you just typed. On the Chart Format tab, in the WordArt Styles group, click the More button to expand the gallery, and select one of the WordArt options.

Alternate Instruction for Microsoft 365 Apps icon If necessary, select the text you just typed. On the Chart Tools Format tab, in the WordArt Styles group, click the More button to expand the gallery, and select one of the WordArt options.

 

Click anywhere outside the title.

Add trendlines to the chart to forecast exponential growth for the next 40 years.

 

Click the Chart Elements button that appears near the upper right corner of the chart. Point to Trendline, click the arrow that appears, and click More Options.

 

In the Add Trendline dialog, click OK. (Northeast should be selected by default.)

 

In the Format Trendline task pane, click the Exponential radio button.

 

In the Forecast section, type 4 in the Forward box.

 

Click the Fill & Line button near the top of the task pane.

 

Click in the Width box and change the value to 3.

 

Click the End Arrow type box and select Open Arrow (the third option in the top row).

 

Right-click any data point in the Midwest series and select Add Trendline.... Apply the same formatting options as you applied to the Northeast trendline: exponential, forecasting forward for 4 periods, 3 pt width, and arrow type End Open Arrow.

 

Follow the same steps to add a trendline to each of the other data series using the same formatting options (exponential, forecasting forward for 4 periods, 3 pt width, and arrow type End Open Arrow). The final chart should look similar to Figure EX 7.65.

A chart with multiple trendlines

FIGURE EX 7.65

 

Save and close the workbook.

 

Upload and save your project file.

 

Submit project for grading.