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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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.

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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
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.