Any Excel Advanced User Here?

K

ksher

Veteran
 Bedfordshire
I hope someone can provide me some ideas on putting up a graph.

I try to present sales volumes for 11 areas over the past 12 months. I know how to do normal line graph/bar chart/pie chart on Excel, but the problem is that 1 area had much higher sales than other areas, say around 500 units per month. All other areas had the value range between 20 and 200 units. Therefore, if I include the area with largest sales volumes. The trends of other 10 areas will not be easily seen (lines will look like flat).

What type of line graph is the best for presenting that sort of data? I had seen graph with left y-axis representing one type of value and right y-axis representing another type of value. But I just don't know how to make it in Excel.

Does anyone have any ideas or suggestions? Thank you.
 
Pie chart

You can then show the measures for each period as actual values that u require

J
 
ksher said:
I hope someone can provide me some ideas on putting up a graph.

I try to present sales volumes for 11 areas over the past 12 months. I know how to do normal line graph/bar chart/pie chart on Excel, but the problem is that 1 area had much higher sales than other areas, say around 500 units per month. All other areas had the value range between 20 and 200 units. Therefore, if I include the area with largest sales volumes. The trends of other 10 areas will not be easily seen (lines will look like flat).

What type of line graph is the best for presenting that sort of data? I had seen graph with left y-axis representing one type of value and right y-axis representing another type of value. But I just don't know how to make it in Excel.

Does anyone have any ideas or suggestions? Thank you.
Click to expand...

Your on the right track.

Simply create a 2D line graph as per normal.
Then Highlight the data series you need a second y-axis scale for.
Then right click & sellect format data series and pick plot series on secondary axis.
Excel will auto scale the selected data series to match the size of the others. and show a second scale on the right hand side of the graph.

Hope this helps :o
 
Or make the axis logarithmic. Whatever you do you might want to point out whatever it is you do to your audience as if you have a "star" team or area, they might like to be seen to be way ahead of the pack.
 
Thank you. I will try again in the morning. But I think it is the only way to show the movement over 12 months for all 11 areas. Both primary and secondary y-axis show the same type of measure but at different scales, it just looks strange.

I also have to show gross margin movement for those areas over 12 months on one graph.
 
Stuart Truman said:
Or make the axis logarithmic. Whatever you do you might want to point out whatever it is you do to your audience as if you have a "star" team or area, they might like to be seen to be way ahead of the pack.
Click to expand...

i'd be inclined to do this.

however it depends what you're trying to represent; how the areas are performing using their own arena as a backdrop, or how they are performing relative to one another?

i have found YoY sales growth to be the best indicator of performance.

i understand, not knowing your audience or aim, this advice is pretty useless.
 
Stuart Truman said:
Post the data (with anything that would cause business problems removed) and let us all have a go for you!
Click to expand...

Thanks. I will do it in the morning. The file is on my company network. I have to use my work laptop to log on first (I am using my own desktop atm).

The end users are senior management at board level. FC asked me to put sales volumes and gross margin per unit trends for 11 areas over the past 12 months on the graphs.
 
Stuart Truman said:
Post the data (with anything that would cause business problems removed) and let us all have a go for you!
Click to expand...

some sort of shared service centre?!

are we on to something here, bring your homeworkto us....think i have enough of my own! :)
 
Just make sure you don't post anything that will get you into trouble! Remember the "Red Face Test" (don't write or say anything you wouldn't want to be on the front page of tomorrows paper, or hauled up in front of the boss over)
 
I would do one graph showing the actual values of all teams with the lead team being way ahead, then a second one looking at just the remaining teams with an appropriate scale.

All depends on what you want to demonstrate within the data.
 
You must log in or register to reply here.
Back
Top Bottom