As much as I enjoy sharing my knowledge I have found that this series is just as beneficial for me as it is for others. I sometimes ask myself or others “Do you remember that Excel thing that does….”
In Part 2 of this series I continue to sort through tips from YouTube videos and website bookmarks.
‘Picking Excel Formulas in a Data Validation Drop Down Menu’
I love learning new Excel tricks. It’s like a hobby for me but it also allows me to offer better solutions at work. Every once in a while I come across something so unique that I lose track of time as I study it. This is exactly what happened when I watched this video from Excel TV Excel trainer Szilvia Juhasz (www.xszil.com) explains how to create a data validation list that allows you to select which formula that you want to use in the current cell. Each drop down entry is tied to a named range. It’s probably not something that you’ll use everyday but this tip gets full points for creativity.
‘Dueling Excel – Match 3 Consecutive Items – Duel 165’
Sometimes we are presented with a data challenge and we complicate the solution. The requirement is simple enough to understand but I could not think of a quick and easy solution. In this video, Mr Excel’s solution is based on the simple MATCH function. Of course there is a twist to it so watch this short video and enjoy.
‘Lookup A Number Value for Each Letter in a Cell” Podcast #1667’
So, you have a code like this “AGGTACCGTA” in a cell and you need to (a) lookup each individual letter, (b) return the number assigned to each letter, and (c) add them all up. Mere mortals should shield their eyes and look away. In the YouTube video, Bill Jelen (aka Mr Excel) provides a vba solution and Mike Girvin writes a mega formula. One of my favorite videos as each solution is so interesting.
Mr Excel & excelisfun Trick 162 Count Between Upper Lower Time
I really liked two things about this video. (1) How Bill used the ‘Fast Excel’ tool to analyze which of Mike Girvin’s formula solutions was faster. ‘Fast Excel’ was created by Charles Williams. His website is great for serious Excel users who want to learn more about how Excel calculates. (2) Although Mike’s formula solution may be more practical, Bill’s use of Excel’s data table is very creative! The data table feature is one of Excel’s best kept secrets.
‘Save Excel Settings in Custom Views’ by Debra Dalgleish
Over the years, I have learned so much from Debra Dalgleish. If you’re serious about learning Excel then you must visit www.contextures.com/ This video by Debra demonstrates how to use Excel’s neglected ‘Custom Views’ feature. Sure, it takes some time to set it all up but it could save you a ton of time in the long term. Many of Excel’s features take time to study and understand but afterwards you’ve got another tool that you can use when building Excel models.
About Me
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.