When you declare a range as a table in Excel 2007, any calculations entered to the right of the table are automatically copied throughout the table. Episode 987 talks about how to solve this problem.
This video is the 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 video is the 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.
Basically we start out with massive amounts of data and see how we're gonna analyze as well plus fire up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel Netcast.
I'm Bill Jelen.
You know I switched over to doing the podcast in excel 2007 earlier this year but I'm still really talking about tricks that have been around in excel 2003.
Let's talk about something that's brand new in excel 2007 here and actually something a little bit annoying.
On the home tab we have this great "Format as Table" command and you know we can go in and choose one of the formats and okay I get that.
Creates a nice looking format.
I can turn on banded Rows And all kinds of things but there's a few things that happen with "Format as table" that can be a little bit of a problem and one of them is if we would come over here and enter just some sort of a quick little calculation =2+2 and it happens to be in the first row you see that automatically they're going to take that data and move it all the way down well I mean what happens if you were just trying to do some little formula there.
They've now auto filled this down.
What you have to watch for is this little lightning bolt.
The lightning bolt will pop up and we can say "undo calculated column".
It's also possible if you would just do Ctrl Z.
Control Z would undo. It would keep your original calculation there but you see that it undoes those extra columns so watch out for that I know a lot of people like this table functionality even if it's just for the cosmetic purposes and not for the fact that it makes the pivot table automatically expand and so on.
But you're going to have a few you know things to deal with when you switch over to the table.
Well there you have it. Want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Thanks, you stopped by I will see you next time for another Netcam MyStery 7 you you
Basically we start out with massive amounts of data and see how we're gonna analyze as well plus fire up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel Netcast.
I'm Bill Jelen.
You know I switched over to doing the podcast in excel 2007 earlier this year but I'm still really talking about tricks that have been around in excel 2003.
Let's talk about something that's brand new in excel 2007 here and actually something a little bit annoying.
On the home tab we have this great "Format as Table" command and you know we can go in and choose one of the formats and okay I get that.
Creates a nice looking format.
I can turn on banded Rows And all kinds of things but there's a few things that happen with "Format as table" that can be a little bit of a problem and one of them is if we would come over here and enter just some sort of a quick little calculation =2+2 and it happens to be in the first row you see that automatically they're going to take that data and move it all the way down well I mean what happens if you were just trying to do some little formula there.
They've now auto filled this down.
What you have to watch for is this little lightning bolt.
The lightning bolt will pop up and we can say "undo calculated column".
It's also possible if you would just do Ctrl Z.
Control Z would undo. It would keep your original calculation there but you see that it undoes those extra columns so watch out for that I know a lot of people like this table functionality even if it's just for the cosmetic purposes and not for the fact that it makes the pivot table automatically expand and so on.
But you're going to have a few you know things to deal with when you switch over to the table.
Well there you have it. Want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Thanks, you stopped by I will see you next time for another Netcam MyStery 7 you you
