How do I fix data labels in Excel?

If you build charts in MS Excel you may discover that some charts (especially ones that have positive and negative numbers) don’t look great as the axis labels overlap the data.

Table of Contents Show

YouTube Stop the Axis Labels overlapping the chart
Stop Labels overlapping chart
Subscribe to our YouTube channel- Today’s Excel Tip for the day
How do I lock a label in Excel?
How do I align data labels in an Excel chart?
How do you fix overlapping data labels in Excel?

YouTube Stop the Axis Labels overlapping the chart

Watch, like, subscribe

As shown below, the April label overlaps the data for the first bill.

Stop Labels overlapping chart

There is a really quick fix for this.

As shown below:

Right click on the Axis
Choose the Format Axis option
Open the Labels dropdown
For label position change it to ‘Low’

The end result is you eliminate the labels overlapping the chart and it is easier to understand what you are seeing .

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Change the invert if negative colour in Excel charts

Turn off automatic date and time grouping in Excel Pivot Tables

Date Axis in Excel Chart is wrong

Chart Data Table number formats not working

I have a combination chart, with stacked column bars and a plot line. At the top of each stacked column is a no-fill total bar with visible data labels. The data labels overlap with the points on the plot line and I would like to manually raise them about an inch for better readability.

Currently, the data labels for total are set to inside base, but I don't see a way to manually adjust this position for all labels at once beyond the three options listed (center, inside end, inside base).

I know I can go through and move each individual label around, but am looking for a way to do it all at once, keeping it uniform and clean.

Here's an image of a similar type chart. In my case, I need the data labels raised for the totals and I'm using stacked columns.

asked Sep 28, 2017 at 15:01

JordanJordan

1231 gold badge1 silver badge7 bronze badges

Let's say you're starting from a chart like this: stacked columns of widget sales by type, and a line chart showing a general revenue trend.

You want to display the total widget label above each column, but the line is in the way - so any labels would need to be raised above the line somehow.

Add a 'ghost' series

You can't position labels arbitrarily on the chart without manually moving them round - not ideal. So you can create a 'ghost' series where the column is invisible but the labels that sit above it are visible.

In your data table, add a column titled ghost. Enter a formula something like =B2+5000, and copy down. The ghost column can not be on the same vertical axis as your stacked data on the chart - so make sure the formula uses the data from your line series (column B in my data), plus an extra amount to push the label higher up the chart. I've used 5000 here, but play with the number once you're up and running to nudge the labels up and down.
Right-click your chart and click Select Data. Add a new series, and select the range for your 'ghost' column.
On the ribbon go to the Chart Tools, Design tab and click Change Chart Type. In the Custom Combination screen, scroll down and set 'ghost' to Clustered Column (ie unstacked column) and click OK.
Right-click the new column that's appeared on your chart and click Add data labels. Now right-click a column from that series, and change the fill to No fill.
Right-click the labels that have appeared. Click format data labels. Make sure Outside end is selected, then tick value from cells. Now select the data range containing the labels you want to see on your chart - in my case 'Total Widgets' (don't include the column heading itself). Finally untick Value from the Label Contains...
That's it! Nudge the labels up or down as you need using the formula in the 'ghost' column.