|
I keep several on-going records of numerical data and the relevant date, using Microsoft Excel 2010, and I then plot graphs of the data against the date. The problem (well, annoyance really) is that when I want to format the date axis the axis options given are in some cases in normal date form (such as 17/01/2012) and sometimes the axis options given are in number form, such as 40925. I know that the number is the number of days since the beginning of the year 1900, but knowing that doesn’t make it any less irksome. The question is …….. how can I get the axis options to always be in normal date form?
|
|
Try formatting the cells to say they contain dates.
|
|
or put a ' before the date to indicate alphanumeric data
|
Excel 2010 chart, to get x-axis in date format, according to Microsoft:
" Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.
On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Horizontal (Category) Axis.
On the Format tab, in the Current Selection group, click Format Selection.
In the Format Axis dialog box, under Axis Options,
To change the axis type to a text or date axis, under Axis Type, click Text axis or Date axis. Text and data points are evenly spaced on a text axis. A date axis displays dates in chronological order at set intervals or base units, such as the number of days, months or years, even if the dates on the worksheet are not in order or in the same base units.
Note: Selected by default, Automatically select based on data determines the axis type that makes the most sense for your type of data. "
|
>> Excel 2010 chart, to get x-axis in date format, according to Microsoft:
>>
It looks as if Microsoft's explanation refers to a different version of Excel 2010 to the one I've got. I can get to the "format axis" stage merely by right clicking on the axis and then selecting "Format Axis". "Axis Options" starts off with Minimum (auto or fixed), Maximum (auto or fixed) and similarly Major Unit and Minor Unit, all of which are (in some cases) already in the dreaded number form. "Number" gives a choice of 12 categories and I select "date". This makes the chart x-axis show as dates but unfortunately the "Axis Options" list still remains in number form.
|
>> Is your "axis type" selected to be based on "Automatically select based on data" or
>> "Text Axis" or "Date Axis".
In all cases except one, the box I see doesn't give me these options. You only seem to get these options when the dates are shown in date form.
The dates in Axis Options that I see are in number form.
What I do is this ...........
I prepare the spreadsheet on Sheet 1 with the dates in column A. The numerical data I put in column B. I format the cells in column A as dates. I produce the chart on Sheet 2. When I go to Format Axis, the dates in the Axis Options part of that menu are shown in number form.
|
>> The dates in Axis Options that I see are in number form. >>
Oh well. time to contact MS on their official help forum.
>> I produce the chart on Sheet 2. >>
Just to satisfy my curiosity, would you please try and see what happens if you draw the chart on Sheet 1 and report back.
|
>> Just to satisfy my curiosity, would you please try and see what happens if you
>> draw the chart on Sheet 1 and report back.
Exactly the same.
|
If you care to anonymise your data, save the spreadsheet as sample1 or whatever, and upload it somewhere for us to download to have a look at it, it may then be possible to trace where the process is going wrong for you.
|
|
I think I've found the answer as far as future workbooks/charts are concerned. I took the one and only chart that had the Axis Options in normal date form and saved it as a template. It may be a bit of a fudge but I'll see how it goes.
|
|
I'm beginning to think that Excel 2010 needs repairing or reinstalling.
|
Microsoft Excel 2010 question - rtj70
