### Excel Magic Trick 1118

April 18, 2015

DESCRIPTION See how to Extract Word That Occurs Most Frequently: (00:07) MODE calculation for text. (00:57) COUNTIFS function for basic mode calculation (01:38) INDEX, MODE and MATCH function (with function argument array operation) to extract […]

### Excel Magic Trick 1117

April 17, 2015

DESCRIPTION See how to calculate an average for 12 different samples when numbers are in a single column: (00:25) PivotTable (average in one condition/criteria) (02:22) AVERAGEIFS Function (average in one condition/criteria) (05:37) AVERAGE, ROWS and […]

### Excel Magic Trick 1116

April 16, 2015

DESCRIPTION (00:37) INDEX and MATCH when we have a two fields (columns): Sample Number and Number To Be Averaged. Use MATCH to lookup first occurrence of each sample number with Exact Match, which only finds […]

### Excel 2013 PowerPivot Basics 13

April 13, 2015

DESCRIPTION See how to improve spreadsheet performance (calculation speed) by replace a workbook with array formulas to do 3 way lookup for big data by using PowerQuery and PowerPivot: 1. Using PowerQuery import three data […]

### Excel 2013 PowerPivot Basics 12

April 12, 2015

DESCRIPTION See how to create Student GPA Grade by Class for each High School, with each report on a new sheet, automatically using a PivotTable and Show Report Filter Pages. This feature is NOT available […]

### Excel 2013 PowerPivot Basics 11

April 11, 2015

DESCRIPTION See how to: (00:47) Group By Date: Excel PivotTable: 1) Drag Date to Rows, Right-click Group, Years and Months (02:04) Group By Date: PowerPivot PivotTable: (02:07) Add Revenue Table to PowerPivot Data Model (02:46) […]

### Excel 2013 PowerPivot Basics 10

April 10, 2015

DESCRIPTION Topics in this video: (00:34) Essence of PivotTable (01:08) Overview of Filter Context and CALCULATE (02:30) Look at how Filter Context works on a Calculated Field (03:53) COUNTROWS function to see “Filtered Table” Example […]

### Excel 2013 PowerPivot Basics 09

April 9, 2015

DESCRIPTION See how to create 9 different DAX functions or Calculated Fields (00:08) Overview of Reports we need to create (01:05) Calculated Fields Overview (01:58) Calculated Column For Net Revenue using ROUND and RELATED function […]

### Excel 2013 PowerPivot Basics 08

April 8, 2015

DESCRIPTION See how to: (00:08) Overview (01:45) Update Calendar Table in Excel file when we anticipate new transactional data with new dates. (03:35) Update Existing Connections to Source Data Files when File Name or File […]

### Excel 2013 PowerPivot Basics 07

April 7, 2015

DESCRIPTION See how to: (00:17) Calendar Table in Diagram View with no Calculated Fields (00:58) Format Date Column (01:08) Create Calculated Column #01: Month Number using MONTH function. (01:53) Create Calculated Column #02: Month Name […]