“What is Excel?” I’ve been asked this over the years by people who are curious about Excel or want some insight into what Excel can do. Answering the question is difficult because of the endless ways that people have used Excel to serve their needs. In this video I answer “What is Excel?” several ways, […]
For VLOG EVERY DAY IN AUGUST 2016 I’m dedicating it to 31 consecutive days of basic Excel and calling it Excel Ignited! My regular work had focused on complex Excel skill and data management needs. But Excel Ignited will start with the most basic in Excel. That initial spark that grows into a roaring fire […]
Excel’s PowerQuery (Get & Transform) forces the user to think in terms of full column, full rows, full datasets. This creates a problem: What if you need to shift a column up by just 1 cell? That’s easy in native Excel. In PowerQuery this takes several steps. In this example, artists aren’t matched with the […]
The goal is to split a name out of a cell that included the person’s age, and the make, model and year of the car they drive. (download the workbook: http://datascopic.net/ParseGT) I started this video with the idea of showing you a complex array formula solution. But I kept thinking about an easier way. Ah! […]
This video is in response to a question about a previous video where I showed how to unwind some data. (https://www.youtube.com/watch?v=zlEp9IXdaoI) Delta Sport asked about going in the opposite direction: starting with the unwound data, wind it back up again. This is challenging. It calls for multiple steps in both native Excel and Power Query. […]
For this Funky Funktion Friday, we continue expanding on the party list from the previous videos. This time we have 2 issues: 1. A new column is added to the source data, how do we get it to show up in the final list? 2. We need a sum but Get & Transform doesn’t treat […]
Kevin Lehrbass and I went head-to-head and came up with 2 solutions for 1 problem: how can Excel alphabetize a list of titles and ignore: A, An and The? Kevin uses a sweet array formula while I went the guerrilla route and implement a manual step. We both agree that any solution has to consider […]
Continuing from last week’s FunkyFunktionFriday, we expand on the Full Outer Join and I show you how to write IF statements in the PowerQuery (Get & Transform) editor. You’ll see how to add a custom column, do more data shaping and boost your overall familiarity with the gifts that continue to flow from PowerQuery. My […]
Today’s FunkyFunktionFriday continues from last week and explores the joins in Excel’s Power Query (Get & Transform). We start with a list of people who were invited to a party, and a list of people who are coming and the food they’re bringing. It would be great to have a single list that not only […]