When you have a defined table in Excel 2010 and add a formula next to it, the formula will automatically copy to all of the rows of the table - no more double-clicking the Fill Handle! Episodes #1237 shows this intuitive time saver in action.
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 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, that's on 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.
I'm just building this normal way, using 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.
Causes 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 for me that are going to copy 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 for some reason.
But, if for some reason that [ inaudible ] then just don't pick at the table and you won't have that.
That Problem and if you're saying well, wait I like the formatting.
Okay! 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, is our behavior.
So, there you go lots of different options, Whether you like the table or, don't like the table.
You can still...
Hey! I want to thank you for stopping by.
Will 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 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, that's on 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.
I'm just building this normal way, using 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.
Causes 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 for me that are going to copy 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 for some reason.
But, if for some reason that [ inaudible ] then just don't pick at the table and you won't have that.
That Problem and if you're saying well, wait I like the formatting.
Okay! 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, is our behavior.
So, there you go lots of different options, Whether you like the table or, don't like the table.
You can still...
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
