 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
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. ALLpurchases 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
Excel Trader PackageTechnical 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates