Chart data source selection changes when the file is edited from Excel Online

Question

I have an Excel workbook containing a sheet of various charts that are locked for editing. The data for the charts comes from other sheets in the workbook which are editable by other users (they can only input data into certain unlocked cells). Whenever other sheets in the file are edited, the data source selection for Horizontal (Category) Axis Labels changes and selects boxes that were previously unchecked. This happens with a number of charts, even when data has not been input into the data source range for a particular chart. Is there a way to preserve the axis selections as I am having to continually edit the file to revert the data selection back to what it should be. Thanks.

Answer 1

Hi Victoria_CET,

I found that your link contains personal information (tenant name...), to protect your privacy, I masked the link and create a new link via Personal OneDrive, would you mind working on this workbook (Sample file) temporarily?

Appreciating for your understanding.

Best Regards,

Hugo

Answer 2

Thanks - did you open it in the app or browser? I've just opened the file in my browser, changed a figure in period three, and the top chart now looks like this (note that Groups A-D are now included, whereas before only All groups were):

Answer 3

I can not see the issue, changed C2 in Sheet1 to 99% and Sheet2 looks pretty good:

Answer 4

Thanks for your reply. I've created a sample file that contains dummy data and two charts where only certain items are checked in the data source range (similar to how they are set up in the original file). It seems the issue only happens when the file is edited in the browser (rather than the app). Thanks.

[PII is masked by Hugo Liang MSFT Support]

Answer 5

We need a concrete example to reproduce that.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data.

At best make a copy of your original file and anonymize the necessary data. For this please download this file

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Open your Excel file

Right-click on the sheet tab

Choose "View Code"

Press CTRL-M

Select the downloaded file and import

Close the VBA editor

Select the cells with the confidential data

Press Alt-F8

Choose the macro Anonymize

Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.

https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.