Plot graph using the last 10 values in MS Excel

Question

So I got Sheet1 with a table containing some data. To simplify let's say there's a column 'x' with some data and a column 'y' with some data. Every now and then more values are manually added to the table.

On Sheet2 I need Excel to automatically plot a graph only using the last 10 values in the table. So when more values are added the graph changes automatically to the new last 10 values. Is this possible?

Furthermore, let's say there's a third column 'z' that's always a constant value (for example to show some kind of a maximum for 'y' in the graph). How to make Excel automatically fill the cell with the number when the according 'x' and 'y' cells are filled? I know I could manually fill the 'z' column all the way down but I suspect that would screw up with plotting the last 10 values.

I'm using MS Excel 2013.

Thank you very much!

Answer 1

You'll need to create a chart using dynamic named ranges as the series data.

Start by setting up the data table with headings x, y and z.

Select x heading and click Insert > Table, make sure "My table has headers" is checked then click OK

In column z, to set 20 as the autofilled value, add formula =20

Put your data in columns x and y

Create your chart based on columns x and y. This example creates a XY Plot.

Assuming you've only got one table in your workbook, it will be named "Table1".

Go to Formulas > Name Manager > New

xPlot: =OFFSET(Table1[[#Headers],[x]],COUNTA(Table1[z])-9,0,10,1)

Also create:

yPlot: =OFFSET(Table1[[#Headers],[y]],COUNTA(Table1[z])-9,0,10,1)

Right click on your chart and click Select Data...

Remove the Series that is there

Create a new Series

If your workbook was called Chart Example.xlsx, then set:

Series X values: 'Chart Example.xlsx'!xPlot

Series Y values: 'Chart Example.xlsx'!yPlot

Click OK and try adding more data.