How to change sparkline style in excel 2013

Excel Challenge Help Again...

A Skills Approach: Excel 2013 Chapter 7: Advanced Charts and Graphics

1 | Page skill review 7.1 Last Updated 5/5/15

skill review 7.1 In this project, you will analyze U.S. population growth over the last 40 years. You will include a screenshot from the 2010 U.S Census Web page.

Skills needed to complete this project:

 Applying Quick Styles and Other Formatting to Sparklines

 Changing the Sparkline Type

 Adding Markers to Sparklines

 Adding a Data Series to a Chart

 Creating a Combo Chart

 Formatting Other Chart Elements

 Formatting a Data Point or a Data Series

 Inserting a Shape

 Applying Quick Styles and Other Formatting to Shapes

 Creating a Chart Template

 Applying a Chart Template

 Adding and Removing Trendlines

 Forecasting Values on a Trendline

 Changing Trendline Options

 Capturing a Screenshot in Excel

 Resizing and Moving Pictures

 Applying Styles to Pictures

1. Open the start file EX2013-SkillReview-7-1. 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. 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.

3. Add Sparklines to the data and apply a Quick Style.

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

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

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

Range. Click OK.

d. On the Sparkline Tools Design tab, in the Style group, apply the Sparkline Style Accent 1, (no dark or

light) style. Click the More button to expand the gallery, then click the first style in

the third row.

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

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

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

c. On the Sparkline Tools Design tab, in the Style group, click the Marker Color button, point to High

Point, and select Red, Accent 2 (the fifth color from the right in the top row of theme colors).

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

a. Select cells A3:F4.

b. On the Insert tab, in the Charts group, click the Column Chart button, and choose Clustered

Column (the first chart type under 2-D Column).

Step 1

Download start file

A Skills Approach: Excel 2013 Chapter 7: Advanced Charts and Graphics

2 | Page skill review 7.1 Last Updated 5/5/15

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

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

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

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

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

h. Click OK.

i. Because the second series is not adjacent to the first, you will need to reset the column labels (the

years) as the X axis labels. In the Horizontal (Category) Axis Labels box, click the Edit button, and then

click and drag to select cells B3:F3. Click OK.

j. Click OK to accept the changes to the chart.

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

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

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

c. Excel automatically suggests a line chart for the Total US population series, but the scale difference

between the two series makes the New York data unreadable. Click the Secondary Axis check box

next to Total US population series.

d. Click OK.

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

a. 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 Top.

b. 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 - Yellow, Accent 5 style (the second style

from the right in the third row from the bottom).

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

a. Select the data point for 1990 for the series New York, NY by clicking the 1990 column once to select

the series and then clicking it a second time to select just that data point.

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

9. Save this chart as a new chart template.

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

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

c. Click Save.

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

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

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

c. Click anywhere in the chart to insert the shape.

d. Click and drag the shape to reposition it so it is pointing to the highlighted data point.

A Skills Approach: Excel 2013 Chapter 7: Advanced Charts and Graphics

3 | Page skill review 7.1 Last Updated 5/5/15

e. On the Drawing Tools Format tab, in the Shape Styles group, click the More button to expand the

gallery. Select the Subtle Effect - Yellow, Accent 5 style (the second style from the right in the third

row from the bottom).

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

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

11. Create a new chart from the template you saved.

a. Select cells A3:F5.

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

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

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

e. Click OK.

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

12. Change the outline color of a data series.

a. Select the Los Angeles, CA data series by clicking anywhere on the line in the second chart.

b. On the Chart Tools Format tab, in the Shape Styles group, click the Shape Outline button, and select

Gray - 50%, Accent 6 (the color at the far right side of the first row of theme colors).

Figure EX 7.43

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

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

b. On the Insert tab, in the Charts group, click the Insert Column Chart button, and select Clustered

Column (the first chart under the 2-D Column section).

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

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

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

A Skills Approach: Excel 2013 Chapter 7: Advanced Charts and Graphics

4 | Page skill review 7.1 Last Updated 5/5/15

14. Edit and format the chart title.

a. Click the chart title and type: Population Trends by Region

b. On the Chart Tools Format tab, in the WordArt Styles group, click the More button to expand the

gallery, and select the Fill - Green, Accent 1, Shadow style (the second style from the left in

the top row).

c. Click anywhere outside the title.

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

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

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

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

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

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

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

g. Click the End Arrow type box and select Arrow (the second option in the top row).

h. 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 arrow.

i. 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 arrow).

Figure EX 7.44

A Skills Approach: Excel 2013 Chapter 7: Advanced Charts and Graphics

5 | Page skill review 7.1 Last Updated 5/5/15

16. Add a screenshot from the U.S. Census Web site.

a. Go to the Population Migration worksheet. This is where you will place the screenshot.

b. Open a Web browser window and go to:

http://www.census.gov/dataviz/visualizations/051/

c. Scroll down the page, so you can see the map. You will be taking a screenshot of just the title, Net

Migration between California and Other States: 1955-1960 and 1995-2000, and the map as shown in Figure

EX 7.45, so position the browser window accordingly.

d. Click back to the Excel window. On the Insert tab, in the Illustrations group, click the Screenshot

button, and select Screen Clipping.

e. The computer screen will dim slightly, and the Excel window will minimize, showing you the Web

browser window. Click and drag to select the part of the screen image you want to add to the

worksheet.

f. When you release the mouse button, Excel becomes active again.

g. If necessary, click and drag the image to position it near the upper left corner of the worksheet.

h. Resize the screenshot image by selecting the resize handle at the lower-right corner of the image

and dragging toward the center of the image until the image is approximately the size shown in

Figure EX 7.45.

i. On the Picture Tools Format tab, in the Picture Styles group, click the Drop Shadow Rectangle

picture style. If this style is not visible on the Ribbon, click the More button to expand the gallery

and select the fourth style from the left in the top row.

17. Save and close the workbook.

18. Upload and save your project file.

19. Submit project for grading

Figure EX 7.45

Step 3

Grade my Project

Step 2

Upload & Save

http://www.census.gov/dataviz/visualizations/051/