11
5
I can swear that I have seen that behaviour where Excel automatically fills in a newly inserted row with the same formula as on the row above.
I know this can be done with some VBA, but if possible I'd like to find what triggers this to happen natively (unless I have been dreaming). Do anyone know how this is done?
RipperDoc
Posted 2013-02-20T08:30:32.813
Reputation: 427
6
This is not a dream, and you're right: Excel's AI triggers these things, and not only for tables. Just a small example to demonstrate:
A with increasing numbers, e.g.:1 2 3 4 5
=A1*10 and autofill down the list of numbers to get this:1 10 2 20 3 30 4 40 5 50
6 (or any number) in the next row under 5 (this should be A6 in the context) and press either TAB OR ENTER - as you normally do.This should not occur for formulas only - formatting may be autoapplied as well, e.g. when you add a new column right to the data set, and your "Header" (1st row) has special formatting (black fill / white text) and start typing values there - most likely it will be colored the same way.
I can't provide the full list of cases, but I think you got the clue!)
Peter L.
Posted 2013-02-20T08:30:32.813
Reputation: 877
2
You can avoid the autofill feature completely with array formulas. Just enter the formula like normal but append :column at the end of each cell reference and then press Ctrl+Shift+Enter. The formula will then be applied immediately to all cells in the column without dragging anything
Newer Excel versions will automatically use array formulas to fill down when there's a new data row
Beginning with the September 2018 update for Office 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas.
If you're on an older version of Excel or want to know more about array formulas then continue reading
For example putting =B3:B + 2*C3:C in D3 and Ctrl+Shift+Enter is equivalent to typing =B3 + 2*C3 and dragging all the way down in a table starting from row 3
That's fast to type, but has a disadvantage that unused cells at the end (outside the current table) are still calculated and show 0. There's an easy way to hide the 0s. However the even better way is to limit the calculation to the last column of the table. Knowing that in an array formula you can use X3:X101 to apply to only cells from X3 to X101 we can use INDIRECT function to achieve the same effect
=LOOKUP(2, 1/(A:A <> ""), A:A) in some cell outside the table to find the last non-blank cell in the table and name it LastRow. Alternatively use =COUNTA(A3:A) + 2 when the table's first row is 3B3:B use =INDIRECT("B3:B" & LastRow)For example if you want cells in column D to contain the products of cells in B and C, and column E contains sums of B and C, instead of using D3 = B3*C3 and E3 = B3 + C3 and drag down you just put the below formulas in D3 and E3 respectively and press Ctrl+Shift+Enter
=INDIRECT("B3:B" & LastRow) * INDIRECT("C3:C" & LastRow)
=INDIRECT("B3:B" & LastRow) + INDIRECT("C3:C" & LastRow)
From now on every time you add data to a new row, the table will be automatically updated
Array formula is very fast since the data access pattern is already known. Now instead of doing 100001 different calculations separately, they can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU. It also has the below advantages:
- Consistency: If you click any of the cells from E2 downward, you see the same formula. That consistency can help ensure greater accuracy.
- Safety: You cannot overwrite a component of a multi-cell array formula. For example, click cell E3 and press Delete. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. As an added safety measure, you have to press Ctrl+Shift+Enter to confirm the change to the formula.
- Smaller file sizes: You can often use a single array formula instead of several intermediate formulas. For example, the workbook uses one array formula to calculate the results in column E. If you had used standard formulas (such as
=C2*D2,C3*D3,C4*D4…), you would have used 11 different formulas to calculate the same results.
For more information read
phuclv
Posted 2013-02-20T08:30:32.813
Reputation: 14 930
0
For me, it was working for two columns of formulas and suddenly stopped for one of them only. I think I must've copied and pasted a different cell over it and messed up the link. To fix it, I selected the cell and went into Home > Editing > Fill > Series and reaffirmed the details. Hope that helps.
Oli
Posted 2013-02-20T08:30:32.813
Reputation: 1
This is an answer to a different problem and doesn't answer the question as asked. – DavidPostill – 2016-04-04T10:58:53.647
0
I was having this issue where a table stopped autofilling ... I don't know why it suddenly stopped working, but here is the fix:
enter Overwrite all cells in this column with this formula This worked brilliantly for me, hope it helps anyone else.
S3DEV
Posted 2013-02-20T08:30:32.813
Reputation: 113
0
Wright (re-wright if necessary) the formula and press enter. Next time when you add data to the next row it automatically populate the formula.
Natalie
Posted 2013-02-20T08:30:32.813
Reputation: 1
Read over "Why do I need 50 reputation to comment" to ensure you understand how you can start commenting.
– Pimp Juice IT – 2017-10-13T13:26:18.133To *write* something on paper, to make a *right* turn, and the *Wright* Brothers that supposedly flew the first plane. – Pimp Juice IT – 2017-10-13T13:27:24.220
Learning this stuff is a *rite of passage.* – Scott – 2017-10-13T13:41:22.033
0
This is the default behavior of Excel if you enter a formula into a blank column in a Table. To get this, you need to insert a Table:
Peter Albert
Posted 2013-02-20T08:30:32.813
Reputation: 2 802
Thanks, that works, but without using Table before, I have seen this effect on individual cells. I believe it must be some kind of Auto-fill setting. – RipperDoc – 2013-02-20T08:59:04.770
there's two ways you can very quickly do it: 1. enter the formula and then double click on the small "+" when you hover across the bottom right corner of the cell 2. Select the full range and enter the formula with Ctrl-Enter. But neither option is a "real autofill" like in the tables. Pretty sure it doesn't exist (unless custom-built in VBA) – Peter Albert – 2013-02-20T09:09:11.130
0
One way of having excel do this for you automatically is by copy and pasting the desired formulas in multiple rows. If you do it one-by-one it isn't clear that the copy and paste should be automatically completed for every row thereafter. If you copy and paste in a block, excel will take over and automatically copy and paste the formula for future rows.
user300038
Posted 2013-02-20T08:30:32.813
Reputation: 1
0
The problem you are seeing is likely because some columns in you table are not set to auto fill. You can make the column auto fill by selecting a cell that has the formula, grabbing the green box in the bottom-right corner, and dragging to the last row or the table. Once you do that the column becomes auto filled so that all new rows will automatically have the formula in that column.
Dennis
Posted 2013-02-20T08:30:32.813
Reputation: 1
-2
You can do this by using the Justify function under the Fill dropdown. It is located under the Home tab in the editing functions. On mine it is far right side of tool bar.
XLRookie
Posted 2013-02-20T08:30:32.813
Reputation: 1
Care to add details how that happens? Based on this it doesn't do what the OP asked for.
– zagrimsan – 2015-09-21T08:19:24.017What I did was set up a table with headers formatted fill, formulas, etc. for each cell under my headers. Then in the left most column I selected a range that I had not formatted yet, went to the fill drop down and selected justify. Now when I begin entering information in that range of cells the entire row auto formats to the formulas, fill, etc that I had set in my table. Sorry I am not very tech savy so don't know how to put pictures in this comment to make better sense. – XLRookie – 2015-09-21T08:51:22.043
This method causes the row you are currently entering data into to format the same as the row directly above it. – XLRookie – 2015-09-21T09:13:36.530
You can't insert pics to comments, but you can in the answer. However, I tested this and it seems to me that Fill->Justify is not needed to get the auto-filling happen (try it out yourself, just leaving the justify step off from the steps you described). The auto-fill functionality in Excel which is discussed here is the reason for the result, not justifying. – zagrimsan – 2015-09-21T09:26:03.997
Haha this is true. Well that shows you my level of expertise in this area. – XLRookie – 2015-09-21T09:38:16.913
They must be at least four. – Luke – 2018-05-07T07:39:00.673
@PeterL.,, nice interpretation +10 ☺ – Rajesh S – 2019-09-14T04:24:42.257
Interestingly, I can only get this to work in columns, not rows (e.g. if you transpose your example, I cannot get it to work). – RipperDoc – 2013-02-22T00:51:13.680
I have no idea why so... Perhaps because data usually stored in column mode with growing down - it's like more natural)) – Peter L. – 2013-02-22T05:50:57.997