By using various features in Excel, we can represent data graphically. Also, we can show statistical data in columns, bars, or pie charts. These methods help us analyze data quickly. In this article, we will show you how to add data labels in Excel.
How to Add Data Labels in Excel: 2 Handy Ways
In Excel, graphs or charts help to visualize the data. But, if the data labels are not present in those graphs, then it becomes difficult to understand or analyze. In this article, we will show you two handy ways to add data labels in Excel. In the first approach, we will insert the Chart Elements command to add data labels in Excel, and in the second method, we will use Visual Basic Applications (VBA) for the same purpose. You can see a sample data set below that we will use for both methods.
Read More: What Are Data Labels in Excel
1. Inserting Chart Elements Command to Add Data Labels in Excel
In our first approach, we will insert the Chart Elements command to add data labels. See the full procedure below to understand.
Step 1:
- Firstly, select your entire data set to create a chart or graph.
- The cell range is B4:D10 in our example.
Step 2:
- Secondly, we will create a 2D clustered column chart.
- For that, first of all, go to the Insert tab of the ribbon.
- Then, choose the Insert Column or Bar Chart command from the Chart group.
- Lastly, select the Clustered Column command.
Step 3:
- After selecting, you will be able to create the chart.
- Then, name the chart “Height vs. Weight” for example.
- You will notice that there are no data labels in the chart.
Step 4:
- Fourthly, we will add the data labels to our chart.
- For that, click on the chart and you will see three icons right beside the chart.
- Of the three icons, the first one is the Chart Elements command.
- Click on that command and you will see some more commands regarding the chart.
- From those options, mark the Data Labels command which was previously unmarked.
- Finally, after marking the command, you will see all the data labels above the columns accordingly.
Step 5:
- Furthermore, if you want to add the data labels in another way, then select any column of the same variable and then right-click on it.
- For example, we will right-click on the column that represents Height.
- Then, select the Add Data Labels command.
- Finally, you will see the data labels for the columns that represent Height.
Step 6:
- Furthermore, repeat the same process from Step 5 by selecting any column that represents the weight of the person.
- Consequently, you will see the data labels after repeating the process.
Read More: How to Add Additional Data Labels to Excel Chart
2. Applying VBA to Add Data Labels in Excel
There is another way besides the first method to add data labels in Excel. That is applying Visual Basic for Applications (VBA). You can follow the following steps for this method.
Step 1:
- Firstly, we will use the following data set for our procedure.
Step 2:
- Secondly, we will create a 2D clustered column chart by using the above data set.
- For this, we will repeat Step 1 to Step 2 from the previous method to get our chart.
- Then, we will name the chart “Height vs. Weight”.
Step 3:
- Thirdly, choose the Visual Basic command from the Code in the Developer tab of the ribbon.
Step 4:
- Fourthly, a new tab will open after choosing this command.
- From the new tab, choose Module from the Insert tab.
Step 5:
- Then, into the Module paste the following VBA Code which will add data labels after running the program.
Sub Add_Data_Labels()
Dim ch As ChartObject
Set ch = ActiveSheet.ChartObjects("Chart 1")
ch.Chart.SetElement (msoElementDataLabelOutSideEnd)
End Sub
- After that, save the program in the module and press the play button to run the code.
Step 6:
- Finally, you will be able to add data labels to the chart with the help of VBA.
How Does the Code Work?
Set ch = ActiveSheet.ChartObjects("Chart 1")
This line of code assigns the chart to the chart type variable ch on which we want to add data label (Chart 1 in my case).
ch.Chart.SetElement (msoElementDataLabelOutSideEnd)
This line of code adds data labels with Outside End orientation to the chart. There are more orientations that we can use such as Inside End, Inside Base, Data Callout etc. For using any of those, we need to use their corresponding property in SetElemet.
Read More: How to Format Data Labels in Excel
You can download the free Excel workbook from here and practice on your own.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to add data labels in Excel by using any of the methods. Please share any further queries or recommendations with us in the comments section below.
