Free Microsoft Excel 2013 Quick Reference

Excel 2007 chart data labels move on save

I have a macro that fixes the data labels since my client doesnt want them on the position excel has like center,inside end. etc. My client wants the label on top of the bars so basically this is the code in my macro

ActiveSheet.ChartObjects("Chart 1").Activate 
With Selection 
    .Position = xlLabelPositionInsideEnd 
End With 
ActiveSheet.ChartObjects("Chart 1").Activate 
For i = 1 To ActiveChart.SeriesCollection(2).Points.Count 
    ActiveChart.SeriesCollection(2).Points(i).DataLabel.Select = - 17 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the macro does it well and fixes the data labels. My problem is once I save it and re open the file the labels would move to the original position which is xlLabelPositionInsideEnd. This worked fine for me in excel 2003 now I'm stumped in 2007

Additional info:
This seems to be the problem when saving the file in .xls I tried this with just dummy datas and a new workbook and it kept happening to me when im in saving it in .xls but if I save it in .xlsx the positioning would stay put. The problem is that I'm not allowed to use .xlsx format since some clients are still using 03 is there a work around for this.

Post your answer or comment

comments powered by Disqus

I have the below chart in Excel 2003:

The data labels are dynamic and link to data which changes each month. I want to be able to set the data labels so that they change position depending on the data displayed.

If the data label is negative, I want to show the label below the bar. If it is positive, I want to show it above the bar. Does anyone know how I can acheive this without manually moving the data labels each month?

Thanks in advance!

I have a chart with 3 lines with a data label. Is there any way to
automatically put the Highest number to be over the line and the other line
data label?

I have a dashboard with a series of line charts.
The client's required style is that there be data labels only on the rightmost point of the line series, containing the series name and most recent value.
The dashboard is run for 19 different categories, and everything else automatically updates nicely, except these labels, which as the data changes, look terrible. I am currently manually moving labels around or modifying axis parameters on multiple charts x 19.
A sample chart illustrating the problem is attached.

Can anybody think of how I can have excel automatically format the chart so that the labels do not overlap? I suspect it might have to be VBA, but I can't even think of what that VBA would need to do.

I have an "area" chart in excel 2007 with data labels. When I save the excel file in 2003 format, it moves my chart over, and re-aligns all the labels to the middle of the chart. Any way to prevent that from happening? I can send you a private message with the 2 files if that will help. Pl. let me know

Hi All,

I would really appreciate help with the following. I have an Excel 2007
file which contains a number of charts. These charts make use of
defined names which allows the charts to be automatically
updated as new data is entered in the spreadsheet.

The chart date series looks like =Book1!xvalues. When I save
and re open the workbook the same date series for the chart
now reads as =[0]!xvalues. The name of the workbook is now
replaced with [0].

Has anybody found a solution for the above?




I currently use Excel 2007 and in the earlier versions of Excel it was possible to create a stacked column chart, with an included data table, that had a Total Row in the Data Table that wasn't graphically represented in the chart. The Total Row was at the bottom of the data table and didn't have a data legend. I cannot replicate this with Excel 2007. With a bit of work I can include a total row in the source data, move it to the top of the data table and ensure that there is no fill for the Total Data Series so it isn't reflected in the stacked column chart above the data table. The problem is that the Total row is now at the top of the data table when I need it to be at the bottom. If I move it to the bottom then all the stacked columns in the above chart appear to be levitating as the base of the column is the Total Data series which has no fill. I have attached an example of what the old excel generated and what I have been able top do with excel 2007.

Any ideas?


Hi guys,

I have a line chart, excel 2003. The series represents 12-month trend (which changes every month). So since it is now Dec07, my graph is from Jan07 to Dec07, last month is was from Dec06 to Nov07, and so forth.

Does anyone know if it is possible so that my graph automatically shows data label (category name & value) will only show for the last series point?



Using Excel 2003; I have a pie chart whose chosen Chart Options/Data Labels
are "Category Name" and "Percentage" which in turn are linked to a
concatenated formula showing manager name, dollar amounts and then

When these linked formula values are updated, the chart data labels do not
automatically update and require me to go to Chart Options/Data Labels and
deselect then reselect "Category Name" and "Percentage" to force them to
update and then I have to reformat them, etc, etc.

Am I doing something wrong or does excel really not update these values on
the fly? My calculation is set to automatic.

Please let me know if it is possible to have them automatically update.

Thank you.

Orlando Vazquez

How do you position a data label on top of the data point in a radar chart?
In an XY scatter plot you can select this from within the Format Data Labels
window and then select Label Position: Center. In a Radar chart you are not
given this option. Why? How could you work around it using a Visual Basic

Hi, I have chart data labels that are displaying as one line in Excel but
push over into two lines when paste/linked into a PowerPoint slide. Is there
some way to force the labels to stay on one line? And as a general matter,
is it possible to manipulate the size of the box containing the data label at
all? I can select it but can't seem to resize manually. I also looked for
info on this in VBA help but couldn't find anything.

Any help would be appreciated.

Kind regards,

Hello All,

