Automatically updating bar chart?

Question

So for a project at work I have a table in excel that contains numbers for each month of the year up to this one in four categories. It looks (a bit) like this:

+-------+-------+-------+-------+-------+
| Group | Jan   | Feb   |   Mar | Apr   |
+-------+-------+-------+-------+-------+
| AAAA  |   111 |   222 |   333 |   444 |
| BBBB  |   555 |   666 |   777 |   888 |
| CCCC  |   999 |   000 |   111 |   222 |
| DDDD  |   333 |   444 |   555 |   666 |
+-------+-------+-------+-------+-------+

What I'm trying to do now is to create a bar chart that displays all four groups as groups of bars that contain the data for the current month, the one 6 Months and one year ago. Now to do this once would be quite easy, but I'm trying to make it so that when I ad a new month, it automatically updates the whole chart.

Any ideas on how to do this?

Answer 1

You should consider using an Excel Table for this.

1. Convert your data to an Excel Table, Insert > Table.
2. Add 3 columns (after your group, before your months), called Current, 6_Prior, 12_Prior (or something else suitable).

3. Use the following formula's for the three added columns (change reference names as appropriate):

   Current: =OFFSET([@Current],0,COLUMNS(Table)-2)
   6_Prior: =OFFSET([@6_Prior],0,COLUMNS(Table)-8)
   12_Prior: =OFFSET([@12_Prior],0,COLUMNS(Table)-14)

   Assuming that you add the new months' data to the right of the table, this will return the newest month, and the 6 and 12 prior.

4. Create your chart using the Current, 6_Prior and 12_Prior columns as your data source. The chart will automatically update as you add data to your Table.

Answer 2

1. Go to Name Manager. Create new name called Month.

2. Under Refers to, change it to =OFFSET("Group",0,0,1,COUNTA(1:1))

3. Create a bar chart using existing data.

4. Click the bar chart in the figure. The formula should be something like this:

=SERIES(Sheet1!$D$9,Sheet1!$E$7:$I$7,Sheet1!$E$9:$I$9,2)

Change the second part, Sheet1!$E$7:$I$7 to Month.

The chart will now auto update like you want.