How to make sure that added table rows assume all characteristics of the rest of the table?

[JenniferMurphy]

I cannot figure out how to create/format my tables so that new rows behave like all of the other rows. This is mainly a problem when I add a column.

Here's an example. I start with this simple table.

Book2
	C	D
6	Price	Quan
7	$4.99	25
8	$9.99	50
9	$12.00	15
Sheet1

Then I add the Total column and fill in the formula in the first row.

Book2
	C	D	E
6	Price	Quan	Total
7	$4.99	25	$124.75
8	$9.99	50
9	$12.00	15
Sheet1

Cell Formulas
Range	Formula
E7	E7	=[@Price]*[@Quan]

Then I copy E7 to the clipboard and paste it into E8 & E9.

Book2
	C	D	E
6	Price	Quan	Total
7	$4.99	25	$124.75
8	$9.99	50	$499.50
9	$12.00	15	$180.00
Sheet1

Cell Formulas
Range	Formula
E7:E9	E7	=[@Price]*[@Quan]

Now if I add a row, the formula is not propagated. E10 is blank.

Book2
	C	D	E
6	Price	Quan	Total
7	$4.99	25	$124.75
8	$9.99	50	$499.50
9	$12.00	15	$180.00
10	$5.00	10
Sheet1

Cell Formulas
Range	Formula
E7:E9	E7	=[@Price]*[@Quan]

However, if I copy E7 to the clipboard and paste it into E7:E9 (that is, copy it to the entire column including the source cell), . . .

Book2
	C	D	E
6	Price	Quan	Total
7	$4.99	25	$124.75
8	$9.99	50	$499.50
9	$12.00	15	$180.00
10	$5.00	10	$50.00
Sheet1

Cell Formulas
Range	Formula
E7:E10	E7	=[@Price]*[@Quan]

. . . if I add a row, it is propagated.

Book2
	C	D	E
6	Price	Quan	Total
7	$4.99	25	$124.75
8	$9.99	50	$499.50
9	$12.00	15	$180.00
10	$5.00	10	$50.00
11			$0.00
Sheet1

Cell Formulas
Range	Formula
E7:E11	E7	=[@Price]*[@Quan]

But I swear that even that does not always work. Is there some general rule that will make that happen every time?

 

[RoryA]

Normally the formula should autofill as soon as you enter it into the first cell. Did you turn that off in autocorrect options?

 

[JenniferMurphy]

Normally the formula should autofill as soon as you enter it into the first cell. Did you turn that off in autocorrect options?
View attachment 92919

It looks like that was it. I didn't intentionally change that setting. I don't recall ever seeing it.

I have checked that box. Let's see if that works.

Thank you

 

[RoryA]

I think that if you ever clicked the Stop button shown below, that would change the setting for you too:

 

[JenniferMurphy]

I think that if you ever clicked the Stop button shown below, that would change the setting for you too:
View attachment 92923

I don't recall seeing that dialog either, but my fat fingers have done some very bizarre things before, so this wouldn't be the first time.

Thank you again for the help on the propagating. I have made several changes to a large table and it works like a charm.

Thank you, Thank you, Thank you.

 

[JenniferMurphy]

Normally the formula should autofill as soon as you enter it into the first cell. Did you turn that off in autocorrect options?
View attachment 92919

I have a fairly large and complicated (for me) table. The autofill has been working since I got your solution. But just now, I entered a formula in the top cell and it did not fill down. I checked that setting and it is still checked.

Is there anything else I could have screwed up? (sigh)

Thanks

 

[JenniferMurphy]

I just discovered the problem. When I tried to manually fill down by double-clicking the lower right corner of the cell, it only filled partway. Then I noticed that I had accidentally enter a value in that column that should have gone in the adjacent one. When I moved that value over and reentered the formula, it filled down.

I think this is a failure of the error mechanism. If the manual fill knows to go to the first filled cell, the autofill ought to be able to do the same. Or, better yet, put up a message telling me that it found a valoue and offering me the option of canceling the fill, doing it to the first value, doing it all the way either skipping or not skipping any filled cells.

 

[RoryA]

There is no real point to posting things like that here - submit them to the feedback portal for Excel.

 

[JenniferMurphy]

The main point of the post was to inform any future readers that the fill down feature will stop at the first/next non-blank cell.