Does anyone know of a way to insert a custom footer into an Excel 2007 chart? I would like a small box, or similar, to appear on each chart with some text (e.g. "Source: blah blah") and a small logo. Is there a way to do this? I can create it in a on-off way by creating a text box, and even create a template which uses it, but it is not necessarily very well managed and I'm not sure how to specify the contents in VBA code, which would be the tidiest way of doing it, complete with sizing, positioning etc.

Any ideas for a clever solution?



When I make changes to the data that are the data source for a pie chart, the pie chart data labels always fail to update. I would expect this from Excel97, but I'm working in Excel 2003... Is this autoupdating issue still a known Excel bug? Is there a straightforward workaround?


- pk
"The odds are good, but the goods are odd."

I am using Excel 2007 and SharePoint V3. Both are newly upgraded and new to
me. I would love to be able to show an Excel 2007 chart on the main
SharePoint site and have it update when changes are made in Excel. So far,
multiple attempts over a number of weeks, I have been unsuccessful in getting
this to work. I will continue to try as I have seen it done, but I sure
would appreciate some advice if someone can lead me in the right direction.

Thanks in advance for any assistance. Have a great day.

Changing Data By Dragging and Dropping Columns in Excel 2007 chart
Lynn Hanna

After creating a chart, there is bug when you format the data labels. If you
go to "Format data labels..." then , on "numbers" click "custom", click
another option, then "custom" again, the itens shown on custom disappear,
showing only unsupported characters.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

I have tried to print an Excel 2007 chart (several, from the same file) to a .pdf, using the Adobe driver, and all I get is a blank page in my .pdf. I have seen a couple of other posts where people said they get distorted images, but at least they have an image.

Is this a known bug in Excel? I have no trouble printing the same charts to a real printer, or to preview.


I have to customize the quick access toolbar each time I start Excel
2007 as the changes are not saved from one session to the next. I do
not remember this happening in previous versions.

Am I doing something wrong or is that just the way it is?



Excel 2007 Demo: Data Takes Shape with Conditional Formatting

Excel 2007 - Extracting Data from Worksheet


I am trying to extract data from different excel sheets, into one summary sheet (See Example A attached).

I have tried just linking but this is not what I want, as I do not want to have the blanks in the summary sheet.

What is the best way to do this?

Thanks in advance.


Is there a way to add more information to a pie chart data label other than
manually edit, as this disables the link to source aspect.

Hi All,

This the second time I am posting this thread and am hoping that this time someone would reply.

I have a simple line chart for following data

Week Ending Sales
27/04/08 £1,234.00
04/05/08 £1,542.50
11/05/08 £2,776.50
18/05/08 £4,319.00
25/05/08 £5,861.50

The line has data Labels showing the values(With £ sign). But everytime I add a new data in 'Sales' column and extend the range of the line, the data label formatting changes and they show the values with $ sign. (for e.g. if I enter £6000.00 for week ending 01/06/08 and exten the range by dragging it to include that cell as well, all the data labels show values with $ sign)
I do not have a clue why this is happening. It would be great if someone can suggest me how to tackle this problem.

Thanks in Advance,

ManUtd Auto Merged Post Until 24 Hrs Passes;

Don't whether this will help...But I am using Excel 2007

I have a Excel 2007 file. It has a data source and a chart on the same
worksheet. I would like to save this file as a xml file. The problem is not
saving the file as xml but once the xml file is created excel is only
creating a xml file with the data and NOT to chart. Can excel output a chart
as a xml file. Any help. Thanks.

I am a very, very new Excel user, so pardon if this is a stupid question. I have created a pie chart and for each wedge there is a legend with coloured labels. I wanted to add the percentage as well, so I go to Data Labels, click Percentages and Best Fit, and voila, there are my percentages, but they are outside the Pie Chart and I want them to be NEXT to the legend.

1% small red block - Manufacturing
5% small blue block - Medicine, etc.

Can anyone help? I could send you the chart.

PS: When I got the chart, it was like that, except the percentages were skew, so I fiddled and now nothing works properly. I have tried to make the chart area smaller and bigger.

Thank you very much in advance.

I've searched all over and have not found a definitive solution, so I hope someone here can help me.

I upgraded from Office 2003 to 2007. I converted one particularly important work related Excel 2003 workbook to Excel 2007 (xlsx version). All appeared to function normally at first but...

...on several of my Line Charts with Markers, one data series will not update and seems to be "stuck" at the last plotted element of a data range before I converted to Excel 2007. Curiously, the other data series in the same chart update to the current element in their appropriate data ranges. The charts draw their data from a worksheet in the same workbook.

I've tried deleting the horizontal axis labels, replotting the affected data series, cutting and pasting the chart back to its original location, etc., and nothing works. To complicate matters, there seems to be no rhyme nor reason to which data series might not update, and on some of the charts, every data series is plotted correctly.

I've seen threads similar to this in other forums, but they typically detail the entire chart not updating...not just a single data series. Any suggestions for a different approach to fixing this would probably save what little is left of my sanity...

No luck finding an answer? You could always try Google.