TO ASK A QUESTION: If you have a question or need help with Office, please feel free to use the 'Click to Contact' link at the bottom of this page. You'll get a form that you can use to email a question to me. (I had been getting a ton of spam when accepting direct emails, so only emails that use this form will get through to me.)
Please be sure to mention the version of Office you are using when you send your question.
I answer all e-mails that I receive via this form, as long as they are polite :)
Since disabling comments on this site, I'm actually hearing from more of you with questions ... so, as it seems people prefer to email rather than comment, I'm going to leave comments disabled. As always, you can ask me any Office-related questions you have. If the question is outside of my expertise, I'll try to direct you to where you can get an answer.
Show the Data's Exact End Date on a Line Chart X-Axis (Excel)
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..."
TrackBack URL for this entry:
http://www.arouet.net/cgi-bin/mt2/mt-tb.cgi/14
