How to draw a 'sane' scatter plot in Excel?
You mean like this?
In Excel, a series usually consists of multiple data points and gets its own entry in the legend. Since you want each data point to get its own entry in the legend, you can simply create multiple series, each with only one data point.
To reproduce the above image, create a scatter plot, as usual. Ensure the chart is selected, then go to Chart Tools -> Design tab -> Select Data
. You should see this dialog box:
For each row in your table, add a new series, with the series name, x, and y values like this:
For the chart axis titles, you can add them as usual, select them, then in the formula bar select the cell with the label you want, then press Enter.
For people with the same problems, there is a usefull tool named FunFun, which allows people to use web languages like Html, Css and JavaScript in Excel.
Javascript has a lot of powerful libraries for data visualization, like Charts.js and D3, that you can use with this Excel add-on to create any chart you want.
If you want to test out different libraries, Funfun hosts an online editor with an embedded spreadsheet, where you can see the output of your code instantly.
I made this chart with Chart.js on the FunFun website that I directly loaded in Excel by pasting the link of the playground below:
https://www.funfun.io/1/edit/5a2959d1cf76561801b73403
Here are some screenshots(Website to Excel Add-in):