Excel automatically update graph when adding new columns and rows in data

2
votes

Line graph and source table

My excel graph comes from a table in another sheet, as shown in the above image.

The X-axis of the graph shows the month/year headers in blue in row 3 except column A&B (.... 43.july17, 44.aug17, ...).

The Y axis shows the Ave. subs length in row 57 except column A&B.

Every month I need to insert a new month/year column, in this case between column AY and AZ. I also need to insert new monthly row data, in this case under Month 14 in row 17, so that the Ave. Subs length row is moving 1 row down every month.

Data under the TOTALS column (AZ) are not included in the graph. I currently use the graph formula: 

=SERIES(,'Cohorts(32015)'!$C$3:$AX$3,'Cohorts(32015)'!$C$57:$AX$57,1)

However, it does not automatically update when I add new columns to the table. Is there a way to do this?

1
excelgraphchartsformula

1 Answers

0
votes

Yes it is :).

There is a function in Excel called "Name Manager". There you can define ranges (name a range or a cell/cells) and therefore make ranges dynamic as you insert or delete columns or rows.

Guide:

Go to "Formulas" -> "Defined Names" -> "New.."

enter image description here

I create two ranges (notice I use absolute reference with $).

enter image description here

1 - The first one is for the axis values (43.july17, 44.aug17 etc..). I call it "Month_Name". Notice you need to do it from $C$3 given your example. 

=OFFSET(Sheet1!$AS$3,0,0,1,COUNTA(Sheet1!$AS$3:$AZ$3)-1)

2- The second one is for your data range (Ave. subs length). I call the range: "Ave_Sub".

=OFFSET(Sheet1!$AS$57,0,0,1,COUNTA(Sheet1!$AS$57:$AZ$57)-1)

Click on your series (in your graph/chart) and change your series names to the sheet name + named range i.e. in my case the sheet name is "Sheet1": 

=SERIES(;Sheet1!Month_Name;Sheet1!Ave_Sub;1)

enter image description here

When you add rows or columns now, the graph will automatically expand (I added 1 row and one column).

enter image description here

Details about formula:

So how does it work?

Syntax for the formula is: 

=OFFSET(reference, rows, cols, [height], [width])

and in our case 

=OFFSET(Sheet1!$AS$3,0,0,1,COUNTA(Sheet1!$AS$3:$AZ$3)-1)

Where:

reference: is our start column, $AS$3.

rows and cols: We don't want to offset any column or rows. Therefore: 0,0.

[height]: = 1 since we have one row.

[width] = COUNTA(Sheet1!$AS$3:$AZ$3)-1, we take the first column we want to have in our chart and the last column in the range we possible want to add or delete a column. In our case the "Total" will be our last column. But we don't want "Total" to appear therefore we take -1 in our range.

Generic formulas to expand ranges:

For columns: 

=OFFSET(<sheet name="">!<start cell="">,0,0,1,COUNTA(<sheet name="">!<column name="">:<column name="">) - 1)

For rows: 

=OFFSET(<Sheet name>!<start cell>,0,0,COUNTA(<Sheet name>!<Column name>:<Column name>) - 1)