Join Dennis Taylor for an in-depth discussion in this video Creating in-cell charts with sparklines, part of Excel 2016 Essential Training.
- Sometimes you want a visual to accompany data, but you either don't have room for a chart, or don't want to go to the trouble of creating a chart and bring about all the elements of it and how you want it to look and so on. You want a quick picture of the data in question. I'm going to select this data right here. This is on a worksheet called Sparklines in the file 07 - Charting. When you select data like this, the Quick Analysis button appears. Let's click it and choose Sparklines. Now sparklines, not exactly a misnomer, but sparklines can be lines or columns or win/loss.
And as I slide over these, look in the background. You'll see what's about to happen. Line is probably the most commonly used. I'll select it right now. That gives us a picture of what's been happening. It's a line chart of this data. And here's a line chart of the next row and so on. We see that. We can make changes to this to make it stand out even more strongly, we wish. As we select this data, the contextual tab appears in the ribbon. It's a Design tab with some features associated with sparklines. Lots of choices here for colors; maybe that looks better, maybe not.
We can change not only the sparkline color, but also the weight of it, meaning the thickness. Maybe it'll look better if it's a bit thicker. We also have control over showing all the markers, we can do that, or possibly just the high point. In this case it's at the end in each case. The low point, not only is at the beginning but there it is there. We see some options here for controlling the display of this. Off to the left, and we saw this earlier, this could look better as a column, could look better as a win/loss. One of the downsides of column, by the way, as you look at the different heights here, don't be comparing different rows.
In other words, you might be comparing them for trend purposes, but don't assume the height of the column is proportional. If I'm looking at all these columns at once here, I'm thinking that maybe the last column in all cases is the tallest, and it probably is for each set of data, but the last column here in row 4, represents 156. The column above it, that looks to be almost the same size, is almost 300. They're not proportional as you look up and down here. So it makes sense only to be comparing the data for a given row at a time.
Now the data down below has negatives in it. We can create sparklines in the same way, but there's another way to create sparklines. You can begin by either highlighting the data, or by simply highlighting the location where you want the sparklines to appear, for example, here. And then on the Insert tab on the ribbon, there's the Sparklines section. Choose Line, and we've already selected a location range but not the data range. Click in this white panel, highlight the data, and OK.
Similar chart, looks ok, but we do have in this case, data below zero. Strongly recommend showing the axis here. Now if we show an axis in the previous set of sparklines, nothing would happen but here it does. And I think it's important to show it here. If we went up here, and even if we changed it back to line, we cannot show an axis here because nothing goes below zero. So see what's happening. But here it does. And here too, you might consider the option of going to Sparkline Color, changing the weight of this to make it a little bit thicker, and consider how that looks.
Now, I'm going to zoom in on this a little bit. But also do something with the column width. Column N -- I'm going to make it narrower. That makes these look perhaps steeper, a little more erratic, off to the right, it flattens it out a little bit. I could also -- this could be an attention-getter I don't necessarily want. If I select rows 10 through 13, and drag the row boundary of any one of these downward, say this one, maybe that's twice as tall, that's a different look.
Now it may or may not be better. Are we trying to hide something? Are we trying to accentuate it more? So the whole idea here is, if you adjust column widths and/or row heights, you tell a different story and that's worth thinking about at least at certain points when you're displaying data. So you've got an axis line in these examples. How might this look as a column sparkline? Keep these highlighted, let's go back to the Design tab here, off to the left, choose Column. That might bring out the fact that the data's crossed zero a little better.
Here's line, here's column. Win/Loss maybe even tells it better, but it doesn't give us any sense of volume. It's just negative or positive, win or loss. That's it. And I think you can see with different kinds of data, these are appropriate. Makes a difference sometimes. I think it's always worth exploring the different looks here. My favorite here might be column, because it brings out the negatives more strongly than line does. Line might show the trend a little bit better, but even here I think you can see the trend. The data is a bit erratic here, as it is sometimes with certain kinds of data and that's just the nature of data.
But once again, look at the options here. I've made the rows taller. If I were making a presentation, what if I had done that ahead of time? People wouldn't necessarily be aware of it. Another thing I might do here is simply take this data and on the Home tab center it this way. Maybe that de-emphasizes the idea that I've made the rows taller. But you can explore that a bit. Let's go back to a more normal setting. Click in the upper left corner. Double-click a row boundary, we're back to here. If I just select column N, and make this narrower, you'll see what's happening that way too.
So you've got some room to explore. These are called sparklines, they were introduced in Excel 2010. Sometimes they're simply described as in-cell charts. Pretty fair description of this feature. Easy to get to, maybe sometimes just the easiest way, highlight the data, and you'll see the little button here, and choose Sparklines that way. Now, one other option here that you want to take a look at with a different set of data that's organized a little bit different. This is more vertically oriented. If we select this data, and use that Quick Analysis button, and choose Sparklines, what happens here? We're not seeing a preview in the background.
It simply doesn't handle the data. But if you wanted to show the data, and I'm saying ahead of time, this is not going to look that great, how might this data look as a sparkline? I can either select the data first, or the sparkline location first. I've done the data here, Insert tab, Sparklines, Line, there it is. Where do we want the sparklines? Click in this panel, click cell S11, right below this, click OK.
Now, I wouldn't say that's a great choice because it's below the data, but I think it could be used in some scenarios. So keep in mind you have the ability to put this where you want. And here too, as we did with the previous versions, Sparkline Weight, make it a little bit thicker, usually makes it look a little bit better. Possibly put in the axis, although in this case it doesn't go negative so that wouldn't help. Here too, maybe the column version could be better. So, you've got options here, in-cell charts, by way of what are called sparklines.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros