Displaying single and multiple data series.Single data series

[Donald Macleod]

I was asked to produce a chart displaying outstanding staff appraisals for 4
regional offices. The data used is 'sales', 'admin' and '%age of total
staff', (the total staff being those employed in that region) . I managed to
produce a chart showing 4 sets of 3 columns, a nice colour scheme, separate
sheets generated for each month, etc.

Now I've been asked to include a 'reference' column which just shows the
total staff for all 4 offices. This sounded simple but hasn't turned out
that way.

I have produced another data series and selected a single cell as the data
source but while this displays on the chart, Excel assumes I want it shown
with the first set of data and that I also want a blank column on each of
data sets 2-4. What I really want is for this column to be separate from the
others and shown on the left.

Any help appreciated would be appreciated. Even being told it isn't possible
would be a step forward.

 

[Jon Peltier]

Set up your data as shown below:



Sales Admin %Total Reference

Reference [data]

Office A [data] [data] [data]

Office B [data] [data] [data]

Office C [data] [data] [data]

Office D [data] [data] [data]



Select the entire range, and create a clustered column chart. Double click
on the Reference data point (the lonely column above the first category),
and on the Axis tab, select Secondary. Select Chart Options from the Chart
menu, and on the Axes tab, uncheck the Secondary Y Axis box.



Voila.


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

 

[Jon Peltier]

The table didn't paste correctly from Notepad. Here's what the data should
look like:

Sales Admin %Total Reference
Reference [data]
Office A [data] [data] [data]
Office B [data] [data] [data]
Office C [data] [data] [data]
Office D [data] [data] [data]

Top left cell must be blank, completely clear.

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

Set up your data as shown below:



Sales Admin %Total Reference

Reference [data]

Office A [data] [data] [data]

Office B [data] [data] [data]

Office C [data] [data] [data]

Office D [data] [data] [data]



Select the entire range, and create a clustered column chart. Double click
on the Reference data point (the lonely column above the first category),
and on the Axis tab, select Secondary. Select Chart Options from the Chart
menu, and on the Axes tab, uncheck the Secondary Y Axis box.



Voila.


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

I was asked to produce a chart displaying outstanding staff appraisals for
4 regional offices. The data used is 'sales', 'admin' and '%age of total
staff', (the total staff being those employed in that region) . I managed
to produce a chart showing 4 sets of 3 columns, a nice colour scheme,
separate sheets generated for each month, etc.

Now I've been asked to include a 'reference' column which just shows the
total staff for all 4 offices. This sounded simple but hasn't turned out
that way.

I have produced another data series and selected a single cell as the
data source but while this displays on the chart, Excel assumes I want it
shown with the first set of data and that I also want a blank column on
each of data sets 2-4. What I really want is for this column to be
separate from the others and shown on the left.

Any help appreciated would be appreciated. Even being told it isn't
possible would be a step forward.