|
Formatting ALL data labels for ALL data series at once
I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data
Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Answer: Formatting ALL data labels for ALL data series at once
No, you're not missing anything. By default, Excel applies data labels to each data point individually, so you have to change the label settings for each data series separately. However, there is a workaround that allows you to format all data labels for all data series at once.
Here are the steps:
This should format all data labels for all data series at once. Keep in mind that this will also change the labels for any future data series you add to the chart. If you want to revert back to the default settings, simply repeat the steps and choose "Value" instead of "Series Name" and "Horizontal" instead of "Rotate all text 90 degrees". |
Formatting ALL data labels for ALL data series at once
You can pick Show Series for all series of labels at once, if you select the
chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
Thanks Jon.
I forgot to mention i'm using XL 2007. I don't see a chart menu / options. How can i bring that up? On any case, i built a macro to do what i wanted. Sub FormatChartDataLabel() ActiveSheet.ChartObjects("Chart 1").Activate Dim ch As Chart Set ch = ActiveChart Dim sc As SeriesCollection Set sc = ch.SeriesCollection Dim s As Series For Each s In sc Set dl = s.DataLabels dl.ShowSeriesName = True dl.ShowValue = False dl.Orientation = -4171 Next End Sub "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
Ah yes. Excel 2007 has lost a lot of the familiar old dialogs. Since you're
using code in your post, here is what the macro recorder provides in 2003, and it works in 2007: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ HasLeaderLines:=False, ShowSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False However, since you're using code, looping through all series in a chart shouldn't be as painful as manually formatting each series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... Thanks Jon. I forgot to mention i'm using XL 2007. I don't see a chart menu / options. How can i bring that up? On any case, i built a macro to do what i wanted. Sub FormatChartDataLabel() ActiveSheet.ChartObjects("Chart 1").Activate Dim ch As Chart Set ch = ActiveChart Dim sc As SeriesCollection Set sc = ch.SeriesCollection Dim s As Series For Each s In sc Set dl = s.DataLabels dl.ShowSeriesName = True dl.ShowValue = False dl.Orientation = -4171 Next End Sub "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
Thanks.
I only coded the macro because i couldn't find a way through the UI. It unfortunate that the UI does not have a way to do this... Thanks Vasco "Jon Peltier" wrote: Ah yes. Excel 2007 has lost a lot of the familiar old dialogs. Since you're using code in your post, here is what the macro recorder provides in 2003, and it works in 2007: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ HasLeaderLines:=False, ShowSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False However, since you're using code, looping through all series in a chart shouldn't be as painful as manually formatting each series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... Thanks Jon. I forgot to mention i'm using XL 2007. I don't see a chart menu / options. How can i bring that up? On any case, i built a macro to do what i wanted. Sub FormatChartDataLabel() ActiveSheet.ChartObjects("Chart 1").Activate Dim ch As Chart Set ch = ActiveChart Dim sc As SeriesCollection Set sc = ch.SeriesCollection Dim s As Series For Each s In sc Set dl = s.DataLabels dl.ShowSeriesName = True dl.ShowValue = False dl.Orientation = -4171 Next End Sub "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
Hi Vasco,
You don't have a UI method to change all the alignments and type of info displayed with one command, but in 2007 the Format Data Labels dialog box is Modal, which mean you can leave it open and click on each of the sets of data labels one at a time, make your changes and go to the next set without closing the dialog box. Cheers, Shane "Vasco" wrote in message ... Thanks. I only coded the macro because i couldn't find a way through the UI. It unfortunate that the UI does not have a way to do this... Thanks Vasco "Jon Peltier" wrote: Ah yes. Excel 2007 has lost a lot of the familiar old dialogs. Since you're using code in your post, here is what the macro recorder provides in 2003, and it works in 2007: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ HasLeaderLines:=False, ShowSeriesName:=True, ShowCategoryName:=False, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False However, since you're using code, looping through all series in a chart shouldn't be as painful as manually formatting each series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... Thanks Jon. I forgot to mention i'm using XL 2007. I don't see a chart menu / options. How can i bring that up? On any case, i built a macro to do what i wanted. Sub FormatChartDataLabel() ActiveSheet.ChartObjects("Chart 1").Activate Dim ch As Chart Set ch = ActiveChart Dim sc As SeriesCollection Set sc = ch.SeriesCollection Dim s As Series For Each s In sc Set dl = s.DataLabels dl.ShowSeriesName = True dl.ShowValue = False dl.Orientation = -4171 Next End Sub "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
this works to add the series name but not the alignnment. Is t here a way to
change to alighment to all of them too? (I have excel 2003) thanks! "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
Formatting ALL data labels for ALL data series at once
You cannot apply other formatting to more than one series of labels at a
time. One series at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Meenie" wrote in message ... this works to add the series name but not the alignnment. Is t here a way to change to alighment to all of them too? (I have excel 2003) thanks! "Jon Peltier" wrote: You can pick Show Series for all series of labels at once, if you select the chart, go to the Chart menu Chart Options Data Labels tab. This does all series at once, not just the ones you've already labeled. You cannot apply other formatting to more than one series of labels at a time. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Vasco" wrote in message ... I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data Labels/Inside End). Then, i actually want the data series name to show instead of the value and also want the text orientation to be vertical. I can change this for a single data series, but not for all at once. Am i missing something ? |
| All times are GMT +1. The time now is 03:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com