MrExcel Excel Videos

Mike from the UK has thousands of records with latitude and longitude. He wants to add a hyperlink to each record that will show the location on a map. Adding hyperlinks manually will take forever. This video shows how to quickly create the hyperlinks in bulk.

James asks how to take 10 years of check data and convert it to a summary table with months going across the top and years going down the side. James would prefer not to have to add new YEAR and MONTH columns to the original data. Episode 1752 will show you how to solve this in a few clicks.

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.