Another Excel Expert Challenge! This time Wyn Hopkins of @AccessAnalytic stepped up to take on a challenge determining the top 4 color choices that were voted on. In this challenge Wyn had to make decisions about handling votes that didn’t fit the rules, and had quality issues. These challenges are a way to show how […]
Let’s go back to some basics. There’s a lot of cool, brand new stuff in Excel. But I still see basic goofs committed by longtime professionals. Recently I saw a lot of graphs that showed values in the order: 1. Afternoon 2. Evening 3. Morning 4. Overnight That alphabetical order makes no sense based on […]
Next up! Helen Wall (linkedin.com/in/helenrmwall) as the Excel Expert who’s taking on an Excel challenge that she hadn’t seen before I hit the record button. This time: we have data about scheduled shifts and several questions need to be answered about the data. – How many distinct people are assigned? – How much will be […]
Joins/Merges in Power Query are so useful but can be hard to understand. I’ve done videos on the Inner, Outer and Anti Joins. Recently, Semi-Joins have been made available. Again: they can be tough to understand and see a reason for their use. In this video you’ll see: Intro: 0:00 Overview of Joins 0:23 Data […]
The Excel team has released a lot of functions. PIVOTBY is really slick and helpful because it’s dynamic and doesn’t require a refresh when your underlying data changes. But there are warnings. You’ll see PIVOTBY with COUNT, PERCENTOF and COUNTA. You’ll see strengths and limitations. And! Something odd about COUNTA that I never noticed until […]
Another Excel Experts Live Challenge. This time the guest is Model-Off Champion and superstar in the Financial Modeling World Cup competitions. This time we’re looking at a challenge I faced with a series of course details that got all jumbled and needed to be de-jumbled. Watch Diarmuid solve this in record time. And we get […]
It’s great when people ask for Excel help. It’s not so great when the request is belligerent. In this video I show how I handled a belligerent request to count the number of seconds in the year 2024. You’ll see Dynamic Arrays, SEQUENCE, date formatting, the DAY function and COUNTA … and insight on how […]
Bill Jelen, Mr. Excel, recently posted a video requesting input on ways to retrieve specific data while hiding sensitive data. The challenge: how can you use a person’s last initial and the last-4 of their social security number to retrieve relevant details. HOWEVER! The full social security number should be available but not easily revealed. […]
You’ve got a grid and you want to input a code and retrieve the value tied to that code; e.g., input 33 and you want the name Trish. This is easy if you have a single column of codes alongside a single column of values. That’s a plain VLOOKUP or XLOOKUP. HOWEVER! that’s not how […]