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

  • Thread starter Charity Worker in Africa
  • Start date
C

Charity Worker in Africa

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?
 
B

Bill Martin

Charity said:
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?
Click to expand...

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

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
 
B

Barb Reinhardt

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

Bernie Deitrick

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
 
D

David

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)
Click to expand...

Where is that feature found?
 
Top