Hiding some labels on value axes; Changing series names

I've created a column chart in Excel 2002. It has 9 columns, increasing in
value from left to right. The 8 columns on the left have values ranging from
2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows the
values for the 8 columns, while the secondary y axis shows the value for the
9th column.

First question.

The primary y axis scale goes from 0 to 16 with a major unit of 4. The
secondary y axis scale goes from 0 to 55 with a major unit of 5. I would
like to hide some of the numbers shown on each axis, so as to make it
clearer which axis is the relevant one for each column. On the primary y
axis, I'd like to hide the number 16, so that only 0-12 would show. On the
secondary y axis, I'd like to hide the numbers 0 to 35, so that only 40-55
would show. I wasn't able to do this by using the chart wizard. Can it be
done somehow?

Second question.

I did not try to name any of my series when creating them, so that each
column simply has below it a number from 1 to 9.

When I click on each data series, the relevant formula for the series
appears in the formula bar. For example, for the first column on the left,
the following appears: =SERIES(,,Sheet1!$B$2:$B$10,1)

I don't know what the two commas immediately after the opening parentheses
represent and I haven't been able to find in the online help a reference to
SERIES to let me know. I suspect the commas may have to do with the way all
my numbers appear on the worksheet. They appear in cells from B2 to P10,
generally in a diagonal fashion, so B2 equals the first column's value, C3
equals the second column's value and so on. The only departure from that
fashion is when I want a stacked column, in which case the values of the
stacks in the column goes from, say, H7 to K7, and then the next column's
value appears in L8.

In any event, when I tried to rename my series using "source data" and then
"series", it wouldn't work. I also tried typing the name in the formula bar,
replacing "sheet1!" with a name, but that didn't work either.

How else can I change the series names to text?

Leslie -

1. Custom Number Formats. Double click the primary axis, and on the
Number tab, enter a custom number format of:

[>=16]" ";0

Double click on the secondary axis, and apply this custom format:

[<40]" ";0

2. Read all about the chart series formula:

http://peltiertech.com/Excel/Charts...iesFormula.html

Briefly, =SERIES(,,Sheet1!$B$2:$B$10,1) means no name is specified
(before the first comma); no X labels are specified (between commas 1 &
2) so the chart probably says 1, 2, 3...; the Y values are in
Sheet1!$B$2:$B$10; and it's the first series in the chart.

This names the series "My Series":
=SERIES("My Series",,Sheet1!$B$2:$B$10,1)

This names it by whatever is located in cell B1:
=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$10,1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Leslie wrote:

> I've created a column chart in Excel 2002. It has 9 columns, increasing in
> value from left to right. The 8 columns on the left have values ranging from
> 2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows the
> values for the 8 columns, while the secondary y axis shows the value for the
> 9th column.
>
> First question.
>
> The primary y axis scale goes from 0 to 16 with a major unit of 4. The
> secondary y axis scale goes from 0 to 55 with a major unit of 5. I would
> like to hide some of the numbers shown on each axis, so as to make it
> clearer which axis is the relevant one for each column. On the primary y
> axis, I'd like to hide the number 16, so that only 0-12 would show. On the
> secondary y axis, I'd like to hide the numbers 0 to 35, so that only 40-55
> would show. I wasn't able to do this by using the chart wizard. Can it be
> done somehow?
>
> Second question.
>
> I did not try to name any of my series when creating them, so that each
> column simply has below it a number from 1 to 9.
>
> When I click on each data series, the relevant formula for the series
> appears in the formula bar. For example, for the first column on the left,
> the following appears: =SERIES(,,Sheet1!$B$2:$B$10,1)
>
> I don't know what the two commas immediately after the opening parentheses
> represent and I haven't been able to find in the online help a reference to
> SERIES to let me know. I suspect the commas may have to do with the way all
> my numbers appear on the worksheet. They appear in cells from B2 to P10,
> generally in a diagonal fashion, so B2 equals the first column's value, C3
> equals the second column's value and so on. The only departure from that
> fashion is when I want a stacked column, in which case the values of the
> stacks in the column goes from, say, H7 to K7, and then the next column's
> value appears in L8.
>
> In any event, when I tried to rename my series using "source data" and then
> "series", it wouldn't work. I also tried typing the name in the formula bar,
> replacing "sheet1!" with a name, but that didn't work either.
>
> How else can I change the series names to text?
>
>

