Insert a row below last line

[zilliman]

Hello,

How can I insert (or add) a new row to the bottom of a table so that Excel now updates all other worksheets and formulas with the new size of the table (+1 row) ?

I can right click on the row number and do "insert" but that inserts above the last line. I want it to be an "add" really, not an "insert".

A workaround is to add a dummy last row and always insert above it if there is no other way.

many thanks

[Aardigspook]

Welcome to the forum.

If you use an actual Excel Table then any formulae which refer to it will automatically update if you add a row to the bottom. The Table will automatically expand to incorporate any new row and since references to Tables don't use row/column numbers but instead use a format like Table1[Data], any related formulae don't need to be changed.

For example:
In A1 type 'Data'. In A2 to A5, put in 1,2,3,4. Now select any one of those cells, go to the 'Insert' tab and click 'Table' (2nd from left) - click OK on the dialogue box which opens.
This Table is automatically named 'Table1' - you can change this on the 'Table Tools | Design' tab which appears whenever you have a Table cell selected.
Now select a cell somewhere else - C1 for example (any cell will do as long as it's not immediately adjacent to the table). Enter this: =SUM(Table1[Data])
You'll note that as soon as you start typing 'Ta' you'll see an option for 'Table1' appear which you can click to auto-select. The same happens when you type the '[' with the top option being 'Data'.
You'll get the expected result of '10'.
Now type any number you want in A6. You'll see the table automatically expand to include row 6 and your Sum will change - without you having to alter the formula at all.

These pages give more info:
https://support.office.com/en-gb/art...9-6c94334e492c
https://support.office.com/en-gb/art...3-C8AE6D2B276E

I hope that helps (and isn't teaching you to such eggs )

[zilliman]

Hi and thanks for the elaborate reply.

I was not using a table, simply a few rows of data, then right click on a row and "insert" but I was looking for "insert after". This is because even without a table, any references to a Range do get updated if you do "insert" on the right click, but that means you are pushing your last row down one and inserting above it which is not what I really wanted.

We also do not have "insert above" which would insert a row above an existing one, inheriting from the existing row which I have selected and right clicked on! Instead the "insert" pushes everything down one, but inherits from the row above (which was not selected)!

It would be more intuitive if we had "insert above" in which case the current row gets pushed down one, and "insert below" in which case a new row is made beneath the one selected, and in both cases the new rows inherit from the selected one. Since this is the one selected and the one I am interacting with.

[Aardigspook]

Although I agree it would be useful to have what you want, unfortunately I don't know of any non-VBA way to do it. Tables go some way towards it, but there are times when they're not what you want.
If it's not too much hassle to have a blank row at the bottom of your data, that's probably the easiest solution to your original question. Sorry I can't be of more help.