I work full time as a Data Analyst currently specializing in Microsoft Excel and Microsoft Access. To keep up to date I watch a fair amount of YouTube videos and follow several websites.
This series of posts will feature videos or articles from other sites that I have found to be very helpful. I find that my favorites combine technical knowledge and creativity. Let’s take a look.
Implicit Intersection
What is it? Here is a great comprehensive post by Mynda Tracy at myOnlineTraininghub.com. Also, it’s mentioned in a YouTube video (6:47) by Mike Girvin and Bill Jelen. The biggest potential danger that I see with implicit intersection is that you forget to press ‘Control Shift Enter’, which is necessary for array formulas, and instead of getting an error message you may actually get a numerical answer. Please read Mynda’s post but basically it’s when your array formula is in the same row as the range for your array formula.
MMULT function
The MMULT function is one of those rare functions that you think you’ll never use. However, I watched Mike Girvin’s video and then one day someone said “I’m sure there’s no quick easy way to do this….”. It was fun to write a quick array formula using the MMULT function and hear “You’re a genius!”. Not really, i just watch Mike Girvin and others on YouTube!
“VLOOKUP Into A Range of Zipcodes”: Podcast #1676
Some non technical people think that being a data analyst (or a chess player) is all about memorizing. This video by Bill Jelen proves that creative thinking is an essential skill for data analysts. Watch the video and you’ll see how challenging the question is and how Mr Excel creates a solution in seconds! One of my favorites of all time. What this solution has taught me is that sometimes we should consider various solutions, not just the first idea that pops into our mind.
Introducing the amazing “Flashfill” !
Microsoft Excel
I have used Excel’s ‘Autofill’ feature for a long time. Excel 2013 has a new feature called ‘Flashfill’. But what would I use it for? This video from Microsoft shows you an interesting way to use ‘Flashfill’ and avoid writing formulas for a common text cleanup task (that I have done the long way using formulas many times!).