Excel – How to create dynamic Scatter Plot/Matrix with labels and categories on both axis in Excel 2010

axischartsmicrosoft excelmicrosoft-excel-2010

Let us consider a following data set:

Name   |  Age  |  Hair Color
----------------------------- 
John   | Young | Brown
Sophie |  Old  | Blond
Adam   | Mature| Blond
Mark   |  Teen | Dark
Jeremy |  Old  | Grey
Alex   | Young | Brown

etc...

Both Age and Hair Color, can take only defined values(Young/teen/mature/old and Blond/brown/Dark/Grey). Name is the only real variable here.

I want to create a Scatter Plot / Matrix that will look something like that:

The desired chart

I know that I schould use this tool to add labels to the scatter plot.

I also found this youtube video that explains how to display categories on Y-axis

Moreover I need the chart to be dynamic as explained in another youtube video.

How do I combine all these approaches to get a Scatter Plot with categories as values on both axis?

Best Answer

Excel (and the Chart Labeler add-in) can create this type of chart, with a fair bit of creativity.

enter image description here

Here's how:

  1. Layout your data. You'll need a number of helper columns to accomplish this.

xy_data

  1. First, you'll need to convert your categorical data into numbers, then plot those numbers on your chart. In this sample, that happens in the X_Age and Y_Hair columns.

  2. Use the Chart Labeler Add-on to add your labels for your data points. In this case you had two overlapping labels (Alex & John) that had to be manually adjusted, but you could use VBA to do this for you.

  3. Add a data series for your X Axis labels. They're the three X Label columns in the data sample (X values, Y values and point labels). Then, use the Chart Labeler Add-on to add the labels below the points.

  4. Add a data series for your Y Axis labels. They're the three Y Label columns in the data sample (X values, Y values and point labels). Again, use the Chart Labeler Add-on to add the labels to the left of the points.

  5. Finally, you'll need to do a ton of format work, especially cleaning up the axis min/max values, removing the default axis labels, etc...

  6. To make these dynamic, you'll just need to create a named range for each data series (column), using Count and Offset to make them grow with your data. Then use the range names in place of the series cell references in your Chart series formulas.

EDIT:

For risk analysis (or anything else where you're doing quadrants), set your values to be negative to positive and you can use Excel's regular axis for your quadrants. Also, add another helper data series to pull your High Risk (or other values you want highlighted) and you can format those to draw attention.

Risk_Quad

For more on highlighting values in a scatterplot, check out my answer to this question: Highlight Scatterplot Values

Related Question