MrExcel Excel Videos

Joe needs to transpose all of the zip codes for each county into a single row. This is a tricky task - sort of like Repivoting data that Power Query unpivoted. In episode 1953, see how to use two formulas, filter, and text to columns to solve the problem.

By default, Excel creates a bar chart, not a histogram. If you really need a histogram instead of a bar chart, this episode will show you how to change the Gap Width to make a bar chart into a histogram. Note the 5-second "fast motion" of creating the bar chart can be viewed in slow motion here:

The ugly data today has SmithJohn without a space. How can you split the text at the capital letter? Mike and Bill duel with a VBA and a formula method.

A question from Michael: he has 20000 cells with numbers and needs to add an apostrophe before each number. F2 - Home - ' - Enter would be way too tedious. Bill and Mike compare two of the many methods to solve this. Add a comment with how you would quickly solve this problem!

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.