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!

GET IT FREE!.

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.

 

Insert multiple Excel cell sparklines at once line chart

 

 

 

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.

 

same for all sparklines axis option excel 2010 / 2013

 

 

 

Choose the Right Sparkline Chart Type to Display Your Trends

 

sparklines chart type line winloss or column

 

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.

sparkline line chart trend 1

 

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.

sparkline column chart trend

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.

 

sparkline win loss chart trend

 

 

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.

 

high point and low point of data set for sparkline chart

 

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.

 

merging cells to make one large sparkline 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

 

 

Email Newsletter

Learn How to Solve Your Business Problems Using Excel.

Get exclusive access and updates to Excel tools, tutorials and content that will help you become the office hero!

You have Successfully Subscribed!