Bug Fix Needed: Data Labels - Value From Cells - Text Not Updating
When using the new Value From Cells option for data labels in an XY Scatter chart, if the range you select for the Value From Cells contains any BLANK cells and then you save the file and close the file ... when you re-open the file and try to edit the blank cells to add data labels to the chart, the data labels remain blank. Normally, you can edit the cells to edit the data label text and the data label text updates immediately.
Current Work-Around: (1) Go to Format Data Labels (2) Click on the Reset Label Text button (3) Uncheck the Value From Cells box (4) Re-Check the Values From Cells box.
Application where this is important: Timeline Charts
Version: Excel 2016 on Windows 10, and Mac (occurs on more than just my machine, based on user feedback)
My data labels were appearing fine until I added some new data, then the labels for the new data were not appearing (which led me to this post).
I was able to get around this bug by navigating to the "Format Data Label" pane, selecting one of my already visible labels, and clicking "Clone Current Label" (which only appeared near the bottom of "Label Options" when one label is selected). This is tentative but I think did the trick for me.
My computer no see Label position
Ed Puckett commented
Ozgur, I'll keep that solution in mind. I have a matrix chart for a smaller set of data and it appears to be working well.
Ozgur Ozcan commented
Ed Puckett I was dealing with a similar problem. In order for you to visualize lets say I have number data in cell A1 and percentage data related to this number in B1, number data in C1 and percentage data in D1 and so forth. What I wanted is just to show the percentages not the numbers in data labels but went nuts. Here is how I solved it, during range selection if you select the range starting from A1 Excel get confused and shows some percantages (i.e. you have 5 data but labels show 3 of them but shifted somehow and does not indicate the correct data). Then I tried selecting the range starting from the first percentage data (cell B1) instead of first number data (cell A1) and everything went fine. I hope this will be helpful for your case.
Thanks x 20
Ed Puckett commented
I wanted to throw my hat into the ring as well. I am having the same problem. All but one data label (pulled from a cell range) is showing. Driving me crazy! See attachment.
Bill Schrank commented
Happens for Line charts too. Very irritating bug.
As many have noted, this is a very old issue (seemingly going back to at least 2012).
This is the best example of the issue laid out and acknowledged by Microsoft.
Mohamed's work around is pretty slick if you're able to use an scatter plot with data only above 0.
Here’s the work around I found after much frustration and forum digging (if you’re willing to use macros).
' Activate the chart (this doesn't work if directly accessing the chart objects)
' Select the datalabel series
' Reset the data labels
' Reshow the label range
Selection.AutoText = True
Selection.ShowRange = True
This is incredibly annoying I have my pie chart saved as a template with all the correct labels I want visible and when the data range is updated those same labels are removed. I then have to go into label options and reapply them to the pie chart.
Mohamed Aly commented
I found a way that worked for me...
Hi there, i came across this thread as this issue was driving me crazy for the past two weeks. I'm not the end-user of the models I build and the workbooks needs to be locked for editing so the work around couldn't possible help in my case.
My data originally incorporated NA() function to hide the labels when empty. First I replaced the NA() with "" (blank) and that didn't work either. Then I replace with negative value then set my min. value of the vertical axes to 0 (this way any negative value is out of sight) and that worked. I saved, closed, reopened and all works fine.
It seems that as along as the label isn't empty at any time it won't disappear.
Hope this helps someone out there.
Select one data cell for X and Y axis and create a graph. Later select the graph and choose the whole data sets either just dragging with the mouse or using keyboard . If you are selecting the data set for X it automatically selects for Y.
This is working for me in MS Excel 2019.
Does anyone know if this has been resolved? Having the same issue in Excel 2016. I can fix within a session but when I save, exit, and re-open, I pretty much always run into the same issue.
Manually it works! thanks!
Doesn't appear to be resolved. FYI, I ran into this doing a combo chart with ClusterBar and Line series, not a scatter chart. TG for good SEO to this page!
Another workaround is to save your chart as a template (while labels appear correctly) and reapply the template each time excel starts playing games
Should also work fine with the macro at file open
I'm also looking for a non-manual fix for this issue
Apie Heunes commented
I have the same issue in Excel 2013. Thanks for the solution posted as it solved my issue with the data labels. I have a Dashboard model to display project data but have to do this "Work-Around" every time I select a new project. Is there any permanent fix for this in Excel 2013
Hello. Has this ever been resolved? I'm having the exact same issue with a bubble graph I created in Excel. The manual workaround from OP works, but is there a way to automate this? Or an actual fix?
Tried the macro and it worked till excel decided to forget from which cells the values are suposed to come now I'm back at square one.
Any update on a real fix for the issue?
Data labels are missing for unknown reasons. When I construct the XY scatter plot for the first time everything is OK (all labels are there), than if some of my data are missing or if I rearrange the data arrays some labels of data points on XY graph are missing (on every graph different labels!). I have more than one XY graphs in one worksheet. Please addvise ASAP!
Ancheng Deng commented
In addition to my previous comment, here is the tutorial page to set macro to run at file open: https://analysistabs.com/excel-vba/run-macro-automatically-opening-workbook/