Excel Charts : Automatically Updating Custom Leader Lines

< Back to Search results

 Category: [Excel]  Demo Available 

Excel Charts : Automatically Updating Custom Leader Lines


See also:  

Charting for Beginners

Got a Excel Chart question? Use our FREE Excel Help!

This chart has 2 textboxes for displaying information regarding the 1st and 4th slice. The custom leader lines will adjust along with the pie chart data to always connect the textbox corner with the two end points of the pie slice. This effect is created by using a combination chart, pie and xy-scatter, and some math formula.

Below is all the data and formula required to create the chart. The highlighted cells contain the actual charted data.

1   Auto leader lines   % Angle Radius
2 a 3   0.272727273 98.18182 0.61
3 b 1   0.090909091 32.72727  
4 c 1   0.090909091 32.72727  
5 d 3   0.272727273 98.18182  
6 e 3   0.272727273 98.18182  
9 Slice   X Y    
10 1 90.00 3.74E-17 0.61    
11     0.95 0.95    
12   -8.18 0.603791 -0.086812051    
14 4 -73.64 0.171857 -0.585290714    
15     -0.95 -0.95    
16   -171.82 -0.60379 -0.086812051    


1   Auto leader lines   % Angle Radius
2 a 3   =B2/SUM($B$2:$B$6) =360*D2 0.61
3 b 1   =B3/SUM($B$2:$B$6) =360*D3  
4 c 1   =B4/SUM($B$2:$B$6) =360*D4  
5 d 3   =B5/SUM($B$2:$B$6) =360*D5  
6 e 3   =B6/SUM($B$2:$B$6) =360*D6  
9 Slice   X Y    
10 1 90 =COS(RADIANS(B10))*$F$2 =SIN(RADIANS(B10))*$F$2    
11     0.95 0.95    
12   =B10-E2 =COS(RADIANS(B12))*$F$2 =SIN(RADIANS(B12))*$F$2    
14 4 =90-SUM(E2:E4) =COS(RADIANS(B14))*$F$2 =SIN(RADIANS(B14))*$F$2    
15     -0.95 -0.95    
16   =B14-E5 =COS(RADIANS(B16))*$F$2 =SIN(RADIANS(B16))*$F$2    
Although the finished chart is a pie chart it is easier to construct if we begin with the xy-scatter chart.
Select the range C10:D16 and use the chart wizard to create a standard xy-scatter chart

Use the source data dialog to add another series to the chart.

Select the 2nd data series and change the chart type to pie.

Use the source data to specify the location of data, B2:B6, and labels, A2:A6, for the pie chart.

Change the axis properties for both x and y axis. Set the minimum and maximum value to -1 and 1 respectively.

The only problem with the chart in its current configuration is that the area available for the leader lines is restricted to the plot area, which is too close to the edge of the pie chart.

To increase this area we need to explode and re assemble the pie chart. Select the pie and drag the slices away from the center. The further away you drag the slices the smaller the pie chart will end up.

After exploding and thereby reducing the size of the pie we need to drag the slice back together. If you do this whilst all the slices are selected then the pie will return to its original size. You have to do each piece individually. So select the pie and then select an individual slice. Drag this back to the center and then repeat for all the other pieces.

You now have a smaller pie chart which will allow more space for the leader lines. The actual radius of the chart can be entered in to the cell F2. The end point of the leader lines, where the 2 lines meet, is controlled by the values used in cells C11:D11 and C15:D15. The position of contact of the leader lines with the pies circumference is calculated via the formulas.

Format both axis to remove lines, labels and tickmarks.

You should now have a pie chart with leader lines for slices 1 and 4. This will automatically adjust for any changes in the pie charts data.

Further formatting of the pie, leader lines, legend and chart can be applied. As can the textboxes used to hold any information needed.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

Count Lines in a Cell
Count of Each Item in a List
Excel VBA Macro - Count Or Sum By Fill Color
Count Words in a Cell
Create Excel Workbook & Worksheet Templates
Excel Custom Number Formats


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. Escort in Auckland XPAGE


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


stars (0 Reviews)