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 a scatter chart with a series of numbers in Excel. Most of the time, those numbers are high (>1000). I would like to display the scatter chart with those numbers in the default color, but if the number is < 1000 then display those in red. So essentially I want to highlight just those.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

There's at least three ways to accomplish what you've asked about:

  1. Adding a helper series for your highlighted points (as pnuts),
  2. Manually formatting (as Rhys Gibson added),
  3. Adding a formatting "band" to highlight the values.

The method you choose will depend greatly upon your chart layout (scatter charts can be laid out in at least two different ways), how many points you need to highlight and how often they'll change. I've included samples of 2 different ways to highlight your points: adding a helper series and adding a highlight band (personally, I'll almost never manually highlight a few points).

format_scatter

If your scatter chart is laid out in a traditional XY configuration (like the top right chart), you'll need to consider which axis' values you're evaluating to be less than 1000 (vertical or horizontal or both)? I've highlighted both for the sample. The general steps for this are:

  1. Organize your base data (two columns X2 and Y1, in this sample).
  2. Create your base chart, using your base data (blue values are the original series).
  3. Create a helper column for your highlighted values (in this case less than 1000, X3 and Y1 for the vertical, and X2 & Y2 for the horizontal). If you're highlighting both axis, you'll need two helper columns); or
  4. Create a helper column for your highlight bands (in this case Y4).
    • Once you've added the helper column, you'll need to change that series chart type to Column.
    • Then change your formatting to your preferred color, change the gap to 0, and you'll probably need to tweak the axis' labels.

If your scatter chart is laid out more like a line chart like the bottom chart (which is much more flexible than a typical line chart), then you'll need to do things a little different:

  1. Organize your base data (two columns X1 and Y1, in this sample).
  2. Create your base chart, using your base data (blue values in are the original series).
  3. Create a helper column for your highlighted values (in this case less than 1000, X1 and Y2 in this sample); or
  4. Create a helper column for your highlighted bands (in this case Y3), and follow the formatting columns from above.

EDIT: It's worth noting that in the highlighted values, I've returned NA() if the value didn't match the target requirement. This keeps the point from being plotted at all, instead of having to deal with points plotted as 0 (or some other value).

share|improve this answer

Plot the high and low values from separate columns (or rows):

Simplified scatter chart with 'highlighting' The data point shapes and their colours will probably need to be changed if you want red + another colour and a particular shape but now each 'series' can be selected separately (by clicking on one of the data points from each 'series', in turn) and formatting to suit. I take it that how to format (described all over the place) is not a required part of an answer to your question.

share|improve this answer
2  
Could you maybe add a few steps on how to do this or provide a screenshot? I guess that would be very helpful! –  slhck Sep 19 '12 at 14:50

Excel doesn't support 'conditional formatting in charts'.

If you can't plot these numbers in separate series (as per pnuts solution) then another option is to manually colour each data point (right click|Format Data Point) as long as you don't have too many. This is a hassle though if your data changes.

share|improve this answer
    
This does work and I think would often be 'good enough' so +1 but imagine having to do that for data such as used by @David Vandenbos above. (I think I'd rather Photoshop the chart with a highlighter!) –  pnuts Sep 20 '12 at 10:35

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.