How do you make the best of a sparkline in Excel? Well I’ve decided to answer that question in this post. Excel sparklines are ideal for quickly plotting trends especially when you are looking at a data set / series or a report. There are several features that you may or may not already know about, but I will review the top 5 things that I feel will help you make the best use out of Excel sparklines.
Download the Workbook For Free Before We Begin
The Excel file is indeed free. When you click the ‘GET IT FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Build and Group Multiple Sparklines at Once in Excel
Instead of building each sparkline individually for a data set, you can select a destination range and then associate the data to build an entire set of sparklines. Excel is smart enough to realize which sets of data go with which destination cell.
How to Setup Multiple Sparklines at the Same Time
STEP # 1 ⇒ Select the range of cells at the end of your data set (could be below or to the very right of your current data set).
STEP # 2 ⇒ Go to the insert tab in the quick access ribbon then under the sparklines section select the line chart.
STEP # 3 ⇒ Next the create sparklines window will appear at which point you’ll select the data range you’d like to have displayed in a sparkline.
STEP # 4 ⇒ Click OK.
STEP # 5 ⇒ Enjoy the fruits of your labor. I know what you’re thinking, that was easy.
Use the “Same For All Sparklines” Option When Comparing Data Set Trends in Excel
When you are comparing similar sets of data, you want to make sure that you are comparing apples to apples. To ensure that your sparklines are providing an accurate visual comparison, you’ll need to ensure that you set the vertical and horizontal axis values to be the same for all sparklines. This will ensure that you’ll be able to easily tell which set of data is performing the best.
How to Select the “Same For All Sparklines” Option When Comparing Data Sets
STEP # 1 ⇒ Select the range of sparklines that you want to apply the same horizontal and vertical axis values to.
STEP # 2 ⇒ Now select the sparkline tools from the quick access ribbon. Under the group area, you’ll then see axis. Click on axis.
STEP # 3 ⇒ Under both the vertical axis minimum value options and horizontal axis maximum value options you’ll want to select “same for all sparklines”. You’ll need to do this in two steps.
Choose the Right Sparkline Chart Type to Display Your Trends
Using the right chart type to display your data trends is extremely important. This will ultimately allow you to more easily identify efficiencies or ineffeciencies in your processes by accurately reflecting visual meaning in your data. Let’s have a look at how to decide which chart type will be the best out of the three for our data set.
A View on How to Choose the Best Excel Sparkline Chart Type to Suit Your Needs
Use Line Charts When you have Data Sets that are Greater Than 12 Values
Below we have a data set that is greater than 12 data points so we’ve used the line chart to chronologically visualize the trend.
Use Column Charts for Data Sets that are Equal to or Less Than 12 Values
The column chart is a little more ideal for looking at trends or charts that are less than or equal to a total of 12 data points. Here’s a perfect example. Again this is somewhat of a discretionary view point but typically you won’t want to include more than that many data points in a column/bar sparkline.
Use the Win / Loss Chart Type When you Are Looking at Negative Versus Positive Values or Percentages
You can use the win / loss chart type when you are looking at changes in data points. For example below we are looking at the month over month change (delta) between our monthly quantity shipped.
Easily Identify Your Worst And Best Months Using Excel Sparkline High and Low Points
Being able to use the tools available to you can help save you a ton of time when it comes to analyzing your data. A great way to visually identify your worst and best months for a particular metric can be done by changing the color on your sparkline chart of the highest and lowest points (each having a different color of course). Let’s dive in.
How to Visually Identify the High and Low Points on Your Sparkline Chart
STEP # 1 ⇒ Select your sparkline chart / charts.
STEP # 2 ⇒ Go to the sparkline tools section on the quick access ribbon and then select from the show area high point and low point.
STEP # 3 ⇒ Go to the sparkline tools section on the quick access ribbon and then under marker color you can change the color of your high points and low points.
Note: You can also identify negatives points, starting point or ending point of your data set.
Merge Multiple Cells to Make One Large Excel Sparkline Chart
This is for some reason my favorite trick when it comes to sparklines in Excel. I think the reason for this is because it saves you the time from having to build a regular chart when you really only want to show a trend. The time to use this is when you don’t need all of the other junk that you typically see on a chart including axis names, axis numbers, legends etc… The process of doing this is really quite simple and painless. Let’s have a look.
How to Create a Large Sparkline Chart By Merging Cells in Excel
STEP # 1 ⇒ Select the range of cells where you’d like to build the merged sparkline chart.
STEP # 2 ⇒ Then from the home tab of the quick access ribbon click the merge and center button.
STEP # 3 ⇒ Now select the merged cell and from the insert tab on the quick access ribbon select and add your sparkline chart type.
STEP # 4 ⇒ Once you do this you may notice that there are large peaks and valleys in your trend. You can fix this by going to the ribbon and selecting sparkline tools, then axis, and then changing your minimum values and maximum values. And there you have it, the final product.
Quick Tips:
- Increasing your maximum value to something significantly greater than your highest data point will provide you with some space at the top of the chart to add notes or titles.
- Setting your minimum value to zero will smooth our your trend line or chart.
Conclusion
As you can see, using Excel sparklines is actually quite easy. There are quite a few things you can do with them as well to identify and look at your key performance metrics (i.e inventory trends, month to month changes, highest and lowest months etc…). To summarize here is what we learned.
- You learned how to build multiple sparklines at once instead of creating them one at time.
- You learned how to set your axis values to be the same for all sparklines. This allows you to easily compare multiple sets of data on the same scale.
- You learned how to choose the right sparkline chart type based on your data set and values.
- You learned how to find the high and low points of your data set to identify where you are under performing or over performing.
- Finally, you learned how to merge multiple cells to make one large sparkline chart.
I think that pretty much covers everything. These are some of the most simple ways to make the best of using a sparkline in Excel. Let me know in the comments if you have any other crazy and fun ways that you can or have used sparklines.
Other Resources and Sources
◊ Excel Chart Helper Infographic from My Excel Online – Source
◊ Microsoft 2010: Advanced Training – Video Course From Infinite Skills on Udemy.com (Affiliate) -Source/Resource
