In Excel 2010, 2013, and 2016, there is a feature called sparklines that let you create mini-charts inside a single Excel cell. You can add sparklines 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.
In the data below, I have revenue from ten stores over the last six quarters. Using sparklines, I can quickly see which stores are increasing revenue and which stores are performing badly.
However, if you look at Store 2, you see that revenue went from $56K to about $128 and the trend line is going down. This is because Store 2 was doing better than Store 1 in terms of sales volume but the trend line is going down because it’s not moving in the same direction as revenue.
Store 8 saw a revenue increase from $38,000 to $44,000, but the trend line is very similar between the two stores. This means that sparklines don’t allow you to see the data in absolute terms. Graphs created with sparklines are just relative to the data in that row, which is important to understand. ..
For comparison purposes, I created a normal Excel chart with the same data and here you can clearly see how each store performs in relation to the others. ..
This chart shows how the same data can be interpreted in different ways depending on how you choose to display it. The regular charts help you see trends between many rows or data and the sparklines let you see trends within one row of data.
There is a way to adjust the sparklines in the image so that they are compared to each other. I’ll mention how to do this down below.
Create a Sparkline
In Excel, you can create sparklines by clicking in the cell next to your data points and then choosing between Line, Column, and Win/Loss under Sparklines.
Option 1: Choose the Win/Loss type to display data in a table. Option 2: Choose the Win/Loss type to display data in a graph. Option 3: Choose the Win/Loss type to display data in a text box.
Select one row of data and then click on the button again.
To apply the sparkline to all the other cells, just grab the bottom right edge and drag it down just like you would a cell with a formula in it. ..
Customizing Sparklines
There you will find a variety of tools that you can use to customize your sparklines. The first tool is the size slider, which you can use to increase or decrease the size of your cells. You can also change the color of your cells, as well as add text to your sparklines. The next tool is the opacity slider, which allows you to control how much light is shown in your graphs. You can also adjust the brightness of your graphs, and make them more or less visible. The last tool is the font slider, which allows you to choose a different font for your sparklines.
You can choose to have a mini chart with more columns by clicking on the Type drop-down menu and selecting “Columns.” You can also change the color of the bars and lines. The Win/Loss is meant for data with positive and negative numbers. You can also add markers to the graphs like High Point, Low Point, Negative Points, First & Last Point and Markers (marker for every data point). ..
Under Style, you can change the styling for the graph. Basically, this just changes the colors of the line or columns and lets you choose the colors for the markers. To the right of that, you can adjust the colors for the sparkline and markers individually. ..
The Axis options are important because they let you control how sparklines look. If you click on one of the options, you’ll see some more options called Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options.
If you want to make the sparklines relative to all the other rows instead of just its own row, choose Same for All Sparklines under both headings. Now when you look at the data, you’ll see that you can compare the charts in terms of absolute values. I also found that viewing the charts in column form makes it easier to see the data when comparing all sparklines.
Store 1’s columns are noticeably higher than Store 8’s, but their revenue values are comparatively small. The blue columns indicate where the stores’ data points fall on a scale from low to high; these points are important because they can help us understand how the stores compare. ..
Sparklines are a way to make fancy looking Excel spreadsheets for your boss. They’re not very useful on their own, but they can be used to make other charts and graphs look nicer. If you have any questions about sparklines, feel free to post a comment below! ..
