Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
I have table like this:
2002 2003 2004 2005 2006 2007 Q1 100,00 100,00 100,00 98,95 102,79 Q2 100,00 100,00 99,65 99,01 Q3 100,00 100,00 98,84 99,01 Q4 100,00 100,00 98,23 99,00 The problem is that when I chart the table I get into X-axis label years 2002-2007 right after each other. Excel doesn't seem to understand that they are supposed to fit only every fourth value in the line. So what I want to x-axis label is either this: 2002 Q2 Q3 Q4 2003 Q2 Q3 ...etc OR 2002 2003 2004...etc What excel gives me now is: 2002 2003 2004 ... etc Anyone knows how to fix this ? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
I don't understand what you mean by "Excel doesn't seem to
understand that they are supposed to fit only every fourth value in the line." Is it you want to show the four quarters repeatedly for each year, and this group of quarters labeled for each year? Assuming that is, set up yr sheet as follows. In A1: 2002 In A2: Q1; in B2: Q2; in C2: Q3; in D2: Q4; In A3:D3 the respective values for Q1:Q4 Repeat for 2003 in cells E1:H3, and repeat until you have all yr years Now select A1:H3 (just to show the effect for two years; adjust your range accordingly) and see whether you like what you get (it should display two rows of axis lables: Quarters and below the year, the years separated by vertical border lines). HTH, Henk "Sirritys" wrote: I have table like this: 2002 2003 2004 2005 2006 2007 Q1 100,00 100,00 100,00 98,95 102,79 Q2 100,00 100,00 99,65 99,01 Q3 100,00 100,00 98,84 99,01 Q4 100,00 100,00 98,23 99,00 The problem is that when I chart the table I get into X-axis label years 2002-2007 right after each other. Excel doesn't seem to understand that they are supposed to fit only every fourth value in the line. So what I want to x-axis label is either this: 2002 Q2 Q3 Q4 2003 Q2 Q3 ...etc OR 2002 2003 2004...etc What excel gives me now is: 2002 2003 2004 ... etc Anyone knows how to fix this ? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
HEK wrote:
I don't understand what you mean by "Excel doesn't seem to understand that they are supposed to fit only every fourth value in the line." Is it you want to show the four quarters repeatedly for each year, and this group of quarters labeled for each year? Assuming that is, set up yr sheet as follows. In A1: 2002 In A2: Q1; in B2: Q2; in C2: Q3; in D2: Q4; In A3:D3 the respective values for Q1:Q4 Repeat for 2003 in cells E1:H3, and repeat until you have all yr years Now select A1:H3 (just to show the effect for two years; adjust your range accordingly) and see whether you like what you get (it should display two rows of axis lables: Quarters and below the year, the years separated by vertical border lines). HTH, Henk Sorry for being unclear. I'll try to make more sense now. I'm not compromising the table structure. It has to stay the way I posted it. No I'm plotting one curve from the table values so that I get one curve plotting values from 2002 Q1 to 2006 Q1. Plotting is fine but I don't know how to get right labels to X-axis. In other words, what should I but to "Source Data - Category (x) axis labels". I can do it with "=companyname!$B$38;companyname!$A$40:$A$42;compan yname!$C$38;companyname!$A$40:$A$42;companyname!$D $38;companyname!$A$40:$A$42;companyname!$E$38;comp anyname!$A$40:$A$42;companyname!$F$38;companyname! $A$40:$A$42;companyname!$G$38" But there has to be easier way, right ? Here is table with column and row numbers: A B C D E F G 38 2002 2003 2004 2005 2006 2007 39 Q1 100,00 100,00 100,00 98,95 102,79 40 Q2 100,00 100,00 99,65 99,01 41 Q3 100,00 100,00 98,84 99,01 42 Q4 100,00 100,00 98,23 99,00 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
I'm not compromising the table structure.
Okay, then you are compromising chart structure. Or perhaps you can have the best of both worlds. In another area of the worksheet, or in a second worksheet, arrange the same data, but optimized for charting, as Henk suggests. Use links so that updating the main table also updates the chart data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sirritys" wrote in message oups.com... HEK wrote: I don't understand what you mean by "Excel doesn't seem to understand that they are supposed to fit only every fourth value in the line." Is it you want to show the four quarters repeatedly for each year, and this group of quarters labeled for each year? Assuming that is, set up yr sheet as follows. In A1: 2002 In A2: Q1; in B2: Q2; in C2: Q3; in D2: Q4; In A3:D3 the respective values for Q1:Q4 Repeat for 2003 in cells E1:H3, and repeat until you have all yr years Now select A1:H3 (just to show the effect for two years; adjust your range accordingly) and see whether you like what you get (it should display two rows of axis lables: Quarters and below the year, the years separated by vertical border lines). HTH, Henk Sorry for being unclear. I'll try to make more sense now. I'm not compromising the table structure. It has to stay the way I posted it. No I'm plotting one curve from the table values so that I get one curve plotting values from 2002 Q1 to 2006 Q1. Plotting is fine but I don't know how to get right labels to X-axis. In other words, what should I but to "Source Data - Category (x) axis labels". I can do it with "=companyname!$B$38;companyname!$A$40:$A$42;compan yname!$C$38;companyname!$A$40:$A$42;companyname!$D $38;companyname!$A$40:$A$42;companyname!$E$38;comp anyname!$A$40:$A$42;companyname!$F$38;companyname! $A$40:$A$42;companyname!$G$38" But there has to be easier way, right ? Here is table with column and row numbers: A B C D E F G 38 2002 2003 2004 2005 2006 2007 39 Q1 100,00 100,00 100,00 98,95 102,79 40 Q2 100,00 100,00 99,65 99,01 41 Q3 100,00 100,00 98,84 99,01 42 Q4 100,00 100,00 98,23 99,00 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Yes, I think I'll surrender and do it that way ;-)
I just hate to use extra sheet for something like that. (Don't want to put it to original sheet, so that extra table wouldn't print) Thanks for trying thought ;-) -Aki |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Jon is one of the foremost authorities on Excel charting. Perhaps you should
listen to him. -- Don Guillett SalesAid Software "Sirritys" wrote in message oups.com... Yes, I think I'll surrender and do it that way ;-) I just hate to use extra sheet for something like that. (Don't want to put it to original sheet, so that extra table wouldn't print) Thanks for trying thought ;-) -Aki |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Think about it. You can either use an extra sheet, which takes up a few KB
and a few minutes, or you can do crazy things with formulas and trying to use unwieldy discontiguous ranges, and waste hours. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sirritys" wrote in message oups.com... Yes, I think I'll surrender and do it that way ;-) I just hate to use extra sheet for something like that. (Don't want to put it to original sheet, so that extra table wouldn't print) Thanks for trying thought ;-) -Aki |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Don: fully agree, and I want to add: Jon is also ver open in sharing his knowledge and extremely patient with all of us that still have to learn what Jon already mastered ages ago. I cannot agree more to Jon's advice re the extra table. The extra table is done very quickly. You can still use the original table in any report instead of the new table that is simply made to make the charting easier. "Jon Peltier" wrote: Think about it. You can either use an extra sheet, which takes up a few KB and a few minutes, or you can do crazy things with formulas and trying to use unwieldy discontiguous ranges, and waste hours. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sirritys" wrote in message oups.com... Yes, I think I'll surrender and do it that way ;-) I just hate to use extra sheet for something like that. (Don't want to put it to original sheet, so that extra table wouldn't print) Thanks for trying thought ;-) -Aki |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Thanks guys for the help and patience, I don't mean to dishonor anyone,
I KNOW you guys know these things better than me. But there was one problem with using extra sheet and chart from that When I made extra table and it's values where like "='Sheet1'!B71" the value came out like "0" if the 1st table was empty. Now the excel plots that "0" as opposed not plotting empty cell. I wan't the curve just stop, not to drop to value zero. The same problem arises when I use "" or NA(), in formula, if corresponding cell in table1 was empty cell. Any suggestion how to solve this if I chart from another sheet/table. (I can't assume the value area would be widened every time new value will be added in future, since ppl who know nothing about excel has to be able to update this chart) Feeling sorry for being such a hard case, Aki Koikkalainen |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
=IF(LEN('Sheet1'!B71)=0,NA(),'Sheet1'!B71)
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Sirritys" wrote in message oups.com... Thanks guys for the help and patience, I don't mean to dishonor anyone, I KNOW you guys know these things better than me. But there was one problem with using extra sheet and chart from that When I made extra table and it's values where like "='Sheet1'!B71" the value came out like "0" if the 1st table was empty. Now the excel plots that "0" as opposed not plotting empty cell. I wan't the curve just stop, not to drop to value zero. The same problem arises when I use "" or NA(), in formula, if corresponding cell in table1 was empty cell. Any suggestion how to solve this if I chart from another sheet/table. (I can't assume the value area would be widened every time new value will be added in future, since ppl who know nothing about excel has to be able to update this chart) Feeling sorry for being such a hard case, Aki Koikkalainen |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
I can't get Axis labels as I want!
Ok, I have no more complains, the last post solved my last problem.
Thanks alot for help! -Aki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
labels closer to axis | Charts and Charting in Excel | |||
Excel2003: Axis labels are truncated | Charts and Charting in Excel | |||
Can tick mark labels be flipped about axis? | Charts and Charting in Excel | |||
Align Clustered Column Chart Category Axis Labels | Charts and Charting in Excel | |||
Labels on Chart with Negative Value Axis | Charts and Charting in Excel |