When you have a defined table in Excel and add a formula next to it, the formula will automatically copy to all of the rows of the table.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 19 – Tables!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Chapter 19 talks about the table functionality that was added to Excel 2007.
So we have a simple little data set here, it's typical of most data sets that we have headings across the top, and then just rows and rows and rows of data.
So when you have a data set like that, you can convert it to a table.
I use Ctrl+T, you can also come back here and say Format as Table, choose one of these table formats, Home tab.
So now we have the formatting, we have the filter drop-down, but the really cool thing about this table, is that as we add new columns, they become part of the table.
As we add new formulas, they also become part of the table.
So I'm going to come here and add a little bit of formatting.
It's a little bit strange to format the cell before you enter the formula, but you need to get in that habit, because watch what happens: I'm going to type a new heading here, Gross Profit %, and we'll do =Profit/Revenue.
So I'm just building this the normal way using the arrow keys or the mouse, either one.
And as soon as I press Enter, BAM, it just copies it straight down, no more double clicking that fill handle, copies it throughout the extent of the table.
So, kind of a great little bit of automation there, you know, you have a table, you have one formula, you’re going to copy it down all the way.
Now, if, for some reason, you wouldn't want that to happen, then you can come here and say “Undo Calculated Column”, or stop doing this all together.
But if, for some reason, that bugs you, then just don't make it be a table, and you won't have that problem.
And if you're saying “Well wait, I like the formatting.” OK, then convert it to a table, but then use Table Tools, Design, and Convert to Range, then you get to keep the formatting, but you don't have the other bizarre behavior.
So there you go, lots of different options, whether you like the table or don't like the table, you can still control this… (unclear) Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Excel In Depth chapter 19 – Tables!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Chapter 19 talks about the table functionality that was added to Excel 2007.
So we have a simple little data set here, it's typical of most data sets that we have headings across the top, and then just rows and rows and rows of data.
So when you have a data set like that, you can convert it to a table.
I use Ctrl+T, you can also come back here and say Format as Table, choose one of these table formats, Home tab.
So now we have the formatting, we have the filter drop-down, but the really cool thing about this table, is that as we add new columns, they become part of the table.
As we add new formulas, they also become part of the table.
So I'm going to come here and add a little bit of formatting.
It's a little bit strange to format the cell before you enter the formula, but you need to get in that habit, because watch what happens: I'm going to type a new heading here, Gross Profit %, and we'll do =Profit/Revenue.
So I'm just building this the normal way using the arrow keys or the mouse, either one.
And as soon as I press Enter, BAM, it just copies it straight down, no more double clicking that fill handle, copies it throughout the extent of the table.
So, kind of a great little bit of automation there, you know, you have a table, you have one formula, you’re going to copy it down all the way.
Now, if, for some reason, you wouldn't want that to happen, then you can come here and say “Undo Calculated Column”, or stop doing this all together.
But if, for some reason, that bugs you, then just don't make it be a table, and you won't have that problem.
And if you're saying “Well wait, I like the formatting.” OK, then convert it to a table, but then use Table Tools, Design, and Convert to Range, then you get to keep the formatting, but you don't have the other bizarre behavior.
So there you go, lots of different options, whether you like the table or don't like the table, you can still control this… (unclear) Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
