Are You Series(ous)?

Do you work with different series in your Excel charts?

Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:

Setup:

I started with the chart I ended with in my last post, Points Of Interest.

I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.

The horizotal gridline scale is in units of 10, so I will use the same scale for my series’ that I create for the “fake” gridlines.





Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)

Also note that I put data that is used for formatting on a separate worksheet from “Value Data”. This makes it easier to maintain data as it needs to updated overtime

1. Horizontal gridlines

I am going to add fake horizontal gridlines to the chart, so first I’l remove the current horizontal gridlines.

  • Click on the chart (This will activate the Chart Tools Group on the Ribbon)
  • Click on “Layout” in the chart tools group
  • Click on gridlines
  • Click on Primary Horizontal Gridlines
  • Click on “None”

2. Add “Fake” horizontal gridlines

I copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.

 I could click on each of the new series on the chart and change the chart type from column to line and apply all of the formatting. But I would like to do it with VBA. So I’ll step through each part of the process separately
 

3. Change the chart type:

First I need to chage the chart type for each series that is not a value series which was series 1. So I’ll loop through the SeriesCollection starting at number 2

1:  Option Explicit
2: Sub SetChartType()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Set wb = ThisWorkbook
7: Set ws = wb.Worksheets("Sheet1")
8: With ws
9: .ChartObjects(1).Activate
10: For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count
11: .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine
12: Next i
13: End With
14: 'Tidy up
15: Set ws = Nothing
16: Set wb = Nothing
17: End Sub

4. Format the color of the lines

I would like each of the horizontal lines to be white

1:  Option Explicit
2: Sub SetChartColor()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Sheet1")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.ForeColor.RGB = lWhite
17: End With
18: Next i
19: End With
20: End With
21: 'Tidy up
22: Set ws = Nothing
23: Set wb = Nothing
24: End Sub

5. Change the line type:

The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked

1:  Option Explicit
2: Sub SetLineProperties()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Chart")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.Weight = 1.5
17: .Format.Line.DashStyle = msoLineRoundDot
18: End With
19: Next i
20: End With
21: End With
22: 'Tidy up
23: Set ws = Nothing
24: Set wb = Nothing
25: End Sub

I added the major horizontal gridlines back to the chart and made them very thin and light grey

The final chart:

The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.

I hope you enjoy the post and that you find something of value in it.

Additional resources:

How do you use the SeriesCollection in your VBA or work with series in your charts? Let us know in the comments below.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s