I have an Excel 2016 worksheet with three tables:
- Table with X coordinates (multiple rows and multiple columns)
- Table with Y coordinates (same number of rows and columns than X)
- Table with labels (same number of rows and columns than X)
I need to make a scatter plot, and the tables are too large to manually add each series to the chart. Each position on the X table has the value Y and label on the same position in the tables Y and Label.
I need to plot a single serie, with all the X, Y and label data.
My plan is to turn the tables into three columns (a column each for X, Y, and label), using some Index function, and use the chart feature in the classic way.
But is there a simpler/more practical solution?
41 Answer
Select the Y values, and insert the chart. It will use your Y values and dummy X values (1, 2, 3, etc.).
Right click the chart, and choose Select Data.
Select the series in the list, and choose Edit. The box for 'Series X Values' is blank. Click in the box and select the X values. It doesn't matter if they are not right next to the Y values.
Finally, click the '+' icon next to the chart and check Data Labels. You'll get default labels that display the Y values.
Select the data labels and press Ctrl+1 to open the Format Data Labels task pane. Under Label Options, click the bar chart icon, then click on Label Options. Under Label Contains, check the box for Value From Cells, and select the range that contains these values. Then uncheck the other Label Contains boxes.
2
