Excel – How to define a graph, based on automatically changing data

chartsmicrosoft excelworksheet-function

I have an Excel sheet (called Sheet1), where I regularly add some data. By pushing a button, a summary of that data gets written on a next page (called Evolution), and on that next page, there's also a graph, and that graph seems to be wrong in the sense that it does not update: the graph has been set as having its "Chart data range" like =Evolution!$B$2:$F$10, but when a row gets added to this range, it does not automatically update to =Evolution!$B$2:$F$11.

There's one thing which might be helpful: I already have defined a name Evolution_Total_Count (in cell A3), which calculates the amount of rows I need for the graph, but here's where the fun starts: I'm thinking of a chart data range like this:

  • The idea is:
    • Start with cell B2
    • The number of rows to take can be found in the name Evolution_Total_Count
    • The number of columns equals 5 (there are five columns indeed)
  • The reality is:
    • This is not the correct way to use the Offset worksheet function. (But what is the right way and how can I test this, how can I test a formula, returning a range?)
    • When I enter the name Evolution_Total_Count in the Offset worksheet function, Excel is not using the value (currently being 9), but the cell reference, where that name is defined (A3).

Does anybody know how I can define the chart data range of my chart, preferebly using the name Evolution_Total_Count? (I prefer not to create a name for the whole table.)

For clarity purposes, hereby a sample of what my Evolution worksheet looks like:

   |          A           B              C            D             E              F
1  |
2  |  Amount of         Date    Still open   Still open  First answer  Second answer
   |    entries                   (amount)      average       average        average
3  |          9    20/07/2018            7  12,14285714   8,368421053    8,863636364
4  |               23/07/2018            8       13,625          7,95    8,869565217
5  |               26/07/2018           11  17,63636364   7,043478261    7,785714286
6  |               06/08/2018           10         22,5   6,272727273    7,444444444
7  |               09/08/2018           10         24,8   5,621621622           6,95
8  |               10/08/2018           10         18,9   5,413043478           6,25
9  |               11/09/2018           13  30,38461538   4,677419355              7
10 |               17/09/2018           11  37,54545455   5,117647059    7,109589041
11 |               26/09/2018           10         27,5   4,901234568    6,823529412

Best Answer

You can set up your data as a table and base your graph off of that. Your Chart Data Range would still be a data range instead of the table name and it would include the header, so that if row 1 was your header row your range would be:


When you add a row, the range will adjust to


Related Question