Hiding some labels on value axes; Changing series names

[Leslie]

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 Peltier]

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/ChartsHowTo/ChartSeriesFormula.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]

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

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/ChartsHowTo/ChartSeriesFormula.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/
_______

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 Peltier]

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/
_______

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

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/ChartsHowTo/ChartSeriesFormula.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?