Jump to content


sparkline not recognizing data

sparkline missing data gap

  • Please log in to reply
1 reply to this topic

#1 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 06 September 2016 - 03:09 PM

Another project, another problem.  I had been successfully using sparklines as you can see in the attached excerpt (the data wasn't confidential, but I deleted it anyway for efficiency).


Row 15 has a gap, and it looks to me like the sparkline isn't recognizing the data in the cell with the gap nor the following cell.  A15:K15 is selected as source data, and "show empty cells as gaps" is checked.


Row 16 also has a gap, but the sparkline looks fine.  I last worked on this a year ago; what did I do right then that I'm doing wrong now?


Thank you yet again!

Attached Files

#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 06 September 2016 - 04:28 PM

Hi Jenny,


The reason this is happening is that a sparkline can only draw a line between two data points if there are two data points in a row to draw a line between.


In the case of row 15, there is an empty space and then only a single item of data, meaning there is no end point for a line to be drawn to.


The solution is to enable Markers for your sparklines. This means that markers will appear for each value instead of lines alone, and will allow you to see the lone data point in row 15. The default markers can be a little hard to see, so you may want to customize them to be larger or to appear in a different color.


You can see a complete walkthrough of adding markers to sparklines in the video lesson: Essential Skills Lesson 4-20 Apply a common vertical axis and formatting to a Sparkline group.


I have also attached a copy of your workbook with the markers applied:


Attached File  sparkline problem.xlsx   205.99KB   194 downloads

sparkline problem.png


I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users