MrExcel Excel Videos

Techniques for creating fake random data in Excel. Create whole numbers, names, company names, addresses, or values that end in 00 in order to create sample data in Excel. Also here - a cool way to select the next 1000 rows using the Name box and R1C1 formatting!

Today's problem is sent in by Bill. He is looking for a random selection of 250 items out of 2500. But, he also wants to clear out two kinds of garbage data first. Bill thinks he needs a =WORDCOUNT() function, but there are many ways to solve this problem. See Mike Girvin introduce Right-Click-O-O to sort, plus IFERROR, FIND ALL, Text to Columns, RAND and much much more.

I have 2500 records. There are 40 unique values in one column. Which values occur most often? It takes just six clicks to solve this problem in Excel.

After trying to scrape some data from a web table into Excel, some modern web trickery is causing the data to copy to the clipboard in a way that all the data pastes into a single cell in Excel. Power Query comes to the rescue, pulling the data correctly into Excel.

Today's problem: 225000 cells that contain words. "Find All" cells that contain a search term. Copy the cell locations to Word. This is an ugly process given the way the data is set up. Today, a VBA macro unwinds the data into a single column, with cell address, so the Filter search tool in Excel 2010 can find the items.

While collecting possible tips for my 40th Excel book, I ran across two tricks that were new to me. Shift+F8 lets you select non-contiguous ranges without using Ctrl+Mouse. And Go To Special Row Differences. For more info about the book: MrExcel XL Book - 40 Greatest Excel Tricks

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.