MrExcel Excel Videos

Ahmed is looking for a formula that will return certain text if another cell has been crossed out. This is not possible with the Excel built-in functions. But it is possible using a User Defined Function in VBA. Today's episode shows you how.

In Excel, Page Down goes down one screen. Ctrl+Page Down goes to the next worksheet. How do you do Page Down to go right one screen full of columns? In today's episode, we look at Alt+Page Down, plus how to go right one screen in a macro. This is the first mention of the upcoming book, Excel Macro Recorder Success.

I was doing a webinar and it sounded like a simple question; how do you delete a slicer in Excel? But, when you delete a slicer, why does Excel seem to remember the slicer settings?

Power Query is a free tool from Microsoft for Excel 2010 & Excel 2013 that lets you import imperfect data into Excel, clean it, and then later re-do the same steps with the simple click of Refresh. Table of Contents: The Power BI Stack 0:05 The data on a website 1:20 Power Query data from web 1:40 Choosing a Table 1:55 Cleaning the data 2:10 Removing "kts" 2:30 Removing Feet and KM using custom delimiter of "feet" 3:10 Introducing the M language 3:50 Link to Power Map video 5:10 Contrasting with regular Excel 5:43 Refresh button 6:00 Refreshing after 30 minutes 6:50 The drudge of daily steps 7:10 Final refresh 8:00 Note: This video also shows some Power Map, but it is a repeat of what I did in episode 1674:

A question from my Power Excel seminar in Tulsa... several co-workers have a problem in Excel 2010 when they select a range of cells. The color of the selected range is a green color, making it hard to see the cells. Today's video shows where to find this setting.

The Hot Topics newsfeed at MrExcel.com highlights five interesting discussions at the MrExcel Message Board from the previous day. This is a great way to see what other people are trying to do with Excel and then to get the opinions of several Excel experts on how to solve the problem. This is like a modern day Athenian Agora - Socrates, Plato and Aristotle all frequented the Athenian agora, discussed philosophy and instructed pupils there.

How to download past drawing results of the Mega Millions game and find the most common numbers in the last year, quarter, or month. This long video is a followup to episode 453, where I glossed over how to put the results in a long column. Update: +MrBubeTube pointed out that the Iowa lottery website offers the results in a nice tab-delimited form that imports better to Excel: http://www.ialottery.com/Results/MM.txt

Today's question is how to extract the last 3 digits between this and that. While Bill starts down the formula road, he switches to a VBA User Defined Function. Mike creates a formula with 15 different functions. Finally, Charles Williams and his Speed Tools jump in with a cool Regular Expressions (RegEx) function to simplify things. (Update from Oct 1 2014: See Charles Williams blog post about an improvement to SpeedTools to handle this: Extracting Digits from Text: Using Formulas and Designing a Missing Excel Function: GROUPS ) Table of Contents: Definition of the Problem 0:20 Bill tries a formula 1:09 Bill switches to VBA 2:30 Mike does a formula 4:49 Using RegEx in Excel 11:25 Timing the Results 14:00

Pivot tables are great when the source data is formatting correctly. But today, our data set has the Quarter field stretching across the columns instead of down the rows. This creates an awkward pivot table. The old solutions (see episodes 1787 and 705) took a lot of manual effort. Today, I show how the Power Query tool for Excel 2010 and Excel 2013 make this incredibly easy.