Thread: data labels don't move on bar chart

Hi, I need some advice on a problem I have with bar charts. They have data lables on them showing the value of the bar. When I add new data to the source data (for a new month) a new bar appears on the right of the charts as you'd expect but none of the data lables move along. I have to delete and recreate them individually.

I'm sure there is a simple change to the chart to get the labels to stay with the data they represent rather than anchor to the chart, but I haven't found the answer.

Can you advise how I can get this working please?

***** update *****

I attach a sample file.

It has a 'chart' and a 'data' worksheet. The data labels are fine at present, but to add new data (this has 12 rolling periods) in the data sheet I would copy lines 3 to 13, select line 2 and past them. Then I would change the date in A13 to the new period and change the figures as required in C13,D13,E13.

If I then look at the chart the old data will have moved to the left and the new data would be in the right hand bar, however all the labels would have stayed where they were and I'd have to delete them and add then again individually. This is the problem I'm trying to solve. I'm sure I'm doing something stupid but I cant see what.

Thanks in advance for your assistance.

Regards
Pete

Hello Pete,

could it be that the "labels" have been created with individual text boxes?

If you click a data series and format it to show data labels, what happens? Do more labels pop up?

It might be a good idea to post a data sample. Take a copy of the file, remove all unneccessary data and replace confidential data with dummy data. Then upload the file

cheers

Thanks Teylyn.

They are proper data labels... right clicked the bar, 'format data point', 'data labels' and put a tick in the VALUE box of "label contains"

I'll sort out a sample to attach.

Regards,
Pete

Woolmep,

in the interest of getting responses to your posts, don't edit the first post, but reply to suggestions and provide additional information in replies. This way the post will light up with new replies and people will know that there is new information. If you just edit an existing post, there will be no indicator that anything has changed.

After I found that you had attached a file (to your original post), I opened it with Excel 2010. The chart was illegible.

(By the way, it's a column chart, not a bar chart. As you can see in the chart menu, bars run horizontally, columns run vertically. This can be a critical distinction in certain situations, so it pays to get the terminology sorted.)

I think there are several problems with the chart, apart from the labels.

The series Unscheduled Downtime, Scheduled Downtime, Exception Downtime and Availability are all percentages. The series Threshold % is not a percentage. You'll never be able to chart that on one Y axis and keep a readable scale.

The whole chart behaves wonky when I dealt with it. I've never had problems with charts in 2010, so I believe there must be something wrong with the chart. OK, 2010 is still in beta, but pretty well-behaved. I'm not in front of XL2003 right now, but I will be in about 10 hours, so I'll have another look then.

In the meantime, could you please take a screenshot of what the chart looks like on your system and attach it here? Then I'd have a point of reference.


cheers

Jeepers! It looks nothing like that in Excel 2010. I'll have a look with Excel 2003 in the morning.

FYI, the attached screenshot shows what it looks like when I open it in 2010. Verrrrry strrrrange!!

OK, got it to display in Excel 2003. Don't know what's up in 2010.

The problem, as I see it, is that you have deleted individual data labels from series, where the value is 0 and you don't want to see the "0.00%" splattered all over the chart. That's understandable, but unfortunately, when you delete individual labels, the setting to add labels for the whole series gets broken, so adding a new column of data will not have labels applied.

What to do? Well, it's really easy:

- remove all labels from all series
- one by one, add the labels to each series
- format the labels to the font of your liking and then set the number format of the labels to custom format

0.00%;-0.00%;;

This format will suppress zero values, so you will only see data labels for values greater than or less than zero.

Now, when you add a new column of data, the series-wide label will be applied.

hth