Sparkline data range

Question

I was tinkering with sparklines yesterday and discovered a downside to the whole thing.  It seems that if you don't have a congruent dataset (A2:A10) but instead an incongruent dataset (A2,A4,A6,A8,A10) it doesn't like you anymore.  Mind you I have the Beta version of Office 2010.  Was this fixed in the released version or was this by design or is it just lame that way?

Answer 1

I think it's just made that way.

If you could hide the cells that you don't want included (A3, A5 etc) then you could base the sparkline on A2:A10 and use the option not to show data in hidden rows.  Otherwise I guess you need to make a range containing cells linked to the alternate cells in your original range and build the sparkline from the new range which could be on a hidden sheet.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

I'm runnig 2010 RTM (not beta) and that is the behavior. 

1.  If there are non-numeric entries or blanks  in the interviening cells you can highlight the entire range and it will work. 

2.  If you can hide the rows you don't want the sparklines to plot, then you can highlight the entire range and it will work.

3.  Now for the real trick: In 5 adjacent cell create 5 sparklines, one for each value A2, A4,... then select all five sparklines and choose Sparkline Tools, Design, Axis, then pick Custom Value under Value Axis Minimum... and set this to whatever minimum you want.  Repeat that for the Value Axis Maximum. 

---

If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Thanks Bill.  If my data were laid out that way I would certainly use that exact solution.  Unfortunately I can't so this is kind of a bummer.