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

7 Answers

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

1

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.

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.

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

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.

1

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.)

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy