How to make clustered scatter plot with custom error bars?

Hi all,
I'm looking for help making a clustered plot where the X-axis is categorical data and the Y axis is numerical data with custom error bars in the Y axis. Each cluster contains percent errors corresponding to three dimensions (i.e. X/Y/Z) and the clusters are different settings in which the error is measured. The custom error bars reflect results of a statistical test I calculated outside of Excel.

I can currently do this with a clustered column chart (see image below), but would like to represent the plotted data as points (as in a scatter plot) rather than columns while retaining the error bars around them. The result would be identical to the graph below but with points placed where the columns are currently. I've attached an example file containing the same chart and its underlying data.

Any advice on how to do this in Excel would be very much appreciated. I'm also open to solutions using software other than Excel.

Edit: In hindsight, 'scatter plot' was a poor choice of words in the title. What I'm looking for is something that plots the exact same data as shown below (agregates only, not individual measurements as in a true scatter plot) but simply using dots/circles in place of columns.


Excel Chart Question - Example.png

Your sample file only includes the summary statistics and not the raw data that I assume you want to plot on the chart as points. Assuming you have the data elsewhere for the "scatter chart":

1) Create several series of (x,y) pairs for each point you want to chart. I am not sure what you are going for, but it might be arranged in the spreadsheet like:

Please Login or Register  to view this content.

2) create a scatter chart based on the first 2 columns, then add the remaining pairs of columns as additional data series (I usually use the Select Data Dialog for this).
3) For the error bars, add a 7th data series to the chart that places a point where you want the error bars to originate from, then add error bars to that series and format as desired.

Your sample file only includes the summary statistics and not the raw data that I assume you want to plot on the chart as points. Assuming you have the data elsewhere for the "scatter chart":

1) Create several series of (x,y) pairs for each point you want to chart. I am not sure what you are going for, but it might be arranged in the spreadsheet like:

Please Login or Register  to view this content.

2) create a scatter chart based on the first 2 columns, then add the remaining pairs of columns as additional data series (I usually use the Select Data Dialog for this).
3) For the error bars, add a 7th data series to the chart that places a point where you want the error bars to originate from, then add error bars to that series and format as desired.

On edit: then, to get the "category axis", I would use a technique like this http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html to "replace" the scatter charts horizontal axis with the setting1 and setting2 labels.

If I am understanding correctly, it should be as simple as converting your column chart to a line chart. Try this:

1) Select B2:G4
2) Insert a line chart
3) Format the data series to have the desired marker shape and size and no line
4) Add error bars to the series and format as desired

Is that what you are looking for?

You must have done something different than I did -- maybe left row 3 out of the chart horizontal axis range or something. I get the same thing as in your picture (where all the series are vertical over the two categories) if I simply change your original chart from a column to a line chart. Did you try it with both row 3 and 4 as the category axis (making a multi-level category axis)?