1

In Excel, I have a table with "years" column not sorted (it's a calculation result) and a "value" columns and I want to draw a chart of these values.

Scatter plot xy require ordered x numbers.

Line chart doesn't plot x values proportionally. How can I do ?

Exemple of my table with scatter plot and line charts

(I use MS Excel 365)

  • I think if you right-click on the axis labels and choose "Format Axis", then select "Date Axis" from the Axis Options, you will get the desired result – cybernetic.nomad Jan 8 at 18:39
  • Yes it works in the line chart ! Thanks a lot ! I will add your answer, with details, if you're agree. – AdriZ Jan 9 at 9:34
  • Careful: the line chart with a date axis ignores the fractional parts of the X values. – Jon Peltier Jan 14 at 4:08
  • Indeed. Not perfect but acceptable for my usage. – AdriZ Jan 14 at 10:27
1

A few formulas will fix the data for you. Columns A and B contain your original data. Columns C thru F have formulas in these cells, copied down as far as needed:

C2: =RANK(A2,A$2:A$12,1)+COUNTIF(A$2:A2,A2)-1
D2: (not formulas, just values 1, 2, 3, etc.)
E2: =INDEX(A$2:A$12,MATCH($D2,$C$2:$C$12,0))
F2: =INDEX(B$2:B$12,MATCH($D2,$C$2:$C$12,0))

Just plot columns E and F in your chart.

Sorted Data

If you have the new Dynamic Arrays, it's even easier. Columns A and B have the original data. These formulas are only entered into the indicated cells, and they fill values down automagically:

C2: =SORT(A2:A12)
D2: =SORTBY(B2:B12,A2:A12)

Sorted Data with Dynamic Arrays

|improve this answer|||||
  • Thank you. Really interesting. – AdriZ Feb 18 at 13:51
1

A solution is to use the line chart specifying x-axis as a "date axis".

Double click the x-axis, on the side bar choose axis options icon axis options icon then select "Date axis" Date axis.

Drawback of this method: the fractional parts of the X values are not displayed.

Specifying the number format of X-axis labels with "decimal places: 0" gives me an acceptable result.

an acceptable result.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.