Using a dummy data series on
the secondary axis.
|
|
|
Here is our data set. The
actual chart data is in the range A1:D5
I have added formula to E2:E5 to store the cumulative total to be
displayed.
| |
A
|
B
|
C
|
D
|
E
|
1
|
|
Data A
|
Data B
|
Data C
|
Total
|
2
|
Qrt
1
|
2
|
4
|
4
|
=SUM(B2:D2)
|
3
|
Qrt
2
|
2
|
2
|
3
|
=SUM(B3:D3)
|
4
|
Qrt
3
|
3
|
1
|
2
|
=SUM(B4:D4)
|
5
|
Qrt
4
|
4
|
1
|
1
|
=SUM(B5:D5)
|
|
|
|
Select the range A1:E5 and use
the chart wizard to create a standard Stacked column chart.
|
|
|
Select the data series 'Total'
and via the Format Data Series dialog (CTRL+1) change the Axis
to Secondary axis.
This will make the 'Total' data
series appear in front of the other data series.
|
|
|
To finish off format the
'Total' data series to have no border and no pattern. This will
allow the other data series to be seen. Also apply Value data
labels.
To remove the extra data series
form the legend see "Delete
legend entry"
|
|
|
Using a dummy series on the
same axis
|
|
|
This time our data set includes
2 helper columns. E2:E5 contains a constant value that will be
used to display the data labels. F2:F5 contains formula for the
cumulative total.
| |
A
|
B
|
C
|
D
|
E
|
F
|
1
|
|
Data A
|
Data B
|
Data C
|
Dummy
|
Total
|
2
|
Qrt
1
|
2
|
4
|
4
|
2
|
=SUM(B2:D2)
|
3
|
Qrt
2
|
2
|
2
|
3
|
2
|
=SUM(B3:D3)
|
4
|
Qrt
3
|
3
|
1
|
2
|
2
|
=SUM(B4:D4)
|
5
|
Qrt
4
|
4
|
1
|
1
|
2
|
=SUM(B5:D5)
|
|
|
|
Format the 'Dummy' data series
as before, setting the border and pattern to none.
Apply the values data labels.
|
|
|
Format the 'Dummy' data series
as before, setting the border and pattern to none.
Apply the values data labels
|
|
|
In order to get the data labels
to display there cumulative total instead of there true value we
need to alter the text.
Static method:
Select the data labels and then select an individual data label.
Simply edit the text in the data label.
Dynamic link method:
Select the data labels and then select an individual label. Go to
the formula bar and enter '=' (without quotes) and then
select the cell to be linked. This will enter the full cell
address. If you want you can just type the address directly in
the formula bar.
Here are 2 free add-ins to make
the chore of linking data labels to cells easier.
|
|
|
The use of a dummy series on
the same axis is suitable for use with 3d stacked column charts
|
|
|
|
|
|
AJP Excel Information
