# Excel: how to automatically sort scatter plot (or make proportional X-axis on line chart)

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 ? (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

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. 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) • Thank you. Really interesting. – AdriZ Feb 18 at 13:51

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 then select "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. .