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 […]
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 […]