Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Charts : Display Total on Stacked Column Chart

| | Information Helpful? Why Not Donate.

Also see our huge range of Charting Software . Got a Excel Chart question? Use our FREE Excel Help

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 the 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.

The use of a dummy series on the same axis is suitable for use with 3d stacked column charts.

Back to Excel Charts Index

Also see our huge range of Charting Software

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Charts : Display Total on Stacked Column Chart

| | Information Helpful? Why Not Donate.

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)

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)

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS