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.
Add leader lines to stacked column
Add leader lines to stacked column
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.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
