MrExcel Excel Videos

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.

Abhijeet asks if it is possible to add a label to the midpoint of a line chart with some descriptive text. In the process, you will see some nice charting improvements in Excel 2013, including the all-important Values from Cells option for chart labels.

Tracy from Texas asks about how to filter a pivot table to only show the rows where the calculated item is not zero. With a data set of invoices and payments, I will show you how to create a pivot table, add a calculated item for Balance, hide the grand total column, then a hack to filter the pivot table to show items with a non-zero balance.

Can you draw a line in Excel Power Map? Well, it is not built-in, but you can use some interpolation in Excel to simulate a line.

Some days, you don't have time to watch a long video to learn something cool about Excel. Introducing the Excel Quick Tips at 40+ Amazing Excel Tricks ideas | excel, microsoft excel, microsoft

Phsa asks how to plot every 100th row in Excel. Episode 1919 uses a quick Formula-Based Criteria and an Advanced Filter to solve the problem.

My best wishes to Ian at the Footscray Community Arts Centre. After five years of bringing great Excel tips to his co-workers, he is moving on to pursue a Master's Degree. His co-workers sent in their favorite Ian tips.