Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
Hi, I am using Excel 2003.
Let's say we want to implement a new system, and to get an idea of what to buy, we survey a number of like organisations to see what they are using, whether or not they recommend the system and if they have had any problems. Eg: Response Recommend Problems WSBS 5 4 1 ABC 2 0 2 DEF 1 1 1 GHI 0 0 0 XYZ 2 1 1 The left column shows hthe system. The Response column shows how many organisations use each system. The Recommend column shows how many organisations recommend the system (out of the original response) and the last column shows how many organisations had problems with the system. If I wanted to present this information in a clear and concise manner, what type of chart(s) should I use? I did three separate ine charts initially but I am thinking I should combine them into one to give it more relativity. I have viewed a number of options and some of them look too cluttered or clumsy. As an add on (and not necessary an Excel question) what other information should I solciit in my survey to make a meaningful presentation without confusing people with too much graphics? Keep it simple and use different ones, or combine? I would be very grateful for any suggestions! :-) Thanks. Trish |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
I think I would plot it this way. Use only the Recommend/Problems data, and
take the negative of the Problem data: Rec. Prob. WSBS 4 -1 ABC 0 -2 DEF 1 -1 GHI 0 -0 XYZ 1 -1 Make a stacked column chart. The recommendations reach above the axis, the problems below. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Trish" wrote in message ... Hi, I am using Excel 2003. Let's say we want to implement a new system, and to get an idea of what to buy, we survey a number of like organisations to see what they are using, whether or not they recommend the system and if they have had any problems. Eg: Response Recommend Problems WSBS 5 4 1 ABC 2 0 2 DEF 1 1 1 GHI 0 0 0 XYZ 2 1 1 The left column shows hthe system. The Response column shows how many organisations use each system. The Recommend column shows how many organisations recommend the system (out of the original response) and the last column shows how many organisations had problems with the system. If I wanted to present this information in a clear and concise manner, what type of chart(s) should I use? I did three separate ine charts initially but I am thinking I should combine them into one to give it more relativity. I have viewed a number of options and some of them look too cluttered or clumsy. As an add on (and not necessary an Excel question) what other information should I solciit in my survey to make a meaningful presentation without confusing people with too much graphics? Keep it simple and use different ones, or combine? I would be very grateful for any suggestions! :-) Thanks. Trish |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
Thanks Jon, I have just done what you suggested and it looks good. Is there
a way I can put the labels below the problem area rather than "in" the problem area? Thanks so much for your help :-) "Jon Peltier" wrote: I think I would plot it this way. Use only the Recommend/Problems data, and take the negative of the Problem data: Rec. Prob. WSBS 4 -1 ABC 0 -2 DEF 1 -1 GHI 0 -0 XYZ 1 -1 Make a stacked column chart. The recommendations reach above the axis, the problems below. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Trish" wrote in message ... Hi, I am using Excel 2003. Let's say we want to implement a new system, and to get an idea of what to buy, we survey a number of like organisations to see what they are using, whether or not they recommend the system and if they have had any problems. Eg: Response Recommend Problems WSBS 5 4 1 ABC 2 0 2 DEF 1 1 1 GHI 0 0 0 XYZ 2 1 1 The left column shows hthe system. The Response column shows how many organisations use each system. The Recommend column shows how many organisations recommend the system (out of the original response) and the last column shows how many organisations had problems with the system. If I wanted to present this information in a clear and concise manner, what type of chart(s) should I use? I did three separate ine charts initially but I am thinking I should combine them into one to give it more relativity. I have viewed a number of options and some of them look too cluttered or clumsy. As an add on (and not necessary an Excel question) what other information should I solciit in my survey to make a meaningful presentation without confusing people with too much graphics? Keep it simple and use different ones, or combine? I would be very grateful for any suggestions! :-) Thanks. Trish |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
On Tue, 27 Nov 2007, in microsoft.public.excel.charting, Trish said: "Jon Peltier" wrote: I think I would plot it this way. Use only the Recommend/Problems data, and take the negative of the Problem data: Rec. Prob. WSBS 4 -1 ABC 0 -2 Make a stacked column chart. The recommendations reach above the axis, the problems below. Thanks Jon, I have just done what you suggested and it looks good. Is there a way I can put the labels below the problem area rather than "in" the problem area? Annoyingly, as you discovered, data labels only show "Center", "Inside Base", and "Inside End" as display locations. I can see three ways around this, with various levels of usefulness/difficulty 1) abandon the positive/negative display and just go for a stacked column chart with the Rec and Prob adding up to "Response". This was my first idea, but I admit Jon's looks better (no problem with being able to compare bars that don't line up). This way means you can just use X axis labels. 2) create a second stacked bar series which simply copies the "Prob" column, format it to be invisible (no area colour and no border colour) and format its data label to be "Inside Base". Manually delete it from the legend, if any. 3) Third option is to change the chart type of that series to "Line" instead of "Column" type, and format the data label to be "Below". Again, invisible formatting and delete from the legend, since all you're interested in is the label. How much better it would be if Microsoft offered "Outside End" as a label position! -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
On Wed, 28 Nov 2007, in microsoft.public.excel.charting,
Del Cotter said: 1) abandon the positive/negative display and just go for a stacked column chart with the Rec and Prob adding up to "Response". This was my first idea, but I admit Jon's looks better (no problem with being able to compare bars that don't line up). This way means you can just use X axis labels. Oh, there's another couple of options I forgot! 1.5a) format the Y axis scale so that the X axis crosses it at "-9999", i.e. at the bottom of the chart. 1.5b) format the X axis so that, although it crosses the Y axis at zero, the tick mark *labels* are "Low" instead of "Next to axis" I've been assuming it's the category labels you're talking about, and not the value of the numbers. The numbers are so small I think you should trust people to use their eyes instead of showing the numerals, or choose a table instead if you really prefer the integers, or use Autoshape pictures to turn the stacked columns into countable stacked shapes if you prefer graphics but want people to see the integers. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
I think 1.5b is just what the OP needed. I'd avoid 1.5a, since moving the
axis off zero distorts the relationship between length of bar and value of data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 28 Nov 2007, in microsoft.public.excel.charting, Del Cotter said: 1) abandon the positive/negative display and just go for a stacked column chart with the Rec and Prob adding up to "Response". This was my first idea, but I admit Jon's looks better (no problem with being able to compare bars that don't line up). This way means you can just use X axis labels. Oh, there's another couple of options I forgot! 1.5a) format the Y axis scale so that the X axis crosses it at "-9999", i.e. at the bottom of the chart. 1.5b) format the X axis so that, although it crosses the Y axis at zero, the tick mark *labels* are "Low" instead of "Next to axis" I've been assuming it's the category labels you're talking about, and not the value of the numbers. The numbers are so small I think you should trust people to use their eyes instead of showing the numerals, or choose a table instead if you really prefer the integers, or use Autoshape pictures to turn the stacked columns into countable stacked shapes if you prefer graphics but want people to see the integers. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Best chart format to use?
Del, thank you for your response and lots of useful suggestions. Actually I
did come across a solution that was helpful, which i am happy to pass on. Double click the 0 line (ie the Category axis) and that opens the Format Axis box. Under "tick mark labels" select "low". This was perfect for my given problem. Again, I appreciate your support. Cheers. Trish "Del Cotter" wrote: On Wed, 28 Nov 2007, in microsoft.public.excel.charting, Del Cotter said: 1) abandon the positive/negative display and just go for a stacked column chart with the Rec and Prob adding up to "Response". This was my first idea, but I admit Jon's looks better (no problem with being able to compare bars that don't line up). This way means you can just use X axis labels. Oh, there's another couple of options I forgot! 1.5a) format the Y axis scale so that the X axis crosses it at "-9999", i.e. at the bottom of the chart. 1.5b) format the X axis so that, although it crosses the Y axis at zero, the tick mark *labels* are "Low" instead of "Next to axis" I've been assuming it's the category labels you're talking about, and not the value of the numbers. The numbers are so small I think you should trust people to use their eyes instead of showing the numerals, or choose a table instead if you really prefer the integers, or use Autoshape pictures to turn the stacked columns into countable stacked shapes if you prefer graphics but want people to see the integers. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart format | Charts and Charting in Excel | |||
Format Pie Chart | Charts and Charting in Excel | |||
Chart format question | Excel Worksheet Functions | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
Pie Chart format | Charts and Charting in Excel |