Protect Chart Labels

Posted on 2013-06-18
Medium Priority
Last Modified: 2013-06-26
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.

Question by:anya_morris
  • 5
  • 3
  • 2
LVL 50
ID: 39258448

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
LVL 10

Assisted Solution

by:Gašper Kamenšek
Gašper Kamenšek earned 1000 total points
ID: 39258553

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...

Hope this helps
LVL 50
ID: 39258651
GasperK, in what way does that provide any functionality specific to a dynamic chart as opposed to a static chart?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 10

Expert Comment

by:Gašper Kamenšek
ID: 39258667
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.
LVL 50
ID: 39258702
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.


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.

Author Comment

ID: 39259292
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.
LVL 50
ID: 39260858
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

Author Comment

ID: 39270630

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.
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 39270807

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?

Author Comment

ID: 39277427
Great answer. Thank you.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question