Ever had a worksheet of data in Excel and quickly wanted to see trends in the data? Maybe you have some test scores for your students or your company’s revenue from the last 5 years and instead of creating a single chart in Excel, which takes time and ends up with an entire sheet, some Small chart in one box would be better.
Excel 2010, 2013 and 2016 have a cool feature called sparklines that basically allow you to create a mini chart inside an Excel cell. You can add a sparkline to any cell and keep it right next to your data. This way, you can quickly visualize data on a row-by-row basis. It’s just another great way to analyze data in Excel.
Before we get started, let’s go through a quick example of what I mean. In the data below, I have revenue from ten stores for the past six quarters. Using sparklines, I can quickly see which stores are growing in sales and which are underperforming.
Obviously, you must be careful when looking at data using sparklines as it can be misleading depending on the numbers you are analyzing. For example, if you look at Store 1, you see that sales have increased 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 sales are only between $38K and $44K. So sparklines don’t let you see the data in absolutes. The graphs are generated relative to the data in that row only, which is important to understand.
For comparison purposes, I went ahead and created a normal Excel chart with the same data and here you can clearly see how each store performs compared to the others.
In this chart, Store 8 is a flat line relative to Store 1, which is still a trending upward line. So you may see the same data that can be interpreted in different ways depending on how you choose to display it. Regular charts help you see trends between multiple rows or data, and sparklines let you see trends within a single row of data.
I should note that there is also a way to tweak the options so that the glitter charts can also be compared with each other. I’ll cover how to do this below.
Create a curved line
So how do we create a sparkline? In Excel, that’s really easy. First, click the box next to your data points, then click Insert and then choose between Row, Pillarand Winning Below Polylines.
Choose from any of the three options depending on how you want the data to be displayed. You can always change the style later, so don’t worry if you’re not sure which style will work best for your data. The Winning type will only really make sense for data with positive and negative values. A window will pop up asking you to select a data range.
Click the little button on the right and then select a row of data. Once you’ve selected the range, go ahead and click the button again.
Now click OK and your sparkline or minigraph will appear in that one box. To apply the sparkline to all other rows, simply grab the bottom right edge and drag it down like you would a cell with a formula in it.
Customize the bends
Now that we have our sparklines, let’s customize them! Firstly, you can always increase the size of the plots to make the graph larger. By default, they are quite small and difficult to see precisely. Now go ahead and click on any cell with a line and then click Design tab below Sparkline Tool.
Starting from the left, you can edit the data if you want to include more or less columns. Below Type, you can change the minigraph type you want. Again, Win/Loss means data with positive and negative numbers. Below Showsyou can add markers to charts like High score, Bad score, Negative points, The first day & Last The point and Marker (marker for every data point).
Below Style, you can change the style of the chart. This basically just changes the color of the row or column and lets you choose the color for the markers. To the right of it, you can adjust the colors for the sparkle lines and individual markers.
The only other important aspect of sparklines is Axis option. If you click that button you will see some options called Vertical axis min value option and Vertical Axis Maximum Value option.
If you want to create sparklines relative to all other rows instead of just its own row, select Same for all bends under both headings. Now when you look at the data you will see that you can compare the graphs in absolute value. I’ve also found that viewing the histogram as a column makes it easier to see the data when comparing all the sparklines.
As you can see by now, the columns in Store 1 are much higher than the columns for Store 8, which tend to increase slightly but with a much smaller turnover value. The light blue columns are low score and high score because I checked those options.
That’s all there is to know about sparkle charts. If you want to create a fancy looking Excel spreadsheet for your boss, this is the way to do it. If you have any questions, feel free to post a comment. Interesting!
