For a list of my Excel courses at Lynda/LinkedIn: https://www.linkedin.com/learning/instructors/oz-du-soleil There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday. Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition My old blog: http://datascopic.net/blog-2-2
Dependent Dropdown Lists in Excel are pretty handy to know. But there’s an interesting problem when you’ve gotta stack multiple dependent dropdown lists and the source data might add or remove data in the different stages. This video shows how to make this happen using Dynamic Arrays, UNIQUE and the FILTER functions. Also check out […]
The new Wolfram Data Types are pretty interesting, but there are things that you have to watch out for. This video is a sampling of 8 things that are weird about the data types and how they work within Excel. 1:19 Fascinating and Incomplete Entries 2:12 Unexpected Translation 3:29 False Negatives. When looking for a […]
NOTE: this videos shows how I build a solution from scratch. Cristiano Galvão of Excel Turbo posted something fascinating on LinkedIn. He had a main table of data and one column showed each record as Active or Inactive. There were two interesting things about the spreadsheet: 1. If a record was Inactive, the font was […]
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 […]