renaming of the values on the x-axis (i.e. category names)

[wildetudor]

Say I have a row of values, I select them and I create a column chart. The
names of the values on the horizontal axis (i.e. the name of each category)
will be, since I did not provide data for them, integers: 1, 2, 3, ...

Is there a simple way to rename each category (for example, have "Costs"
instead of "1" etc), without having to create cells that have those names and
then recreate the chart including those cells?

Thanks.

 

[Jon Peltier]

You don't need to recreate the chart. Select the first charted series and
look in the formula bar:

=SERIES(,,Sheet1!$B$2:$B$10,1)

The formula has four arguments:

Series Name (blank, text, or an address)
Category Labels (blank, an address, or a literal array)
Values (an address or a literal array)
Plot Order (a number)

If Series Name is blank, Excel uses Series 1, Series 2, etc.
If Category Labels is blank, Excel uses 1, 2, 3
An address could be
- a cell address, Sheet1!$B$2:$B$10
- a name reference, Sheet1!MyData or Book1.xls!MyData
A literal array means a list of values, like {1,3,4,8} or
{"alpha","beta","gamma"}

You can enter any of these items right in the formula bar.

You seem to want to keep the worksheet free of such clutter as source data
for your chart. So you could enter a literal array for your category names.
Keep in mind that you are limited to around 250 characters for this literal
array, including the curly braces. Also keep in mind how much harder it is
to edit the series formula than worksheet cells. I suggest you put your
labels into a range and use this address in the formula.

You can place your range of labels next to the values as is customary, or on
a part of the sheet which is off screen, or even on another sheet. If you
place them in a hidden row or column, by Default Excel will not plot them
(and revert to 1, 2, 3), but you can change the chart's behavior to plot
hidden cells.

- Jon

 

[wildetudor]

This answers my question, thanks very much Jon!

You don't need to recreate the chart. Select the first charted series and
look in the formula bar:

=SERIES(,,Sheet1!$B$2:$B$10,1)

The formula has four arguments:

Series Name (blank, text, or an address)
Category Labels (blank, an address, or a literal array)
Values (an address or a literal array)
Plot Order (a number)

If Series Name is blank, Excel uses Series 1, Series 2, etc.
If Category Labels is blank, Excel uses 1, 2, 3
An address could be
- a cell address, Sheet1!$B$2:$B$10
- a name reference, Sheet1!MyData or Book1.xls!MyData
A literal array means a list of values, like {1,3,4,8} or
{"alpha","beta","gamma"}

You can enter any of these items right in the formula bar.

You seem to want to keep the worksheet free of such clutter as source data
for your chart. So you could enter a literal array for your category names.
Keep in mind that you are limited to around 250 characters for this literal
array, including the curly braces. Also keep in mind how much harder it is
to edit the series formula than worksheet cells. I suggest you put your
labels into a range and use this address in the formula.

You can place your range of labels next to the values as is customary, or on
a part of the sheet which is off screen, or even on another sheet. If you
place them in a hidden row or column, by Default Excel will not plot them
(and revert to 1, 2, 3), but you can change the chart's behavior to plot
hidden cells.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______