2

I have created a load of sparklines in Excel for a variety of diseases over time. However, in order to identify trends from these sparklines, it would be useful to include a little more information about the x axis than what a sparkline shows. What I need is some indication of time on the x axis so that users can work out if there is, for example, a seasonal trend in these diseases. All I can think of is to make a fake static x axis at the top of the sparklines column. What makes it tricky is the fact that the data for these sparklines are in a pivot table, and the idea is that the pivot table and sparklines would change as more data is added to the spreadsheet, so I can't just add a static x axis.

Although I am currently using Excel, I am open to the idea of using other programs if they better fit the needs of this task.

Improve this question

2 Answers 2

Reset to default
1

Perhaps you could textually indicate the time range in a cell above the sparklines. A formula like the following might work:

="From "&TEXT(MIN(A1:A100),"mm/dd/yyyy")&" to "&TEXT(MAX(A1:A100),"mm/dd/yyyy")

where A1:A100 is the column of dates in your source data. You could make the formula dynamic (i.e., so that it adjusts itself accordingly as new data is added) either by making the source data an Excel table, making the source data a dynamically defined named range, or by adjust the formula with something like the following:

="From "&TEXT(MIN(INDIRECT("A1:A"&COUNTA(A:A))),"mm/dd/yyyy")&" to "&TEXT(MAX(INDIRECT("A1:A"&COUNTA(A:A))),"mm/dd/yyyy")

Without knowing the layout of your data, it's hard to get more specific.

Improve this answer
1
  • Thank you! I included a formula to add spaces in the middle so that the dates were at either end of the cell: =TEXT(MIN(A1:A100),"dd/mm/yyyy")&REPT(" ",CELL("width",B1)-1/3.5*CELL("width",B1))&TEXT(MAX(A1:A100),"dd/mm/yyyy")
    – Abbie
    Commented Sep 16, 2014 at 9:04
1

I don't believe there is a built in way to do this (except plotting it as a chart) but Mr. Excel has a video here about adding labels in the cells surrounding. It looks like it takes a bit of fiddling but the results are alright.

Note in the video his video is often over the cells he works with but his spoken instructions seem to be easy enough to follow.

Video Here

Improve this answer
1
  • That's a brilliant video (despite putting his face over what he's doing!). I didn't end up using these tricks but they are very useful to know for the future :-)
    – Abbie
    Commented Sep 16, 2014 at 9:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .