Fahim Shahriyar Dipto
In Microsoft Excel, you can create a chart with your dataset. Different types of charts are available in Excel. The doughnut chart has a hole in the center of it, which gives it the shape of a doughnut. It shows data as part of a whole relationship. There is also a pie chart that is almost the same as this doughnut, but there is no hole in the middle. But you cannot show the data labels in the doughnut chart like the other charts. You should go for a manual process or use the VBA macros to insert the data labels outside the doughnut chart in Excel. In this article, we are going to show you how to show labels outside the doughnut chart in Excel. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
2 Methods to Display Labels Outside Doughnut Chart in Excel
For creating a doughnut chart, you need to insert a dataset first. Then we can create the chart. Here, we have taken a dataset of the Sales Report of XYZ Company where we imputed the Month-wise Sales.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Creating Combo of Pie and Doughnut Charts
You can use the pie chart and the doughnut chart together to show the data label outside the doughnut chart. Generally, the doughnut chart doesn’t provide the data labels. But in the pie chart, you can display the data labels. Follow the steps to do that.
📌 Steps:
- Firstly, select the entire data range >> navigate to the Insert tab >> Insert Pie or Doughnut Chart >> choose Doughnut.
- Consequently, a doughnut chart will be displayed in your file, and as you can see, there are no labels in the chart.
- At this moment, select the dataset again and hover over the Home tab >> choose Paste from Clipboard >> pick Paste Special.
- Subsequently, the Paste Special window appears. Check New series in Add cells as type. Also, check the Series Names in First Row and the Categories (X Labels) in First Column.
- Lastly, hit OK.
- Sequentially, another doughnut chart is inserted. Right-click on the chart and pick the Change Series Chart Type from the Context Menu.
- Eventually, the Change Chart Type window pops out. Choose Pie >> Pie chart from the All Charts option. Click OK.
- A new pie chart has been inserted, covering the doughnut chart.
- Now, right-click on the chart and choose Add Data Labels from the Context Menu.
- As you can see, the data labels have been added to the chart. Now, choose the Data Labels from the Chart Elements and pick the Outside End.
- Apparently, the labels are outside the chart. Now pick up the Format Data Series from the Context Menu after right-clicking the chart.
- Moreover, in the Format Data Series window, check the No fill in the Fill section and No line in the Border section.
Finally, the doughnut chart has labels outside the chart, like the image below.
Note: You cannot show the Legends from the above process. But if you insert another doughnut chart having the same size as before, where you can show the Legends.
Read More: How to Create A Doughnut, Bubble and Pie of Pie Chart in Excel
2. Employing VBA to Label Outside in Doughnut Chart
The main limitation of the first method is the absence of Chart Legends in the final output of Doughnut chart. In such a situation, you can use VBA macros. It can automatically insert a doughnut chart, where the label will be outside of the chart and Legends will be available. By applying the code, you will be able to insert the labels along with the chart from your dataset. Follow the steps to do it.
📌 Steps:
- Firstly, navigate to the Developer tab >> choose Visual Basic.
- Consequently, choose the Insert tab >> Module >> Module 1.
- Now, write up the following code in the Module 1 box.
Option Explicit
Sub pie_as_Sheet2()
Dim Wbook As Workbook
Dim WSheet As Worksheet
Dim Chng_Shape As Shape
Dim lab As DataLabel
Dim x As Long, y As Long, w As Long, h As Long, cd As Long
Dim circ As Shape
Set Wbook = ThisWorkbook
Set WSheet = Wbook.Sheets("VBA")
Set Chng_Shape = WSheet.Shapes.AddChart2
cd = 75
With Chng_Shape.Chart
With .ChartArea
.Format.Fill.ForeColor.RGB = RGB(244, 244, 244)
.Height = 250
.Width = 400
.Left = 80
.Top = 80
End With
.ChartType = xlPie
.SetSourceData WSheet.Range("B4:C12")
.HasTitle = False
.HasLegend = False
.ApplyDataLabels xlDataLabelsShowLabel, , , , , True, , True, , vbLf
With .FullSeriesCollection(1).DataLabels
.Position = xlLabelPositionOutsideEnd
.NumberFormat = "0.0%"
End With
With .PlotArea
x = .Left
y = .Top
h = .Height
End With
Set circ = .Shapes.AddShape(msoShapeOval, x + h / 2 - cd / 2, _
y + h / 2 - cd / 2, cd, cd)
With circ
.Line.Visible = msoFalse
.Fill.ForeColor.RGB = RGB(244, 244, 244)
.Shadow.Type = msoShadow30
End With
End With
End Sub
Code Breakdown
- Firstly, we set all the variables needed to construct the code.
- Secondly, we fixed the worksheet name to VBA where we want to insert the chart.
- Eventually, the chart size was fixed, and the circle radius was set to 75.
- Then, the dataset was selected and we applied the xlDataLabelsShowLabels command to display the data labels. We also set the Number Format command to 0.0%.
- Following that, we inserted the PlotArea object to specify the plot area of the created chart.
- Later, we declared the circle shape and the color of the chart with RGB (244, 244, 244).
- Finally, we choose the msoShadow30 as a MsoShadowType to define a certain type of shadow.
Finally, a doughnut chart has been inserted, which labels the outside of the chart like the image below.
Read More: How to Change Color Based on Value in Excel Doughnut Chart
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to show labels outside the doughnut chart in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.
Related Articles
- How to Create Progress Doughnut Chart in Excel
- Insert Leader Lines into Doughnut Chart in Excel
- How to Make Doughnut Chart with Total in Middle in Excel
- Change Hole Size of Excel Doughnut Chart
- How to Make 3D Doughnut Chart in Excel (Create with Easy Steps)
- Create Curved Labels in Excel Doughnut Chart (With Easy Steps)
- Excel Doughnut Chart with Multiple Rings (Create with Easy Steps)
