Creating graphs in Excel?

gerturdeanna

Hi

Is anyone any good with creating graphs in Excel that could help me?

I think I have too much data!

I would like to have a line graph to show the correlation between amount of samples sent out per month, per store, per range against amount of sales per month, per store, per range.

I was hoping to cut and paste part of the data but it won't formatt correctly!!! ARRRGGGHHHHH

Gert

rmg1

From what you've put, you should have 5 columns of data.

Month
Store
Range
Sales
Samples

Try formatting your data like that (unless that's how it already is) and then graphing it.
Then it's just playing with the axes and labels.

Personally I'd go:-
Samples on the left
Sales on the right
Month at the bottom (this lot covers your axes)
The stores/ranges as lines graphs.

That should just about cover everything but aline graph may not be the best type for this amount of data.

nickbeee

I feel your pain. I never managed to get any decent charts out of Excel after moving from 1-2-3. I usually find some right-clicking on data series is necessary to verify what they are plotting and correcting them by highlighting the appropriate data series on the sheet.

gerturdeanna

rmg1 wrote: »

From what you've put, you should have 5 columns of data.

Month
Store
Range
Sales
Samples

Try formatting your data like that (unless that's how it already is) and then graphing it.
Then it's just playing with the axes and labels.

Personally I'd go:-
Samples on the left
Sales on the right
Month at the bottom (this lot covers your axes)
The stores/ranges as lines graphs.

That should just about cover everything but aline graph may not be the best type for this amount of data.

Hi rmg1

I'll try that - what other graph would you think would work better?

Thanks

Agrajag

To my reading, you will find a 'pivot table' with a graph the most useful, and it is very quick and easy to do.
If you send me a private message with a sample of your data, I can give it a go. If you do, tell me which version of Excel (2003, 2007, 2010, other?) you are using

Neal

Prothet_of_Doom

I use minitab for that. I do what's called a main effects plot, which takes the main factors which effect sales, and plots a number of graphs with averages of the other factors.

Really hard to see what the data means on one graph so I'd go multiple

So plot
Sales per month (all other factors ignored)
Sales per store (You can do a line per store per month)
Sales per range (You can do a line per range per month)
Sales per range per store per month (Here I'd do a number of graphs - one graph per range. so on each graph you have a line for the sales per store per month)
Sales per Store per range (as above but you have one graph per store, and a line per range.

All graphs have months on the X axis, Sales on the Y axis and the lines are a range or a store

cookie365

This is exactly what pivot tables were designed for.

http://spreadsheets.about.com/od/datamanagementinexcel/ss/8912pivot_table.htm