Cygnus Prime

Excel Article

How to get graphs to update automatically when new data is added to a plain table

In the previous article we looked at the problem of extending plain tables and ensuring that formulas continue to work.

The same idea is used, to extend the graph range to include the first empty row at the bottom of the table. Below is our starting point.

Table 1

Now Joe wants to include a graph of his distances. First select the distance cells in the plain table including the first empty cell underneath the table.

Table 2

Next from the insert menu, select one of the graph types, in this case we choose the simple line graph.

Table 3

Below is the resulting graph. You should notice that we have one empty entry on the right hand side of the graph. If this is for personal use it might be ok to stop here.

Table 4

However we can improve the result. Right click on a empty part of the graph - towards the right hand side - and choose select data.

Table 5

On the right hand side of the dialog, under Horizontal category axis, click on the edit button. Then in axis labels dialog, select the date cells in the table including the first empty cell at the bottom as below.

Table 6

Close the axis label. In the select data dialog under Legend Entries, click the Edit button and enter a name for the series, in our example it is "distance" Finally click ok on the select data dialog. The resulting graph looks is shown below.

Table 7

Finally if we extend the table in the usual way, the graph also extends nicely.

Table 8

On the subject of graphs here is one additional tip. Often you want to have the graphs all on their own worksheet, for example to create a scorecard. It is however easier to create and fine tune the graph on the same worksheet as the data, and when you are finally happy with it, copy the whole graph and paste it on to the final destination worksheet. This is easier than trying to create it on the final destination worksheet.

In a future article we will show how using Excel tables can get around these problems, but also highlight a few of the downsides of Excel tables.

Screen shots created in Excel 2007 on Windows 7

Back to the top