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 […]

Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567

About this tutorial: Video duration: 2:40 Download Excel File: In this video learn how to do a Two Way Lookup from a Rectangular Lookup Table using the VLOOKUP and MATCH functions. In our example we need to lookup a product name in the row and a month name in the column and return the intersecting […]

Basic Excel Business Analytics #28: Power Query: Import Multiple Text Files & Build Grade Dashboard

About this tutorial: Video duration: 21:54 Download files: Learn about: 1) (00:04) Notes about downloading files 2) (00:36) Look at Finished Grade Dashboard 3) (01:10) Look at Text files that we need to import from a folder 4) (02:07) Power Query to Import Text Files (extension “.txt”) from a Folder 5) (06:14) What is a […]

Summarize Survey Results with PivotTable in One Minute – Excel Magic Trick 1577

About this tutorial: Video duration: 1:1 Download Excel File: In this video learn how to create a Survey Result Report in less than one minute with a PivotTable.

Excel Magic Trick 1332: Power BI Desktop: Import Multiple Excel Files & Build Dashboard

About this tutorial: Video duration: 15:23 Download File: See how to use Power BI Desktop to Import Multiple Excel Files & Build Dashboard 1. (00:01) Introduction 2. (01:40) Download Files 3. (01:59) Download Power BI Desktop. Why would we need to do this if we have Excel? 4. (03:09) Import and transform many Excel Files […]

Excel Intersection Operator, An Excel Party Trick? – Excel Magic Trick 1570

About this tutorial: Video duration: 1:10 Download Excel File: In this video learn how to use the Intersection Operator, which is a space. Learn about the #NULL Error. Related Videos: Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569 Excel Intersection Operator, An Excel Party Trick – Excel Magic Trick […]

DAX Formulas for 1/2 Year Running Totals in Power BI or Power Pivot (Excel Magic Trick 1561)

About this tutorial: Video duration: 11:6 Download Excel File: Download Start Power BI Desktop: Download Finished Power BI Desktop File: Entire page with all Excel Files for All Videos: Learn how to create a DAX Calculated Colum and DAX Measure for Half Year Running Totals in Power BI Desktop or Excel Power Pivot. Topics: 1. […]

Basic Excel Business Analytics #34: Power Query: Import & Merge Multiple Sources Access, Text, Excel

About this tutorial: Video duration: 5:1 Download files: Learn about how to use Power Query to Import & Merge Multiple File Types (Access, Text, and Excel) into one Proper data Set using the Append feature. Power Query Tab, Get External Data. Excel 2016: Data Tab, Get and Transform group. Download Excel File Not: After clicking […]

Excel Basics 6: Customize Quick Access Toolbar (QAT) and Show New Ribbon Tabs

About this tutorial: Video duration: 10:18 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start & Finished File: Download Full Written Notes: Class web page link: Topics in this video: 1. (00:04) Introduction 2. (00:40) Customize QAT 3. (07:36) Show New Ribbon Tabs 4. (09:33) Summary – Conclusion

Excel Basics 8: Default Data Alignment In Excel & How it Can Help

About this tutorial: Video duration: 9:25 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start & Finished File: Download Full Written Notes: Class web page link: Learn about Default alignment in Excel and how it an help you with Data Entry, formulas and tracking down errors.

Excel Basics 4: PivotTables & SUMIFS Function to Create Summary Reports (Intro Excel #4)

About this tutorial: Video duration: 33:26 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start & Finished File: Download Full Written Notes: Class web page link: Topics in this video: 1. (00:01) Introduction 2. (01:22) What Excel can do: 1) Calculations, Data Analysis 3. (01:58 and 03:22) What is Data…

Excel Magic Trick 1315: Create A Unique Sorted List with Power Query

About this tutorial: Video duration: 4:16 Download File: See how to create a unique sorted list with Power Query.

Keyboard Shortcut to Open Smart Tags in Excel, Word & PowerPoint (Excel Magic Trick 1557)

About this tutorial: Video duration: 1:10 Download Excel File: Entire page with all Excel Files for All Videos: In this video learn how to open a Smart Tag with a Keyboard. Open the Auto Fill Options or Paste Options feature with a Keyboard.

Highline Excel 2016 Class 16: Conditional Formatting to Visualize Data: Built-in & Logical Formulas

About this tutorial: Video duration: 44:21 Download Files: In this video learn about Conditional Formatting Topics : 1. (00:16) Introduction 2. (00:43) Built-in Feature: Cell Contains 3. (03:28) Logical Formula: Highlight Row (Record) where cell contains a value. 4. (10:00) Built-in Feature: Below Average 5. (10:55) Logical Formula: Highlight Row (Record) where sales are below […]

Excel Magic Trick 1209: BINOM.DIST.RANGE Function for Calculating Binomial Probabilities

About this tutorial: Video duration: 6:12 Download File: See how to use BINOM.DIST.RANGE Function for Calculating Binomial Probabilities. BINOM.DIST.RANGE Function can calculate individual probabilities or probabilities between an upper and lower x value: 1. (00:10) Introduction to Binomial Experiment and BINOM.DIST.RANGE Function 2. (01:58) Reminder of BINO.DIST function and how it works and how it […]

Turn on Power View in Excel 2013 / 2016 for Windows (Excel Magic Trick 1362)

About this tutorial: Video duration: 2:24 Download File: See how to: 1. File, Options, Add-ins, Manage: select “Com Add-ins”, Click Go, Check Power View, OK 2. Right-click any Ribbon Tab, “Customize the Ribbon”, Add Group, Rename Group 3. With Group Selected, from Choose commands from dropdown select “Commands not in the Ribbon” 4. Select “Insert […]

Excel Magic Trick 1207: PERCENTILE.EXC, PERCENTILE.INC, QUARTILE.EXC, QUARTILE.INC Functions

About this tutorial: Video duration: 12:52 Download File: See how to use the different percentile and quartile functions in Excel:: 1. (00:11) What is a percentile? 2. (00:47) Difference between PERCENTILE.EXC and PERCENTILE.INC functions AND the difference between the QUARTILE.EXC and QUARTILE.INC functions. 3. (2:30) Algorithm for calculating position of item in list for .EXC […]

Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552

About this tutorial: Video duration: 22:45 Download Excel Finished Files: Entire page with all Excel Files for All Videos: In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure. Learn about the Table.Sort […]