Excel 2007 features an amazing expanding formula bar. Todays podcast takes a look at how to handle extremely long formulas in Excel 2007. Episode 625 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen. Here's a cool trick that I learned from some of the folks in Springfield Illinois.
Now one of the problems we have in excel 2003 is that if you have a very very long formula the formula tends to overwrite the spreadsheet so here I'm going to select cell Q2 and you'll see that the formula is so long that it actually covers up the first two and a half rows of the spreadsheet, so I can't see the formula and the value at the same time.
In excel 2007, they did a significant improvement here where when we select that cell, rather than making the formula bar, extend down over the worksheet, they give us some options.
We can say hey, let's extend the formula bar, and it will show us basically the formula, one line at a time.
But the cool trick, the trick that I didn't know is that it's possible to take that formula bar and make it be as long as we want so if we really have a long formula and we want to be able to see the entire formula bar and the number at the same time then just grab this margin here between the formula bar and the spreadsheet and make the formula be as long as we want.
Or we can just collapse it and say, hey only show us one line at a time.
And then if we need to, we can either go through a line at a time or expand the whole thing out.
Very cool functionality, that's in excel 2007.
As I'm doing the seminars, I'm starting to see more and more people switching over.
We're up to about, on average, about 12% now.
Though I've run into a couple of seminars where we had as many as thirty percent had switched over.
I expect once this service release comes out in Q1.
I'm going to start to see more and more people switching over to excel 2007.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen. Here's a cool trick that I learned from some of the folks in Springfield Illinois.
Now one of the problems we have in excel 2003 is that if you have a very very long formula the formula tends to overwrite the spreadsheet so here I'm going to select cell Q2 and you'll see that the formula is so long that it actually covers up the first two and a half rows of the spreadsheet, so I can't see the formula and the value at the same time.
In excel 2007, they did a significant improvement here where when we select that cell, rather than making the formula bar, extend down over the worksheet, they give us some options.
We can say hey, let's extend the formula bar, and it will show us basically the formula, one line at a time.
But the cool trick, the trick that I didn't know is that it's possible to take that formula bar and make it be as long as we want so if we really have a long formula and we want to be able to see the entire formula bar and the number at the same time then just grab this margin here between the formula bar and the spreadsheet and make the formula be as long as we want.
Or we can just collapse it and say, hey only show us one line at a time.
And then if we need to, we can either go through a line at a time or expand the whole thing out.
Very cool functionality, that's in excel 2007.
As I'm doing the seminars, I'm starting to see more and more people switching over.
We're up to about, on average, about 12% now.
Though I've run into a couple of seminars where we had as many as thirty percent had switched over.
I expect once this service release comes out in Q1.
I'm going to start to see more and more people switching over to excel 2007.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
