MrExcel Excel Videos

One cell has a security name, ticker symbol, then price. The complicated part is that the security name might have 3, 4, or 5 words. How can you extract the last 2 words into new cells and everything else as the security name? Bill and Mike try Flash Fill, a VBA function, and a long formula to solve the problem.

Tom would like to do a VLOOKUP to pull all employees for a given department. While VLOOKUP can not return multiple results, Bill and Mike come up with formulas to simulate the request.

It is common in Excel to have columns of months, quarters, weeks, or years. But those data sets make horrible pivot tables. You really need one column with the time value and one column with the sales value. This episode shows how to use Mike Alexander's Multiple Consolidation trick to unwind the data, but there is a twist; this data set has 3 columns of labels. Update! If you have Excel 2010 or Excel 2013, there is a faster way to solve this problem using Power Query. See

Calculating workdays between two dates for a farmers market that is open 4 days a week. This dueling podcast shows how to use a new feature introduced in Excel 2010 to calculate dates for any non-standard work week.

"... Not A Custom List." Justin is back today. His pivot table filter list is still sorted in the wrong order. Yesterday, in episode 1784, I theorized it was due to a custom list, but that was not the problem. Today, another reason that the filter list could be out of sequence and how to fix it.

Justin on Twitter asked what determines the order of the drop down for a selection list in a pivot table. In Episode 1784, Bill shows a pivot table that ends up in a seemingly random sequence - three large cities at the top, followed by alpha after that. This episode is full of accidental discoveries - things that can help Justin solve the problem, but also plenty of useful tricks that can let you control the order of items in a pivot table.

Someone asked if I could create a macro that would print faster than Ctrl+P. I am guessing they are upset about the extra click required after pressing Ctrl+P starting in Excel 2010. In today's episode, how to add QuickPrint to your Quick Access Toolbar and how to record a macro to your personal macro workbook to print in one keystroke.

Bob has a two-row data set with sales from different countries arranged across the columns. He wants the country with the largest sales to appear first. Episode 1782 shows you how to sort data left-to-right.

Grab some data from the web, paste to Excel, and you get all sorts of things that you did not expect. Today, there were tiny icons, hyperlinks, columns of the wrong width. Today's episode shows Remove All Hyperlinks and a great way to get rid of the pasted icons in a single command.