• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Protect Chart Labels

Is there a way to protect chart labels of a dynamic chart so that you can't accidentally edit them?

The reason why i'd like to be able to do this, is because if I accidentally change a label in a dynamic chart it stops automatically updating.

Thank you in advance.

Anya
0
anya_morris
Asked:
anya_morris
  • 5
  • 3
  • 2
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

can you explain in a bit more detail how editing a label would break a dynamic chart? Do you have an example?

I suggest to store the label text in the worksheet and use the XY Chart Labeler tool to configure the chart labels. Then you can edit the labels in the worksheet cells and nothing will break.

cheers, teylyn
0
 
Gašper KamenšekExcel MVPCommented:
Hi,

if the Chart is dynamic, then you should just protect it by Review/Protect Sheet and in the dialog box do not check Edit Objects and nobody will be able to edit a chart...

Options
Hope this helps
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
GasperK, in what way does that provide any functionality specific to a dynamic chart as opposed to a static chart?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Gašper KamenšekExcel MVPCommented:
Define a dynamic chart :)

To me that says either a chart based on the PivotTable or a chart based on an Excel Table... So a Range formatted as a table. The only other option is to use a Dynamic named range, which can be tedious particularly with charts.

The main thing is whichever method is used, protecting the sheet does not(!) influence the dynamics of the charts... The only thing it does do, is it protects the chart so it cannot be altered in any way manually.

The way I understood the Q above is that manual changes are the one destroying the chart.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Dynamic chart: To people working a lot with charts that means that the data source for the chart dynamically grows and shrinks as data is added or removed, depending on the number of rows/columns in the source table. Pivot Table, maybe, Excel Table, maybe. But lots of other options as well. A dynamic chart can be fed with dynamic range names to define the data series instead of fixed cell references.  Or filtering techniques. Or data substitution formulas. These were about long before Excel Tables were released.

http://peltiertech.com/Excel/Charts/Dynamics.html

Since the asker mentions "dynamic chart" twice in the question, I assume that it's not a chart with a fixed cell reference. Protecting the sheet and the chart object removes the option to edit the chart altogether. But it does not explain why a dynamic chart would break when chart labels are edited.

Of course, any chart has an element of dynamics in that it re-renders when the data values change. But that does not make it a dynamic chart.

To troubleshoot the underlying issue, a sample file showing the behaviour would be very helpful.
0
 
anya_morrisAuthor Commented:
I've attached the file with the dynamic chart.

When I update the price in the table the chart labels change to reflect the update.

However, if I manually remove the percentage in the labels and then update the price in the table, then the labels will not automatically update anymore.
copy-BD-Report-Design-Other-2013.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Here is where the XY Chart Labeler tool will help. It works on all charts, not just XY charts.

When you manually edit the text in the labels, the connection to the underlying data is broken. That is by design. Instead of editing the labels, edit the text in the source data.

If you want a custom text in some of the labels, create the text in a worksheet cell. The cell with the label text can contain a formula that concatenates other data from the chart source. Then select the label, hit F2, enter a = sign and click the cell with the desired label text.

This last step is what the XY Chart Labeler tool automates. It is useful for charts with many labels. If you only have a few data points, like in the pie chart, you may as well do it by hand.

As per the discussion above, this is not a dynamic chart. Also, you should avoid pie charts if you have more than three data points. And 3D never improves a chart, especially not a pie chart.

cheers, teylyn
0
 
anya_morrisAuthor Commented:
Teylyn,

Thank you for your answer.

The tool you're describing is good, however I don't need it at the moment. My problem is that somebody might change the label text manually and then break the link to the underlying data. I suppose I can protect parts of the excel sheet if I give it to someone to work on? Would that be a good solution?

Also, I'm still not sure why my chart is not a dynamic chart.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

As I said above: any chart is kind of dynamic, since it will update the plot when the underlying data changes. If you plot 12 months, from Jan to Dec, the chart has 12 data points. If any of the 12 numbers change, the chart will reflect these changes and draw a different point or bar. But that is not what some people consider a "dynamic chart".

An example for a dynamic chart: The chart plots 12 months. The data table shows many more months. Maybe 2 or 3 years worth. Each month, another column is added to the data table. A dynamic chart can be set up to plot only the last 12 months of the table with xxx months, or it can be set up so that the user selects how many months to plot and what the start month of the chart should be.

That is where the "dynamic" comes in. Instead of having a fixed data source, for example $A$1:$B$12, a dynamic chart uses various techniques to define the data source dynamically, based on either user selection or other parameters (plot the last 12 months only, for example).

I hope that explains the concept of dynamic charts.

Getting back to your concern that a user might change a label and break the chart: Use the measures that Excel provides to protect your sheet. GasperK has shown a way to protect objects in a sheet. A chart is an object. If you dis-allow editing of an object, then the user won't be able to edit the labels and break the chart.

In the long run, the best way to solve this is user education, so people understand what they are doing when they click and overwrite things like labels in a chart.

What measure you want to take will depend on

- how critical it is to protect the integrity of your chart
- how easy/hard it is to educate the users in proper behaviour
- whether or not you want to use sheet protection to protect parts of the sheet from accidental changes.

Sheet protection is a pretty straight-forward way to avoid accidental changes. Why not give it a try?
0
 
anya_morrisAuthor Commented:
Great answer. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now