MrExcel Excel Videos

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.

Touma sends in what should be an easy question. Sheet1 contains customer, item, and quantity sold. Sheet2 is a price list with unique prices per customer name. I could have solved this with PowerPivot or with a two-way lookup. As I started to do the lookup, though, I discovered that the sales database include a lot of items that are not in the pricing table. Some are simple misspellings, but others are simply missing. This is representative of real life - the pricing manager's data doesn't match the sales data.

The classic formula for a multiplication table is =$A2*B$1 which is a great way to teach people about mixed references. However, while reading Mike Girvin's new Ctrl + Shift + Enter book, I ran across this awesome one-formula trick using Matrix Multiplication and the MMULT function.