MrExcel Excel Videos

Another episode of the Dueling Excel podcast. This time, we tackle Siva's problem of how to add 500 to a cell that contains many numbers: 123,234,345,456. Last week, in Duel 192, Bill used VBA and Mike used LET. Today, we both use Power Query, but in completely different ways. Bill seeks out the Power Query equivalent of the DAX CONCATENATEX function and finds a formula bar hack with Text.Combine. Mike seeks a single-formula solution in Power Query but runs into a problem using "each" with two iterators. Table of Contents (0:00) Introduction of the problem (0:37) Bill's Power Query solution (2:31) Concatenating text in Power Query with Text.Combine (3:39) Mike's Solution (6:09) Mike explains "each" keyword (6:55) Difficulty due to...

A look at a free add-in from Sam Radakovitz on the Excel team. This tool lets you create fake data or sample data. If you need Excel data for your next book, blog post, YouTube video, or just to make it look like you've been hard at work, the Fake Data tool makes it easy to create the data. Download from Fake Data — Sam Radakovitz Table of Contents (0:00) Introduction (0:32) Generating tables of fake data (1:19) Adding columns to existing data (2:50) Changing case in Excel data (3:21) Cleaning web characters from data (4:20) Wrap up

Download the add-in from Fake Data — Sam Radakovitz Today, a review of a free Excel add-in from Sam Radakovitz. This add-in will help you to change you data before sending it to someone. You can mask names, change numbers, and so on. Table of Contents (0:00) Introduction (0:34) URL to download add-in (1:00) Alter data in Excel (1:13) Remove last name from names (1:25) Change cities to other city names (1:43) Change all numbers to randomize them (2:36) Change first names to other names (2:52) Wrap up

A new recipe on a favorite trick from Albuquerque New Mexico. First, a little roasting of Hatch chiles. Then, how to create a filter criteria in a cell below your data. This episode shows two different ways to filter - one for Ctrl+T tables and a faster way for regular ranges. Table of Contents (0:00) Roasting Hatch chiles (0:37) Introduction (0:45) Creating Criteria row for a Ctrl+T table. (1:40) Using AutoFilter on a regular range (2:19) Wrap up

As of August 25, 2020, there have been 85 launches of the Atlas V rocket. Don't take my word for it. We ask Tory Bruno, the CEO of United Launch Alliance. He says there have been 85. But Excel insists there are 36. Wolfram Alpha says their data is expertly curated. This begs the question: expertly curated by whom? And when we report an error, who is checking it? And what errors are coming to Excel that we don't know are errors?

Experimenting with the Wolfram Alpha data types, I discovered that Excel can predict when the Sun will rise at your location with amazing accuracy. Since the sunrise time is based on Latitude and Longitude of the viewer, this means that Excel must know where you are located, based on the I.P. address of your computer.

Thanks to a comment from Gaetan, I realized that the Excel dynamic array function FILTER could be used to filter out columns as well as rows. As long as your second argument evaluates to a one-row array constant, you are good to go. Examples in this video: Filtering to specific column with a hard-coded array constant. Filtering to specific columns using XMATCH or MATCH. Filtering to columns using formulas in a helper row.

UserVoice viewers have been asking Microsoft for a TEXTSPLIT function. Thanks to a comment from Daniel Dion, it turns out that we've had a function to do TEXTSPLIT since Excel 2013! In this episode, we break down the FILTERXML function in Excel.

Jonathan remembers that Excel 2010 would let you Select Visible while filtered, then clear the filter. It is not working today. Or is it working? The bug seems to be with the grey selection boxes that show you what is selected. The good news, once you know it is a bug, you can keep those visible cells as the only ones selected.