How do I control auto-fill in Excel?

[Kevin_P86]

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]

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

[Lyndsey]

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]

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

[Lyndsey]

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
>

[ivm37]

So, let's say I have something in cell A1 and then a bunch of data in A2 - A50, and I want a formula like =A2 - A1, =A3 - A1, =A4 - A1,... in the cells B2, B3, B4, ... Is there any way to do that without a macro? Some kind of special symbol to put before A1, for instance, so that it knows the A1 should be kept constant when I auto-fill but not A2?

Thank you for your help!

[arthurbr]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.