Creating graphic-in-cell charts with Sparklines

- A relatively new feature in Excel charting is called sparklines. Think of it as in-cell charts. For example, we're looking at some numbers here on this worksheet called sparklines. It's like saying give me the big picture. I don't need a full-fledged chart, show me in this cell and this cell what's going on. What's happened over the last 12 months? Show me the overall trend. Recognize there are minuses here. You can begin by either highlighting the destination location of where you want these to appear. Or you could highlight the source data.

Either way it makes no difference. Let's suppose we highlight the source data for these sparklines. You'll find this feature on the charts tab and there are three kinds of Sparklines, even though it has the word "lines" in its name, there's a Line option, Column option, Win/Loss. Line is probably the most common. I'm going to choose Line here. And because I highlighted the source data, it asked me to select the destination data. "Select where to place sparklines." It doesn't necessarily mean we have to highlight cells adjacent to the data, but often that does make good sense.

So we're putting them in column N, and clicking OK. And because the trend, either that dramatic or because column N is relatively wide, that might not make much of a statement. So a couple of options here. I'm going to make column N narrower. Another option might be, I'm going to select rows 3, 4, 5, 6, and make them taller, maybe twice as tall. Now, you're not always going to be doing that when you create trend lines, but it depends upon the data. That certainly is an option.

Does this tell the story sufficiently enough to get the point across? It might. It could be helpful here if we had an axis line to indicate that the values have crossed zero. In other words some are negative, some are positive. So selecting these, recognize something else that's occurred. In the ribbon menu system, we've got a Sparklines tab, contextual tab that appears only when trend lines are selected. Sparklines. Lots of options here about changing the color of the line if we wish. We can go to the option out here for Sparklines and maybe change the color there, make it blue, make the lines thicker, maybe.

Weight, maybe that helps a little bit. We can put Markers at every point. Let's tackle the Axes first, and simply Show the axis. And I think that would be helpful in this context. Again, consider making the column wider, or the rows taller. We can also add Markers. You could put in just Markers for the High points, although it's fairly obvious in this case. Highs and Lows both. Or maybe neither. Why not just put Markers on All points? That might look a little crowded, but again it could emphasize the data in a different kind of way.

So you've got some flexibility here in how these are being displayed. Let's also consider the fact that because these cross negatives, Column charts might be better. So we have a Column Sparkline. Again, available from the contextual tab, Column, we see the information displayed this way. Now the mistake you don't wanna make here as you're looking at these, focus on California for a bit, and recognize the highest column there. It's the last one. Look at the Texas Sparklines. The second last column is the highest.

But what numbers are being reflected there? The high value for California is 548. The high value for Texas is 473. To my eye, the Texas column looks a bit higher. So you don't want to be comparing actual column heights on these. And there are no numbers associated with them that you might get in a regular chart. The focus for the most part tends to be within the row here. We're looking across. We're seeing how this is reflected. So think of them as being treated one by one.

Now you can also put Sparklines in different locations, and I could even put them down here in a different kind of way. This would be a little bit unusual but we can do this too. Here I'm selecting the destination area first. I'm going to go to the Charts tab in the ribbon, select Line, and the source data, this time it's asking me to fill in, so it's these cells right here. Now data actually doesn't flow from region to region. So line choice here probably is not the best. Nevertheless we will see this.

And I'm immediately going to change that to be Column. And here too, we might want to consider making the row taller. So what are we seeing now? In this list here, and do recognize we're seeing values below zero, we're simply seeing the data for California, Texas, Florida and New York, left to right. So, in situations like this, if you were making a presentation, I think you might have to pause and explain to your audience what's being shown. So there is some merit to this, but I think the better example is the one we see over in column N.

Another option here too, if you're simply concerned with, "Did we make money, did we not? "Was it profit or loss?" And this works best in the cells over here. Simply change to the third option under Sparklines, which is Win/Loss. And it's simply a yes/no kind of display. And we see quickly what's going on here. So we didn't have any negative months after April, although we had, through all the different states here, negative months early on in the first quarter. Down here too.

Consider the possibility of Win/Loss. And here it's gonna look quite a bit different. We see what's happening. So, everything's positive across these months here. But over here, we've got that mix of positive and negatives. So the idea is, we don't want a full-fledged chart sometimes, just give me the big picture quickly. We can create these Sparklines. And again, sometimes you'll get involved in the fine tuning of these, but for the most part these are easily and quickly created. And they do give us a quick read on the data.