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

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.
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.