Jon:

Thanks very much, yet again, for your help.

Your answer to my first question worked perfectly for me.

Unfortunately, although I now understand properly the SERIES function
format, I still can't get the category labels beyond the first of them to
change from a number to a name, even when I insert the name between the
first and second commas in the relevant series formula. I'll just have to
experiment with that some more.

Again, many thanks.

Leslie

"Jon Peltier" <DOjonNOT@SPAMpeltiertechME.com> wrote in message
news:eW7f$5GLEHA.3852@TK2MSFTNGP10.phx.gbl...
> Leslie -
>
> 1. Custom Number Formats. Double click the primary axis, and on the
> Number tab, enter a custom number format of:
>
> [>=16]" ";0
>
> Double click on the secondary axis, and apply this custom format:
>
> [<40]" ";0
>
> 2. Read all about the chart series formula:
>
> http://peltiertech.com/Excel/Charts...iesFormula.html
>
> Briefly, =SERIES(,,Sheet1!$B$2:$B$10,1) means no name is specified
> (before the first comma); no X labels are specified (between commas 1 &
> 2) so the chart probably says 1, 2, 3...; the Y values are in
> Sheet1!$B$2:$B$10; and it's the first series in the chart.
>
> This names the series "My Series":
> =SERIES("My Series",,Sheet1!$B$2:$B$10,1)
>
> This names it by whatever is located in cell B1:
> =SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$10,1)
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Leslie wrote:
>
> > I've created a column chart in Excel 2002. It has 9 columns, increasing
in
> > value from left to right. The 8 columns on the left have values ranging
from
> > 2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows
the
> > values for the 8 columns, while the secondary y axis shows the value for
the
> > 9th column.
> >
> > First question.
> >
> > The primary y axis scale goes from 0 to 16 with a major unit of 4. The
> > secondary y axis scale goes from 0 to 55 with a major unit of 5. I would
> > like to hide some of the numbers shown on each axis, so as to make it
> > clearer which axis is the relevant one for each column. On the primary y
> > axis, I'd like to hide the number 16, so that only 0-12 would show. On
the
> > secondary y axis, I'd like to hide the numbers 0 to 35, so that only
40-55
> > would show. I wasn't able to do this by using the chart wizard. Can it
be
> > done somehow?
> >
> > Second question.
> >
> > I did not try to name any of my series when creating them, so that each
> > column simply has below it a number from 1 to 9.
> >
> > When I click on each data series, the relevant formula for the series
> > appears in the formula bar. For example, for the first column on the
left,
> > the following appears: =SERIES(,,Sheet1!$B$2:$B$10,1)
> >
> > I don't know what the two commas immediately after the opening
parentheses
> > represent and I haven't been able to find in the online help a reference
to
> > SERIES to let me know. I suspect the commas may have to do with the way
all
> > my numbers appear on the worksheet. They appear in cells from B2 to P10,
> > generally in a diagonal fashion, so B2 equals the first column's value,
C3
> > equals the second column's value and so on. The only departure from that
> > fashion is when I want a stacked column, in which case the values of the
> > stacks in the column goes from, say, H7 to K7, and then the next
column's
> > value appears in L8.
> >
> > In any event, when I tried to rename my series using "source data" and
then
> > "series", it wouldn't work. I also tried typing the name in the formula
bar,
> > replacing "sheet1!" with a name, but that didn't work either.
> >
> > How else can I change the series names to text?
> >
> >
>

Leslie -

You have to put the cell address for the range of labels into the formula.

If you don't have the labels in a worksheet range (and you really
should, for example, in $A$2:$A$10), you can enter them as text. Each
label must be in quotes, and the array of them must be in curly braces,
like this:

{"A","B","C","D","E","F","G","H","I","J"}

so in your formula, they look like this:

=SERIES(,{"A","B","C","D","E","F","G","H","I","J"},Sheet1!$B$2:$B$10,1)

