Bob Umlas from the Excel Magic book passed on another amazing tip to me at the Microsoft Summit. If you need to add a total to data that was subtotaled using SUM formulas, you can do it in 1 double-click. Episode 967 shows you how.
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.
How we're gonna analyze as this. Well, let's fire up a pivot table.
Let's if you can solve this problem.
Hey, all right, welcome back to the MrExcel netcast.
Weren't those last two netcast amazing with tips from Mike Gel Girvin.
I learned things as I was watching him do those netcast and he has just as much energy in real life as he does on the videos.
If you're a fan of this podcast, you need to go to YouTube and check out ExcelIsFun all one word.
[ 0:34 ] What am I holding up here, this is a gift from Michael.
Michael is a five-time world record holder in Boomerangs and has his own line of boomerangs.
Is that cool.
Now, you know Michael is from Washington. I'm from Ohio.
Oh hey, you know this guy named David Beim. I've gotten to David's boomerang school back in Cleveland.
He's like absolutely I know David, like what a small world.
So, a couple of great tips the past couple of days.
Well, it's actually Monday morning at the summit. We're gonna go meet the office guys today.
Yesterday spent a lot of great time with some Excel MVPs a lot of folks from the MrExcel message board were there.
Just had a great time and as usual Bob, unless comes up with the amazing tip of the day.
So, you have some numbers here that have been summed and you have multiple sums throughout.
So, you have numbers and then sums, numbers and then sums, numbers and then sums, and right here in row 19, you need to come up with a total of all the records excluding the sums.
Now, because they're sums not subtotals, you can't use the SUBTOTAL function.
Now, I pitch to Bob you know, It's kind of dueling Excel.
I said we'll just take the whole range and divide by 2 and he says oh yeah, okay that's, that's one method, but Bob blew me away with this one.
He says come here to cell 19 and we're going to use the AutoSum button, but we're not just going to click the AutoSum button.
We're going to double click, double click the AutoSum button and lo and behold check this out.
It just wrote a formula that grabbed all of the totals.
Isn't that amazing.
Hey, you gotta check out Bob's book. This Isn't Excel, It's Magic.
There's two versions. The red version is updated for Excel 2007, the blue version for Excel 2003 and again, thanks to Mike Gel Girvin for just a lot of fun yesterday brought you a couple of those podcasts.
Mike, I think is gonna do a book for us with all kinds of great ways to get up to speed with Excel.
What, one exciting guy.
All right, so this is Bill Jelen from the MVP summit in Seattle, hoping you get back to you next time for another netcast from MrExcel.
Well, thanks for stopping by, We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze as this. Well, let's fire up a pivot table.
Let's if you can solve this problem.
Hey, all right, welcome back to the MrExcel netcast.
Weren't those last two netcast amazing with tips from Mike Gel Girvin.
I learned things as I was watching him do those netcast and he has just as much energy in real life as he does on the videos.
If you're a fan of this podcast, you need to go to YouTube and check out ExcelIsFun all one word.
[ 0:34 ] What am I holding up here, this is a gift from Michael.
Michael is a five-time world record holder in Boomerangs and has his own line of boomerangs.
Is that cool.
Now, you know Michael is from Washington. I'm from Ohio.
Oh hey, you know this guy named David Beim. I've gotten to David's boomerang school back in Cleveland.
He's like absolutely I know David, like what a small world.
So, a couple of great tips the past couple of days.
Well, it's actually Monday morning at the summit. We're gonna go meet the office guys today.
Yesterday spent a lot of great time with some Excel MVPs a lot of folks from the MrExcel message board were there.
Just had a great time and as usual Bob, unless comes up with the amazing tip of the day.
So, you have some numbers here that have been summed and you have multiple sums throughout.
So, you have numbers and then sums, numbers and then sums, numbers and then sums, and right here in row 19, you need to come up with a total of all the records excluding the sums.
Now, because they're sums not subtotals, you can't use the SUBTOTAL function.
Now, I pitch to Bob you know, It's kind of dueling Excel.
I said we'll just take the whole range and divide by 2 and he says oh yeah, okay that's, that's one method, but Bob blew me away with this one.
He says come here to cell 19 and we're going to use the AutoSum button, but we're not just going to click the AutoSum button.
We're going to double click, double click the AutoSum button and lo and behold check this out.
It just wrote a formula that grabbed all of the totals.
Isn't that amazing.
Hey, you gotta check out Bob's book. This Isn't Excel, It's Magic.
There's two versions. The red version is updated for Excel 2007, the blue version for Excel 2003 and again, thanks to Mike Gel Girvin for just a lot of fun yesterday brought you a couple of those podcasts.
Mike, I think is gonna do a book for us with all kinds of great ways to get up to speed with Excel.
What, one exciting guy.
All right, so this is Bill Jelen from the MVP summit in Seattle, hoping you get back to you next time for another netcast from MrExcel.
Well, thanks for stopping by, We'll see you next time for another netcast from MrExcel.
