Excel doesn’t show all data points

microsoft excelmicrosoft-excel-2010

I have an Line graph in Excel 2010 displaying data on 7 areas every friday through the end of the year. The graph currently shows the data through the end of October but will not display data beyond that point. How can I get the chart to show the remaining weeks? Below is image of test data set and lay out.
enter image description here

Best Answer

Click on any of the graphs; in the formula bar you will see the 'Formula' that creates this graph, for example =SERIES(Sheet1!$A$3;Sheet1!$B$2:$N$2;Sheet1!$B$3:$N$3;1)

  1. The first parameter is the Title of each graph;
  2. the second parameter is the X-range
  3. the third parameter is the Y-range
  4. the fourth parameter is the sequential count.

Your problem is the second parameter; but not just anyone: typically, all your graphs share this parameter, and you can only edit it in #1. Here is what you need to do:

  1. use cursor down or up to step through all graphs, until you see graph 1 in the formula bar (not necessarily your first graph; and it doesn't really matter which one of yours it is)
  2. in this formula, edit the X-range to include the remaining columns, for example, change $B2:$N2 to $B2:$W2.
  3. edit the Y-range to include the remaining columns too, for example, change $B3:$N3 to $B3:$W3. Press <RET>. Your first graph should now show all data points.
  4. repeat this for all other graphs (X might be automatically ok, but Y needs to be adjusted.

Note that there are more fancy ways to do so, by using the Excel-supplied dialogs and controls, but they are very different in each version, and some of them behave counter-intuitive. Generally, Excel tries to be clever, and sometimes that helps, and sometimes not. I always use the 'manual' way as described, and once you understood the four parameters, it is straight forward, and you can create any kind of special graphs with it.

Related Question