MrExcel Excel Videos

This duel started out as a simple question - do three lookups and multiply the results for an employee review system. But it evolved into a long discussion about array formulas and can you replace two function arguments with an array. Also along the way, a custom VBA function to solve the problem with a shorter formula.

Today's question: how do you change the font size in a chart legend in Excel. There are a lot of settings for the legend, but none appear to let you change the font size. The answer is hidden in plain sight.

Quinton from Jacksonville is searching for a way to have the Default Excel Table format persist from one workbook to the next, but the defaults only apply to one workbook. In Episode 1793, see how to use a custom Book.xltx template to store the table format for all future tables.

The second annual ModelOff competition starts on October 26, 2013. Play from your desk for a chance to win a trip to NYC for the ModelOff Finals. Or - compete against your co-workers for bragging rights. I am a judge for ModelOff 2013 and you can win one of 5 free entries to the competition.

Today, you have a column of numbers with a few #REF! errors scattered throughout. You want to sum the non-error cells. This is easy using Excel 2010 and AGGREGATE or more difficult using an array formula in earlier versions of Excel.

Every day, I see a tweet telling people to sort their VLOOKUP table A-Z. Does this really help? No! See a head-to-head comparison of unsorted versus sorted and then other ways to make your VLOOKUPs truly faster!

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