MrExcel Excel Videos

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.

Ugh - you import data that has Row 1 headings and title repeated every 40 rows throughout the data. We've all been there. Today, a cool macro from Jim Simons called The Sequencer to quickly add the "original sequence" numbers so you can sort, and then sort back. Also in today's episode: An ad for LiveLessons Excel VBA & Macros at http://tinyurl.com/l9z2yp4 Plus, a tip from Bob Umlas on using EVALUATE to shorten the macro from Episode 1907, and an event handler from Brian Crosby to improve episode 1907.

Rafiqul wants to enter 10 in A1 and then have the numbers 1:10 appear starting in A2, A3, and so on. This podcast offers three solutions: a formula, a macro, and then, as a bonus, a cool trick with the fill handle and the Series dialog box.

Ross has a data set in Excel. He wants to highlight the entire row if the date is more than 60 days old. Today's episode shows how easy it would be to highlight the date cell and the formula necessary to highlight the entire row using Conditional Formatting.