×

Automatically Extending Excel Chart Series' Ranges For Next Year

By Chris Newman •  Updated: 11/21/14 •  6 min read
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!

Automatically-Extending-excel-chart-series-Ranges-For-Next-Year-7

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 Sub

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

Automatically Extending excel chart series

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

  1. Only extend the ActiveChart
  2. 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.


Keep Learning

Chris Newman

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.