MrExcel Excel Videos

When a baseball pitcher is in the game for 7 and 1/3 innings, the stats books show this as 7.1 innings. But to calculate ERA, you need this to calculate as 7.333. Is there a way in Excel to easily convert between 7.1 and 7.333? It turns out Excel has a built-in function for this!

URLs in the video: Excel Daily: Macro Mondays -Save A Back Up Copy Of Your Excel Workbook With Today's Date - How To Excel At Excel Top 10 MrExcel Forum Topics: Hot Topics from MrExcel.com Forum The giveaway for Ctrl+Shift+Enter is over. There is a lot happening in today's podcast. A new Excel Daily newspaper. A giveaway of Mike's new book. A place to browse the top 10 trending Excel topics from the MrExcel Message board. Convert first name last name in two columns to First name and last initial in a new column. Convert formulas to values by dragging.

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.