Rolling Graphs

[alimilne]

Hi,

I am trying to create an excel based dashboard for work in order to track performance on a monthly basis and all i have managed to create so far is a basic graph which updates when new data is added. What i'd really like is to have it just show the last 3, 6 or 12 months of data (without having to delete previous rows.)

I've seen them before however they've always just had one line on the graph whereas mine would have around 10. Hoping for some expert guidance!

Thanks!

 

[Saba Sabaratnam]

Hi

The easiest way to do this is to select data table that the graph is based on and filter out all data points that you do not want to graph.

Kind regards

Saba

 

[pvr928]

Hi alimilne

Assuming you have an Excel Table called 'ExampleTable' with one column header called 'A' (and at least 12 rows in the body of the Table), try this formula as a chart reference:

=OFFSET(INDIRECT(ADDRESS(ROWS(ExampleTable[A])+1,COLUMN(ExampleTable[A]))),-2,0,3,1)

This references the last row in the table, and then SUMs the last three cells in the column (for 3 months). Adapt the '-2' and '3' in the formula for 6 and 12 months respectively.

Cheers

pvr928

 

[pvr928]

Hi alimilne

It appears my solution does not work with Excel Tables.

Instead try this:

1. Create a Defined Name, say 'Range_ref' [ALT - M - N - 'New'] and insert this formula:

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

where your data starts in cell A1

2. Insert into a Series Values of your chart:

=Sheet1!Range_ref

where the name of sheet where the data is located is called 'Sheet1'.



Cheers

pvr928