Scatter plots are a great way to visualize groups of data. Compared with bar graphs (with or without error bars), they have the added advantage of showing how the data are spread out. However, the basic scatter plot that Excel creates needs some tweaking to get it right.
Its great. I am wondering whether it is possible to add mean line and error bar?
Thank you for this great job.
Shafiul
Hi Shafiul,
this requires some advanced Excel fu. To get just the mean lines, you could work with a second data series. Let’s say your means are 2.5 and 3.5 for categories 1 and 2, respectively:
| A | B | C | |
|---|---|---|---|
| 1 | 0.7 | 2.5 | |
| 2 | 1.3 | 2.5 | |
| 3 | 1.5 | <- intentionally left blank to force a break in the line | |
| 4 | 1.7 | 3.5 | |
| 5 | 2.3 | 3.5 |
Plot this as a line. With a little tweaking you will get two horizontal lines. You can adjust the width of these by changing the 0.7/1.3 and 1.7/2.3 values.
By the way, I have recently taken to draw plots with R and ggplot2. It was not easy to learn, but it was worth it…
If you prefer to stick with Excel, also have a look at Jon Peltier’s work, he’s a real Excel chart wizard. Browse his blog to get lots of inspirations.
Best
Daniel