MrExcel Excel Videos

Bill and Mike take a look at how to do a reverse lookup in Episode #1000. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Manfred from Germany has an interesting problem. Each record takes up six rows in his spreadsheet. Formulas need to point to the first row of the company. Those formulas need to be copyable. But those formulas are in conditonal formatting rules. Episode 1075 tries to solve this problem. This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Do a picture lookup in Excel. Today's dueling Excel podcast shows how to do a picture lookup in Excel.

How to import a list of file names into Excel Use the new Get & Transform Tools in Excel 2016 If you don't have 2016, them download the free Power Query add-in for Excel 2010 or Excel 2013 Power Query is not available for Android phones, the iPad, iPhone, Surface RT, or the Mac Start from a blank worksheet Data, Get Data, From File, From Folder Enter the folder name (or browse) Make sure to click Edit Open the filter on file type and remove anything that is not a PDF Open the filter on folder and remove any garbage subfolders Keep only File Name and Folder Drag Folder heading to the left of File Select both columns Choose Add Column, Merge Columns, Type a new Name Click the New Column and Remove Other Columns Home, Close & Load The...

Download Excel file from Press F9 Until Close Press F9 Until Close Guess Until Correct Brute Force Solving Measure of Closeness L: I am the commissioner of a swim league There are eight teams this year. Each team hosts one meet and is the home team. A meet will have 4 or 5 teams. (5 because you need some meets to have 5 to solve the problem. 4 because some pools only have 4 lanes.) How to arrange the schedule so every team swims against every other team twice? In the past, when we had 5, 6, or 7 teams, I could solve it by pressing F9 until I was close. But this year, with 8 teams, it is not coming out. Need to press F9 many times Customize your model so it results in a one-number "Measure of Closeness" Keep you eye on that number and...

Kent Ohio Seminar: Is there a way to control the default file location when Excel opens? File, Option, Save and change the default file location Controls where the initial File, Open starts

Doug: How to combine four sheets where each has a different number of rows? Use Power Query Format each worksheet as a Table with Ctrl+T Rename the tables For each table, new query From Table. Add a custom column for Region Instead of Close & Load, choose Close & Load to… Only Create a Connection Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add Close and Load and the data appears on a new worksheet For the one table with extra column: the data shows up for only that sheet's records For the one table where the columns were in the wrong order: Power Query worked correctly! Easy to Refresh later

Tim asks: How many of each item is available to sell Complicating factor: An item is comprised of multiple cartons Bill Method #1: Add a helper column with INT(Qty Needed/On Hand) Add Subtotals for the Min of Helper at each change in Product Collapse Subtotals to the #2 View Select all data. Use Alt+; for Select Visible Cells Paste to a new range Ctrl+H to change Space Min to nothing Mike Method #2 Copy the Product column to the right and use Data, Remove Duplicates Next to the unique list of products, use MINIFS Note that MINIFS is only available in Office 365 Bill Method #3: a regular pivot table fails because Calculated Fields won't work in this case. Select one cell in your data and press Ctrl+T to convert to a table. Instead, as...

Tara: how to create a chart with 2 fields along the x-axis Create a pivot table with two fields in the Rows area Make it into a pivot chart