MrExcel Excel Videos

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

Tad from Springfield IL asks: How can I filter to 27 specific invoice numbers? With a large data set, scrolling to find the 27 invoices will not be fun. Searching for each invoice and remembering to click Add to Selection will not be fun. Instead - use the Advanced Filter Set up a criteria range… First cell contains the Invoice heading. List the invoices vertically below. Choose one cell in the data set. Data, Advanced Filter Filter in Place Use a Criteria Range Select the criteria range including the heading Click OK

R.A. asks? Can you filter data that has been subtotaled? How can you get the subtotals to appear at the top of each group? When you create the subtotals, uncheck Summary Below Data Can you use a Filter in a data set that has been subtotaled? Yes - but you should include blank cells as well How can you get the percentage calculation to work in the subtotal rows? Right now, the percentage is totaling the other rows instead of doing the calculation After adding subtotals, copy the percentage calculation from a non-subtotaled row to all other rows.

Rhonda from Cincinnati: How to look up both row and worksheet? Use the Date column to figure out which sheet to use Step 1: Build a regular VLOOKUP and use FORMULATEXT to see what the reference should look like Step 2: Use Concatenation and the TEXT function to build a reference that looks like the table array reference in the formula Step 3: Build your VLOOKUP, but for the table array, use INDIRECT( results from step 2) Step 4: Copy the formula from Step 2 (without the equals sign) and paste in to the formula from step 3