You can also go to the Source Data dialog, Series tab, and enter the
labels into the Category (X) Axis Labels box, without the braces, but
with the quotes.

If you have long labels, entering them as a literal array like this may
cause problems with the limitation on the length (number of characters)
of the formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Leslie wrote:

> Jon:
>
> Thanks very much, yet again, for your help.
>
> Your answer to my first question worked perfectly for me.
>
> Unfortunately, although I now understand properly the SERIES function
> format, I still can't get the category labels beyond the first of them to
> change from a number to a name, even when I insert the name between the
> first and second commas in the relevant series formula. I'll just have to
> experiment with that some more.
>
> Again, many thanks.
>
> Leslie
>
> "Jon Peltier" <DOjonNOT@SPAMpeltiertechME.com> wrote in message
> news:eW7f$5GLEHA.3852@TK2MSFTNGP10.phx.gbl...
>
>>Leslie -
>>
>>1. Custom Number Formats. Double click the primary axis, and on the
>>Number tab, enter a custom number format of:
>>
>>[>=16]" ";0
>>
>>Double click on the secondary axis, and apply this custom format:
>>
>>[<40]" ";0
>>
>>2. Read all about the chart series formula:
>>
>> http://peltiertech.com/Excel/Charts...iesFormula.html
>>
>>Briefly, =SERIES(,,Sheet1!$B$2:$B$10,1) means no name is specified
>>(before the first comma); no X labels are specified (between commas 1 &
>>2) so the chart probably says 1, 2, 3...; the Y values are in
>>Sheet1!$B$2:$B$10; and it's the first series in the chart.
>>
>>This names the series "My Series":
>>=SERIES("My Series",,Sheet1!$B$2:$B$10,1)
>>
>>This names it by whatever is located in cell B1:
>>=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$10,1)
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Leslie wrote:
>>
>>
>>>I've created a column chart in Excel 2002. It has 9 columns, increasing
>
> in
>
>>>value from left to right. The 8 columns on the left have values ranging
>
> from
>
>>>2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows
>
> the
>
>>>values for the 8 columns, while the secondary y axis shows the value for
>
> the
>
>>>9th column.
>>>
>>>First question.
>>>
>>>The primary y axis scale goes from 0 to 16 with a major unit of 4. The
>>>secondary y axis scale goes from 0 to 55 with a major unit of 5. I would
>>>like to hide some of the numbers shown on each axis, so as to make it
>>>clearer which axis is the relevant one for each column. On the primary y
>>>axis, I'd like to hide the number 16, so that only 0-12 would show. On
>
> the
>
>>>secondary y axis, I'd like to hide the numbers 0 to 35, so that only
>
> 40-55
>
>>>would show. I wasn't able to do this by using the chart wizard. Can it
>
> be
>
>>>done somehow?
>>>
>>>Second question.
>>>
>>>I did not try to name any of my series when creating them, so that each
>>>column simply has below it a number from 1 to 9.
>>>
>>>When I click on each data series, the relevant formula for the series
>>>appears in the formula bar. For example, for the first column on the
>
> left,
>
>>>the following appears: =SERIES(,,Sheet1!$B$2:$B$10,1)
>>>
>>>I don't know what the two commas immediately after the opening
>
> parentheses
>
>>>represent and I haven't been able to find in the online help a reference
>
> to
>
>>>SERIES to let me know. I suspect the commas may have to do with the way
>
> all
>
>>>my numbers appear on the worksheet. They appear in cells from B2 to P10,
>>>generally in a diagonal fashion, so B2 equals the first column's value,
>
> C3
>
>>>equals the second column's value and so on. The only departure from that
>>>fashion is when I want a stacked column, in which case the values of the
>>>stacks in the column goes from, say, H7 to K7, and then the next
>
> column's
>
>>>value appears in L8.
>>>
>>>In any event, when I tried to rename my series using "source data" and
>
> then
>
>>>"series", it wouldn't work. I also tried typing the name in the formula
>
> bar,
>
>>>replacing "sheet1!" with a name, but that didn't work either.
>>>
>>>How else can I change the series names to text?
>>>
>>>
>>
>
>