Why does Excel automatically inserts a formula. How can I take advantage of the feature.

If I hadn't seen this behavior, I would never have believed it!

Open the attached xlsx file then insert a blank row before row 5.
After the insert, your spreadsheet will look like this:
     A     B    C
1  1      3    =a:a*b:b
2  1      3    =a:a*b:b
3  1      3    =a:a*b:b
4  1      3    =a:a*b:b
5                                                <notice, this row is completely empty, and there is no formula in cell C5.
6  1      3    =a:a*b:b

now, type "2" in cell A5  and "4" in cell B5.

excel MAGICALLY inserts the formula =a:a*b:b in cell C5.

To me this is an interesting discovery, but I cannot seem to control it.  

For instance, If I manually type those values and formulas into a new spreadsheet, the behavior does not repeat.
But, if I copy the cells to the new sheet, the behavior does repeat. Clearing formats and conditional formats, does not change behavior.

Does anybody have any idea what is going on?


p.s. If I could control this feature, I think I would use it a lot.  When I insert blank rows in the middle of spreadsheets, I often what the preceding row's formulas to be copied down.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It is how the Excel AI works.
When you fill formulas in multiple cells with dragging, it assumes that you want that formula also in another rows of that range, no matter if inserted or appended below.
But if you write that formulas by hand, no magic happens.
Saurabh Singh TeotiaCommented:
This feature is their as an option in excel which if you go into file-->options-->Advanced-->Extend data range formats and formulas..

Enclosed is the screenshot for your reference..what does this do that it enables this behavior what you are talking about assuming you apply the same formula their which you have applied in the cell above..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
You also get this behaviour automatically if you create a Table (in previous Excel versions you got a similar thing with "lists")

Select a range of cells
From "Insert" tab select "Table" and OK
Now if you put a formula in the first row of the table it will automatically populate the whole of that column
If you add data in the first row after the table Excel automatically inserts another row in the table and formulas are automatically populated to that row too

regards, barry
rberkeConsultantAuthor Commented:
ok, I understand almost everything except the following:

Open my sample spreadsheet and insert a row between rows 2 and 3.
In the new  row, the extend formula does not work.

Does excel think every table must have 4 rows of column headings where the extension behavior does not apply?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.