How to highlight data points in a scatter chart in excel?

Question

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.

Answer 1

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).

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).

Answer 2

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

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.

Answer 3

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.

Answer 4

For people with the same problems, there is a usefull tool named FunFun, which allows people to use web languages like Html, Css and JavaScript in Excel.

Javascript has a lot of powerful libraries for data visualization, like Charts.js and D3, that you can use with this Excel add-in to create any chart you want.

If you want to test out different libraries, Funfun hosts an online editor with an embedded spreadsheet, where you can see the output of your code instantly.

I made this chart with Chart.js on the FunFun website that I directly loaded in Excel by pasting the link of the funfun online editor below:

https://www.funfun.io/1/edit/5a32ad9f61242f75d940543f

Here are some screenshots(Website to Excel Add-in):

You then load the project in excel with funfun: