How to add leader lines to stacked column in Excel?
In Excel, it’s impossible to add leader lines for stacked column, but here, I will tell you a tricky way to show leader lines in a stacked column in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To add leader lines to stacked column in Excel, the steps are a little difficult and long, please be patient to do step by step.
For example, you have below data needed to make a stacked column with leader lines.
Before creating a Stacked Column chart, you need to prepare the data range as below steps:
Please do as follow:
1. You need to create some data as leader lines.
(1) In Cell C9, type this formula =SUM(B1:B2)/2;
In Cell C11, type this formula =SUM(B2:B3)/2;
In Cell C13, type this formula =SUM(B3:B4)/2;
In Cell C15, type this formula =SUM(B4:B5)/2;
In Cell C17, type this formula =SUM(B5:B6)/2;
In Cell 19, type this formula =SUM(B6:B7)/2;
In Cell 21, type this formula =SUM(B7:B8)/2.
Note: B1: B8 is the original data range you have, you can change the cell as you need.
(2) In Cell 10, Cell 12, Cell 14, Cell 16, Cell 18, Cell 20 and Cell 22, type 0.001.
(3) Then select range C9: C22, right click to select Format Cells to open Format Cells dialog , and format the cells you need.
2. Then create the data as the data labels.
(1) Copy series names (except header) from the original range, and paste them in Cell A23.
(2) Copy original data in column B and paste them in Cell D23.
Now the table has created. And you can insert the stacked column.
3. Select all the table you created (in our case, we select the range of A1:D29), and click Insert > Column > Stacked Column.
4. Then click at the column to show Chart Tools tab, and click Design > Switch Row/Column.
5. Then click at the legends in the chart, press Delete key to delete them. Then click Layout > Data Labels > Center to add data labels to the stacked column.
In Excel 2013, click Design > Add Chart Element > Data labels > Center.
6. Click at any series in the chart, and right click to select Format Data Series from context menu.
7. In the Format Data Series dialog (or Format Data Series pane), shrink the gap width. See screenshot:
8. Close the dialog. Then click at the right column and right click to select Format Data Labels from context menu. See screenshot:
9. Then select a label in the right column, and go to the Format Data Labels dialog/pane, check Series Name and uncheck the Value in the Labels Options section. See screenshot:
Repeat this step to change all the data labels contents to series name in the right column. After changing all labels to the series names, close the Format Data Labels dialog. Then you stacked chart will show as following screenshot:
10. Right click at right column and select Format Data Series from context menu.
11. Select a series in the right column, then go to the Format Data Series dialog/pane, and click Fill > No fill.
Repeat this step to remove all series’ fill color in right column. After all fill color being removed, close the Format Data Series dialog.
12. Delete the axes and grid lines.
13. Right click at middle column, and select Format Data Series. Then select a series in the middle column, next go to the Format Data Series dialog/pane and click Fill > No fill.
Repeat this step to remove fill color of all the series but the o.1% series. After removing fill color of all series in middle column, please close the Format Data Series dialog. See the below screenshot:
14. Now remove all the data labels in the middle column one by one. And the stacked column with leader lines has been created.