MrExcel Excel Videos

Upendra asks how to transpose data while keeping a live link between the original data and the transposed data. A normal Paste Special Transpose will turn the data but there are no links. In today's podcast, two formulas that will solve the problem.

VLOOKUP is returning zero when the matching record in the lookup table is blank. Bill and Mike duel with various solutions to replace the 0 with "No Price Found".

Tom's co-workers created a monster today. They have 15000 rows of formulas that are pulling data forward from a query, but filling the extra rows with "". So, when Tom tries to create a pivot table, it is seeing the "" as text and causing the pivot table to count instead of sum. Normally, you would use a Ctrl+T table, but the "" won't let that work. So - pretend like it is Excel 2002 and dust off the =OFFSET function to create a dynamic range. I really thought I would never have to do this again, but Tom's co-workers forced me into it.

Nirml asks how to extract all records between certain dates for a customer, using a formula. This requires a fairly complex array formula. Using a page from Mike ExcelisFun Girvin's new book, I explain how to extract records using a formula.

While most in the USA are enjoying a long holiday weekend, another podcast looking at how data created in desktop Excel 2013 will render on the iPhone Excel. Among other things, they get an A+ for sorting, but an F for summing filtered data.

Today, Bill looks at Excel on the iPhone.

Ams asks if there is some VBA that will tell you the previously selected cell. Say you are in A1, then click in B1. Is there any VBA that will tell you that the previous cell was A1? It seems like this should be possible. In today's episode, see how Bill puzzles out the solution by using the macro recorder to learn about Names then VBA AutoComplete to find the right property. Near the end of the video, I add two new names to show that the value of A1 was "X" when you selected it, but "Z" when you left it.

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.