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 r...
Charting
excel
Formulas and Functions
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