Excel 2010 Chart with Date
Solved
schpop12
Posted messages
51
Status
Membre
-
rogerbontemps -
rogerbontemps -
Hello
I am currently working on a document with numerous line charts with markers. The x-axis corresponds to dates.
To create my chart, I selected my data table -> insert chart -> line with markers
The chart creates automatically without any issues.
My problem is that the spacing between my points is fixed and does not correspond to the interval between my dates. When I go to axis options, Excel indeed does not take the dates into account.
I change my chart type to "line," but it's still the same.
What’s weird is that if I do insert chart -> simple line, and select my data one by one, it works well there. But I have too much data to redo everything.
Do you have any ideas or tricks to force Excel to take the x-axis as dates?
I am currently working on a document with numerous line charts with markers. The x-axis corresponds to dates.
To create my chart, I selected my data table -> insert chart -> line with markers
The chart creates automatically without any issues.
My problem is that the spacing between my points is fixed and does not correspond to the interval between my dates. When I go to axis options, Excel indeed does not take the dates into account.
I change my chart type to "line," but it's still the same.
What’s weird is that if I do insert chart -> simple line, and select my data one by one, it works well there. But I have too much data to redo everything.
Do you have any ideas or tricks to force Excel to take the x-axis as dates?
10 réponses
Hello,
In Excel 2003, you select a chronological axis in the chart options.
It seems that the terminology has changed a bit with 2010. We now talk about text or date axes.
https://support.microsoft.com/en-us/office/change-the-scale-of-the-horizontal-axis-in-a-chart-637897f6-0d51-4ec5-bef9-25d2c83a8450?ocmsassetid=hp010342254&correlationid=17a1986f-f661-4193-9297-1db2016bf89c&ui=en-us&rs=en-us&ad=en
In Excel 2003, you select a chronological axis in the chart options.
It seems that the terminology has changed a bit with 2010. We now talk about text or date axes.
https://support.microsoft.com/en-us/office/change-the-scale-of-the-horizontal-axis-in-a-chart-637897f6-0d51-4ec5-bef9-25d2c83a8450?ocmsassetid=hp010342254&correlationid=17a1986f-f661-4193-9297-1db2016bf89c&ui=en-us&rs=en-us&ad=en
Hi
I just did the manipulation and unfortunately Excel still maintains a fixed interval between two points. It doesn't take into account that they are dates.
As a result, I have the same gap between two measurements spaced one month apart and two others spaced one year apart.
This is important to me, especially when I need to calculate a trend.
I just did the manipulation and unfortunately Excel still maintains a fixed interval between two points. It doesn't take into account that they are dates.
As a result, I have the same gap between two measurements spaced one month apart and two others spaced one year apart.
This is important to me, especially when I need to calculate a trend.
1- Check that the dates are correctly recognized as dates by Excel. When a cell contains 13/01/14 and you switch to Standard format, it displays 41652 (the number of days since 1/1/1900).
2- Join a significant but anonymous excerpt from the file:
go to Cjoint.com Upload the file Create the link Copy the link in the next message.
2- Join a significant but anonymous excerpt from the file:
go to Cjoint.com Upload the file Create the link Copy the link in the next message.
Hello
You might consider opting for the "scatter plot" type, which will allow you to have a real scale on the horizontal axis
Regards to tontong by the way
Best regards
You might consider opting for the "scatter plot" type, which will allow you to have a real scale on the horizontal axis
Regards to tontong by the way
Best regards
Thank you for the response
1- the dates are indeed taken into account as such
2- here is an example of the sheet http://cjoint.com/data/0AnpQE8CTq8.htm
on the last two lines I deliberately entered 2020 (instead of 2013) as the year, to clearly see the issue;
I am working in "curve" precisely because the Microsoft help indicates that it is the only mode to offer a chronological axis.
I would like to clarify that if I start from scratch and insert a chart -> curve and enter the values manually it works. But I have more than 40 tables with 6 entries to redo. I would like to understand what is blocking, since they are the same data and the same type of table?
Thank you for your responses anyway.
1- the dates are indeed taken into account as such
2- here is an example of the sheet http://cjoint.com/data/0AnpQE8CTq8.htm
on the last two lines I deliberately entered 2020 (instead of 2013) as the year, to clearly see the issue;
I am working in "curve" precisely because the Microsoft help indicates that it is the only mode to offer a chronological axis.
I would like to clarify that if I start from scratch and insert a chart -> curve and enter the values manually it works. But I have more than 40 tables with 6 entries to redo. I would like to understand what is blocking, since they are the same data and the same type of table?
Thank you for your responses anyway.
Excel tries to plot a chart and adjusts when the values are not suitable!
The whole issue comes from the fixed values for the X axis scale.
They are currently set to 1900.
You should set the minimum to 1/1/2010 and the maximum to 1/11/2013 in the axis format, using the month as the unit and 1/1/2010 as the intersection with Oy.
The whole issue comes from the fixed values for the X axis scale.
They are currently set to 1900.
You should set the minimum to 1/1/2010 and the maximum to 1/11/2013 in the axis format, using the month as the unit and 1/1/2010 as the intersection with Oy.
Hi
On the file I uploaded, can you change the X-axis?
On my end, I don't have the min/max setting. Neither in date form nor in number form.
It just gives me the setting between graduations.
I don't understand anything.
On the file I uploaded, can you change the X-axis?
On my end, I don't have the min/max setting. Neither in date form nor in number form.
It just gives me the setting between graduations.
I don't understand anything.
Oh man, I finally found what's wrong with the automatic generation of the chart.
You'll notice that in line 4 there are empty cells, that's the name of my series.
To create the chart I select from B4 to I52.
It creates my chart with the name of my series just fine, except that it's not chronological (it doesn't consider the X as dates) and I don't have the axis option.
Now I make the same chart, but by taking only my values from B5 to I52. My chart is created automatically just the same, except that my series have generic names (series1, series2,...). But the big difference is that the chart is chronological and the X-axis is finally parameterizable as a date.
I really struggled with this at first. Is this a bug or am I just not creating my chart correctly?
Anyway, thank you for your help.
You'll notice that in line 4 there are empty cells, that's the name of my series.
To create the chart I select from B4 to I52.
It creates my chart with the name of my series just fine, except that it's not chronological (it doesn't consider the X as dates) and I don't have the axis option.
Now I make the same chart, but by taking only my values from B5 to I52. My chart is created automatically just the same, except that my series have generic names (series1, series2,...). But the big difference is that the chart is chronological and the X-axis is finally parameterizable as a date.
I really struggled with this at first. Is this a bug or am I just not creating my chart correctly?
Anyway, thank you for your help.
Hello,
I generally use Excel 2003 and to open Xlsx files I go through a converter. When I give you an indication it is "2003" or general. That explains a bit of the hassle. I cannot describe the exact path with the exact terms.
To add a series name, it is possible to define a cell reference.
The path is something like:
Tab Create >> Select Data Source >> Edit Series.
I generally use Excel 2003 and to open Xlsx files I go through a converter. When I give you an indication it is "2003" or general. That explains a bit of the hassle. I cannot describe the exact path with the exact terms.
To add a series name, it is possible to define a cell reference.
The path is something like:
Tab Create >> Select Data Source >> Edit Series.