Inserting Sparklines

Starting in the 2010 version, Excel has a type of line chart called Sparklines and Sparklines are little miniature charts that actually fit into the cells of the worksheet. Now you might not always want a whole table or you might not always have room for one. So Sparklines might fit the bill for you. Well, let's check it out. First, click anywhere in the data are, it doesn't matter and Sparklines like any other chart are things that we insert. So go to the Insert tab. And over here to the right of the Chart section, we have Sparklines.

Now click Line and the Create Sparklines dialog-box comes up. First thing we have to do is define the Data Range. So delete whatever is in here and let's start with the first number here. I'm not going to select the column headers. It's just the numbers. I'm just going to drag over. Now you could drag all the way down if you want. I'm just going to press Ctrl+Shift+Down Arrow key. It's just a much easier and faster way of selecting and scroll up. So now we have the Data Range. And now click here in the Location Range. And the Location Range is where do we want the Sparklines to go? So let's start over here in H5.

Now here we have to drag down if we press Ctrl+Shift+Down Arrow. Now there's new place for it to start and let's go ahead and let's scroll back up. So click OK. And boom! It just inserts those Sparklines and just click somewhere inside the Sparklines so you can get a better look. Now when you do that you have the Sparkline Tools in the Design tab showing up on the Ribbon bar. Before we change anything there, let's just take a look at the column itself, Column H. If we want these lines to be flattened out a little bit more, you put your mouse pointer here on the right border of Column H. Your mouse pointer turns to a two headed arrow.

Now you can click and drag out to the right and those lines flatten out. If you want them more compressed, just drag them back and now the lines are a little bit more craggy. I'm just kind of drag in a little bit to the right, a happy medium there. Well, we can do some formatting here. First, let's decide what kind of thickness we want for the lines. Go over here to the Sparkline Color. Choose Weight. And yes I know that's confusing. Why is Weight in with the Color tab? I don't know. It just is. And I'll choose the heavier line so that they stand out a little bit more.

As far as Markers go, we can display High Points, Low Points, First, Last Points and so on. So let's go over here in the Show tab and maybe choose High Point and Low Point. And now we can see with each one there's a High and Low. If you want, you can go here into Marker Color. Go over here to Markers. And we can change them as well, make them a little bit more visible. If you don't care about High Point and Low Point, maybe you just want First and Last. I'll turn those off and I'll turn on First and Last Points. And we can see those also. And I'll just turn off those Markers.

It really depends on what you need. There is no right way or wrong way. Well, even though these are called Sparklines, we also have a column variety. So over here click Column. And now we can see kind of little miniature column charts. And this might be a little bit more visible of where the high numbers are and where the low numbers are. Well, speaking of low numbers, you'll notice that some of these numbers here are negative. And here's another one. Wouldn't it be nice if we could make those negative numbers really stick out like a sore thumb? When you're looking at the data here, it's kind of hard to tell.

So click somewhere in the Sparklines, and again if you need to go back to the Design tab and choose Win/Loss. Right now, it's still kind of hard to tell. But let's click on the Style dropdown. Maybe choose a dark theme and go over here to Marker Color. Click that and for negative points, let's choose a light color. I'll choose a light green. And now we can see those negative numbers really sticking out. And you can see over here Negative numbers are turned on. And if you want, you can always turn them off. And that green goes away. Turn them on, the green comes back.

So the next time you need to create some charts to display just a general overview and you don't want to fuss with the whole charting package, try using Sparklines. It can save you a lot of time.