Excel 2007's table functionality is cool, but viewer Jim is wondering why Excel is changing his formulas. In Episode 728, we will take a look at how to control this behavior,
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, we're in excel 2007 today. I got a email from Jim.
He says. Hey while ago you talked about format as a table in Excel 2007, but he's having some problems with that and let's go back and take a look at this format as a table.
In excel 2007 you can use hit Ctrl T or go to the insert tab and choose table and basically it defines your area.
A couple of things immediately we get the green bar format, we get the auto filter which are now just called filter dropdowns at the top and it's easy enough to to change the format to something new you know 54 different patterns here, and so all of that's real nice, but the one thing that can kind of drive us crazy is if we go and add a new column to the table.
So let me just come over here and have something called GP percent and so that's profit divided by revenue. Enter that formula. It automatically gets copied down all the way well.
Ok that's great it avoids having to double click the fill handle, but here we have a formula and it's calculating a bonus and let's say we just come here and we change one of these ok so for some reason. I want to override the bonus and in this case, we're not paying 2%, but we're paying 3%.
When I press enter you'll see that actually even though I just changed that one cell the entire column changed and that could be dangerous if you're not expecting it.
Now the trick to this.
What microsoft says to do is to watch for this little lightning bolt right here.
Immediately after the end of that calculation you'll see everything changes.
You go to the lightning bolt, and you do undo calculated column and now that allows it to go back to where most people are being paid 2% this one deal though is being paid 3%.
Ofcourse then we get the inconsistent calculated formula that shows up there, so that's one thing to do.
So you know the table functionality is cool, but we have some problems.
You just have to be aware of the fact that it's going to just automatically start doing this. The calculated columns.
If you really just like the calculated columns because it looks cool then one solution is to go ahead and convert it to a table here.
I'll do it here Ctrl T convert to a table and then immediately on the table tools, choose this icon for convert to range.
Convert it back to normal range. OK, well now we have the great formatting but we don't have to worry about excel automatically extending our formulas or automatically changing formulas or anything.
So a couple of cool tips for dealing with the new table functionality in excel 2007.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Well, we're in excel 2007 today. I got a email from Jim.
He says. Hey while ago you talked about format as a table in Excel 2007, but he's having some problems with that and let's go back and take a look at this format as a table.
In excel 2007 you can use hit Ctrl T or go to the insert tab and choose table and basically it defines your area.
A couple of things immediately we get the green bar format, we get the auto filter which are now just called filter dropdowns at the top and it's easy enough to to change the format to something new you know 54 different patterns here, and so all of that's real nice, but the one thing that can kind of drive us crazy is if we go and add a new column to the table.
So let me just come over here and have something called GP percent and so that's profit divided by revenue. Enter that formula. It automatically gets copied down all the way well.
Ok that's great it avoids having to double click the fill handle, but here we have a formula and it's calculating a bonus and let's say we just come here and we change one of these ok so for some reason. I want to override the bonus and in this case, we're not paying 2%, but we're paying 3%.
When I press enter you'll see that actually even though I just changed that one cell the entire column changed and that could be dangerous if you're not expecting it.
Now the trick to this.
What microsoft says to do is to watch for this little lightning bolt right here.
Immediately after the end of that calculation you'll see everything changes.
You go to the lightning bolt, and you do undo calculated column and now that allows it to go back to where most people are being paid 2% this one deal though is being paid 3%.
Ofcourse then we get the inconsistent calculated formula that shows up there, so that's one thing to do.
So you know the table functionality is cool, but we have some problems.
You just have to be aware of the fact that it's going to just automatically start doing this. The calculated columns.
If you really just like the calculated columns because it looks cool then one solution is to go ahead and convert it to a table here.
I'll do it here Ctrl T convert to a table and then immediately on the table tools, choose this icon for convert to range.
Convert it back to normal range. OK, well now we have the great formatting but we don't have to worry about excel automatically extending our formulas or automatically changing formulas or anything.
So a couple of cool tips for dealing with the new table functionality in excel 2007.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
