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.

I have some data which I would like to plot in the scatter chart in the following format

  date, category, value
  01-Jan,Cat1,0.03
  01-Jan,Cat2,0.13
  01-Jan,Cat3,0.27
  01-Jan,Cat4,0.32
  02-Jan,Cat1,0.12
  02-Jan,Cat3,0.90
  03-Jan,Cat1,0.01
  03-Jan,Cat2,0.02
  03-Jan,Cat4,0.40

I would like time on the x axis and value on the y axis, and category to give the color or shape of the point.

I can readily plot the scatter graph, but cannot work out how to map the categories as colors. How do I do that?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

The simplest solution is to use "helper" columns to break each category into its own series, which can then be independantly formatted. For instance, in Column D:

  1. Create a header called Cat1.
  2. For each data row, enter the forumula =IF(B2="Cat1",B2,NA()). This will enter a value for all Cat1 rows, and #N/A for all others (#N/A points won't plot on an Excel Chart).
  3. Create your Scatter chart with Date as your X Axis and each Helper Column for your Y Axis.

Here's an example of what it could look like:

scatter plot with multiple series

To further simplify things, you can format your data as an Excel Table and that will speed up the creation of your categories by automatically filling the series data. Here's what it could look like:

d

share|improve this answer
add comment

If i understand the Question correctly, you can click on format on the ribbon at the top when you are on your chart or right click on the icon for a point in your scatter graph and choose the paint pot to change colour

share|improve this answer
    
I think he wants to do that programmatically using the value in the category column. My suggestion would be to use R... –  beroe Sep 16 '13 at 16:22
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.