### Excel Magic Trick 1313

August 14, 2017

DESCRIPTION Learn how to create an Array Formula to Create Sorted Unique List in Cell. This is a huge complicated formula that requires the Excel 2016 function TEXTJOIN. There are many interesting formula elements in […]

### Excel Magic Trick 1312

August 10, 2017

DESCRIPTION Learn: (00:08) Problem introduction. (00:33) How to use NETWORKDAYS.INTL to count the number of working days between a start and end date. NETWORKDAYS.INTL is in Excel 2010 or later versions. (01:46) Formula to count […]

### Excel Magic Trick 1311

August 7, 2017

DESCRIPTION See an Array Formula that can extract (from a proper data set) multiple invoice items for one lookup value & show total. See the functions: COUNTIFS, ROW, AGGREGATE, ROWS, INDEX, IF and SUMIFS. See […]

### Excel Magic Trick 1309

July 31, 2017

DESCRIPTION Learn how to create an array formula to display a unique list in a single cell. This formula comes from Bill Szysz at YouTube. See the functions: MATCH, ROW, IF and the new Excel […]

### Excel Magic Trick 1301

June 26, 2017

DESCRIPTION Learn how to create an array formula to show a sorted list of modes in a single cell. See the Functions: TEXTJOIN, INDEX, SMALL, MODE.MULT, COUNT, ROW, INDIRECT. Learn about difference (and history) between […]

### Excel Magic Trick 1289

April 20, 2017

DESCRIPTION Why Doesn’t =ROW(1:LEN(A1)) Work? Learn 6 methods to remove “USD” from a column of values such as \$ 1.23 USD, \$ 56.34 USD, etc. that resulted from multiple downloaded CSV Files, including Replace feature, […]

### Excel Magic Trick 1288

April 17, 2017

DESCRIPTION Why Doesn’t =ROW(1:LEN(A1)) Work? Learn the formula =ROW(1:LEN(A1)) does not work. See that the INDIRECT Function comes to the rescue to create a formula that does work: =ROW(INDIRECT(“1:”&LEN(A1))) Work. Alos learn about the robust […]

### Dueling Excel 133

March 15, 2017

DESCRIPTION See Mr Excel and excelisfun use two methods to sum the three biggest values that match three criteria (3 conditions) 1. Advanced Filter and LARGE and MAX value 2. SUM and LARGE functions with […]

### Dueling Excel 130

February 22, 2017

DESCRIPTION Text Formula To Show Calculation (Not Formula) From A Cell See Mr Excel and excelisfun use two methods to show formula calculation in a cell: 1. Use Ampersand (Join Symbol), Text in Double Quotes, […]

### Excel Magic Trick 1271

February 16, 2017

DESCRIPTION Extract Top 20 Values & Dates: Formula, Array Formula or Power Query? Learn how to extract Top 20 values and dates for those values using Formulas when there are no duplicates, Array Formula for […]