MrExcel Excel Videos

Excel MVP Jordan Goldmeier stopped by yesterday for a guest appearance on the podcast. Traditional rules say that a User Defined Function is not allowed to change other cells, but Jordan has found a way to allow UDFs to break the rules. Watch how he uses this technique to create a Rollover effect in cells, first in a periodic table of the elements, and then in a dynamic chart. The link to the periodic table is at Apress/dashboards-for-excel

Rupesh asks how to increment an invoice number in the form of YYMMM001, where the 001 should count up until you run the macro in a new month. A bit of VBA solves the problem.

Stacy has to adjust formulas every year so they average the last five years of figures. This episode offers several techniques to make this easier. The Laminated Formula Tip card mentioned in the episode is for sale at: Formula Tips from MrExcel (Laminated Card)

How to create an Excel chart that will look good on a black and white printer.

Chris has 400K records from the Board of Elections. The date of birth field is a string of 8 digits in YYYYMMDD format. Today's episode shows how to convert that number to a useful date and then how to apply a filter to copy everyone born in a certain decade.

This follow-up video to episode 1505 shows how to automatically create an invoice register as your are using one of the Invoice Templates from Excel.

Fred set up a workbook in Excel 2010 that uses off-sheet Data Validation. Everything works fine in Windows, but the dropdown arrow is not appearing in Excel:mac and presumably not on Excel 97 either. While Excel now allows for Validation to point to another sheet, it never used to do that. Here is the workaround to let your validation dropdowns work on a mac.

Generate 3 sets of the numbers 1-20 with no repeats in any set.

You have a different rate per category, and half of the categories have a Max but the others do not. Mike and Bill offer different options for calculating the rate in Excel.