Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
I'm sorry if detracts from your question, but why would you want to use a scatter plot to display this kind of nonnumerical data? I don't understand. –  Excellll Oct 10 '12 at 14:22
    
@Excellll I.e. risk analysis. I have risks(names) with frequency categories(age) and impact categories(hair color). Those that display in upper right part of the graph are a huge threat –  user1581900 Oct 10 '12 at 15:31
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
Thanks a lot!!! –  user1581900 Oct 10 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.