One of the best features of the new Excel 2007 table functionality is the new table formulas that Excel can create. Once you enter a formula in the first row of a dataset, Excel 2007 will automatically copy the formula down to all of the rows. Episode 423 shows you the details.
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Just 14 days left to go until Excel 2007 hit store shelves.
Today’s a 2007 Tuesday, continue to talk about some of the new table functionality.
Last two 2007 podcasts on last Tuesday and Thursday showed you how to set up a table and how to do some formatting tricks.
The really amazing thing about the new table functionality, is how it handles formulas.
One of the big problems we can have with a formula, is that sometimes if you have five thousand rows of data, somewhere someone is screwed up the formula down and wrote 4200, and you'll never ever find that there's an error down there.
I have a table set up here with data in columns A, B, C and D, if I add a new column called Profit, put the heading in cell E1, it automatically extends my table and watch this: once I enter the formula, Sales minus cost of goods sold, so I type the “=” sign, click on the sales button, type “-“, click on cost of goods sold (COGS).
And Excel has built a completely different formula than anything we've ever seen.
The formula is =Table2[ [ This Row ],[ Sales ] ]-Table2[ [ This Row ],[ COGS ] ], that's the new table nomenclature when you're building formula.
So I will hit Enter to accept this formula and Excel automatically copies the formula down to all of the rows in the table.
I don't have to copy the formula, I don't have to worry about one formula becoming different than any of the others.
Now, what if you actually have a situation, where you need a formula to be different?
You can use the AutoCorrect button here and say: hey, Undo the Calculated Column or simply just entering a different formula, let's say in cell 3, will turn off that functionality.
But I think it's great functionality, I'm going to use it most of the time.
Now if you come completely outside of the table and you start to point at cells in the table, for example I'll use this Sales minus the previous Sales, Excel just goes ahead and builds the normal formula, as usual, C4-C3.
So the old formula still work, it's just that you now have the ability to go through and create these new formulas, that will be automatically copied down to all of the rows in the table.
No more need to double click the fill handle or grab the fill handle and drag, when you define a table, Excel will automatically copy that formula down to all of your rows.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Just 14 days left to go until Excel 2007 hit store shelves.
Today’s a 2007 Tuesday, continue to talk about some of the new table functionality.
Last two 2007 podcasts on last Tuesday and Thursday showed you how to set up a table and how to do some formatting tricks.
The really amazing thing about the new table functionality, is how it handles formulas.
One of the big problems we can have with a formula, is that sometimes if you have five thousand rows of data, somewhere someone is screwed up the formula down and wrote 4200, and you'll never ever find that there's an error down there.
I have a table set up here with data in columns A, B, C and D, if I add a new column called Profit, put the heading in cell E1, it automatically extends my table and watch this: once I enter the formula, Sales minus cost of goods sold, so I type the “=” sign, click on the sales button, type “-“, click on cost of goods sold (COGS).
And Excel has built a completely different formula than anything we've ever seen.
The formula is =Table2[ [ This Row ],[ Sales ] ]-Table2[ [ This Row ],[ COGS ] ], that's the new table nomenclature when you're building formula.
So I will hit Enter to accept this formula and Excel automatically copies the formula down to all of the rows in the table.
I don't have to copy the formula, I don't have to worry about one formula becoming different than any of the others.
Now, what if you actually have a situation, where you need a formula to be different?
You can use the AutoCorrect button here and say: hey, Undo the Calculated Column or simply just entering a different formula, let's say in cell 3, will turn off that functionality.
But I think it's great functionality, I'm going to use it most of the time.
Now if you come completely outside of the table and you start to point at cells in the table, for example I'll use this Sales minus the previous Sales, Excel just goes ahead and builds the normal formula, as usual, C4-C3.
So the old formula still work, it's just that you now have the ability to go through and create these new formulas, that will be automatically copied down to all of the rows in the table.
No more need to double click the fill handle or grab the fill handle and drag, when you define a table, Excel will automatically copy that formula down to all of your rows.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
