MrExcel Excel Videos

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.

This question came up in my Miami Power Excel seminar. Every day, a report is downloaded to Excel with tiny sections, each separated by a blank row. We need a quick way to remove all the blank rows, but the data should not be sorted. Thanks to some great shortcut keys from Smerling, the problem becomes simpler.

From the Miami Power Excel seminar - when you download Quickbooks customer list to Excel, some rows have the customer name in the address field and others do not. How do you remove the duplicate name before joining text?

A case study from my PASS webinar - taking Billboard Hot 100 data through Power Pivot and then creating a Win Loss Sparkline. Table of Contents 0:50 Creating a static lookup table using a pivot table 1:50 Filling Pivot Table blank cells with -1 for Win/Loss functionality 2:45 Converting pivot table to static values 3:15 Data in Power Pivot 3:55 Killing Compact Layout in favor of Tabular Layout 4:10 Adding a decade slicer for filtering 4:20 Preserve column widths in a pivot table 4:40 Formatting a Slicer 5:00 Sorting top values to top of pivot table 5:55 Selecting multiple decades from the slicer 6:10 Filtering to top 10 items in a pivot table 6:40 Which artists had staying power? 7:05 Building the presentation layer above the pivot...

You have a bunch of Forms Control Checkboxes in Excel and you need to know how many in a certain column are checked. The solution in today's episode is the wrong way to go. The better way is back in podcast 1871: To register for the Oct 21, 2014 Miami Seminar: Bill "MrExcel" Jelen at IMA 8 CPE Seminar

Melissa wants to apply a color to values in the 1000-1999, 2000-2999, and so on ranges. This Dueling Excel podcast comes down to a question of what did Melissa really mean by her question. Bill does a VBA Macro to color all bands. Mike does an input cell and conditional formatting to highlight one chosen band.

Some data downloaded from Carnegie Learning is causing problems in Excel. Although the data looks like HH:MM times, they all add to zero. Here are the steps needed to convert the text times to real times.