Changing data series line weights in Excel chart (many at once)

Question

Is there a way to edit the weight of all lines at once on an Excel line chart?

I have a graph with about 50 data series on it, and it's too much to go through all of them and change the weights individually, and it's hard to see the data if the lines were thinner, it'd be a bit easier). I can't see a way to multi-select them, which seems a bit awful.

Edit: I eventually got it working with the following code (after learning a bit of VB). I doesn't seem to help readability of the graph much though! Chris helped me sort it out, so I'll give him the tick.

Sub onepxlines() With Selection MsgBox ("Selection: " & TypeName(Selection)) If TypeName(Selection) = "ChartArea" Then Dim area As ChartArea Set area = Selection MsgBox ("Area parent: " & TypeName(area.Parent)) If TypeName(area.Parent) = "Chart" Then Dim chart As chart Set chart = area.Parent Dim srs As Series For Each srs In chart.SeriesCollection srs.Format.Line.Weight = 1.5 Next End If End If End With End Sub

chris neilsen · Accepted Answer · 2011-11-16 06:34:14Z

Probably easiest to write a quick macro, somthing like

Sub SetWeights() Dim srs As Series For Each srs In ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection srs.Format.Line.Weight = 0.75 Next End Sub

Cheers, I'll try this out :)

Chris Dennett
– Chris Dennett

2011-11-16 14:51:50 +00:00
Commented Nov 16, 2011 at 14:51 — Chris Dennett
– Chris Dennett, Commented Nov 16, 2011 at 14:51

32bits · Accepted Answer · 2013-12-12 19:14:07Z

This is a more copy-and-paste compatible version of Chris's answer:

Sub SetWeights() Dim srs As Series For Each srs In ActiveChart.SeriesCollection srs.Format.Line.Weight = 0.25 Next End Sub

Just select the chart and run the macro.

Bill · Accepted Answer · 2016-04-05 21:12:13Z

Enhancement: create a VB variable (LineWT) that points to a cell in the spreadsheet. Then instead of "Format.Line.Weight = 0.25" change 0.25 to LineWT. Then you can change the line weight by changing it on spreadsheet and clicking the macro button. You can use similar code to set X/Y Axes names with pointers to the spreadsheet.

David Markman · Accepted Answer · 2014-09-04 18:14:41Z

This worked perfectly for me. I used this code to have it edit all lines in all charts on all sheets of a workbook

Sub ChangeLineType() 'This macro will print all embedded charts in the active workbook ' Application.ScreenUpdating = False Dim Sht As Object Dim Cht As ChartObject For Each Sht In ActiveWorkbook.Sheets For Each Cht In Sht.ChartObjects Cht.Activate ActiveChart.ChartArea.Select 'ActiveWindow.SelectedSheets.PrintOut Dim srs As Series For Each srs In ActiveChart.SeriesCollection srs.Format.Line.Weight = 0.25 Next Next Next End Sub

Rhys Gibson · Accepted Answer · 2011-11-16 00:15:11Z

0

I don't think you can multi-select lines. The simplest way is probably to change one and then to select each on in turn use Ctrl-Y to go through and re-do the line weight change.

answered Nov 16, 2011 at 0:15

Rhys Gibson

4,6283 gold badges45 silver badges46 bronze badges

There's got to be something better than this. Maybe something like a custom chart style?

Chris Dennett
– Chris Dennett

2011-11-16 01:54:19 +00:00
Commented Nov 16, 2011 at 1:54

Add a comment |

ChaimG · Accepted Answer · 2016-11-14 01:47:41Z

This code changes the line width of all charts in the active spreadsheet including chart sheets:

' Sets the line thickness for all charts in the active spreadsheet. Sub ChangeLineType() Dim Cht As Chart Dim Chts As New Collection Dim Sht As Object ' Can be Chart or WorkSheet For Each Sht In ActiveWorkbook.Sheets If TypeName(Sht) = "Chart" Then Chts.Add Sht Else For Each Cht In Sht.ChartObjects Chts.Add Cht Next End If Next Application.ScreenUpdating = False For Each Cht In Chts Dim Srs As Series For Each Srs In Cht.SeriesCollection Srs.Format.Line.Weight = 0.25 Next Srs Next Cht End Sub

(Adapted from David Markman's answer.)

D Loffell · Accepted Answer · 2017-05-19 09:01:08Z

In the Home tab, Font section, select the Borders drop-down. Pick your required line style or color. A pencil will appear. Instead of using the pencil to click on each line you want to change, go to the corner of the grid of cells you want to change, and hold down the CNTRL key. Drag the pencil over the required grid of cells, and all will change to the style or color you have selected.

Stack Exchange Network

Changing data series line weights in Excel chart (many at once)

7 Answers 7

You must log in to answer this question.

Hot Network Questions

Changing data series line weights in Excel chart (many at once)

7 Answers 7

You must log in to answer this question.

Related

Hot Network Questions