Bar chart with character data in x and y axis ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
How can I create a bar chart with character data in x and y axis, not
number?
The bar chart is used for tracking production with lot name in y axis and
step name
in x axis. I know the list of first step to last step but how can I create
a chart like that.
Need your help.
Daniel
(e-mail address removed)
 
How can I create a bar chart with character data in x and y
axis, not number? The bar chart is used for tracking
production with lot name in y axis and step name in x axis. I
know the list of first step to last step but how can I create a
chart like that.
Click to expand...

y axes can only ever be numbers, never labels, so you need to create a
combination bar and column chart; the lot name will be the x axis of the
bar chart, and the step name will be the x axis of the column chart.

First make the bar chart using your lot and step number data:

Step
Lot 1 1
Lot 2 4
Lot 3 3
Lot 4 1
Lot 5 2

Use the Chart Wizard to make a bar graph out of this. The step numbers
are a random list I made up, ranging from 1 to 4, i.e. 1,4,3,1,2. Your
real data will be different of course. Now type in the following series
in another part of your spreadsheet.

Step names
First #N/A
Second #N/A
Third #N/A
Fourth #N/A

I have called the steps "First", "Second" etc. You of course will have
your own names. Copy the range, click on the chart and do Edit...Paste
Special. The Paste Special dialogue box will appear. Check that the
following options are selected:

Add cells as: New Series (*not* New Points)
Values (Y) in: Columns
Series names in first row: YES
Categories (X values) in First column: YES
Replace existing categories: NO

Now adding them in has added them as a new bar series, but don't worry.
Select them however you want (unfortunately as I have made them #N/A you
can't just click on the bars, as there aren't any). If you are having
difficulty, just change one of the #N/A to a number so you have a bar to
click on. You can always change back in a minute.

Right click on the selected series, do "Chart Type" and change the type
of that series from Bar to Column. Now the labels are along the x axis.

You need to change the bar charts numeric Y axis from its automatic 0 to
4 to a better 0.5 to 4.5 (if it's four steps you're having), then make
all the offending scale axes invisible (basically the y axes of the bar
series and the column series).

Now you have two x axes, both of them labels! Delete the "Step names"
from the legend if you want to.
 
Now you have two x axes, both of them labels! Delete the "Step names"
from the legend if you want to.
Click to expand...

There's always something I forget. The Lots in the bar chart axis will
run from bottom to top, the opposite order to what they are in the list.
To make them run from top to bottom, select Format Axis and tick
"Categories in reverse order".
 
There's always something I forget. The Lots in the bar chart axis will
run from bottom to top, the opposite order to what they are in the
list. To make them run from top to bottom, select Format Axis and tick
"Categories in reverse order".
Click to expand...

*Aaaand* because when you do that the labels disappear, also tick "Value
(Y) axis crosses at maximum category".

What else will I forget?
 
Thanks very much Del Cotter
You save my life.
I tried your procedure and it work fine
the problem is too many step.
Don't know other software to make life easier?
Again thank you
Daniel
 
Another chalenge,
In the list of operation and step sequence , I have a problem of some
operatiions having the same name (but different sequence number). How can I
get around like that ? In order to make a graph with character data in x and
y axis?

operaition step sequence
A 1
B 2
c 3
B 4
D 5

Thanks for your help.
Daniel
 
Back
Top