Is there a way to format the data labels of several chart data series at once?

[Alexander Plötz]

Hello, first time poster here, any help is appreciated.

I have a chart containing about 150 data series, and I would like to have their data labels shown in a custom number format. So far the only way I found to do that is by going through each series one by one, as I seem to be unable to select more than one. This is not a nice task to look ahaed to. Am I missing something?

Thanks in advance,

Alex

[NickyC]

Hi Alexander, welcome to the forum

a macro should do this quite easily

if you upload a copy of your file and let us know the data labels you want, we'll take a look

alternatively, you can tweak this macro to reflect the number format you want:

Please Login or Register  to view this content.

[Alexander Plötz]

Hi NickyC,

thanks very much for the quick reply. I looked briefly into macros after posting, but since I've never used them before, I was quickly discouraged. (Btw: are there some good resources you could point me to if I wanted to learn more about using macros? The in-application how-to is not really helpful thus far.)

See the attached file (as the name says, it's still very much a place for experiments). The chart in question is the "colorful" one. All the series' data labels need to be formatted like this:

##" "?/??;##" "?/??;""

At the moment only two series have this formatting; this was done manually, of course.

If it's not asking to much, I would appreciate a concise step-by-step description of the process (on a beginner's level, as far as macros are concerned), so that I will be able to reproduce it, since I will have a similar task of formatting the chart columns' colors in a later step.

Thanks,

Alex

[NickyC]

Hi Alexander
if you have never used macros before, this is how you copy one from this website inhto your workbook

To copy and use a macro

• press alt+F11 to open the VBA area
• select insert > module
• copy the code from this website and paste it into the VBA module you just inserted
• Return to the spreadsheet. On the developer tab, select macros and click the name of the macro you want to run

If you cannot see the developer tab:

• Click the File tab
• Click Options
• Click Customize Ribbon
• Under Customize the Ribbon and under Main Tabs, select the Developer check box

This macro should apply the formats you want to the selected chart:

Please Login or Register  to view this content.

you will have to save the file as a macro-enabled workbook with the extension .xlsm if you want the macro to be available in future.



I have copied the macro into a copy of the workbook and applied it you your chart so you can see it, in the attached file.

[NickyC]

... on your more general question, I am self-taught in macros, which means I'm quite good at some stuff and hopeless at others. I'd recommend proper training if you're likely to use them regularly. To get started, I'd suggest using the macro recorder while you are performing simple actions (typing in formulas, copying etc) then looking in the VBA area (Alt+F11) to see how Excel records what you have done. This will get you started on how the syntax and commands operate, and basics like objects, properties and methods.

This website is also very good for hints and explanations.

The macro above is a simple one. It says that, for each series in the active chart, apply data labels and then apply the format you specified.

When I was getting started I found a good tip is to record the actions you want to perform and then add a bit of code to set conditions on how to apply it where and how you want. For example in your case, I would select a series in the active chart and record the process of formatting it. Then I'd add the code

Please Login or Register  to view this content.

above that code, and

Please Login or Register  to view this content.

below to apply it to each series in the chart.

you would also need to tweak the macro by replacing "selection" with "ss" so it doesn't only format the selected series.

[Alexander Plötz]

Thanks again, NickyC,

I think I am a bit closer now to understanding the general syntax.

I took your advice about recording my edits, but with confusing result. What I want to do next is to give all Series' with the same name the same color. I started out with trying to record how I do that action once. The strange thing is that the recorded macro shows all my selections, but there is no trace of the actual formatting change.

[BTW: I know it's not exactly classy to ask on a forum if someone could just do ones own work, but since you were already so kind to whip up that first macro, maybe, if your time allowed, there would be room for a second one? I would still be able to learn a big deal from that ;-)

It would be: give a solid fill of a specified color to all series' with the name "x" (for example "KP" or "Korr" from my actual workbook; so I would just have to change the names and the color value in the code before running it again).]

[Alexander Plötz]

OK, by now I've found the command for changing the color, but I'm still a bit of a loss about how I would filter out only series' with a certain name.

[Alexander Plötz]

Nevermind, I figured it out now.

Wouldn't have got there without your help though. So thanks again.

[Alexander Plötz]

Sorry, I have to come back to this one more time.

Starting from NickyC's advice I have arrived at the following code to color a chart's bars by series:

Please Login or Register  to view this content.

Now I have another chart (pie) where the same names apply, but they are the points of one single series. So how would I change the code to make it work with data points instead of series'? I tried'n'erred a while, but my knowledge about the syntax is just to limited right now.

Thanks,

Alex