Excel automatically charting for concrete range of data

Question

Excel masters, I need help about charting in excel. I have a table with data ("WEEK number", "date of start", "some data") and chart! the problem is: I need when I add new week data the chart should automatically update but I need to see only 53 weeks. For example: if I add week 30 I need to see data on the chart from week 30 (2018) to week 30 (2019)

Thank you for your help in advance

Week #  Start Date  Data
20  05-13-2018  21,866    
21  05-20-2018  20,317    
22  05-27-2018  18,078    
23  06-03-2018  19,254    
24  06-10-2018  17,990    
25  06-17-2018  19,589    
26  06-24-2018  22,346    
27  07-01-2018  18,985    
28  07-08-2018  18,482    
29  07-15-2018  17,493    
30  07-22-2018  21,217    
31  07-29-2018  16,205    
32  08-05-2018  16,534    
33  08-12-2018  16,694    
34  08-19-2018  18,190    
35  08-26-2018  20,559    
36  09-02-2018  24,503    
37  09-09-2018  26,074    
38  09-16-2018  24,092    
39  09-23-2018  33,828    
40  09-30-2018  28,979    
41  10-07-2018  28,493    
42  10-14-2018  30,634    
43  10-21-2018  29,473    
44  10-28-2018  28,202    
45  11-04-2018  30,088    
46  11-11-2018  36,070    
47  11-18-2018  36,689    
48  11-25-2018  35,509    
49  12-02-2018  27,794    
50  12-09-2018  27,802    
51  12-16-2018  16,521    
52  12-23-2018  13,786    
1   12-31-2018  18,271    
2   01-07-2019  27,336    
3   01-14-2019  29,837    
4   01-21-2019  31,464    
5   01-28-2019  31,395    
6   02-04-2019  31,383    
7   02-11-2019  28,152    
8   02-18-2019  30,795    
9   02-25-2019  25,183    
10  03-04-2019  28,344    
11  03-11-2019  38,064    
12  03-18-2019  36,815    
13  03-25-2019  36,741    
14  04-01-2019  35,849    
15  04-08-2019  35,199    
16  04-15-2019  28,407    
17  04-22-2019  16,427    
18  04-29-2019  29,678    
19  05-06-2019  28,270    
20  05-13-2019  24,046    
21  05-20-2019  21,191    
22  05-27-2019  21,480    
23  06-03-2019  23,919    
24  06-10-2019  20,532    
25  06-17-2019  20,575    
26  06-24-2019  19,111    
27  07-01-2019  19,279    
28  07-08-2019  22,265    
29  07-15-2019  5,979

Answer 1

You can use defined names to automatically update a chart range in Excel.

You can refer to any array/range by a name in excel. There are formulas to find the specific array/range and we call them Array Formulas. One of the most common array formula is OFFSET. If you are not familiar with this formula, please google some tutorials online before continue.

In your example, let's presume Week # is in Column A, Start Date is in Column B, and Data is in Column C, and the name of the worksheet is SheetName.

In order to dynamically look up a 53-Week range in Column A, B and C using OFFSET formula, you need to find the starting point (i.e. the cell in each column corresponds to the first week of the 53-week range) .

For instance, if your latest Week # is Week #29 in Cell A63, the starting point would be Week #29 in Cell A11. One way of finding the latest week # is to find the corresponding maximum/latest date in the Start Date column, and use MATCH formula to find the row number. Such logic is translated into the following formulas:

Start Point in Column A (the Week # column) ="A"&MATCH(MAX(B:B),B:B,0)-52

Start Point in Column B (the Start Date column) ="B"&MATCH(MAX(B:B),B:B,0)-52

Start Point in Column C (the Data column) ="C"&MATCH(MAX(B:B),B:B,0)-52

Then you can find the 53-Week range in each column by referencing the relevant starting point in the OFFSET formula. Please note you need to press CSE Ctrl+Shift+Enter upon finishing entering each array formula to make it work.

For Column A (the Week # column) =OFFSET(INDIRECT("SheetName!A"&MATCH(MAX(SheetName!$B:$B),SheetName!$B:$B,0)-52),,,53)

For Column B (the Start Date column) =OFFSET(INDIRECT("SheetName!B"&MATCH(MAX(SheetName!$B:$B),SheetName!$B:$B,0)-52),,,53)

For Column C (the Data column) =OFFSET(INDIRECT("SheetName!C"&MATCH(MAX(SheetName!$B:$B),SheetName!$B:$B,0)-52),,,53)

The next step is to create three names for the above ranges. Press Ctrl+F3 to open the Name Manager in excel, and manually create the names and copy and paste the above formulas in the 'Refers to:' field.

Then you can create a chart from the existing data, and replace the hard-coded series values with the range names to make it dynamic as shown below:

Click anywhere within the chart, then you will see the Chart Design tab shown on top of the the ribbon of your excel. Go to Select Data and Edit the Data and Date value as shown below:

I have created a sample bar chart below showing the data for the most recent 53 weeks. Please note I have added 5 weeks' of new data to test the result, and I have put the Week # as the data label for the bar chart instead of the actual dollar value.

Lastly, you can refer to the following article for more clarifications.

How to use defined names to automatically update a chart range in Excel