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:
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?
Excel (and the Chart Labeler add-in) can create this type of chart, with a fair bit of creativity.
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
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.
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.
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.
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...
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.
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.
For more on highlighting values in a scatterplot, check out my answer to this question: Highlight Scatterplot Values