Tuesday, April 12, 2011

Format column sparkline charts by using the data and merge cells

Sparklines are great, but there may be times that 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) had the events of downtime in only four of the twelve months. Here'sSparkline datadata: the data, I created this column sparkline.Sparkline with general horizontal axis (default)Okay, but it does not highlight the fact that there are no events in January and February and none from June to November. This is because there is no axis Data. I can remedy this axis setting of an option for the sparkline horizontal (or x-) by selecting the option date axis type . Here is the option in Excel (just make sure your sparkline is selected):Horizontal axis options for sparklinesselect date axis type and in the Sparkline Date range dialog box that appears, I'll be sure to select the cells that contain dates (not the data itself). Now, you can see the sparkline exhibition space that reflectsSparkline with date horizontal axismonths missing:, but I'd like to make a few more things: I want the sparkline largest, which extends through the four columns of data, and I also want my axis labels for the columns under horizontal sparkline.I do this by selecting the cell that contains the cell, and then the sparkline next three cells to the right with it. I'll use the command Merge through lengthen the sparkline on B3: E3. Now it looks likeSparkline merged over 4 cellsthis: Finally, I'll add text labels for March, April, may and Dec under the sparkline. I type "Mar Apr may" (separated by spaces) in cell B4, which is under the three-column sparkline, and use the command Merge across to merge the cell C4. You type the "Dec" in cell E4, under the last column sparkline. I will make them smaller and bold font (I used 8 pt) and then put the text in the cell by adding or removing spaces between Mar, Apr & may, until they line up nicely under the three-column sparkline. There!Sparkline with labels in cells belowFor more information about sparklines, see using sparklines to show trends in data. Look for more blog posts about sparklines here via the keyword "sparklines".

View the original article here

No comments: