My best wishes to Ian at the Footscray Community Arts Centre. After five years of bringing great Excel tips to his co-workers, he is moving on to pursue a Master's Degree. His co-workers sent in their favorite Ian tips.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1918.
Ian’s Greatest Hits.
Well, something a bit unusual today, we're going down under to Australia, to the city of Melbourne.
Actually, to the area called Footscray.
And our podcast today is inspired by the Footscray Community Arts Centre.
And I have a shot out to Ian.
Ian is the much-loved finance manager at the Art Centre, he regularly watches the MrExcel podcast and he takes that knowledge.
He's the Excel guru, who teaches his co-workers the wonders of Excel.
His co-workers wrote to me and said, that after 5 years Ian is moving on, to pursue a Master's degree.
They are going to miss you there.
So Ian, in your honor today, your co-workers are sending in a recap of the greatest hits.
Here, they send in their favorite, favorite tips, that you've taught them over the years.
And I want to get started off really easy.
You have a column of numbers, you need to total those numbers, go to the first blank cell and what do you do?
You press Alt+=.
Alt+=, and then Ctrl+Enter to stay right in that cell.
Another one: if we have leading zeros and you type in the number, you lose the leading zero, right?
Ha, but you taught them: type the apostrophe (‘) first, and the leading zeros stay.
They even went on to find the custom number format.
But while those are pretty simple, I love that you've taught them Ctrl+T. So we have a data set here, Ctrl+T, that gets us the auto filter drop-down.
So, the filter drop-down.
So we can go through and start to mine that data.
So we can find all of the records for Ford, all the records for Product DEF, all the records in the West Region, and start to mine that data.
Then, of course, down here press the Alt+=, to get the formula that gives us the total of just the visible cells, using the subtotal function.
I'm going to remove that and undo the filters, and finally: they say their favorite trick is: now that we have that data, we can create a Pivot Table.
All right.
And that's good, I'm glad you showed them the Pivot Table.
Certainly one of my favorite tricks in all of Excel.
So just take Region, Product and Revenue, and very quickly summarize that data.
So Ian, I appreciate you being one of the Excel disciples out there and taking this knowledge to your co-workers.
I'm sure that most people watching the MrExcel netcast, or the Excel Is Fun netcast, are the guru in their office and take these tricks on to their co-workers.
Ian, your co-workers certainly appreciate it and they're going to miss you.
All right, hey, I want to thank everyone who watches the MrExcel netcast and I appreciate you taking that knowledge and spreading that knowledge, and making everyone more efficient in Excel.
I want to thank Ian and all of his co-workers, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1918.
Ian’s Greatest Hits.
Well, something a bit unusual today, we're going down under to Australia, to the city of Melbourne.
Actually, to the area called Footscray.
And our podcast today is inspired by the Footscray Community Arts Centre.
And I have a shot out to Ian.
Ian is the much-loved finance manager at the Art Centre, he regularly watches the MrExcel podcast and he takes that knowledge.
He's the Excel guru, who teaches his co-workers the wonders of Excel.
His co-workers wrote to me and said, that after 5 years Ian is moving on, to pursue a Master's degree.
They are going to miss you there.
So Ian, in your honor today, your co-workers are sending in a recap of the greatest hits.
Here, they send in their favorite, favorite tips, that you've taught them over the years.
And I want to get started off really easy.
You have a column of numbers, you need to total those numbers, go to the first blank cell and what do you do?
You press Alt+=.
Alt+=, and then Ctrl+Enter to stay right in that cell.
Another one: if we have leading zeros and you type in the number, you lose the leading zero, right?
Ha, but you taught them: type the apostrophe (‘) first, and the leading zeros stay.
They even went on to find the custom number format.
But while those are pretty simple, I love that you've taught them Ctrl+T. So we have a data set here, Ctrl+T, that gets us the auto filter drop-down.
So, the filter drop-down.
So we can go through and start to mine that data.
So we can find all of the records for Ford, all the records for Product DEF, all the records in the West Region, and start to mine that data.
Then, of course, down here press the Alt+=, to get the formula that gives us the total of just the visible cells, using the subtotal function.
I'm going to remove that and undo the filters, and finally: they say their favorite trick is: now that we have that data, we can create a Pivot Table.
All right.
And that's good, I'm glad you showed them the Pivot Table.
Certainly one of my favorite tricks in all of Excel.
So just take Region, Product and Revenue, and very quickly summarize that data.
So Ian, I appreciate you being one of the Excel disciples out there and taking this knowledge to your co-workers.
I'm sure that most people watching the MrExcel netcast, or the Excel Is Fun netcast, are the guru in their office and take these tricks on to their co-workers.
Ian, your co-workers certainly appreciate it and they're going to miss you.
All right, hey, I want to thank everyone who watches the MrExcel netcast and I appreciate you taking that knowledge and spreading that knowledge, and making everyone more efficient in Excel.
I want to thank Ian and all of his co-workers, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
