ThisCalculator

Everyday

How to Use Data Labels in Excel Charts

Updated · Standard formula, shown below

Data labels put the exact value (or category name) right next to each point on an Excel chart, so readers don’t have to guess from the axis or hover over a point. This guide shows you how to add, position, format, and fix data labels in Excel for Windows and Mac, covering the most common chart types.

What You’ll Need

  • A chart already inserted in Excel (column, bar, line, pie, scatter, etc.)
  • The data used for the chart should be in a worksheet range
  • Excel 2016 or later (most steps work in older versions too)

Step-by-Step

  1. Select the chart. Click once on any blank area of the chart to select it. You’ll see a border around the chart and the Chart Tools tab appear in the ribbon.
  2. Open the Add Chart Element menu. On the ribbon, go to the Chart Design tab (called Design on Mac). Look for the Add Chart Element button in the Chart Layouts group. Click it.
  3. Choose Data Labels. From the dropdown, hover over Data Labels. A submenu appears with options like Center, Inside End, Outside End, and More Data Label Options. Pick a position that suits your chart. For a bar or column chart, Outside End is common. For a pie chart, Outside End works well. For a line chart, Above is often best.
  4. Use the quick + button (alternative). Click the chart, then click the + button that appears at the top-right corner of the chart. Check the Data Labels box. To change the position, click the arrow next to the checkmark and pick a spot.
  5. Format the labels. Right-click on any data label and choose Format Data Labels. A pane opens on the right (or a dialog box on Mac). Here you can:
    • Choose what to show: value, category name, series name, or a combination.
    • Add a separator (comma, semicolon, new line).
    • Change number format (e.g., show percentages, decimals, or thousands).
    • Set a label position (center, inside end, outside end, etc.).
  6. Customize individual labels. Click once on a label to select all labels, then click again on a single label to select just that one. You can then edit the text directly in the formula bar or in the label box. This is useful for highlighting one point.
  7. Use cell values as labels. If you want labels that come from a specific range (not the chart’s default), right-click a label and choose Format Data Labels. Check Value From Cells (or Cell Value on Mac). Select the range of cells you want to use. This is great for custom text like “Q1” or “Target reached”.
  8. Remove labels. To remove all labels, go back to Add Chart Element > Data Labels > None. Or uncheck the Data Labels box in the + menu.

Common Mistakes

  • Labels overlapping. When you have many data points, labels can overlap and become unreadable. Try a different position, or use the Label Contains option to show only the value and not the category name.
  • Showing the wrong data. By default, Excel shows the value. If you want the category name (like month names), you must change it in Format Data Labels. Many people forget this and end up with numbers when they wanted text.
  • Percentage without a pie chart. If you want to show percentages, you need to either use a pie/doughnut chart or add a calculated column with the percentage formula. Excel won’t automatically convert values to percentages for a bar chart.
  • Labels not updating. If you edit the source data, labels should update automatically. If they don’t, you might have manually typed text into a label, which breaks the link. Delete the manual text and re-add the label.
  • Too many labels. On a line chart with 50 points, labels make the chart unreadable. Consider showing labels only for key points (select those individually) or using a data table instead.

Tips

  • Use the Label Options tab in Format Data Labels to control exactly what appears. You can combine value and category name with a separator like a space or new line.
  • For pie charts, use Outside End and enable Leader Lines to connect labels to slices when they’re far apart.
  • To format all labels at once, click once on any label to select the whole series. Then apply font, color, or number format changes.
  • Use the Number section in Format Data Labels to show numbers in thousands (e.g., 1,200 instead of 1200) or with a specific decimal places. This keeps labels clean.
  • If you have a scatter plot, the default label is the y-value. To show the x-value or a custom label, use Value From Cells or change the label contains option.
  • Keyboard shortcut: After selecting the chart, press Ctrl+1 (Windows) or Cmd+1 (Mac) to open the Format pane for the selected element, which can speed up formatting.

Troubleshooting

  • Labels not showing at all. Make sure you selected the chart, not a single data point. If you right-clicked a point and chose Add Data Labels, it might only add one label. Click the chart border first, then add labels.
  • “Value From Cells” is grayed out. This option only works for some chart types (like bar, column, line, scatter). It’s not available for pie charts or doughnut charts. For those, you’ll need to manually edit each label or use a text box.
  • Labels show “1” instead of the actual value. This often happens when the chart is based on a PivotTable or when the data range includes blank cells. Check that the source data is correct and that the chart is set to plot the right series.
  • Labels are cut off. Increase the chart area or the plot area size. Right-click the chart area, choose Format Chart Area, and adjust the size. Or change the label font size to something smaller.
  • Labels don’t match the axis values. If you have a line chart with dates on the axis, the labels might show the date instead of the value. Go to Format Data Labels and uncheck Category Name, then check Value.
  • After editing data, labels are wrong. If you manually typed a label, it won’t update. Right-click that label and choose Reset Label Text to revert to the automatic value.

Frequently Asked Questions

How do I add data labels to only one data point?

Click once on the data series to select all points, then click again on the specific point you want to label. Right-click and choose Add Data Label. Only that point gets a label.

Can I use a formula to create custom data labels?

Yes. Use the Value From Cells option and select a range that contains your custom text or formula results. The label will show whatever is in those cells, including text, numbers, or formulas.

Why are my data labels showing the same number for every bar?

This usually happens when you’ve selected a single data label and then copied it, or when the chart is using a single value as the series. Check that your chart’s source data includes multiple values and that the series is properly defined.

How do I show data labels as percentages on a bar chart?

Excel doesn’t have a direct “percentage” option for bar charts. You need to add a helper column with the percentage formula (e.g., =B2/SUM($B$2:$B$10)), format it as a percentage, and then use Value From Cells to display those cells as labels.

Can I change the position of a single data label?

Yes. Click once on the label to select it, then drag it to a new position. The label will stay where you put it, even if the chart updates.

How do I remove data labels from a chart?

Select the chart, go to Chart Design > Add Chart Element > Data Labels > None. Or right-click a label and choose Delete to remove all labels.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.