Scatter Chart with sales for industry?

[kb9omaaj]

I'm working on a project in which I want to have text (Industry Name) on the x-axis and the annual change in sales on the y-axis. I want the scatter points to represent the top 10 companies for each industry. The data would look something like this:

A Industrials 8%
B Industrials 6.7%
C Industrials 6.4%
D Industrials 5%
E Industrials 5.9%
.
.
.
K Consumer Goods 7%
L Consumer Goods 7.2%
M Consumer Goods 9.1%
.
.
.


I haven't been able to get this to work, so maybe a scatter plot won't. If you could recommend another way to show the different values for each member of a group, I would appreciate it.

[Richard Buttrey]

Hi,

I don't think a scatter chart is appropriate here since you don't have two numeric axes. It's usually used in order to see if there's any correlation between two variables.

In your case can't you just extract the top 10 values for each industry and use a standard column or bar chart?

[kb9omaaj]

Hi,

I don't think a scatter chart is appropriate here since you don't have two numeric axes. It's usually used in order to see if there's any correlation between two variables.

In your case can't you just extract the top 10 values for each industry and use a standard column or bar chart?

Hi! And thank you for your quick reply!

What I'm looking for is a visual representation that shows where all the individual results are for the companies within each industry. That way I can see the high, low, and any groupings of the annual percent change. The problem with a column or bar graph is that I would loose the individual data points, right?

[kb9omaaj]

Delete post (accidental double post)

[MrShorty]

The problem with a column or bar graph is that I would loose the individual data points, right?

I don't see why you would lose them.

I will agree with Richard, a scatter plot is not really appropriate for this, because you are not looking for a numeric x axis -- you are looking for a category axis.

A good portion of charting questions is deciding what kind of chart you want. This really isn't an Excel specific question. Sometimes, there is even some trial and error here trying to figure out a chart type and format that will illustrate what you are trying to see/show.

An important part of many charting issues is getting the data layout in the spreadsheet correct. A well organized and structured table (for the desired chart type) makes creating and formatting the chart easy. A poor table structure makes it difficult.

Not knowing exactly what you want, I made a quick chart (line chart with a multi-level x axis) as a starting point. This chart was really easy to create. I rearranged your data so that broader "industry" groupings were in the leftmost column, the company names are in the 2nd column, and the data are in the 3rd column. I added a "gap" in between industry groupings so that the chart will also have a gap. Then selected A2:C10 and inserted a line chart.

[Richard Buttrey]

@Mr Shorty

I like it. Nice

[kb9omaaj]

Thanks for the suggestions guys, but the chart posted doesn't display in the way I need.

I ended up having to make a legend for the industries that assigned them numerical value that displays the % change the way I need. I'm attaching a screen shot of the chart. It's not the ideal, which would be to have text instead of numbers on the x-axis, but it does shshow the high, low, and clusterings of the % change for each industry. If anyone can figure out how to get text on the x-axis, I would greatly appreciate it.

[Richard Buttrey]

Hi,

We can't really offer much help unless you upload the workbook. Pictures are rarely much use - see guidance in the forum rules.

[kb9omaaj]

Here is the data set with the chart

[MrShorty]

Perhaps a technique like this -- applied to your horizontal/value axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html

Perhaps a line/XY combination chart? : http://peltiertech.com/line-xy-combination-charts/

I added a column (Q) to your "legend table" (O:P) containing 0's. Then added column O and Q as another series to the scatter plot, then changed the data type of this 2nd series to line. This took out the XY scatter X-axis and replaced it with the category axis for the line chart -- including the text labels for each category. The XY scatter data points stayed in the same positions. Is that what you are looking for?

[kb9omaaj]

Those don't display what I need them to. Oh well, I guess I'll just have to live with the chart with the legend. Thanks for your help!