Create sparkline charts

- [Instructor] Sparklines, which were invented by Edward Tufte, and detailed in his book Beautiful Evidence, summarize data in a compact word-sized chart, that fits inside a single worksheet cell. You can create three kinds of sparkline charts Excel; Line, column and win/loss. In this movie I will show you how to create them, and point you to the formatting tools you can use to change their appearance. My sample file is the Sparkline Workbook, and you can find it in the Chapter 7 Folder of your Exercise Files Collection.

This workbook contains a series of monthly results. I have the actual revenue in column B, the target revenue against which revenue will be compared in column C and the difference. Lets say that I want to summarize revenue as a line chart, but I need to make it very small. So I'll make a line sparkline. I will start by selecting cells B2 through B13, and then on the insert tab of the ribbon, I will click the sparklines button, and click line.

Doing so displays the create sparklines dialogue box, and I can select the data range. I just did that. B2 through B13 so it appears within the box. And now I need to select where I want to place the sparkline. I will drag the dialogue box out of the way, and with the cursor flashing in the "Select where to place sparklines" box, I will click cell F2. And when I click okay I get my chart. And you can see that it is summarizing the values I selected, B2 through B13.

We start low at 122, go up to 270, down to 145 and so on. You can also create a column sparkline, so I will click in cell G2, and then select cells B2 through B13 again. Then I'll go back to the insert tab, click the sparklines button, and then click column. The create sparklines dialogue box appears as before, but this time, with the cursor flashing in the "select where to place sparklines" box, I will click cell G2 for column, and click OK.

And there you can see a version of the graph that I created before, but instead of a line graph it's a column graph. And the smallest values have the shortest columns. So you can see the 122 looks a lot like zero, when compared to the next value for February of 270. Now lets look at a win/loss sparkline. Win/loss is an indication of whether a value exceeded, equaled or exceeded zero. So you have positive values, zero and negative values.

I will select cells D2 through D13, and you can see that values in column C were just used to compare revenue versus target. The result appears in the column D. So I have selected D2 through D13, then I'll go to the insert tab, click sparklines, and click win/loss. The data range is selected, and "select where to place sparklines", in this case I'll just click cell H2 because I can see it. Click OK and we get the sparklines.

You can see that there are three negative values, and if I look the first one January and then ninth one for September and the eleventh one for November are all red, or negative numbers. And if I look at June, which is the 6th, I see that it's zero and it's blank. All the other numbers are positive, meaning that revenue exceeded the target so those indications are blue. While I have the sparklines selected, I'll point you to the sparkline contextual tab at the ribbon which appears.

You can see that you have a gallery of different formats that you can apply. And clicking the down arrow allows you to select from all those. Lets say that I want to go to red and looks like dark gray there. There we go, that's for win/loss. And I can also change the sparkline color. So if I change the theme color, I'll change to light blue. You can see that it changes the top, and the marker color, I'll change that one for this one, in this case negative points.

I will make them a medium yellow. There we go. You can do similar things for you axes. And finally if you want to delete a sparkline, select the cell that contains the sparkline and then on the sparkline contextual tab, go all the way to the right side, click the clear button, and you can either Clear Selected Sparklines, or Sparkline Groups. In this case I will just clear the selected one, and it goes away. Sparklines are relatively new in Excel. They are a great tool and are especially useful if you create a dashboard worksheet where you need to summarize a lot of data in a compact space.