MrExcel Excel Videos

How can you use conditional formatting to highlight all formula cells? It is easier in Excel 2013 with ISFORMULA, but possible in previous versions with some VBA. Make a note in the comments if you previously new about Home, Find and Select, Formulas!

I've been annoyed by Compact Form in Pivot Tables since Excel 2007. I am sure you have things in Excel that annoy you as well. Now, you can communicate those annoyances directly to the Excel team using Excel.UserVoice.com.

In this live Dueling Excel podcast, you have to filter out a bunch of columns and filter rows by using MATCH or VLOOKUP. Bill solves this with an Advanced Filter. Mike solves it using amazing steps in Power Query.

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.