Excel & Business Math 27: Bank Reconciliation Made Easy

About this tutorial: Video duration: 45:22 Download Start Excel File: Download pdf Notes: Entire Class Web Site: In this video learn about checks and Bank Reconciliation. Learn about the concepts of why and how we perform bank reconciliation. Also learn about how to show two…

Excel Magic Trick 1379: Power Query Combine Binary "Expand Button" Update January 2017

About this tutorial: Video duration: 7:7 Download File: Learn about how the Power Query (Get & Transform) Combine Binary “Expand Button” has changed in the Update January 2017. See a Text File Import From Folder. See the Functions that are automatically created and utilized in the updated version. Step through some of the M Code […]

Using Excel to Calculate Grades (14 Examples) for Highline College Professional Development Day 2015

About this tutorial: Video duration: 48:28 Download File: Grading Examples Covered in Video: Time Hyperlinked Table of Contents for Video: 1. (00:04) Introduction to downloading file and the grading topics we cover 2. (01:13) Create basic Grading Template 3. (10:32) Relative and Absolute Cell References for a Percentage Grade Formula 4. (12:36) VLOOKUP to lookup […]

Excel Magic Trick 1481: Multiple Table Lookup: SWITCH or INDIRECT Function? VLOOKUP too

About this tutorial: Video duration: 11:39 Download Files: Start Excel File: Entire page with all Excel Files for All Videos: about how to In this video learn how to how to perform lookup from multiple tables. Learn two methods: 1) VLOOKUP and SWITCH Function or 2) VLOOKUP and INDIRECT Function with Defined Names. 1) (00:01) […]

Excel Magic Trick 1539: Extract Daily Sales Records With Formula. FILTER Function (Dynamic Array).

About this tutorial: Video duration: 5:46 Download Excel Start Files: Entire page with all Excel Files for All Videos: In this video learn how to extract sales records based by day with a formula. Learn about the New Office 365 Dynamic Array Functions: FILTER, SORT and UNIQUE. Compare and contrast this new method to the […]

Excel Magic Trick 1504: Lookup Cell Address: 4 Examples: MATCH, ADDRESS, CELL function?

About this tutorial: Video duration: 6:23 Download Excel File: Entire page with all Excel Files for All Videos: In this video see 4 different formulas to lookup a cell reference. Topics: 1. (00:06) Introduction 2. (00:33) Formula #1: MATCH Function 3. (01:55) Formula #2: ADDRESS Function with MATCH & COLUMN Functions 4. (03:08) Formula #3: […]

Excel Basics 1: Introduction To Excel 1: Formatting, Formulas, Cell References, Page Setup

About this tutorial: Video duration: 1:2:55 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Finished File: Download File: Download Full Written Notes: Class web page link: Buy excelisfun products: Topics in this video: 1. (00:01) Introduction 2. (01:30) Excel…

Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569

About this tutorial: Video duration: 2:40 Download Excel File: In this video learn about how to use the Union Operator in standard Aggregate functions, like SUM and AVERAGE and how to use it inside of parentheses inside Statistical and Finance Functions. Related Videos: Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic […]

Excel Magic Trick 1513: COUNTIFS from Multiple Cells!?!? Array Formula or Logical Formula?

About this tutorial: Video duration: 5:26 Download Excel File: Entire page with all Excel Files for All Videos: In this video see how to count values that are greater than a hurdle when the values in in noncontiguous cells (cells not next to each other). See an Array Formula that uses SUMPRODUCT and CHOOSE and […]

Excel Basics 21: Relationships Rather than VLOOKUP for PivotTable Reports (Excel 2016 Data Model)

About this tutorial: Video duration: 21:35 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start File: Download Excel Finished File: Download Excel VLOOKUP File: Download Full Written Notes:

Dynamic Array & Old Array Formulas to Create Student Classes Report – Excel Magic Trick 1608

About this tutorial: Video duration: 7:48 Download Excel File: Learn how to create a report that shows each student by quarter with a list of classes in a single cell. See many of the new Excel 365 Functions like: SORTBY, UNIQUE, FILTER, TEXTJOIN and the ROWS Function. See how we can combine Spilled Arrays formulas […]

Excel Magic Trick 1069: Displaying Decimal and Fractional Inches in Excel: 1/3" or 2.5" or 2 1/3"

About this tutorial: Video duration: 4:6 Download Excel File: Back Slash in Custom Number Formatting to Insert a character into the Custom Number Formatting. See how to use Custom Number Formatting to display inches in Excel: 1. =1/3 with CNF: ?/?” shows: 1/3″ 2. =1/3 with CNF: ?/?” ‘ ‘ ” shows: 1/3″ 3. =1/3 […]

Excel Magic Trick 1279: New Running Count Formula After Each Zero Value (Cumulative Count)

About this tutorial: Video duration: 2:15 Download File: Learn how to create a New Running Count (Cumulative Count) Formula After Each Zero Value using the IF and SUM functions. Trick from Bill Szysz at YouTube. See that the SUM Function ignores Text Values.

Excel Magic Trick 1509: Conditional Format Array Formula to Highlight Row With 2 Lookup Values

About this tutorial: Video duration: 9:49 Download Files: Entire page with all Excel Files for All Videos: In this video see how to color a row with conditional formatting using an Array Formula & the MATCH Function that will lookup Two Lookup Values in a corresponding Table.

DAX Measure & Data Model PivotTable – List Student Classes in Single Cell –- Excel Magic Trick 1607

About this tutorial: Video duration: 4:22 Download Excel Start File: Download Excel Finished File: Learn how to create a report that shows each student by quarter with a list of classes in a single cell. Learn how to create a Data Model PivotTable and a DAX Measure that uses the DAX Function CONCATENATEX using the […]

Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number

About this tutorial: Video duration: 5:49 Download Excel File: Entire page with all Excel Files for All Videos: In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.

Excel Magic Trick 1435: DAX Query to Export Data from Data Model to Excel Sheet & Much More!

About this tutorial: Video duration: 17:49 Download File: Start File: Finished File: Entire page with all Excel Files for All Videos: See how to query the Data Model with DAX Code to Export Data to an Excel Sheet: 1. (00:14) Introduction, including looking at Data Model that downloadable file contains 2. (02:13) Use Existing Connections […]

Excel Magic Trick 1457 Part 2: Regional Settings & Text or Number Date / Times in SUMIFS Function

About this tutorial: Video duration: 3:21 Download File: Entire page with all Excel Files for All Videos: In the English United States Regional Settings, SUMIFS seems to be recognizing Text and Number Dates as equivalent Criteria. But when we change the Reginal Settings, SUMIFS correctly interprets Text Dates and Number Dates as different. This Tip […]

Power Query – Filter Table Based on Distinct NOT Conditions from List – Excel Magic Trick 1592

About this tutorial: Video duration: 9:18 Download Excel File: Learn how to filter imported data using Distinct NOT Conditions from List. See a single M Code Power Query Formula using the functions. Table.SelectRows, Text.Split, List.Intersect and List.IsEmpty. This amazing solution comes from Bill Szysz.

Power Query Running Count Based on Row Condition – Excel Magic Trick 1588

About this tutorial: Video duration: 7:25 COUNTIFS($A$1:A1,A1) Formula in Power Query? – Excel Magic Trick 1588 Download Excel File: Learn how to create a Power Query Custom Column Formula that has an expandable range, counts based on the row condition and is equivalent to the Excel Worksheet Formula COUNTIFS($A$1:A1,A1). See how to use the Power […]