Adding variable in chart series formula

Copper Contributor

Hi,

I've created a spreadsheet which tracks monthly sales of each of the 40 products I sell, and have created line charts for each product showing sales by month.

Each month I add a column to the right of the last data column and add the new month sales.

Is it possible to add a variable in the chart series formula where the variable refers to a specific reference cell, so I can just change the value in the cell and all the charts will automatically update? At the moment I manually edit each series formula in each chart with the latest column reference.

For example, let's take the chart series formula

=SERIES(Data!$F$101,Data!$L$79:$BZ$79,Data!$L$101:$BZ$101,4)

where my last column of sales data is BZ.

I then add the latest month's sales data in column CA. 

What I would ideally like is.... in the reference cell (A1 for example) I change the input from "BZ" to "CA" and this (somehow!) automatically updates all "BZ" references in all the chart series formula to "CA". So the formula above becomes =SERIES(Data!$F$101,Data!$L$79:$CA$79,Data!$L$101:$CA$101,4)

Thanks for any help in advance.

Huw

3 Replies

@huw_jones 

Hello,

If your goal was just to have the source data for the chart to expand and include newly added rows or columns then just convert the source list to a table. There are so many ways of converting a List to a Table (Auto-expandable) the easiest is by using the shortcut CTRL + L or CTRL + T.

However, my understanding is you have a chart for the different products (row labels) and you want to be able to pick up a Different Month (months are in column labels) and have the chart automatically reflect your selected column. then, your goal of dynamically changing the source data Column for a chart can be done in 2 ways:

  1. Creating a preparation table using Index & Match functions
  2. Using an Offset function to dynamically pick up the correct input range.

I have a preference in your situation to using an OFFSET function.

Because of the extensive details that cannot be demonstrated by writing the steps, here are 2 detailed video tutorials I created one for each option. Please watch them  and let me know which method works best for your specific work situation.

By the way, we use both techniques for creating Dynamic Dashboards.

Switching Charts using Index & Match

https://www.youtube.com/watch?v=khXUZKCfPuU

Switching Charts Using an Offset Function

https://www.youtube.com/watch?v=5cDSup6NgHs

 

Hope that helps

Nabil Mourad

We know that a chart uses source data. In this tutorial I show you how to use an OFFSET function to store multiple source ranges in a defined name. Since our Defined name is controlled by a Drop List, Changing our selection from the Drop List automatically changes our Chart. It's an amazing ...

@huw_jones 

Other workaround would be, convert your Data into an Excel Table and then insert the Chart based on the Table data itself so that next time when you add another month column in the Table for the next month or you add more data down the rows, the Chart will be updated automatically.

 

To convert your data into an Excel Table, select all your data including headers and press the shortcut key Ctrl+T and make sure that the CheckBox which says "My table has headers" is checked in the next window which pops up next and click OK to finish.

@huw_jones One option is to set up a chart that has a dynamic range that you can control by specifying which data is included in the chart. I wrote an article with detailed instructions for this at https://www.thinkoutsidetheslide.com/how-to-create-an-excel-chart-with-a-dynamic-range-that-you-sele....

 

Hope that helps.