How can I insert a row and have the existing formulas apply?
 

I have a table which has a column that uses a formula. When I insert a new
row in the middle of the table, Excel inserts a blank cell into the column
with formulas, rather than keeping the formula that applies to the adjacent
rows.

How do I insert a row and keep the column with a formula so it applies to
that new row?

Charity Worker in Africa wrote:
I have a table which has a column that uses a formula. When I insert a new
row in the middle of the table, Excel inserts a blank cell into the column
with formulas, rather than keeping the formula that applies to the adjacent
rows.

How do I insert a row and keep the column with a formula so it applies to
that new row?

-----------------------

There may be a better way, but what I do is "copy" an existing row, then "insert
copied cells" where I want the new row. At that point you can put your new data
over top of the old and the formula columns all work properly.

Bill

To copy cells down, use the keystrokes CTRL D (assuming you haven't redefined
CTRL D to some other function).

"Charity Worker in Africa" wrote:

I have a table which has a column that uses a formula. When I insert a new
row in the middle of the table, Excel inserts a blank cell into the column
with formulas, rather than keeping the formula that applies to the adjacent
rows.

How do I insert a row and keep the column with a formula so it applies to
that new row?

If your formulas refer to other rows, you can't. If your formulas only reference cells in the same
row, then copy a row first and use "Insert copied cells".

Personally, I think it is a good idea to get out of the babit of inserting new or copied rows within
blocks of existing rows. Better to get in the habit of using new rows at the end of the block (and
using the Extend Lists and Formulas feature), or making sure that you copy all your formulas down
the entire way from one above the insertion point.

HTH,
Bernie
MS Excel MVP


"Charity Worker in Africa" <Charity Worker in wrote in message
...
I have a table which has a column that uses a formula. When I insert a new
row in the middle of the table, Excel inserts a blank cell into the column
with formulas, rather than keeping the formula that applies to the adjacent
rows.

How do I insert a row and keep the column with a formula so it applies to
that new row?

Bernie Deitrick wrote

Better to get in the habit of using new rows at the end of the block
(and using the Extend Lists and Formulas feature)

Where is that feature found?

--
David

David,

Tools / Options... Edit tab.

HTH,
Bernie
MS Excel MVP


"David" wrote in message
...
Bernie Deitrick wrote

Better to get in the habit of using new rows at the end of the block
(and using the Extend Lists and Formulas feature)

Where is that feature found?

--
David

Bernie Deitrick wrote

David,

Tools / Options... Edit tab.

HTH,
Bernie
MS Excel MVP

Duh! Thanks.

--
David