Antony in Toronto brings us two more Excel AutoSum Oddities.
This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:
This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2473. Two crazy AutoSum tricks in Excel.
Hey, welcome back to the MrExcel netcast, I am Bill Jelen. Well this week I was really happy to be invited to the Vancouver Power BI and Modern Excel User group. They let me speak at their monthly meeting.
And I talked about Excel, Oddities, Curiosities, and Mysterious Wonders.
Go check that out, they have a recording.
It's all these great tricks that I learned on the road.
I always offered an Excel master pin or later, the Excel guru patch for someone who showed me an awesome trick.
And in this hour I went through about 20 of those tricks that I learned over the 17 years on the road.
And one of them, over here on the right side of the screen, is this great trick for filling in the AutoSum in several cells at once.
I've selected three quarters there and I'm going to press Alt Equals, which is the equivalent of the AutoSum.
And it fills in all of those blank cells with the appropriate AutoSum. Like there it is totalling across.
And even down here, totalling across. It's just working great.
And near the end of the session, Anthony from Toronto, comes on and says hey wait, I have a different trick right?
So I awarded my Excel Guru mission patch for these two wild tricks that I've never seen.
Alright, So what we have here: three months, these are constants, these are just regular numbers typed in the cells.
And then a formula to total Q1.
A blank column, April May, June, again those are constants, and then a formula. Same thing here.
And select this whole area, and then hit the AutoSum.
And it adds a total at the end, which somehow successfully gets us just the formulas in that range. Now this works with these blank columns.
Had there not been blank columns there, had it just been straight data.
So, number, number, number, formula and that repeats then out here we just hit the AutoSum and same thing.
It will find just those formula cells in the group. What a great trick.
I love excel. You know someone took the time to program this in.
Amazing so thanks to Ken and the Vancouver Group for inviting me up to speak at their session.
Of course, over Microsoft Teams it was all virtual.
And thanks to Anthony for that great trick.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below. Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
Hey, welcome back to the MrExcel netcast, I am Bill Jelen. Well this week I was really happy to be invited to the Vancouver Power BI and Modern Excel User group. They let me speak at their monthly meeting.
And I talked about Excel, Oddities, Curiosities, and Mysterious Wonders.
Go check that out, they have a recording.
It's all these great tricks that I learned on the road.
I always offered an Excel master pin or later, the Excel guru patch for someone who showed me an awesome trick.
And in this hour I went through about 20 of those tricks that I learned over the 17 years on the road.
And one of them, over here on the right side of the screen, is this great trick for filling in the AutoSum in several cells at once.
I've selected three quarters there and I'm going to press Alt Equals, which is the equivalent of the AutoSum.
And it fills in all of those blank cells with the appropriate AutoSum. Like there it is totalling across.
And even down here, totalling across. It's just working great.
And near the end of the session, Anthony from Toronto, comes on and says hey wait, I have a different trick right?
So I awarded my Excel Guru mission patch for these two wild tricks that I've never seen.
Alright, So what we have here: three months, these are constants, these are just regular numbers typed in the cells.
And then a formula to total Q1.
A blank column, April May, June, again those are constants, and then a formula. Same thing here.
And select this whole area, and then hit the AutoSum.
And it adds a total at the end, which somehow successfully gets us just the formulas in that range. Now this works with these blank columns.
Had there not been blank columns there, had it just been straight data.
So, number, number, number, formula and that repeats then out here we just hit the AutoSum and same thing.
It will find just those formula cells in the group. What a great trick.
I love excel. You know someone took the time to program this in.
Amazing so thanks to Ken and the Vancouver Group for inviting me up to speak at their session.
Of course, over Microsoft Teams it was all virtual.
And thanks to Anthony for that great trick.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below. Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
