Excel Bubble chart risk map with labels - please help not working in 2016

Question

Hi I am attempting to make a similar bubble chart (or ANY chart) to what was posted here:

Can this 5-attribute 2D risk map be built in excel?

Problem is, it doesn't seem to work in Excel 2016. Seems like the bubble chart is NOT ignoring the #N/A values. It plots all three low, medium, and high series instead of just the rows in the series with values. Excel seems to be using the cell ROW reference as the X-value... makes no sense why it would do that when it literally requires you to specify a range of x-values in the data series.

Additionally, I also couldn't seem to get the second series to work either with the "numbers." I want each bubble to be numbered with MY column "B" so I know which bubble matches which line in the chart. When I follow the advice in the link all it does is add another bubble on top of the other one. No "#" value... and data labels does not allow me to change it as suggested.

Anyone know how to get this to work? Or, any suggestions on how to better plot these? Seems crazy such a simple task is so complicated in Excel. My only alternative seems to be to add EVERT SINGLE ROW as it's own series. That would be extremely time consuming and still doesn't solve the label problem! Is there some way to do this in power pivot/chart now?

Thanks!

Answer 1

Don't select entire columns for your chart data. Select just the relevant ranges.

When the chart sees H:H for X values, it notices the text label in cell H1. Since it can't plot text as a numeric value, it treats all X values as text labels, even the #N/A values it would normally not plot. If you change H:H to H2:H50 (or whatever row) and change the other ranges accordingly, the chart will work as expected: bubbles with #N/A for X (or Y) values will not be plotted, and plotted bubbles will appear at the expected X position.

Then you can add data labels to each series, and use the Value from Cells option, and use range B2:B50 for the labels.