I got a plea one night from someone who needed to match some numbers. He had the right instinct but he stumbled up on a situation where you just have to have seen a lot of Excel over a long period of time to know about this hack. He needed to match the first 4 […]
Custom Data Types in Excel and Power Query are pretty interesting. In this video I continue to explore them. 1. We add 2 Custom Data Types in a single dataset. 2. In situations where a merge/join has to happen, it’s best to do the merge first, and then create the Custom Data Type. 1:25 Introducing […]
The new Custom Data Types are starting to be rolled out and they’re … exciting … interesting … spooky. This video will be the first of several that will explore the Custom Data Types, how they can be used and where to watch for GOTCHAs. In this video we create a basic Custom Data Type […]
It’s one thing to identify if a partial strong of text exists in a cell. It’s another to identify it and also dig it out. That’s the challenge in this video. 1. Identify if an address contains SW, South West, NE or Northeast. 2. If one of those is found, populate what was found in […]
0:00 – 2:17 Intro & Set-up 2:18 – 4:48 A dark day in Arizona 4:49 – 5:33 Sort birthdays by day and month 5:35 – 7:55 Insert 7 rows between each row This video is primarily about patience with Excel and finding solutions. Often people will comment that I make things look so easy. In […]
One way to get good with Excel is to play with it. In this video I decided to play with Dynamic Arrays and Excel’s form controls.The result is a pretty neat solution with tick-boxes and radio-buttons that allow the user to toggle a dataset between sorting ascending and descending.This video includes: – XLOOKUP – Tick […]
How do you calculate the number of months to complete a project? Here are problems: Problem 1: This project had 5 steps and each started and finished on different days … Step1 12JAN – 19FEB Step2 15JAN – 19JAN Step3 30JAN – 27AUG Step4 5JUL – 15JUL Step5 1AUG – 11AUG Problem 2: A stack […]
After working with data in Power Query there is the option to “Add to Data Model.” What is that? A Data Model is helpful when it’s necessary to bring multiple datasets into one. The data can be used in a single pivot table without having to physically merge the datasets into one place. This video […]
I was surprised with a situation where someone insisted that INDEX/MATCH was his only choice for a formula. I wondered “why INDEX/MATCH?” Why not VLOOKUP, XLOOKUP or a Power Query join? When I saw his data and the restrictions that were on him, this was clearly a context where INDEX/MATCH was the wiser choice. There […]