0

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:

• Chandoo
• Jon Peltier
• Chart type enumeration - Excel 2007
• MsoLineDashStyle enumeration - Excel 2007

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

Author: Winston Snyder Labels: Data Visualizations, VBA

0

Hello World! (Sorry, I could not resist J) I was recently asked by our host, Jordan, if I would be interested in being a guest author here at Option Explicit VBA. I quickly and humbly accepted. I will strive to do my best to add something of value. Let’s dive right in.

I was inspired the other day by Chandoo’s post on his blog in regards to tax burden as well as Jared’s subsequent submission regarding service levels. Both charts use a consistent color across what appears to be different series in panel charts that are arranged closely together.

In fact, they are not, the area charts are one series with blank rows or columns inserted in the data range to create the separated effect. Here is a sample initial column chart I created using the same concept

So far, so good - but I would like each "Series" to have a different color. I selected some data points and changed the fill color

Looking good, but I'll need to manually select an additional 22 data points and change the fill color for each point. It gets worse if I want to add additional, "Series" to the chart or decide to go back and change a color - more manual work!

So I thought to myself, "Self, there must be an easier way!" The good news is that there is an easier way through VBA! Let's cook up some code (Option Explicit VBA - Remember?)

I only have one ChartObject with one SeriesCollection, so that part is straight forward. But there are many points in the SeriesCollection to be considered. Additionally, I dont want to plot anyting or add color to anything for points 13 and 25 where I have blank rows in my data.

So, I want to do something with points 1-12, 14-25, 27-38. Sounds like a good candidate for a Select Case..Case..End Select structure.

Option Explicit
1: Sub ColorDataPoints()
2: Dim wb As Workbook
3: Dim ws As Worksheet
4: Dim i As Integer
5: Dim lBlue As Long
6: Dim lRed As Long
7: Dim lGreen As Long
8: Set wb = ThisWorkbook
9: Set ws = wb.Worksheets("Sheet2")
10: lBlue = RGB(79, 129, 189)
11: lRed = RGB(192, 0, 0)
12: lGreen = RGB(155, 187, 89)
13: With ws
14: For i = 1 To .ChartObjects(1).Chart.SeriesCollection(1).Points.Count
15: Select Case i
16: Case 1 To 12
17: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lBlue
18: Case 14 To 25
19: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lRed
20: Case 27 To 38
21: .ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = lGreen
22: End Select
23: Next i
24: End With
25: 'Tidy up
26: Set ws = Nothing
27: Set wb = Nothing
28: End Sub

Now I have a chart with one x-axis and what appears to be 3 different series, when in fact, it is one. Perhaps more importantly, I have a process that requires very little updating as my needs change to display more "Series" or to change colors.

More on the Points Collection.

Download the workbook .

How do you work with points in your charts and VBA? Let us know in the comments section.

Author: Winston Snyder Labels: Data Visualizations, VBA

10

Alright, so this seemed like the next logical step for the rollover method:



This one is kinda complicated, I admit. Unfortunately, I didn't really take the time to clean up the spreadsheet file for others to follow (I don't really have the time these days). Sorry. But try to take it apart - and ask me questions if you have them.

I've canvassed some other folks from the Excel community to see if they would want to do a video tutorial of this - and I think that's what it would take.

Have fun!
Mapper.xlsm

Author: Unknown Labels: Advanced Excel, Data Visualizations, Interactive Hyperlinks, Rollovers