Creative use of sparklines in merged cells with axes

- [Instructor] If you're about to create a visual image of data on a worksheet sometimes you'll create a chart. I've got some data in columns A and B covering a two year period from mid-2015 into mid-2017, and since the data in question here is surrounded by empty cells and worksheet boundaries I could simply click anywhere within this data and press alt + f1 to quickly get a chart. Now, that may or may not be a great looking chart. I'm not too fond of it myself, but it does tell a story in a certain way. Sometimes a better alternative is what we call a spark line, and we see one here already in existence for the data right here.

Let's talk about how to create this. You might not be familiar with it. I'm going to eliminate what we see in K3. Before doing that, notice that this is a visual representation of these numbers. I don't think it's exactly obvious unless it's pointed out to you, but nevertheless that is a depiction. It's a spark line, which for want of a definition could be an in-cell chart. I'm going to right click this and simply the spark line, clear it, and show how to create it. The idea might be we don't want a full fledged chart.

Let's imagine for the moment we're just working with this data right here. We want a quick visual on the screen, and maybe we'll put it off to the right, insert tab in the ribbon. There are three kinds of spark lines despite the fact that it's got the word "line" in the name. Line, a spark line, or simply as a win-loss meaning it would show positives and negatives in different displays, so let's say we choose line here, and immediately we're asked for the source data, data range. Highlight the data right here and okay, and nearly always if you're using spark lines and you've got negative data you will want to go to its contextual design tab once this has been created, go to the access option, and show the access like this.

Now, you'll find it at many times that spark line doesn't quite give you everything you want. This could be better if we choose a different color. I'm not going to go too far with that one. We could do that, but also as we go to spark line color we can change the weight, meaning the thickness of the line, because that might or might not be helpful. The other thing we could do too, although the data to the left might look a little funny for a bit. We could make this taller or we can make it wider, and so there are some situations where that's totally appropriate for the data at hand. Also in the contextual design tab up above you can see there are markers we could add those, but I want to point out here another technique we can use and with larger amounts of data too where we can actually merge a cell like this along with data to make its use much more valuable, so I'm going to press control + z a few times here, and let's create a spark line for the data over here.

Now when the data is oriented vertically, initially it seems as if a spark line is not going to be a good choice, and by the way when you are creating spark lines you can highlight the relevant data first if you wish. I want this data to be represented by a spark line. It's highlighted, I'll go to the insert tab, and this time I'll choose line, spark line option, and the data range is already selected, but the location range here I'm going to put on the bottom and click okay. There it is. I could make the column wider, I could make the row taller.

That's going to be helpful, and once again going to spark line color, changing the weight of it, use that thickness perhaps, maybe add markers. The wider we make this, the better it is. On the other hand though, that's showing the data from left to right, reading top down. That doesn't quite fit, so here's another approach. It does involve transposing the data, so let's say we take this data right here, and I'm going to transpose this onto a separate sheet, so that we're not confused with the data that's already here, so with this data highlighted I'm going to copy it, either right click and copy, or press control + c, go to a new worksheet right here, click in the upper left corner, I'll press control + alt + v.

That's the same as paste special. You could also right click and go to paste special, and then transpose, click okay. That doesn't look too promising at first. Part of the reason is these labels here that we're seeing take up a good deal of horizontal space, whereas the numbers below don't. So, let's go to the home tab here, and in the alignment section you've got a group here for aligning the data counter-clockwise, angling it that way, or maybe better yet in this one, rotate the text up.

I'll drag the boundary between one and two downward like that. There we are. Let's put a spark line right here, and what we're going to do at least initially is not going to be that great. Insert line, the data range here, these numbers here in the background, okay, but I'm going to move this and put it below the first entry over on the left hand side, and then merge this with the other cells to the right. From the home tab, merge and center, and to make this stand out, let's use some of those features available in the contextual design tab first of all.

Spark line color, the way we did before, change the weight of this, make it a bit thicker, perhaps add the markers, but even more important let's make this row taller, maybe three times as tall, something like that, and now the points that we see here are in sync with the data from above, and zooming in a bit more to make this even more prominent, and perhaps here giving a fill color addition to the background, that might help as well too. I think this is a much better use of the spark line feature. We merged the spark line from one cell into all these cells across.

Another option worth exploring here on the design tab is instead of line, how about column? Now, you might say this took a bit longer than creating a chart. It did, but on the other hand I think it's got some readability aspects to it. If it's a situation to where the data, even though it's historical, is still being adjusted over time, you can see the effect of the adjustment here too. Now again, I'm not saying this is better than a standard chart. Once again, if you wanted to create a standard chart quickly, you could for example highlight, you could highlight all this data right here, press alt + f1 and resize and move this around a little bit and it too could be in sync with the data, depending on how wide you make this and so on.

I still think the spark line options here gives us a better quick visual than the standard chart does, but again, don't forget that possibility of merging a spark line cell with other cells to the right to be in sync with the data. A third option not as great, but at least one worth exploring is the win-loss option. This simply accentuates the positives and the negatives this way, and so we can see that advantage too, so column and line, different ways of using the spark line capability to bring out the data in a visual way.