Back
Excel

Format column sparkline charts using the date axis and cell merging

Sparkline charts are great, but there may be times they need a little visual massaging to maximize their usefulness. Consider this scenario: You have a system for which you want to track downtime over the course of a year and (fortunately) it had downtime events in only four of twelve months. Here’s the data:

 


Sparkline data

 

Below the data, I created this column sparkline.

 

Sparkline with general horizontal axis (default)

 

That’s fine, but it doesn’t highlight the fact that there were no events in January and February, and also none from June through November. This is because there’s no date axis. I can remedy this by setting a sparkline option for the horizontal (or x-) axis by selecting the Date Axis Type option. Here’s the option in Excel (just make sure your sparkline is selected):

 

Horizontal axis options for sparklines

 

I select Date Axis Type and in the Sparkline Date Range dialog box that appears, I make sure to select the cells that contain the dates (not the data itself). Now, you can see the sparkline shows the empty space that reflects the missing months:

Sparkline with date horizontal axis

 

But I’d like to do a couple more things: I want the sparkline bigger, stretching across the four columns of data, and I also want labels for my horizontal axis below the sparkline columns.

I do this by selecting the cell containing the sparkline cell and then selecting the next three cells to the right along with it. I’ll use the Merge Across command to stretch the sparkline out over B3:E3. Now it looks like this:

 

Sparkline merged over 4 cells

 

Finally, I’ll add text labels for Mar, Apr, May, and Dec under the sparkline. I type “Mar Apr May” (separated by spaces) in cell B4, which is under the three sparkline columns, and use the Merge Across command to merge that cell with C4. I’ll type “Dec” in cell E4, under the last sparkline column. I’ll make their font bold and smaller (I used 8 pt), and then fine-tune the text in the cell by adding or removing spaces between Mar,  Apr, and May until they line up nicely under the three sparkline columns. There!

 

 Sparkline with labels in cells below

 

To learn more about sparklines, see Use sparklines to show data trendsLook for additional blog posts about sparklines here by using the keyword “sparklines.”  And check out the blog post on our eight best tutorials on Excel charts.

 
– Gary Willoughby
More info on Excel charts: