How do I control auto-fill in Excel?
 

Sometimes in Excel it automatically fills formulas down (when a consistent
formula is used within a column) when a new row is added (or data is entered
into a new row), and sometimes is doesn't. Is there a way to predict when it
will and when it will not do this, or better yet, is it possible to control
this (ie: tell it which columns I want filled and which ones I don't)?

There's an option under:
tools|Options|Edit tab|Extend list formats and formulas

From xl's help:

Select to automatically format new items added to the end of a list to match the
format of the rest of the list. Formulas that are repeated in every row are also
copied. To be extended, formats and formulas must appear in at least three of
the five last rows preceding the new row.

I've always turned this off (personal preference, only). I like to insert the
rows, then copy an existing row and paste over those new rows.

But if you really want control of what happens, you could create a macro that
does exactly what you want.

David McRitchie has some code that may get you started:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

Kevin_P86 wrote:

Sometimes in Excel it automatically fills formulas down (when a consistent
formula is used within a column) when a new row is added (or data is entered
into a new row), and sometimes is doesn't. Is there a way to predict when it
will and when it will not do this, or better yet, is it possible to control
this (ie: tell it which columns I want filled and which ones I don't)?

--

Dave Peterson

I turn this off whenever I can, so I don't have any insight why it doesn't work
as documented in help. I can say that I had the same results as you did,
though.

If I used it, I'd say "I feel your pain".

xl2003 added a Data|List feature. If I turned that on, the formula did get
copied to the new row.

So maybe it's time to upgrade if you're not running xl2003???



Lyndsey wrote:

Hi Dave,

I have a similar problem. The formats extend but the formulas do not. I've
check the extend list formats and formals box and it is selected. I've also
copied the formula to the preceding five rows in that column.

Any suggestions?

Lyndsey

"Dave Peterson" wrote:

There's an option under:
tools|Options|Edit tab|Extend list formats and formulas

From xl's help:

Select to automatically format new items added to the end of a list to match the
format of the rest of the list. Formulas that are repeated in every row are also
copied. To be extended, formats and formulas must appear in at least three of
the five last rows preceding the new row.

I've always turned this off (personal preference, only). I like to insert the
rows, then copy an existing row and paste over those new rows.

But if you really want control of what happens, you could create a macro that
does exactly what you want.

David McRitchie has some code that may get you started:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

Kevin_P86 wrote:

Sometimes in Excel it automatically fills formulas down (when a consistent
formula is used within a column) when a new row is added (or data is entered
into a new row), and sometimes is doesn't. Is there a way to predict when it
will and when it will not do this, or better yet, is it possible to control
this (ie: tell it which columns I want filled and which ones I don't)?

--

Dave Peterson


--

Dave Peterson

Thanks for your support though. I will consider an upgrade.

Lyndsey

"Dave Peterson" wrote:

I turn this off whenever I can, so I don't have any insight why it doesn't work
as documented in help. I can say that I had the same results as you did,
though.

If I used it, I'd say "I feel your pain".

xl2003 added a Data|List feature. If I turned that on, the formula did get
copied to the new row.

So maybe it's time to upgrade if you're not running xl2003???



Lyndsey wrote:

Hi Dave,

I have a similar problem. The formats extend but the formulas do not. I've
check the extend list formats and formals box and it is selected. I've also
copied the formula to the preceding five rows in that column.

Any suggestions?

Lyndsey

"Dave Peterson" wrote:

There's an option under:
tools|Options|Edit tab|Extend list formats and formulas

From xl's help:

Select to automatically format new items added to the end of a list to match the
format of the rest of the list. Formulas that are repeated in every row are also
copied. To be extended, formats and formulas must appear in at least three of
the five last rows preceding the new row.

I've always turned this off (personal preference, only). I like to insert the
rows, then copy an existing row and paste over those new rows.

But if you really want control of what happens, you could create a macro that
does exactly what you want.

David McRitchie has some code that may get you started:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

Kevin_P86 wrote:

Sometimes in Excel it automatically fills formulas down (when a consistent
formula is used within a column) when a new row is added (or data is entered
into a new row), and sometimes is doesn't. Is there a way to predict when it
will and when it will not do this, or better yet, is it possible to control
this (ie: tell it which columns I want filled and which ones I don't)?

--

Dave Peterson


--

Dave Peterson