scatter/points-only-line chart on a single series, but different shapes of points?

Question

I have a data set that looks like the table shown on the left. The data has 3 columns, ranked by values in column 3; column 1 is the index of the row in the ranking; column 2 is the 'category' assigned to that row.

I am not sure if this is a 'single series' or 'two data series' problem... but I would like to draw a chart where, each value in column 3 is shown as a point on the chart referenced to the left Y-axis, but the shape of that point should be determined based on the label next to that value, in column 2.

Can I do this using scatter point chart/point-only-line chart? I tried to do this but it plots all points on the chart with the same shape, I am not sure how to use column 2 to change the shape of the points..

I would be grateful for any advice!

Many thanks in advance

UPDATE

I followed bandersnatch's 2nd suggestion and have got quite close to it but I still have some problems. Testing on the dummy data it works perfectly. but when working on the real data, it appears that those empty cells are considered as a '0' value and as you can see on this screenshot below, these are plotted as well and making an incorrect chart:

I am not sure how to fix this? I already made sure that empty cells are shown as 'gap', see screenshot below. I have shared my data file for anybody interested to play with, at: https://drive.google.com/file/d/1L32urXmkYoA0WZDSXTuBS_UMM3a__bMn/view?usp=sharing

Thanks again

Answer 1

You could edit and change the marker style for just those two points. But that causes some difficulties with the labels in the legend.

A better solution is to arrange your data table as shown below. That causes Excel to plot the data as two separate series, and then you can format the two series separately as you wish.

EDIT: If the data is generated by formulas, and you want to skip plotting a point, for example where the blank cells are in the table above, have the formula return NA(). The formula might look like this:

=IF([some condition],[some value],NA())

This puts #N/A into the cell, and the chart ignores the point. See my recent tutorial Plot Blank Cells and #N/A in Excel Charts for more details.

Answer 2

A better solution would be to:

1. Create an Excel Data Table from your existing data.
2. Create a Pivot Table from your Data Table (which will update as you add rows to your Data Table).
3. Create your chart from the Pivot Table (which will also update as you add rows to your Data Table).

Answer 3

I was able to do this as a single series scatter plot, 1st column vs. 3rd column, where I set the markers of the series to 'None'.

To get the correct markers I used data labels where the label contained the value from the second column. I placed the labels on the 'Center' position. For the second column I replaced the A and B by appropriate shapes from the Wingdings font.

Note: also in the graph then you need to set the font of the labels to Wingdings.

Note 2: If you want to keep the A's and B's also, you could make a 4th column where you create the label based on the A/B column with a simple IF statement (=IF(B1="A","l","n") -- "l" and "n" in wingdings font are circles and squares)

A two-series will also work without the trick with the labels but then you need to rearrange your data such that you have uninterrupted sections of A's and B's.