MrExcel Excel Videos

In Excel, your function arguments contain a mix of off-sheet and on-sheet references. Once you touch another sheet in Point mode, Excel starts using the sheet name for on-sheet references. Mike and Bill discuss three ways to prevent this.

The source of your data validation list contains blank cells and you don't want those blanks to appear in the dropdown. Bob Umlas combines many advanced Excel techniques to solve this problem. Check out Bob's book at Excel Outside the Box

7 year old Excel is Funner tries to teach two old guys about Excel. There are some gems here ... 1:04 Mike shows you can resize the camera tool 2:17 in the outtake, Isaac points out a Camtasia tip

Excel MVP Jordan Goldmeier created a cool holiday card in Excel using Excel VBA to create falling snow flakes. Download the card from http://optionexplicitvba.com/2013/12/22/excel-holiday-card/ I asked Jordan's permission to share the workbook with you on today's podcast. In this episode, you will see how to create a custom type in VBA that has several properties. When Jordan's macro simulates the falling snow, he is managing an array of 150 Snowflake types - a very fast way to manage a large number of items in Excel. Also - Jordan keeps the red Happy Holidays message on top by using conditional formatting. Thanks to Jordan for sharing this macro with everyone!

Wouldyouthinkoftypingsentencewithoutspaces? Well, then why are you typing formulas without spaces? ModelOff 2012 World Champion Alex Gordon suggests you should be adding spaces in your formulas to make things easier to read.

In this dueling Excel podcast, the goal is to use a formula to convert 20131220 to a real Excel date. We'll meander through the usual suspects: Text to Columns, DATE(MID), Flash Fill and then be amazed as Mike provides a crazy TEXT function to solve the problem.

ModelOff Finalist Vlad Smirnov works for a bank and is setting up reserves for outstanding debt based on risk class. But, there are some months where the pivot table does not include all risk classes. Since the SUMPRODUCT formulas are counting on all risk classes being there, Vlad shows how to force the pivot table to always show each risk class for each month.

In this video with a ModelOff finalist, Dublin's Paul Kehoe shows us an annoying problem with hyperlinks that break when you change the sheet name and an easy workaround. The video then shows how to select a hyperlink cell without following the hyperlink, and how you can easily create a "Back to Menu" across many worksheets.

In today's podcast, another guest post by Bob Umlas. First, Bob talks about a problem with multi-line formulas in the Formula Bar and suggests that Microsoft needs to solve the problem. Next, an easy way to unwind multiple lines created in one cell using Alt+Enter. To read more about Bob's book: Excel Outside the Box