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 […]
#COUNTIFS #CountMultipleCriteria #COUNTIFSfunction For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course: https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition My old blog: http://datascopic.net/blog-2-2
It’s easy to split columns in Power Query and split into rows. But here’s a situation where there’s data in 3 columns that will fly apart and get unmatched if we split one column without splitting the other 2 at the same time. One solution would be to split the source data into 3 pieces, […]
A question came in from someone who wanted to know how to merge rows of data into a single cell. Imagine you have a person’s name in one column and then details about the person on multiple rows; and then another name and details about that person; and so on … Now you’d like to […]
One of Excel’s newest functions: LET() Incredible! This function allows the Excel user to create complex formulas that are easier to create and easier to read. But they do require the user to think several steps ahead. In this video I show 3 examples and make 2 cases in favor of using LET: – Very […]
Thank to my friend Gráinne who showed me this in Power Query: Try … Otherwise In native Excel we have the IFERROR function for when we anticipate errors and know what we want to do with them. In this case, we want to add the Shipped and Inventory amounts. But, if someone has put a […]