Newbie needs a little help on the value axis

[Rockitman]

Never created a chart from Excel before.

My chart is simple. I simply need to chart 10 columns. 1 for each year
going back.

Actually each year needs to show 2 columns side by side, 1 showing the count
of our total portfolio (avg 180,000), and the other showing our onboard staff
for the given year (avg 50). As you can see already, the problem I'm having
with the chart I created using the wizard, using column type, is that the
value axis goes clear up to 200,000 in 20,000 increments. So his column
shows nice and fine but the staff on board column is not even visible as it
doesn't approach anywhere close to 20,000. How do I edit those values so
that the first ones would be increments of 10 until 100, then boost it out in
20,000 increments?

Also, the category axis shows numbers 1 through 10 for the columns. How do
I change those numbers to read the year I want?

 

[Del Cotter]

the problem I'm having with the chart I created using the wizard, using
column type, is that the value axis goes clear up to 200,000 in 20,000
increments. So his column shows nice and fine but the staff on board
column is not even visible as it doesn't approach anywhere close to
20,000. How do I edit those values so that the first ones would be
increments of 10 until 100, then boost it out in 20,000 increments?

You *could* hand-roll a custom axis, but that's a bit of an advanced
technique (stick around here and you'll learn the tricks in a while).
Also it kind of defeats the object of a bar graph if the length of the
bar has no linear relationship with the value.

I think a much better option for you is to make use of the built-in
facility for a secondary axis scale. That will go up the right-hand side
while the primary scale goes up the left. When you've made your graph,
and are looking at the invisible stumps of bar that are the Staff
numbers, right click on those bars and select "Format Series". Then
select the "Axis" tab and click the "Secondary Axis" radio button. Your
staff numbers should leap up.

Actually, I worry that it will cause the two bars to be overlapped, in
which case you'll have to be sneaky getting them to display separately.
See below.

Also, the category axis shows numbers 1 through 10 for the columns. How do
I change those numbers to read the year I want?

Have your data look like this before you click on the Wizard button:

[blank cell] Portfolio Dummy1 Dummy2 Staff
2001 180,000 0 0 50
2002 180,000 0 0 50
2003 180,000 0 0 50

The blank cell in the top left clues the (slightly dim-brained) Wizard
which is the series that goes on the category axis. After you've made
the graph, it's all right to fill the cell in with a title or something.

The two dummy series are there to stop your bars from overlapping. Leave
Dummy1 on the primary axis and put Dummy2 on the secondary axis. Now
Dummy 1 overlaps Staff, but we don't care because it's zero and isn't
visible, and Dummy2 overlaps Portfolio, but we don't care because
*that's* invisible. If you're having trouble finding and clicking on
these because they're too small, then temporarily populate the table
with big numbers to make visible bars.

To get the dummy series out of the legend, you can slowly single-click
twice on the legend label (not the symbol) and delete it.