A lot of times what we face in the real world with data and Excel: The Excel solution is easy but the situation is complicated In this video I discuss the importance of People, Processes and Tools. Excel is only a tool. And a tool won’t save a situation if the process is janky (or […]
XLOOKUP and Dynamic Arrays are finally available for anyone on Microsoft365 (formerly known as Office 365). In this video, Wyn Hopkins and I give you demonstrations of XLOOKUP and Dynamic Arrays working together to make simple but robust solutions. 0:00 Opening 1:34 Wyn’s Demo 6:15 Oz’s Demo 10:40 Wrap-up You’ll see it all! XLOOKUP FILTER […]
The new FILTER function, as part of the new Dynamic Arrays in Excel, is fantastic. However, it is set to filter and retrieve data from contiguous datasets. This is a problem when, for example, a person needs 5 columns from a data-dump of 30 columns. This video shows how to use the CHOOSE function to […]
This video is about using Excel in the real world. Here, I was faced with a situation where a new way of paying commissions was created, but a new report for me to use wasn’t also created. Instead, an existing report was tweaked and it seemed good enough until someone asked a question and requested […]
There are a couple of reasons for this video. 1. Often, to be good with data, the technical Excel tips & tricks aren’t necessary. Sometimes we have to think, probe and strategize. 2. This follows on my previous video that showed how graphing the data unlocked a mystery. Here again, is a true situation where […]
This video takes us back to my very first paid project in Excel. A photographer had a problem calculating the price of a finished and framed photo. The main part of the problem was the mystery around the price of the frame. We gathered receipts and I figured, “what the heck, let’s plot the known […]
Excel has a feature called Ideas. It offers to provide information about the user’s dataset, and accepts natural language questions. This video shows areas where you need to watch out or you can get into deep trouble. I’m skeptical of AI/ML (artificial intelligence and machine learning). Why? Because the hype and marketing around this technology […]
One thing has been torture for those of us who work with data: breaking data out of PDF files! Just recently, Power Query got a new feature: import from PDF. In this video I show 2 examples of importing a PDF into Power Query and also show the old methods of a plain old copy-paste […]
This video pulls together a number of Excel features in order to get an accurate inventory number. This is based on a real life situation where an organizations data was messy because they weren’t adding numbers properly or managing errant entries. This video shows how to use SUMIFS, a dropdown list and Excel’s Conditional Formatting […]