Automatically Extending Excel Chart Series’ Ranges For Next Year
It’s That Time Of Year Again!
For most of us, the months of November and December are spent preparing our spreadsheets for next year. If you have historical graphs in your spreadsheets, adjusting your series ranges to include space for next year’s numbers can be quite a pain (especially if you have a ton of graphs to adjust).
This year I decided to bite the bullet and take some time to develop a VBA macro that can do these mind-numbing tasks for me and I will admit I threw a big fist pump into the air at my desk once I ran this code for the first time. Oh, how I love automation!!!
The Problem
Below is an example data set where I have my graphs set up for the years 2013-2014. I want to prepare them for next year and include space for 2015’s numbers (when they get reported). With the code I wrote, I can simply run my macro, tell it how many cells I want to extend it to, and all the chart series in the worksheet are automatically extended!
The VBA Code
Sub Chart_Extender()
'PURPOSE: Extend horizontally all Chart Series in ActiveSheet by X number of columns (can decrease as well)
'SOURCE: www.TheSpreadsheetGuru.com
Dim Rng_Extension As Integer
Dim Series_Formula As String
Dim StartPoint As String
Dim EndPoint As String
Dim CommaSplit As Variant
Dim ColonSplit As Variant
Dim grph As ChartObject
Dim ser As Series
'Determine the length of the extension (in cells)
On Error GoTo BadEntry
Rng_Extension = InputBox( _
"How many cells do you want to extend your chart's series?", _
"Chart Extender")
On Error GoTo 0
'Loop Through Each chart in the ActiveSheet
For Each grph In ActiveSheet.ChartObjects
For Each ser In grph.Chart.SeriesCollection
'Test to make sure not XY Scatter Plot Series
If ser.ChartType <> 75 Then
'Get range of series
Series_Formula = ser.Formula
'X Axis Values
CommaSplit = Split(Series_Formula, ",") 'Delimit by comma
ColonSplit = Split(CommaSplit(2), ":") 'Delimit 3rd part by colon
StartPoint = ColonSplit(0) 'Starting Point of Range
EndPoint = ColonSplit(1) 'Current Ending Point Range
EndPoint = Range(EndPoint).Offset(0, Rng_Extension).Address 'Extended Ending Point Range
ser.Values = StartPoint & ":" & EndPoint 'Combine Start and End Point & Set Series = To It
'X Axis Labels
If CommaSplit(1) <> "" Then
ColonSplit = Split(CommaSplit(1), ":") 'Delimit 3rd part by colon
StartPoint = ColonSplit(0) 'Starting Point of Range
EndPoint = ColonSplit(1) 'Current Ending Point Range
EndPoint = Range(EndPoint).Offset(0, Rng_Extension).Address 'Extended Ending Point Range
ser.XValues = StartPoint & ":" & EndPoint 'Combine Start and End Point & Set Series = To It
End If
End If
Next ser
Next grph
'Completion Message
MsgBox "Your chart has been Extended by " & Rng_Extension & " positions."
Exit Sub
'Error Handling
BadEntry:
MsgBox "Your input must be a whole number, aborting", vbCritical, "Improper Entry"
End SubHow Does It Work?
What the code does is loop through each chart series within a particular graph and then isolates the Series Formula. An example of what this formula might look like is shown below.
The code then takes this string and does a few delimited splits to single out the start and end cells of the two ranges highlighted in the above image (X-Axis and Data ranges).
The macro then uses the Offset function to expand or contract the chart range; essentially finding a new endpoint for the range. Once the new endpoint is found, the Series formula is then reassembled and given back to the chart!
Different Scenarios
I also went ahead and wrote code to handle two additional scenarios
- Only extend the ActiveChart
- Extend every chart in the entire ActiveWorkbook
Download Example Excel File
If you would like to see how I wrote both of these macros and also test all three of them out, you can download the sample workbook I showed above in the animated GIF image.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.