Excel doesn't show all data points
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.
1 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)
- The first parameter is the Title of each graph;
- the second parameter is the X-range
- the third parameter is the Y-range
- 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:
- 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)
- in this formula, edit the X-range to include the remaining columns, for example, change
$B2:$N2to$B2:$W2. - edit the Y-range to include the remaining columns too, for example, change
$B3:$N3to$B3:$W3. Press<RET>. Your first graph should now show all data points. - 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.
1
