In this video, we’re going to look at the use of sparklines for charts in Excel. The sparklines aren’t exactly a chart. Instead, they are an in-cell visualization that’s often used to represent trends, and high and low points and changes over time on a series of data. They’re very, very easy to use and they are extremely efficient in telling the story of the series of data that you have. I have some sample data here that we are going to look at using sparklines with. We have some return on investment types. So we’ve got two groups of investments here in our sample data.
We’ve got property investment and we’ve got bond investments, and we’ve got the return per month for each of the investments within the group. What we’re going to do is we are going to insert some sparklines so we can visually see how this data actually looks. Sparklines can be found on the insert tab, under sparkline. So we have line, column, and then we’ve got win and lose. And we’re going to home in on the line and the column in this video. Sparklines can be created on groups of data with ease, as well as a single series of data. So let me show you what I mean by that. I mentioned that there were two groups.
There is a property and a bond group, and if we select all of the data for the group, and if we go to insert and insert our line chart, our line sparkline, this gives us this new dialog box to create a sparkline. We’ve selected our data range. So now, it’s looking for our location range and we want to place these within the cells right beside the data. So I’m going to select these cells here so we see that O3 to O7 is selected. I’m going to say okay, and very quickly, we have a line chart placed within the cell. So this isn’t a separate chart that you can drag, and you can drop, and you can move around.
Instead, this is an in-cell object. Now, because it’s an in-cell object and not a chart, if you press delete, it doesn’t actually work for removing your sparklines. So if you do want to remove your sparklines, you need to select from the new sparkline ribbon that will appear. You will need to select clear selected sparkline and because I only had one selected, it would select the clear, but if we select clear sparkline groups, it will delete the entire group. Now, it’s important to know that when you set these up, that when they’re set up in groups, everything happens to the entire group. So let’s select this data again and we will go to our insert and we will insert our line.
Again, I’m going to select the location range and say okay. If we select one of these, you’ll see that the blue line appears around showing that it selected an entire group. So if we wanted to modify one of these, we couldn’t modify it on its own, the way it is at the moment. Instead, if we were to modify using the sparkline style, so let’s say for example we were to change color, the entire group changes color. Now, if you wanted to make one of the sparklines different to the others, what you need to do is actually ungroup them.
So to ungroup them, if you select one particular item and say ungroup, that will ungroup this item from the rest of the group but we’ll see then the rest of the group actually remains intact. To ungroup all of them, you need to select the group and then select ungroup and then each one will individually be ungrouped. You can regroup them then by selecting group. Now, grouping them is a great idea because it means that you can modify them all at the same time. So like I’ve shown you there, you have the chart styles, the sparkline style, so you can pick between the different sparklines.
Now, you can see here that they do give a really good representation of how things went. For example, we can see this property investment one remained consistent for two months and then a drop down and up, and down, and up and down and a little bit back up in the end. But at the end, it was down to the start of the year. [END]