Dynamic Chart in Excel

Question

Country      January    February    March   April   May June    July
USA             34        34          45     34      23
China           45        56          34     23      12
Japan           34        54          23     23      24

How can I show the last 3 months in the chart every time I update the data. Considering that column "Country" is fixed. Any workaround or easy formula to use in my "data selection" when creating a chart.

Answer 1

This should work perfectly.

1. Re-arramge the data

    Country    USA  China  Japan
    January    34    45     34
    February   34    56     54
    March      45    34     23
    April      34    23     23
    May        23    12     24

Like so, it will be easier to work with. Here we assume the data starts at cell A1.

2. Create names in Name Manager (ctr+F3 to open)

    name:      refers to:

    months     =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(length,COUNTA(Sheet1!$A:$A)-1),1)
    length     3
    usa        =OFFSET(months,0,1)
    china      =OFFSET(months,0,2)
    japan      =OFFSET(months,0,3)

In length, you note the desired time-period you wish to show (in your case 3 months); this can also be done with a cell reference and a COUNTA if you wish to make it dynamic and account for all the new data.

3. Create the chart

• Click on blank cell and open the desired chart (I have done it with a 2-D Clustered Column). //Note that the chart should be blank.
• Right-click on Select Data and click Add for Legend Entries (Series). Here for Series Name, select whatever is desired (in your case USA China Japan). For Series Value add =Sheet1!usa
• Do this two more times for =Sheet1!china & =Sheet1!japan
• Finally for the Axis Label add =Sheet1!months

Click though OK and you should be all set!

Make sure you check out how the offset function works here: https://support.office.com/en-za/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66