« Finding Generosity in Unexpected Places | Main | Why Do Objects Flip Over? (PowerPoint / Office) »

If you've ever created a line chart comparing data across time (for example, investment banks commonly do this to show the price\volume history for a given stock) -- you know that the last date that appears on your x-axis scale may or may not be the last date in your data.

You can fight with the scale, skipping categories trial and error to get what you need ... and I've also seen some frustrating workarounds like drawing a text box for the correct date over the last x-axis label (or worse, text boxes for the whole x-axis!)... but getting genuine exact start and end dates to appear is actually very easy to do, without trial or error!

First: use a scatter line rather than a line chart. This will give you the type of x-axis you need (a Value axis) to provide exact calculations for your dates. Then, you just need to grab a couple of numbers and one simple calculation:

1. Copy the first and last dates in your data each to another worksheet cell, and change the number format for those two newly pasted dates to 'General' (Format, Cells, Number). The dates will now appear as numeric values.

2. In a separate cell, perform the following calculation on these two cells: the last date minus the first date, divided by one less than the number of x-axis labels you want on your chart. That is:

= (last date - first date) / (desired number of x-axis labels - 1)

3. On your chart, double-click the x-axis to open the Format Axis dialog box, and then select the Scale tab. Enter the following values:

For the Minimum, enter the numeric value for your data's first date. For the Maximum, enter the numeric value for your last date. In the text box labeled Major Unit, enter the result of your calculation in step 2. (If this result goes to several decimal places, be sure to include them. You may need to include as many as seven or eight decimal places to get a precise result.)

That will do it!

Of course, if you happen to be creating a price\volume chart - as mentioned earlier - you will have a couple of other steps to account for the fact that the column series needs a category axis. Post a comment or drop me an e-mail (e-mail link at the bottom of the blog's main page) if you'd like these instructions posted. Meanwhile, if you own a copy of MODD, you can find step-by-step instructions in the How Do I tip sheet "Advanced Chart Types..."

Posted by Stephanie

TrackBack URL for this entry:
http://www.arouet.net/cgi-bin/mt/mt-tb.cgi/11