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!!!
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 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.
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!
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!
How To Create Excel Progress Bar Charts (Professional-Looking!)
Creating A Progress Bar In Excel Progress Bars are simple graphics that can be quite visually powerful by instantly providing...
Make All Charts Plot Non-Visible Cells in Your Excel Workbook
What This Does When you create a chart in Microsoft Excel, the chart's default setting is to not graph any...
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.