Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc-stacked-column-leader-line-1

Add leader lines to stacked column


arrow blue right bubble 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.

doc-stacked-column-leader-line-2

Before creating a Stacked Column chart, you need to prepare the data range as below steps:

doc-stacked-column-leader-line-3

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.

doc-stacked-column-leader-line-4

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.

doc-stacked-column-leader-line-5

4. Then click at the column to show Chart Tools tab, and click Design > Switch Row/Column.

doc-stacked-column-leader-line-6

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.

doc-stacked-column-leader-line-7

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.

doc-stacked-column-leader-line-8

7. In the Format Data Series dialog (or Format Data Series pane), shrink the gap width. See screenshot:

doc-stacked-column-leader-line-9

8. Close the dialog. Then click at the right column and right click to select Format Data Labels from context menu. See screenshot:

doc-stacked-column-leader-line-10

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:

doc-stacked-column-leader-line-11

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:

doc-stacked-column-leader-line-12

10. Right click at right column and select Format Data Series from context menu.

doc-stacked-column-leader-line-13

11. Select a series in the right column, then go to the Format Data Series dialog/pane, and click Fill > No fill.

doc-stacked-column-leader-line-14

Repeat this step to remove all series’ fill color in right column. After all fill color being removed, close the Format Data Series dialog.

doc-stacked-column-leader-line-15

12. Delete the axes and grid lines.

doc-stacked-column-leader-line-16

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.

doc-stacked-column-leader-line-17

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:

doc-stacked-column-leader-line-18

14. Now remove all the data labels in the middle column one by one. And the stacked column with leader lines has been created.

doc-stacked-column-leader-line-1

Relative Articles:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.