Check out Rob Collie's Raw Data podcast: Raw Data Podcast - P3
Today, I ran across an unanswered question in a Math board. No one really needs to know this answer. It is a high school math teacher trying to trick people into doing algebra. ALGEBRA? We don't need algebra when we have Excel and Goal Seek.
Dinesh is trying to insert two decimal points in a number. Excel has some strange mojo going on with the TEXT function. This video takes a look at some formula methods and then perhaps the easiest way: Flash Fill.
A note: while working on the captions for this video, I realized the formulas starting at 0:44 were wrong (they were set up for 000-00-0000 instead of 00-000-000 like Dinesh wanted. I deleted the original 2382 an uploaded this new 2382. Thanks to those of you who commented on the...
The URL in the video is Microsoft Forms
Today, a problem from Laura who is trying to audit an ACH file. She needs to break the column into 205 individual columns. When you use Excel Text to Columns, it would be incredibly tedious to click 204 times in step 2 of the Text to Columns Wizard. Today, from the Ignite 2019 Show Floor, a surprisingly easy way to solve this using Power Query's Split Column Repeatedly.
Ground-breaking news in Excel. The partner to the LET function was just released. Check out the new LAMBDA function in Excel. This makes the Excel formula language Turing Complete. Store formula logic in a name and pass parameters to that logic.
Link to the Brian Jones article: Announcing LAMBDA: Turn Excel formulas into custom functions
Table of Contents
(0:00) Introduction to Lambdas and Revenge of the Nerds
(0:26) Alonzo Church and Alan Turing
(1:52) First LAMBDA: Case Quantity
Excel has supported the List option in Data Validation for a decade. But let's face it: it is not easy to select from the list. You can't start typing and have the list shorten to just the matches, also known as Look Ahead.
Today, a cool trick from Bob Umlas and his friend Alfred. By creating a few hundred range names, you can call up the list quickly. Type the first few letters of the name and you will jump to the names that match those letters.
Thanks to Create Names from Selection, this...
It is like the Christmas where you got almost every thing you asked for. The Excel team has given us almost everything I asked for with the new data types. You can now create your own data types in Excel, without help from the I.T. department.
I've been out for two months, and while I was away from the computer, I read a book about narrative storytelling in radio. This is a longer video, with a lost of "this happened, then this happened, then this happened". I am not sure this will become...
Exploring the Microsoft/NASA Day of Data Dataset from Day of Data
Today (November 1, 2020) marks 20 continuous years of having a human on the International Space Station (ISS). For today's video, I explore the NASA data set to see: (a) Has there really been someone on the ISS every day? (b) What is the most people aboard the ISS? (c) What is the fewest number of people aboard? (d) What is the most common crew size? Along the way, you will see some Power Query, Date.AddDays, the FILTER...
A strange question today: What is the "correct" way to select consecutive worksheets in Excel? Along the way, you will also see:
How to select non-consecutive worksheets in Excel
How to have the page number for Sheet2 continue after the last page number for Sheet1.
Link to the original question on Quora: Bill Jelen's answer to Which is the correct process for selecting consecutive worksheets? - Quora
Table of Contents
(0:00) Introducing the question
(0:19) Using Ctrl+PageDown or Page Up to...