If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data labels not linked to data source
I have a chart that displays two numbers in a stacked bar chart... but
the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
You can use Rob Bovey's XY Chart Labeler to add the labels. It's a free
add-in that you can download from his web site: http://www.appspro.com/Utilities/ChartLabeler.htm viveleroi0 wrote: I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
You don't need to install an addin to do this -- it's
easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . |
#5
|
|||
|
|||
Doesn't require reinventing the wheel at all. The steps I
provided, as you saw, were very fast and simple in the UI. However, if it is something you do frequently, you can automate the process with very simple VBA. I wouldn't consider a short macro to be reinventing anything -- and you only need to even take that step if you want to automate what's already a fast and easy task. Stephanie -----Original Message----- In article , says... You don't need to install an addin to do this -- it's {snip} time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. And, reinventing the wheel in VBA is preferable to using an add-in because... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You don't need to install an addin to do this -- it's easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . . |
#6
|
|||
|
|||
Hi Tushar ... I should have added this originally. Feel
free to drop me an email if you would like a copy of the native Excel solution that I sent to the person who originally posted the request. Stephanie -----Original Message----- In article , says... You don't need to install an addin to do this -- it's {snip} time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. And, reinventing the wheel in VBA is preferable to using an add-in because... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You don't need to install an addin to do this -- it's easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . . |
#7
|
|||
|
|||
Stephanie -
What Tushar was getting at is that several free addins are available on the web to automate this process very nicely. Rob Bovey's Chart Labeler (http://appspro.com) is one of the most widely used, and it has some nice features. It allows you to specify a range which has data labels for the entire series, and it makes sure that the range has as many cells as the series has points. Second, it doesn't put the text into the data labels, it links the labels to the cells so the labels update as the cells change. Third, it copies the cell formats onto the data label formats. Fourth, it incorporates the positions of the labels right in the initial dialog. Finally, it allows you to move a series of labels, or just a single label, with little arrow buttons, which allows much finer control than is possible with the mouse. The code to apply data labels to points is widely available: http://support.microsoft.com/default...;en-us;Q161513 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Stephanie Krieger wrote: Hi Tushar ... I should have added this originally. Feel free to drop me an email if you would like a copy of the native Excel solution that I sent to the person who originally posted the request. Stephanie -----Original Message----- In article , says... You don't need to install an addin to do this -- it's {snip} time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. And, reinventing the wheel in VBA is preferable to using an add-in because... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You don't need to install an addin to do this -- it's easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . . |
#8
|
|||
|
|||
Thank you for that offer. I've had a web page about using that method
for a data label and a chart title for several years now. It is also very effective when used with a text box inserted into a chart. Dynamic Chart Title http://www.tushar- mehta.com/excel/newsgroups/dynamic_chart_title/index.html As far as 'simple VBA' goes, we will have to disagree. My Hover Chart Labels add-in (http://www.tushar- mehta.com/excel/software/chart_hover_label/index.html) has a bare-bones capability to create dynamic labels. Just the userform related code is over 120 lines -- and that is in my particular style of writing compact code. True, the rest of the code -- sans the goodies of a more sophisticated add-in -- is short. It's about 20 lines. But in any general purpose utility, 80-90% of the code is invariably associated with the UI, so that should not be a surprise. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar ... I should have added this originally. Feel free to drop me an email if you would like a copy of the native Excel solution that I sent to the person who originally posted the request. Stephanie -----Original Message----- In article , says... You don't need to install an addin to do this -- it's {snip} time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. And, reinventing the wheel in VBA is preferable to using an add-in because... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You don't need to install an addin to do this -- it's easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . . |
#9
|
|||
|
|||
Thanks, Jon -- I appreciate the detail on that addin.
Certainly sounds like a nice tool -- I just think its usually simpler and faster to let the application do what it can do well on its own. The one thing the addin does, as you described, that you can't automatically do in the user interface is specify the full data series at once when linking the labels to a custom source -- that's nice -- it's the thing I'd write a macro to do ... which is personal preference, I think, unless the addin offers additional perks you can't accomplish in the standard UI. (As you saw in the steps I provided, and I'm sure you know, it's super easy to link the labels to a custom source that updates, and carries over the number format (that last part is Excel default behavior for any linked text boxes or labels) right on the chart itself with no addins or code). I'm curious to see if it offers other options (like more flexible scatter\bubble chart labeling than you can automatically do in the UI). I'll check it out. Always nice to discover new things. Thanks again for the info. Stephanie -----Original Message----- Stephanie - What Tushar was getting at is that several free addins are available on the web to automate this process very nicely. Rob Bovey's Chart Labeler (http://appspro.com) is one of the most widely used, and it has some nice features. It allows you to specify a range which has data labels for the entire series, and it makes sure that the range has as many cells as the series has points. Second, it doesn't put the text into the data labels, it links the labels to the cells so the labels update as the cells change. Third, it copies the cell formats onto the data label formats. Fourth, it incorporates the positions of the labels right in the initial dialog. Finally, it allows you to move a series of labels, or just a single label, with little arrow buttons, which allows much finer control than is possible with the mouse. The code to apply data labels to points is widely available: http://support.microsoft.com/default.aspx?scid=kb;en- us;Q161513 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Stephanie Krieger wrote: Hi Tushar ... I should have added this originally. Feel free to drop me an email if you would like a copy of the native Excel solution that I sent to the person who originally posted the request. Stephanie -----Original Message----- In article , says... You don't need to install an addin to do this -- it's {snip} time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. And, reinventing the wheel in VBA is preferable to using an add-in because... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You don't need to install an addin to do this -- it's easy to do just with Excel. I'm emailing you a sample workbook to demonstrate these steps: 1. Create the % calculations as another column on your data worksheet (if you don't want it to appear on your worksheet, you can hide the column after the chart is setup). Then, select the first individual data label that you want to display the percentage (to do that, select the series of data labels and then click once on the individual label to select only that one). Don't click into the data label, but with it selected, click into the formula bar and type the equal sign, followed by the cell reference containing the percentage you need (once you type the equal sign, you can also browse to and select the data cell instead of typing it in). Then, press Enter to set. The data label will be dynamically linked to that percentage, which is calculated from your actual data. If your chart is very large (that is, if there are too many data labels to do this in a reasonable period of time) or if its something you do frequently -- you can automate this in VBA pretty easily. Let me know if that info would be useful. Hope this helps. Stephanie Krieger author of Microsoft Office Document Designer email: blog: arouet.net -----Original Message----- I have a chart that displays two numbers in a stacked bar chart... but the data labels are integers, which the source data is. I want the data labels to display the percentage that source column A is of B... For example the source data is A1: 5, B1: 10, and the chart displays the data label as 5 and 10 respectively. I want the data label to show 50% (A1/B1) instead. How can I do that? --- Message posted from http://www.ExcelForum.com/ . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Charts which automatically adapt to variable source data length | Excel Engineer | Charts and Charting | 1 | August 15th, 2004 12:47 PM |
Preserving Font of Data Source when Merging | Allison Orange | Mailmerge | 3 | June 9th, 2004 05:06 PM |
Auto-exclusion of data labels for zero values | Max | Charts and Charting | 2 | February 24th, 2004 04:24 PM |
Creating Data Labels from cell data | Mike M | Charts and Charting | 1 | December 20th, 2003 02:54 PM |
Source Data Ranges in Charts | Tushar Mehta | Charts and Charting | 0 | November 30th, 2003 03:29 PM |