MrExcel Excel Videos

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.

Three names in 3 adjacent cells - search through a lookup table to find those exact three cells in sequence. This episode shows an array formula to solve the problem. Also - check out our new theme from Excel guru Szilvia Juhasz.

+Rahim Zulfiqar Ali asks why VLOOKUP(,,,True) returns certain wrong answers when the table is not sorted. The better question would be how it returns the right answer at all when you break the rules. Today, I explain the gory details behind the Binary Search used by the VLOOKUP(,,,True) and show how manipulating key values at the quartiles of the lookup table can send the VLOOKUP into the wrong quadrant of the table. Bottom line: Don't use the VLOOKUP(,,,True) with an unsorted table. Or.... just use VLOOKUP(,,,False) like 99% of the rest of the world.

How to do a Distinct Count or a Unique Count in an Excel 2013 Pivot Table. If you still have Excel 97-2010, watch the outtake for the ugly method prior to Excel 2013.