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

  date, category, value

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?


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:



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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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