Creating Charts and Graphics in Microsoft Excel 2013
- 3/31/2014
- Creating charts
- Customizing the appearance of charts
- Finding trends in your data
- Creating dual-axis charts
- Summarizing your data by using sparklines
- Creating diagrams by using SmartArt
- Creating shapes and mathematical equations
- Key points
Summarizing your data by using sparklines
You can create charts in Excel workbooks to summarize your data visually by using legends, labels, and colors to highlight aspects of your data. It is possible to create very small charts to summarize your data in an overview worksheet, but you can also use sparklines to create compact, informative charts that provide valuable context for your data.
Edward Tufte introduced sparklines in his book Beautiful Evidence (Graphics Press, 2006), with the goal of creating charts that imparted their information in approximately the same space as a word of printed text. In Excel, a sparkline occupies a single cell, which makes it ideal for use in summary worksheets. As an example, suppose Lori Penor wanted to summarize the monthly revenue data for one of Consolidated Messenger’s local branches.
)
Lori can create three types of sparklines: line, column, and win/loss. The line and column sparklines are compact versions of the standard line and column charts. The win/loss sparkline indicates whether a cell value is positive (a win), negative (a loss), or zero (a tie). To create a line sparkline, you select the data you want to summarize and then, on the Insert tab, in the Sparklines group, click the Line button. When you do, Excel displays the Create Sparklines dialog box.
The data range you selected appears in the Data Range box. If the data range is not correct, you can click the Collapse Dialog button to the right of the Data Range box, select the correct cells, and then click the Expand Dialog button. Then, in the Location Range box, enter the address of the cell into which you want to place your sparkline. When you click OK, Excel creates a line sparkline in the cell you specified.
)
You follow the same basic procedure to create a column sparkline, except that instead of clicking the Line button in the Sparklines group on the Insert tab, you click the Column button. To create a win/loss sparkline, you need to ensure that your data contains, or could contain, both positive and negative values. If you measured monthly revenue for Consolidated Messenger, every value would be positive and the win/loss sparkline would impart no meaningful information. Comparing revenue to revenue targets, however, could result in positive, negative, or tie values, which can be meaningfully summarized by using a win/loss sparkline.
To create a win/loss sparkline, follow the same data selection process and click the Win/Loss button.
Months in which Consolidated Messenger’s branch exceeded its revenue target appear in the top half of the cell in blue, months in which the branch fell short of its target appear in the bottom half of the cell in red, and the month in which the revenue was exactly the same as the target is blank.
After you create a sparkline, you can change its appearance. Because a sparkline takes up the entire interior of a single cell, resizing that cell’s row or column resizes the sparkline. You can also change a sparkline’s formatting. When you click a sparkline, Excel displays the Design tool tab.
)
You can use the tools on the Design tool tab to select a new style; show or hide value markers; change the color of your sparkline or the markers; edit the data used to create the sparkline; modify the labels on the sparkline’s axes; or group, ungroup, or clear sparklines. You can’t delete a sparkline by clicking its cell and then pressing the Delete or Backspace key—you must click the cell and then, on the Design tool tab, click the Clear button.
In this exercise, you’ll create a line, column, and win/loss sparkline, change the sparkline’s formatting, and clear a sparkline from a cell.
SET UP
You need the RevenueSummary workbook located in the Chapter09 practice file folder to complete this exercise. Open the workbook, and then follow the steps.
Select the cell range C3:C14.
On the Insert tab, in the Sparklines group, click Line to open the Create Sparklines dialog box.
Verify that C3:C14 appears in the Data Range box. Then, in the Location Range box, enter G3 and click OK. Excel creates a line sparkline in cell G3.
Select the cell range C3:C14.
On the Insert tab, in the Sparklines group, click Column. The Create Sparklines dialog box opens again.
Verify that C3:C14 appears in the Data Range box. Then, in the Location Range box, enter H3 and click OK. Excel creates a column sparkline in cell H3.
Drag the right edge of the column H header to the right until the cell’s width is approximately doubled. Excel displays more details in the sparkline.
Select the cell range E3:E14.
On the Insert tab, in the Sparklines group, click Win/Loss. The Create Sparklines dialog box opens again.
Verify that E3:E14 appears in the Data Range box. Then, in the Location Range box, enter I3 and click OK. Excel creates a win/loss sparkline in cell I3.
With cell I3 still selected, on the Design tool tab, in the Style gallery, click the right-most sparkline style. Excel changes the win/loss sparkline’s appearance.
Click cell G3 and then, on the Design tool tab, in the Group group, click the Clear button and then click Clear Selected Sparkline. The sparkline disappears.
CLEAN UP
Close the RevenueSummary workbook, saving your changes if you want to.