MrExcel Excel Videos

A great "bad data" problem today. A massive spreadsheet from the corporate office has six years of forecast data for dozens of projects. Every time the forecast changes, you have to extract certain records and update a chart. Although I pondered a macro and some tricky (but not quite working) use of Find All, the final solution is something that I had never tried before: Filter, Copy, then Paste Links to create a chartable but live subset of the data.

Building a calendar tool where Task "B" has to fall N number of days after task A. This particular tool is for scheduling house inspection, title search, and financing after an offer is accepted on a house. But the concepts could apply to any similar set of steps where each step must occur N days after a previous task.

After grouping a pivot table by week, you want to show the weeks with the most recent at the top. This does not work because the grouped weeks are text and September (9) sorts before December (12). Today, a workaround.

+Aaron Malek asks if there is a way to have a dropdown in Excel that lists all of the files in a folder. Normally, this might require some VBA, but today, a non-VBA solution using Power Query, the new add-in for Excel 2010 and Excel 2013.

While building a telephone directory, you need to fill a column in Excel with the numbers 7900 to 7999. This episode shows you four different methods.

Free Pittsburgh Excel Seminar on Nov 12 2014: Wands: Excel Reporting Tools for Oracle EBS & SAP - insightsoftware I was a bit surprised when I saw Bob Phillips from the UK roll out a formula where the % sign was used as an operator. You have a cell address, like C3. Slap a % symbol after that in the formula and Excel divides the cell by 100.

Check to see which rows in Excel contain every integer from 1 to 99. This Dueling Excel podcast features six different solutions, including some array formulas and a VBA function.

Another great tip from Smerling in Miami: When you have to automate a complex report, record several small macros instead of one big macro. Then, turn on the macro recorder while you run each small macro in order. The result is a DoAllSteps macro that run the other macros.

Eric in Miami asked me how to create a butterfly chart in Excel. After trying to figure out what a butterfly chart is, I realized he wanted what I call a paired bar chart. Starting in Excel 2010, the simple way to do this is with Data Bars in conditional formatting.