Solved

Protect Chart Labels

Posted on 2013-06-18
10
341 Views
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.

Anya
0
Comment
Question by:anya_morris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 50
ID: 39258448
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
 
LVL 10

Assisted Solution

by:GasperK
GasperK earned 250 total points
ID: 39258553
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
 
LVL 50
ID: 39258651
GasperK, in what way does that provide any functionality specific to a dynamic chart as opposed to a static chart?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:GasperK
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.
0
 
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.

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
 

Author Comment

by:anya_morris
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.
copy-BD-Report-Design-Other-2013.xlsx
0
 
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
0
 

Author Comment

by:anya_morris
ID: 39270630
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 250 total points
ID: 39270807
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
 

Author Comment

by:anya_morris
ID: 39277427
Great answer. Thank you.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

710 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