How to have data labels follow time adjusting line charts?

Apr 24, 2010
Reaction score
This seems like quite the Excel 2007 resource board, so maybe you can help me with a problem I'm trying to solve. I have many charts used to track financial information, this is several years of weekly data, and the charts show the most recent 52, 104, 156 weeks worth of data.

I used to manually go in every month or so and update the x-axis on all the charts and put in fixed dates for the period I wanted to see. This obviously takes a lot of time since there are like 15 different charts. Then I learned about the "Name Manager" feature which I wasn't familiar with. This allowed me to modify the charts to automatically plot the most recent period in question, based on how much data is in the sheet. If I add 4 weeks of raw data, then pull up the chart, it will be re-calculated and the entire line shifts over to the left and the chart now shows the most recent 52 weeks of data. So far so good.

Here is the problem. There are 4 data points in the chart where an underlying value, assume it is shares of Apple Computer, increase due to a new purchase. The line chart shows the total value of all shares, so there is a gap up at that date when the new shares were purchased. I added a data label to the chart that shows the number of shares purchased on the 4 dates in question. The problem is that when I add new data to the spreadsheet, and the line chart automatically moves to the left, the data labels remain in the same position on the chart and do not "follow the date" of the purchase.

I could manually move them, but it would be nice to have them automatically move. I tried creating "data markers" to attach the labels to, since I read in the help somewhere that data labels could be somehow made to automatically follow the markers. But after doing this I found that both the data markers and data labels both remained at the same position on the chart, and neither followed the actual points to the left when the chart adjusts to new data.

Maybe there is some magic checkbox somewhere that I'm not aware of?

Last edited:


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question