Complete Manual Excel Tasks In Seconds With THIS Excel Formula
Complete Manual Excel Tasks In Seconds With THIS Excel Formula
I’ve said many times on the channel: the world of Excel content creation intrigues me! Well, I’ve been involved in it for over a decade now. Specially, spreadsheet ‘hype’ grabs my attention. A few years ago, for example, the release of Excel’s dynamic array formulae (such as =SORT) triggered a flood of content from Excel ‘MVPs’ about how this would ‘change everything’ in Excel.
Members' Monday FREE 1-hour taster session
Download File Here
CLICK HERE TO WATCH THE VIDEO
Now, I’ve found these new additions useful. But, like any approach in Excel, dynamic arrays have their downsides. I’ve found, for example, they can really slow down spreadsheet calculation. And, they’re only available in Excel 365 when a lot of organisations still operate ‘legacy’ versions of Excel such as Excel 2019. Which means they’re only of limited use to people actually using Excel as a tool to help others. So, did they change the game? Hardly.
Don’t get me wrong, I love a bit of excitement and cherish ‘punch-the-air’ moments in Excel – as perhaps you do. I’m no Excel party pooper! But, for me, these moments don’t coincide with the release of new features in Excel. That’s because the tools Excel originally provided us with are adequate. In fact, when you factor in VBA, they are much more than that. People can be too quick to blame Excel’s limitations when they have only a partial understanding of the software. And, to some extent, the Excel content creator community encourages that through its obsession with new stuff in Excel.
Rather, I find breakthroughs come when a real-world consultancy job forces me to apply long-known Excel formulae in new ways, and in new combinations. A change in mindset or pattern of thought, rather than a new formula learned.
A client in football trading tasked me recently with creating a spreadsheet layout across hundreds of columns to house a report. It looked like an annoying Excel manual task – so how to do it? As I mulled it over, I reached for VBA. But, before opening the VBA editor, I felt drawn to a classic Excel formula; I have no idea why, perhaps the scenario reminded me of something I’d encountered years before …
The solution I devised is based on a beautiful synergy between four Excel formulae – COLUMN, MOD, IF and SWITCH. But it’s the MOD formula that makes the magic happen. The key is to understand how Excel numbers columns, just as it numbers rows. Once this is understood and harnessing Excel’s MOD formula, the creation of a complex report layout is a matter of minutes (or even seconds) rather than hours. No VBA, fancy formulae or Excel MVPs in sight. Just Excel’s native capabilities efficiently utilized.
I talk you through it in five minutes, step-by-step, in today’s video. Make sure you download the Excel download file and work along with me – I’d love to hear what you made of it in the YouTube comments.
TIMESTAMPS
00:00 An Annoying Manual Excel Task!
00:06 Excel COLUMN Formula Demo
00:24 Excel MOD Formula Demo
00:42 Excel MOD Formula Explanation
01:31 Excel IF Formula Demo 1
02:25 How To Concatenate Text With A Cell Entry
02:54 Excel IF Formula Demo 2
03:23 Excel UPPER Formula Demo
03:44 Excel SWITCH Formula Demo
04:49 How To Quickly Scale Up Spreadsheet Layout
05:05 How To Convert Formulae To Values
ABOUT MEMBERS' MONDAY
This video is taken from our unique and exclusive Members' Monday learning community.
Looking to build your data analysis skills in a supported environment with expert access, with a group of like-minded individuals?
Need structured materials with a practical focus, and a place to go for help?
Want to take a long-term approach to your learning and get real improvement, rather than lurching from one problem to the next?
You'll love our Members' Monday community!
"I had a massive breakthrough this morning solving a problem with a file I have been unhappy with for seven years!" - RECENT MEMBER FEEDBACK