Protect Chart Labels

Posted on 2013-06-18
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

Expert Comment

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

GasperK earned 250 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

Expert Comment

ID: 39258651
GasperK, in what way does that provide any functionality specific to a dynamic chart as opposed to a static chart?
LVL 10

Expert Comment

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

Expert Comment

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.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


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

Expert Comment

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

teylyn earned 250 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now