Have you ever had a data sheet in Excel and wanted to quickly see the trend in the data? Perhaps you have test scores for your students or income from your company over the past 5 years, and instead of creating a chart in Excel that takes time and ends up consuming an entire worksheet, several small mini charts in one cell would be better.
Excel 2010, 2013 and 2016 have an interesting feature called sparklines that basically lets you create mini charts within a single Excel cell. You can add sparklines to any cell and keep them close to your data. This way you can quickly visualize the data line by line. This is another great way to analyze data in Excel.
Before we start, let’s take a quick look at what I mean. In the data below, I have ten store revenue in the last six quarters. Using sparklines, I can quickly see which stores are increasing revenue and which are performing poorly.
Obviously, you have to be careful when viewing data using sparklines because it can be confusing depending on what numbers you are analyzing. For example, if you look at Store 1, you can see that revenue has dropped from $ 56K to about $ 98, and the trendline is going straight up.
However, if you look at Store 8, the trendline is very similar, but the revenue only grew from $ 38K to $ 44K. Therefore, sparklines do not allow you to see the data in absolute terms. The graphs you create only relate to the data in that row, which is very important to understand.
For comparison, I created a regular Excel chart with the same data, and here you can clearly see how each store compares to the others.
In this graph, Store 8 is almost a flat line compared to Store 1, which still has an upward trend. This way, you can see how the same data can be interpreted differently depending on how you choose to display it. Basic charts help you see trends between multiple rows or data, while sparklines help you see trends in a single row of data.
I should point out that there is a way to tweak the parameters so that sparklines can be compared to each other. I’ll explain how to do this below.
Create a sparkline streak
So how do we create a sparkline? It’s really easy to do this in Excel. First, click the cell next to the data points, then click Insert and then select Line, Column, and Win / Loss under Sparklines.
Choose any of three options depending on how you want to display the data. You can always change the style later, so don’t worry if you’re not sure which one works best for your data. The Win / Loss type really only makes sense for data with positive and negative values. A window should appear asking you to select a data range.
Click the small button on the right and select one row of data. After selecting the range, press the button again.
Now click OK and your sparkline or tiny chart should appear in this cell. To apply a sparkline to all other rows, simply grab the bottom-right edge and drag downward as if you were using a cell with a formula in it.
Customize sparklines fonts
Now that we have our sparklines, let’s set them up! First, you can always increase the size of the cells to make the graphs larger. By default they are quite small and difficult to see properly. Now click on any cell with a sparkline and then go to the Design tab under Sparkline Tools.
Starting from the left side, you can edit the data if you want to include more columns or fewer. In the Type section, you can change the type of the mini chart. Again, Win / Loss is for positive and negative data. In the Show section, you can add markers to the graphs such as High Point, Low Point, Negative Points, First and Last Point, and Markers (a marker for each data point).
In the “Style” section, you can change the style of the graph. Basically, it just changes the line or column colors and allows you to choose the colors for the markers. To the right of it, you can customize the colors of the sparkline and markers.
The only other important aspect of sparklines is the axis parameters. If you click this button, you will see some options such as the minimum vertical axis options and the maximum vertical axis options.
If you want sparklines to refer to all other rows, not just a single row, select Same for all sparklines under both headings. Now when you look at the data, you will see that you can compare charts in terms of absolute values. I’ve also found that viewing bar charts makes it easier to view the data when comparing all sparklines.
As you can now see, the columns in Store 1 are much higher than the columns in Store 8, which had a slight upward trend but with much lower revenue. The blue bars are the low and high points because I checked these parameters.
That’s all there is to know about sparklines. If you want to create a beautiful Excel spreadsheet for your boss, this is the best way to do it. If you have any questions, do not hesitate to leave comments. Enjoy!