Rob on Twitter asks: If I'm using a table, and one of the columns has a formula in it, is there a way to automatically include that formula when I insert a new row? In episode 2349, I show two different ways that this *should be* working and a couple of things that could cause it to break.
Transcript of the video:
[ ♫ ] Theme music [ ♫♫ ].
Learn Excel from MrExcel podcast. Episode 2349.
Insert a row and the formula automatically copies.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question, Tweeted by Rob.
He says if I am using a table and one of the columns has a formula in it.
Is there a way to automatically include that formula when I insert a new row? Rob, thanks for tagging @MrExcel.
Alright, so a couple of things.
Rob, what I tweeted back to you is my second solution. The first solution? It's interesting.
This should just work. Here I have a table and there is a formula out here.
We'll do Ctrl+` (grave accent key and you can see that I have the same formula in all of the cells all the way down.
Press the Ctrl+` key again to go back to normal mode.
I f I insert something in the middle. Right here. So, Alt+ i r.
And we'll type Rob with sales of 150.
As soon as I type 150, then it will add that formula automatically for me.
And, this only happens if you have three or more consecutive rows with the same formula.
And, if in File, Options, Advanced, "Extend Data Range Formats And Formulas" is checked. Now, it is checked by default.
So if it is off now, you would have had to turn this off at some point in the past. It also works down here. So if i add Paul.
With a hundred. It automatically does that right. So it should be working.
But the way that I suggested on Twitter is to use the Table functionality.
This came along in Excel 2007. It is here on the Home tab. Or on the Insert tab the Table icon.
Make sure your data has headings. Click Format as Table.
You can choose any format you want. Check "My Table Has Headers.
The table functionality has some interesting things.
If I would come here and add Paula with 180. It automatically updates.
And you will notice, (I am going to Undo), that they already copied the formula as soon as i inserted the row.
Or here if I would insert a row and add Abby with 250. See that automatically works.
But there are a number of ways that this can get broken.
For example, here is a table where all of the items in that last column did not have the same formula.
Some people are getting three or one or two, three, one, two, or four.
There is different formulas.
And when we create a table out of this so Ctrl+T. Click OK. Then we add someone new.
See they don't know what to do. They don't know what is going to go in there.
So if your table started out without the same formula all the way down. Well, then it's not going to work.
The other way to break this is if.
Let's say that we have to do an override here so Hank sold 210.
But we have some reason that we need to give Hank more. If I would just come here and edit that formula.
Say plus a $500 bonus. Now, watch these.
This is Andy at 210.
And when I press Enter here it gives a $500 bonus to everybody! That's annoying. I don't want that to happen. So I come to the on-grid U.I. here.
Say, "Stop Automatically Creating Calculated Columns".
It goes back and gives me a note that this is not consistent with the other formulas.
But once you've turned that off then you may or may not have the behavior that you're expecting in the future.
So, Rob, hey, it should be working.
Something strange has happened that's causing it to not work for you.
Well, hey, we're on a quest for a better merchandise shelf. I am looking for 100,000 subscribers.
Please, down below the video, click Subscribe.
My new book MrExcel 2020, Seeing Excel Clearly.
Click the "i" in the top right hand corner for more information about that.
Well, hey I want to thank Rob for Tweeting me that question. And I want to thank YOU for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks, get your spreadsheet fix from MrExcel.com [ ♫♫ ]
Learn Excel from MrExcel podcast. Episode 2349.
Insert a row and the formula automatically copies.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question, Tweeted by Rob.
He says if I am using a table and one of the columns has a formula in it.
Is there a way to automatically include that formula when I insert a new row? Rob, thanks for tagging @MrExcel.
Alright, so a couple of things.
Rob, what I tweeted back to you is my second solution. The first solution? It's interesting.
This should just work. Here I have a table and there is a formula out here.
We'll do Ctrl+` (grave accent key and you can see that I have the same formula in all of the cells all the way down.
Press the Ctrl+` key again to go back to normal mode.
I f I insert something in the middle. Right here. So, Alt+ i r.
And we'll type Rob with sales of 150.
As soon as I type 150, then it will add that formula automatically for me.
And, this only happens if you have three or more consecutive rows with the same formula.
And, if in File, Options, Advanced, "Extend Data Range Formats And Formulas" is checked. Now, it is checked by default.
So if it is off now, you would have had to turn this off at some point in the past. It also works down here. So if i add Paul.
With a hundred. It automatically does that right. So it should be working.
But the way that I suggested on Twitter is to use the Table functionality.
This came along in Excel 2007. It is here on the Home tab. Or on the Insert tab the Table icon.
Make sure your data has headings. Click Format as Table.
You can choose any format you want. Check "My Table Has Headers.
The table functionality has some interesting things.
If I would come here and add Paula with 180. It automatically updates.
And you will notice, (I am going to Undo), that they already copied the formula as soon as i inserted the row.
Or here if I would insert a row and add Abby with 250. See that automatically works.
But there are a number of ways that this can get broken.
For example, here is a table where all of the items in that last column did not have the same formula.
Some people are getting three or one or two, three, one, two, or four.
There is different formulas.
And when we create a table out of this so Ctrl+T. Click OK. Then we add someone new.
See they don't know what to do. They don't know what is going to go in there.
So if your table started out without the same formula all the way down. Well, then it's not going to work.
The other way to break this is if.
Let's say that we have to do an override here so Hank sold 210.
But we have some reason that we need to give Hank more. If I would just come here and edit that formula.
Say plus a $500 bonus. Now, watch these.
This is Andy at 210.
And when I press Enter here it gives a $500 bonus to everybody! That's annoying. I don't want that to happen. So I come to the on-grid U.I. here.
Say, "Stop Automatically Creating Calculated Columns".
It goes back and gives me a note that this is not consistent with the other formulas.
But once you've turned that off then you may or may not have the behavior that you're expecting in the future.
So, Rob, hey, it should be working.
Something strange has happened that's causing it to not work for you.
Well, hey, we're on a quest for a better merchandise shelf. I am looking for 100,000 subscribers.
Please, down below the video, click Subscribe.
My new book MrExcel 2020, Seeing Excel Clearly.
Click the "i" in the top right hand corner for more information about that.
Well, hey I want to thank Rob for Tweeting me that question. And I want to thank YOU for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks, get your spreadsheet fix from MrExcel.com [ ♫♫ ]
