MrExcel Excel Videos

Tom sends in today's question. Enter age, gender, lab result and then lookup the rating in a table to indicate if someone's risk is low, moderate, high, or very high. Tom wants to return the result as a color (green, blue, yellow, red) via conditional formatting to the lab result cell. Today's podcast has a little of everything: rearranging the table, exact match, Less than Match, subtracting the number 1, OFFSET, assigning a formula to a defined name, then even a linked picture (aka Camera Tool) lookup for good measure. Bill always says that 99% of his VLOOKUPs end in False. For today's solution, that statistic drops to 25%.

You have a formula with 6 absolute references. You want to keep one of those constant, but change the other 5 references to point to a new range. Today's episode shows two methods - one involves dragging a range five times, the other is even more convoluted. However - the big discovery in this episode is that you can use F4 to change all nine references from Absolute to Relative, provided your selection does not begin or end on a cell reference.

Today's question: a formula is showing an answer in B2. Can you somehow repeat the input values in C2 so the person reading the spreadsheet can see the answer as well as what went into the answer. Bill and Mike duel it out with the & operator and the CONCATENATE function. Mike throws in a little ADDRESS function, and then Shift+F3 or Ctrl+A.

Jim sends in today's question. Jim is a fan of an iPad game called Dots. You have a 6x6 grid of dots that appear in four colors. You are trying to find the longest connection of dots of the same color. Jim wondered if Excel could generate a random 6x6 grid of four colors of dots to look for patterns. In today's episode, see how to use RANDBETWEEN, conditional formatting and a custom number format to generate the grid of dots. But finding patterns is going to be tougher. While the human brain can detect the patterns, a formula in Excel to find the longest connection would be tougher. The video ends with some conditional formatting to highlight the possible connecting cells but no method for finding the longest connection.

In the third episode about trying to hide blank rows, a simple pair of macros to show all and hide blanks. You won't lose the Undo feature for this workbook.

In episode 1736, the goal was to hide blank rows without using a macro. It turns out the real requirement is to hide blank rows without having to remember to run a macro. Thus, an Event Handler macro that would automatically run in the background is acceptable. In today's episode, learn how to set up an event handler macro that silently runs every time the worksheet is changed. This macro will hide rows that are non-numeric in column C.

Cathy asks if there is a way to hide rows without the assistance of a macro. After a quick failed attempt at using conditional formatting, Bill figures out a non-macro way to hide rows in this episode of the Learn Excel from MrExcel podcast.

Calculate the MEDIAN of the last 10 entries in a column. Bill uses OFFSET with COUNT. Mike uses an array formula from his upcoming Ctrl + Shift + Enter book to find the last 10 non-blank cells. To pre-order Mike's book: Amazon.com: Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic (0783324907186): Mike Excelisfun Girvin: Books

Yesterday, in episode 1733, it required two MATCH and one INDEX to merge the data from two worksheets. Today, using PowerPivot in Excel 2010, another way to find the price for each customer/item combination. While PowerPivot is supposed to make life easier, this method still requires two concatenation formulas, two Ctrl+T, two Add Linked Table, one drag from Key to Key, then one RELATED function and an application of Mike Alexander's trick from my episode 493. Which is easier? 1733 or 1734? Make your note in the comments below.