MrExcel Excel Videos

Flash Fill was introduced in Excel 2013 It is great for parsing (breaking apart) text It is great for concatenating as well Make sure the new column has a heading Type the entry for the first row Start to type the entry for the second row - the results will grey in. Press enter to accept the results You can correct an entry after Flash Fill and it will correct others that match the new pattern. You can invoke Flash Fill with Ctrl+E Flash fill tends not to work automatically with numbers. But you can still use it for numbers by using Ctrl+E

April Fools Day Trick for a co-worker. Right-click Quick Access Toolbar Choose Customize Quick Access Toolbar From the top left dropdown, choose Commands Not in the Ribbon Find Speak Cells on Enter. Click Add to add the icon to the toolbar Turn on the feature. Right-click the icon and Remove from Quick Access Toolbar Wait for your co-worker to start typing! Bonus trick as an outtake: Using VBA to add to Speak Cells.

Sometimes, you have to key data from a piece of paper. You want to proofread the data in Excel. You will get whiplash going back and forth between screen and paper. Right-click Quick Access Toolbar Choose Customize Quick Access Toolbar From the top left dropdown, choose Commands Not in the Ribbon Find Speak Cells. Click Add five times to move all the Speech icons. Select some cells, click Speak Cells. Some of my co-workers should have the Stop Speaking icon on their foreheads! You can control if they read by columns or by rows. Pro tip: speed up the voice in the Control Panel Microsoft Anna is the voice in Windows 7

Plot your Excel data on a map using Power Map in Excel 2013 or 3D Maps in Excel 2016. Episode Recap: Power Map is in Office 365 editions of Excel 2013 Power Map Preview can be downloaded for Excel 2013 In Windows editions of Excel 2016, renamed to 3D Maps Works with one worksheet (as a Ctrl+T table or not) Works with multiple worksheets added to the Data Model Once in Power Map, choose Geography Category changes color Height plots the height Consider a flat map to see the whole world Add a time field to animate over time Wheel mouse to scroll in Alt+ mouse up or down to tilt Alt+ mouse left or right to rotate Add labels Theme 2 Use the time scrubber to animate data over time

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