How to Format Data Labels in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, we have a platform where you can format data labels. Basically, we can modify the chart data labels by using format data labels in Excel. This article will mainly focus on how to format data labels in Excel. I hope you enjoy this article and find it interesting.

To format the data labels in Excel, we have shown some step-by-step procedures. Basically, we create a chart and add data labels to that. After that, we modify the data labels using format data labels. All these steps are fairly easy to understand. Moreover, it provides a complete overview of how to format data labels in Excel more precisely. Therefore, we will show the final output of each modification of data labels. To show format data labels in Excel, we take a dataset that includes some countries’ name and their corresponding two product such as fruits and vegetables sales amount.

How to Format Data Labels in Excel


Step 1: Creating a Chart in Excel

Before formatting any data labels in Excel, you need to create a chart from your dataset. After that, we can add data labels and then modify the data labels effectively.

  • At first, go to the Insert tab in the ribbon.
  • Then, from the Charts group, select the Column chart option.

  • In the Column chart option, select the first chart from the 2-D Column section.

  • As a result, it will make a black chart where you need to add data to get your required chart.
  • Then, right-click on the blank chart.
  • A Context Menu dialog box will appear.
  • From there, select the Select Data option.

  • It will open up the Select Data Source dialog box.
  • Then, in the Legend Entries section, click on Add.

  • Then, the Edit Series dialog box will appear.
  • Set a Series name and define the Series values.
  • We define the Fruits column as Series Values.
  • Finally, click on OK.

  • Then, add another series called Vegetables.
  • Here, we define column D as Series Value.
  • After that, click on OK.

  • Then, in the Select Data Source dialog box, click on the Edit option from the Horizontal Axis Labels.

  • In the Axis Labels dialog box, select column B as the Axis label range.
  • Then, click on OK.

  • Finally, click on OK in the Source Data Source dialog box.

How to Format Data Labels in Excel

  • Finally, we get the following chart. See the screenshot.

How to Format Data Labels in Excel

  • To modify the Chart Style click on the Brush sign on the right side of the chart.
  • Here, you have lots of chart styles to select.
  • Then, select any of your preferred chart styles.

How to Format Data Labels in Excel

  • As a result, we get the following chart as a final solution.

How to Format Data Labels in Excel

Read More: What Are Data Labels in Excel


Step 2: Adding Data Labels to Excel Chart

Our next step is basically to add data labels to the chart. As we produce a column chart with our dataset, we need to add data labels in the corresponding columns.

  • At first, right-click on any of the columns of the Vegetables series.
  • A Context Menu will appear.
  • Then, select Add Data Labels options.

How to Format Data Labels in Excel

  • It will add data labels to all the columns of the Vegetables series.

How to Format Data Labels in Excel

  • Then, right-click on any of the columns of the Fruits All the columns in the Fruits series will be highlighted.
  • It will open up a Context Menu.
  • Then, select Add Data Labels option.

  • It will add data labels to all the columns of the Fruits series.
  • We get the following chart after adding data labels in all the columns. See the screenshot.

How to Format Data Labels in Excel

Read More: How to Add Additional Data Labels to Excel Chart


Step 3: Modifying Fill and Line of Data Labels

Our next step is to modify fill and line from format data labels. In this step, we need to open the format data labels after that we can modify the fill and line of the data labels.

  • At first, right-click on the data labels of any column.
  • It will open up a Context menu.
  • From there, select the Format Data labels option.

How to Format Data Labels in Excel

  • It will open up the Format Data Label dialog box.
  • Here, you have four different modification options.
  • In this step, we will discuss the modification of Fill & Line which is the first of four options.

  • By default, we have no fill and no line in our data labels.
  • At first, change the Fill from No fill to Solid fill.
  • You can change the fill color from the color section.

How to Format Data Labels in Excel

  • As a result, we have found the following chart.

How to Format Data Labels in Excel

  • Then, we change the Fill from Solid fill to Gradient fill.

How to Format Data Labels in Excel

  • It will give us the following result in the chart.

  • Then, we change Fill from Solid fill to Picture or texture fill.
  • We can also change the texture color from the Color section.

How to Format Data Labels in Excel

  • As a result, we get the following results in the chart.

  • Next, in the Border section, change the border from No line to Solid line.
  • You can also change the Color and Width of the border.

How to Format Data Labels in Excel

  • As a result, we will get the following result. See the screenshot.

  • Next, change the border from a Solid line to a Gradient line.
  • You can also change the Color and Width of the border.

How to Format Data Labels in Excel

  • Finally, we get the following result. See the screenshot.

Read More: How to Edit Data Labels in Excel


Step 4: Formatting Data Labels by Changing Effects

Our next step is based on the modification of the effects of the data labels from format data labels. In the Effects section, we can modify the shadow, glow, soft edge, and 3-D format.

  • At first, modify the shadow option.
  • It will create a shadow on the data labels.
  • We can change the Preset and Color of the shadow from the drop-down box.

How to Format Data Labels in Excel

  • As a result, we will get the following results.

  • Next, we will alter the glow section of the data labels.
  • Just like the Shadow section, we change the Presets and Color of the glow.
  • We take the size 10.

How to Format Data Labels in Excel

  • It will give us the following result with the glow. See the screenshot.

Read More:  How to Change Font Size of Data Labels in Excel


Step 5: Modifying Size and Properties of Data Labels

In this step, we will modify the size and properties of the data labels from data labels. Here, we can alter the alignment of our data labels. Moreover, we can change the text direction also.

  • At first, we need to modify the alignment.
  • To modify the alignment, we need to click on the third option of format data labels.
  • Then, in the Alignment section, change the Vertical alignment to Middle centered from the drop-down option.
  • It will eventually set the data labels in the middle center.
  • We can set the data label alignment as top, bottom, middle, top centered, or bottom centered from the drop-down option of Vertical alignment.

  • It will give us the following results. See the screenshot.

  • After that, we can change the Text direction.
  • It is basically how you try to represent your data in the chart. It can be in the horizontal direction, rotate all text 90 degrees, or rotate all text 270 degrees.
  • By default, we have horizontal Text direction. You can modify this in the drop-down option of Text direction.

How to Format Data Labels in Excel

Read More: How to Rotate Data Labels in Excel


Step 6: Modifying Label Options to Format Data Labels in Excel Chart

Our final step is to modify label options from data labels. Here, we can modify the label options and can also modify numbers which means we can convert data values in different forms like currency, general, number, etc.

  • At first, go to the fourth option of the format data labels which is known as label options.
  • In the Label Options section, we can modify the Label Contains and Label Position.
  • Then, in the label containing a section, we may include series name, category name, and value for making the data labels more precise.

How to Format Data Labels in Excel

  • Here, we include the category name and value of the labels.
  • As a result, we will get the following results.

  • Then, in the Label Position section, by default, we have an outside end label position.
  • We can set it in the center, inside end, or inside the base.

How to Format Data Labels in Excel

  • Let’s set the label position in the center. We will get the following results.

How to Format Data Labels in Excel

  • After that, you can convert your data label into a different format from the Numbers section.
  • Here you just basically change the category from the drop-down option. It will automatically convert your data labels.


Download Practice Workbook

Download the practice Workbook below.


Conclusion

To format data labels in Excel, we have shown a step-by-step procedure. All of these steps provide you with a basic overview regarding format data labels. This article also covers how to create a chart using your dataset and how to add data labels. I hope you find this article really informative and gain lots of knowledge regarding the issue.


Related Articles


<< Go Back To Data Labels in Excel | Excel Chart Elements | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo