MrExcel Excel Videos

A review of a flexible CRM package from W5Templates. All the data is stored in Excel and synced to your mobile device. For a free trial, visit W5Templates

The link at the end is MrExcel XL Book - 40 Greatest Excel Tricks Creating a holiday gift tracker in Excel. This covers: (a) pulling a data validation list from a table that automatically expands, (b) Inserting hyperlinks. (c) Inserting pop-up pictures. (d) CountIF and SUMIF formulas. (e) Creating graphs. (f) Hiding your top-secret spreadsheets with drawing objects and using the Selection pane to toggle the visibility of the items, (g) The perfect gift for your accountant.

For the IndieGoGo link: MrExcel XL Book - 40 Greatest Excel Tricks Two of the least useful functions in Excel - ROMAN and ARABIC. Learn why Steven Spielberg, Beyonce, and Enron accountants might be the only people using ROMAN. Plus, an announcement about how you can appear in my 40th Excel book.

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.