# Make a mixed bar and scatter graph with Excel 2010

Before you post a comment saying that this is a repost - I would like to say that I have looked at the similar questions but some marked the question as answer without being a true answer (like the one in the link)

I want is to graph this with excel:

Please note that the data points in the categories are not separated into series and are all alike within their category.

Is this possible in any way ___?

UPDATE

@David wanted to have some data to work with. So I copied the data from the link

Category 1 (red): 22,58 20,76 7,1 37,93 68,42

Category 2 (green): 72,41 72,73 71,43 70,83 82,86

-
This is definitely possible, but you'll need to show us a sample of data to direct you to the best solution. – dav May 3 '13 at 16:39

Here's a quick sample of what can be done. This may require some data manipulation to correctly display everything (but that's pretty common with using Excel to create outside the box charts). It uses four data series: one each for your Category 1 & 2, and two hidden, one each for the two labels.

EDIT: To create this type of mixed chart, you can use an Excel Scatter Chart.

1. Format your data. To keep things simple, I prefer to have each series/category separated into two columns for the X & Y values. Here's what the sample looks like:

1. Use your data to create an Scatter Chart. Add each series, using the XY data in the columns. It doesn't matter how many points are any series, they're independant.

2. Add the Label Point series for each category. This creates a single point (I left the Category 2 point gray in the second sample for clarity), which can then be used to create a Label for each Category (simply format the Label to show the Series, Below the point).

3. Everything else is formating to your preference. However, there are two considerations for you:

a. Since there are values very close to each other I recommend using a transparency for the fill, this will allow the overlapping values to have more color saturation, emphasizing them.

b. Another method you can use to show all the values is to introduce "jitter" into the X value. For Category 1, I used `=RANDBETWEEN(48,52)/100` to create a bit of spread to the horizontal locations of the data points, this allows you to better see clustered points.

-
This look fine to me. How did you make it - and do you need to have the same amount of data points in each category? – Norfeldt May 6 '13 at 7:46
@norfeldt, I'll add a little more direction when I get in front of my computer, probably a couple hours. – dav May 6 '13 at 10:16
Thank you - will be looking forward to it :) – Norfeldt May 6 '13 at 13:53
Thank you so much for your time and help! – Norfeldt May 7 '13 at 7:45

Here's a slightly different approach, which I used to answer a similar, if not identical, question.

Start with a range with "Starter" and "Professional" in the first column and two zeros in the second. Insert a bar chart. This provides labels, but the zero means the bars do not show.

In another two columns, place 1 for Starter and 2 for Professional in the first column, and the Y values in the second. Copy this range, select chart, use Paste Special (categories in first column).

Right click on this new series, choose Change Chart Type, select XY Scatter. Again right click on new series, choose Format Data Series, select Primary Axis where given a choice.

-

I used to answered a similar, if not identical, question with a slightly different approach.

Start with a range with "Starter" and "Professional" in the first column and two zeros in the second. Insert a bar chart. This provides labels, but the zero means the bars do not show.

In another two columns, place 1 for Starter and 2 for Professional in the first column, and the Y values in the second. Copy this range, select chart, use Paste Special (categories in first column). You can add jittering as shown in dav's answer.

Right click on this new series, choose Change Chart Type, select XY Scatter. Again right click on new series, choose Format Data Series, select Primary Axis where given a choice.

-