Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sparklines, which were invented by Edward Tufte and detailed in his book "Beautiful Evidence", summarize data in a compact word-size chart that fits inside a single worksheet cell. There are three different kinds of sparklines that you can create in Excel: line, column, and win/loss. In this movie, I'll show you how to create them and point you to the formatting tools you can use to change their appearance. The line and column sparklines are simply smaller versions of line and column charts you've seen elsewhere in this course. Win/loss is a little bit different, but I'll show you how to work with it when we get there.
So the first question is, how do you create a sparkline? Well, to do that you first click the cell where you want the sparkline to go, and then, on the Charts tab of the Ribbon, in the Insert Sparklines group, you click a button for the sparkline you want to create; in this case, it will be a line. So now from within the Insert sparklines dialog, I can go into the body of the worksheet and select cells A2:A13. That reference appears in the dialog. And I want to place the sparkline in F1-- that was the cell that I had selected before.
I can click OK, and Excel creates this chart. Line sparklines illustrate trends, and this type of sparkline is wonderful if you're looking for a quick summary for data in a dashboard. For example, if you have an average sales figure for the last year, you can use this chart to provide a bit of trend. It shows you the ups and downs, and you can look at it and get a sense of when you were up and when you were down. The column sparkline is very similar. You can use the same type of data. To create it, you'd click the chart where you want it to go--in this case F3-- and then on the Charts Tab of the Ribbon, click Column--and this is the column on the Insert sparklines group.
Click Column, select the range for the sparklines, we're going to use the same data, A2:A13, the reference is correct, and we're putting it in cell F3. Click OK. So now we have the third type of sparkline, and this is called a win/loss sparkline. Win/loss indicates whether you have a positive value, a negative value, or a zero value. So let me show you how to create it. First, you select the cell where you want it to go, and then on the Charts tab of the Ribbon, in the Insert sparklines group, you click Win/Loss, and it's exactly the same as before.
You're selecting the data range for your sparkline, so that's C2:C13. You notice that I have a mix of positive and negative values. It's going into F5. Click OK, and there you have the sparkline. And the blue value, which is above the equator of the cell, is a positive value. It means, for example, here for 585, that we met our target. And any red value, which happened in months two and three, so you have -98 and -265.
Now, what would happen if we had a 0 value? Well, for that, Excel puts a blank in that space of the sparkline. So just to show you, let's say that instead of having 1,727 visitors, we had exactly 1,800, which means that this cell here will contain the value 0. When I press Tab, we get a 0 in the worksheet, and you can see that we have a blank instead of either a blue box or a red box inside cell F5 with our win/loss sparkline. Before I close, just a quick note about formatting sparklines: to format them, you click the cell that contains the sparkline, and then on the Sparklines contextual tab, you'll see that you have a bunch of tools, including Styles, so that you can change the appearance of your sparkline.
You can add markers for your data, if you are using a line or a column chart, and also you can even change the sparkline's type. If you want to make a sparkline larger or smaller, all you need to do is resize its row or column. So, for example, if I wanted to make my sparkline a little bit larger, I can just grab the bottom edge of the row header, drag it, and it makes it larger. If I want to make it wider, I just grab the right side of the column header, drag it to the right, and it's larger.
Sparklines are a terrific addition to Excel 2011. They are by far my favorite new feature, and they are especially useful if you create a dashboard worksheet where you need to summarize a lot of data in a compact space.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.