Rahim Ali asked me about how to keep only capitol letters. For example: France: F United States: USA Republic of China: ROC United Arab Emirates: UAE South Korea: SK (In this video I use names as examples to work with.) This can be done with Excel’s Flash Fill but that’s not a dynamic solution. For […]
Excel’s new Dynamic Array functions are trippy! They require a whole new way of thinking. In this video I play with the FILTER function just to see what it does. You’ll see 6 examples of complex, multi-conditional, multiple column formulas. For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course: https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning Website: […]
Dynamic Array Functions in Excel are so fabulous … BUT! As with anything, there are traps, warnings and caveats. In this video I quickly cover 5 things you should know about Dynamic Array Functions and Formulas. For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course: https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning Website: https://ozdusoleil.com My book: Guerrilla […]
7 new Dynamic Array functions have been added to Excel! In this video you’ll see the FILTER and SORT functions and how 4 formulas bring back results in lots of cells! Also: – There’s new nomenclature using the # to reference a dynamic range. – The new SPILL error lets you know that your result […]
There’s a lot in this video! When you get this working, you’ll be entitled to wear your underwear outside your pants like a true Super Hero! The challenge: We’d like random names, but also choose which format we’d like our result in: – First Name Last Name – Last Name, First Name – First Name […]
Ken Puls of ExcelGuru presented a 3rd Excel Challenge and here’s my solution. The challenge is to take a word, assign values to each letter and generate a total for each word. A = 1 a = 1 . . Z = 26 z = 26 The word ‘Attitude’ gets 100 points. As I started […]
Custom Functions in Excel’s Power Query. It’s finally time to take them on. For a while I couldn’t think of a good use. It’d seen the how-to but never really a what-for. In this video I propose a common situation where lists will trickle in and we’ll need to convert the state abbreviations into state […]
ExcelHash is here!! 6 of us Excel MVPs came together to take on the challenge of making an integrated Excel solution by starting with 4 random Excel elements based on your suggestions. We have: – MAX function – FREQUENCY function – Form Controls – 3D Model See the other awesome entries from my fellow Excel […]
Today I had to formally weigh in on the debate between VLOOKUP and INDEX/MATCH. This argument has gone on for years, and last week I heard strains of it rising up again. So, here’s what I say: VLOOKUP is a tool. INDEX/MATCH is a tool. VLOOKUP has weaknesses, e.g., it’s easy to break, and it […]