MrExcel Excel Videos

Two Power Query tricks today: Importing a list of files from a folder into Excel and then cleaning all CSV files in a folder. Episode Recap: The Power Query tools are on the Data tab in Excel 2016 Free add-in for 2010 and 2013 List all files from a folder into the Excel grid using Power Query Choose New Query, From File, From Folder Not obvious: expand the attribute field to get size If your data is in CSV files, you can import all of the files at once into a single grid Promote the heading row Delete the remaining header rows Replace "" with null Fill down for the outline view Delete the grand total column Unpivot the data Formula to convert month names into dates Complete list of steps - world's greatest Undo Next day - refresh the...

Solver is a free add-in for Windows versions of Excel that can find optimal solutions for problems that are more complex than something Goal Seek can solve. Solver has been a free add-in since the days of Lotus 1-2-3 Solver is a product of Visicorp founder Dan Fylstra Solver in your Excel is a smaller version of heavy-duty solvers Learn more about pro solvers: http://mrx.cl/solver77 To install Solver, type Alt+T then i. Check Solver. Solver will be found on the right side of the Data tab You want to have an objective cell that you are trying to minimize or maximize. You can specify multiple input cells. You can specify constraints, inluding some that you would not expect: No half-people: Use INT for Integer Solver will find an optimal...

Creating a what-if Data Table from a blank cell if your model includes RAND. Episode Recap: Today's trick is from Professor Simon Benninga Game of penny-pitching. 50/50 chance that player A or player B doubles their money Play 25 rounds of penny-pitching in Excel Use =RAND() and see if it is greater than 0.5 to find if you are up or down for the day Copy that formula to 25 rows and chart it Hot stock analyst might be up… but press F9 Past results are not indicative of future Expand the worksheet to 250 rows to model a whole year Add statistics about that year The all-important corner cell will be blank The row input cell is blank The column input cell is any blank cell The resulting table models 30 years of results

Three what-if tools in Excel Yesterday - Goal Seek Today - a Data Table Great for two-variable problems Trivia: the TABLE array function can not be manually entered - it will not work Use a Color Scale to color the answers What if you have 3 variables to change? Scenarios? No! Copy worksheet Tables are slow to calculate: calculation mode for All Except Tables Thanks to Owen W. Green for suggesting this tip

Algebra? Inverse function? No, most of us turn to the Price is Right method to back into an answer. Today's episode compares all methods (except algebra). Episode Recap: How to back into an answer in Excel Find an inverse function for PMT by using fx and searching for PMT Try the Price is Right method - higher, lower, higher, lower Goal Seek is an automated Bob Barker Goal Seek even works with changing the term

Learn how to protect formula cells in Excel. Episode Recap: All 16 billion cells on the sheet start out Locked First, unlock all cells. Select all cells using triangle northwest of A1 Ctrl+1 to display Format Cells Go to Protection Tab and uncheck Locked Home, Find & Select, Formulas, Ctrl+1, Locked Review, Protect worksheet Don't bother with a password. Easy to lose. Easy to break. The only person who wins with a password are the Estonians who get $39 Scroll through the Protect Sheet dialog: you can choose to allow sorting, filtering Preventing people from seeing your formulas Choice 1: Locked, and uncheck Selected Locked Cells. Problem: strange to navigate with keys Choice 2: Locked, Hidden, and check Select Locked Cells. Easier to...

VLOOKUP when used with False is a slow function Sorting the data AZ does not speed up the function Sorting by popularity could speed up the function Switching to VLOOKUP with True is faster, but it will report the wrong answer if the item is not found To mitigate the problem, do a VLOOKUP(A2,Table,1,True) to see if the result is A2 first 14000 VLOOKUP(True) and 7000 IF run faster than 7000 VLOOKUP(False)

With a tiered commission, bonus, or discount program, you often have to nest your IF functions The Excel 2003 limit was 7 nested IF statements. You can now nest 32, but I don't think you should ever nest 32 When would you ever use the approximate match version of VLOOKUP? This is the time. Translate the discount program into a lookup table VLOOKUP won't find the answer in most cases. Putting ,True at the end will tell VLOOKUP to find the value just less. This is the only time the VLOOKUP table has to be sorted. Don't want the VLOOKUP table off to the side? Embed it in the formula. F2 to edit the formula. Select the lookup table. Press F9. Enter.

VLOOKUP can not look to the left of the key field! Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value. Or - in real life, rearrange the lookup table This episode features an original letterpress poster from Bobby Rosenstock at justajar.com