MrExcel Excel Videos

At the 2015 MVP Summit, I caught up with Mike Girvin and his 9 year-old son Isaac shows his 4th grade classmates how to create a multiplication table. In this video, you will learn about F4 Roulette, MMULT function, a great array function, and more.

This video, for Excel's 30th anniversary walks through what a game-changer Power Query is for Excel. You start with a folder full of horribly-formatted CSV files. Using the Query Editor, you load all of those files, clean the data, unpivot the data. Tomorrow, when more files are in the CSV folder, a simple Refresh will load and clean all of the data again. Power Query is built in to Excel 2016 under Data, Get & Transform or a free add-in for Excel 2010 and Excel 2013. Download from Download Microsoft Power Query for Excel from Official Microsoft Download Center To try it out yourself with my files, download them from http://www.mrexcel.com/podcast1956.zip

Two very odd discount tier questions came in today. In this question, all of the units up to the discount tier do not get the discount. Only the units above the tier get the discount. And, there are 4 levels. Rather than the long nested IF statement, I try a combination of LOOKUP, INDIRECT, ROW in an Excel array formula.

A loan service fee is .25% of the unpaid principal balance. Is there a way to write a single formula to total all of the service fees, *without* having the complete amortization table?

A classic problem. Your VLOOKUP is hard-coded to return the 3rd column from a table. But then you insert new columns 2, 3, and 4 in the table. Instead of automatically shifting to return the correct 6th column, VLOOKUP still blindly pulls from the 3rd column. Mike and Bill show two alternate ways to solve this problem.

Joe needs to transpose all of the zip codes for each county into a single row. This is a tricky task - sort of like Repivoting data that Power Query unpivoted. In episode 1953, see how to use two formulas, filter, and text to columns to solve the problem.

By default, Excel creates a bar chart, not a histogram. If you really need a histogram instead of a bar chart, this episode will show you how to change the Gap Width to make a bar chart into a histogram. Note the 5-second "fast motion" of creating the bar chart can be viewed in slow motion here:

The ugly data today has SmithJohn without a space. How can you split the text at the capital letter? Mike and Bill duel with a VBA and a formula method.

A question from Michael: he has 20000 cells with numbers and needs to add an apostrophe before each number. F2 - Home - ' - Enter would be way too tedious. Bill and Mike compare two of the many methods to solve this. Add a comment with how you would quickly solve this problem!