Forum Discussion

huw_jones's avatar
huw_jones
Copper Contributor
Sep 09, 2019

Adding variable in chart series formula

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

Charting
excel
Formulas and Functions

3 Replies

Sort By
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 09, 2019

    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.

  • nabilmourad's avatar
    nabilmourad
    MVP
    Sep 09, 2019

    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

Resources