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

Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016

About this tutorial: Video duration: 3:47 Download File: Learn how to: create Automatic Calendar Table in Data Model, New in Excel 2016. This ability was not in earlier versions of Excel. Incoming search terms:excel magic trick 1299

Excel 2013 Statistical Analysis #4: What Is / Are Statistics? Download Stock Data Create Information

About this tutorial: Video duration: 19:28 Download files: Topics in this video: 1. (00:21) What Is / Are Statistics? Define Statistics 2. (02:42) Descriptive Statistics and Inferential Statistics 3. (05:00) Collect, analyze and present raw data on Yahoo Stock Price: Search the internet, download, create formulas and crate a Chart and present the information in […]

Excel Magic Trick 1336: Power Query: Import Big Data Text Files: Connection Only or Data Model?

About this tutorial: Video duration: 11:35 Download File: See how to use Import 10 Text Files and Append (combine) then into a single Proper Data Set before making a PivotTable Report. Compare and Contrast whether we should use Connection Only or Data Model to store the data. 1. (00:18) Introduction & Look at Text Files […]

Import Power Pivot Data Model into Power BI Desktop (Excel Magic Trick 1365)

About this tutorial: Video duration: 3:36 Download File: See how to import an Excel Power Pivot Data Model with tables, relationships and DAX Measures into Power BI Desktop using the File (Upper Left icon), Import, Excel Workbook option in Power BI Desktop.

Excel Magic Trick 1436 Excel 2016: Combine 2 BIG Tables into 1 for PivotTable Report, No Power Pivot

About this tutorial: Video duration: 7:5 Download File: Start File: Finished File: Entire page with all Excel Files for All Videos: See how to use Excel 2016 to take a 900,000 row table and a 1,000,000 roe table and combine them into one in order to make a PivotTable Report. See how to use the […]

Excel Magic Trick 1331: Import Multiple Excel Files & Sheets into Excel: Power Query Get & Transform

About this tutorial: Video duration: 29:48 Download File: See how to use Power Query (Get & Transform) to import multiple Excel Workbook Files with Multiple Excel Sheets into a single Excel File in two different situations: 1. (00:18) Introduction to Append Multiple Workbooks and Merge Multiple Workbooks 2. (01:58) Append Multiple Workbooks into a single […]

Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR

About this tutorial: Video duration: 9:45 See how to use the DAX Functions XNPV and XIRR to calculate the Net Present Value and Internal Rate of Return of Cash Flows that occur at irregular date intervals. See how to use the DAX Functions XNPV and XIRR to calculate the Net Present Value and Internal Rate […]

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

About this tutorial: Video duration: 5:22 Learn how to deal with Power Query Error: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Two solutions are presented in this video. Download Files: Excel Start: Zipped Folder: Download Excel FINISHED Files:… Incoming search terms:msptda […]

Excel Magic Trick 1220: How To Put Double Quotes In Text Formula: Extra Double Quotes Or VLOOKUP

About this tutorial: Video duration: 5:46 Download File: Learn how to either use IF function and Extra Double Quotes or VLOOKUP to put the text 5’6” or 6’ into each cell: 1. (00:10) Problem Setup 2. (01:00) IF Function Example 3. (01:50) How to use An Extra Double Quote in Text Formula to put a […]

Office 2016 Video #08: Create Research Paper in Word

About this tutorial: Video duration: 38:56 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download notes: Class web page link: Topics in this video: 1. (00:01) Introduction 2. (01:00) Rules for MLA Documentation Style 3. (04:19) Set MLA Style 4. (05:10) Set Margins 5. (05:50) Insert Header 6. (09:05) Insert Footer to […]

Excel Magic Trick 1335: Power Query We Couldn’t Refresh The Connection Data Source Not Found Error

About this tutorial: Video duration: 1:43 Download File: In Power Query see how to use deal with the Refresh Error Message: We couldn’t refresh the connection. Here is the error message we got: [DataSource.NotFound] File or Folder: We couldn’t find the folder.

Office 2016 Video #07: Create a Resume in Word

About this tutorial: Video duration: 25:45 Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download notes: Class web page link: Topics in this video: 1. (00:01) Introduction 2. (00:13) Guidelines for Resume 3. (02:18) Look at Word Resume Templates 4. (03:03) Set System Name in Word Options 5. (03:28) How to use […]

Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records

About this tutorial: Video duration: 18:2 Download File: See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import […]

Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE?

About this tutorial: Video duration: 15:52 Download File: See how to use VLOOKUP when there are multiple Lookup Tables with these methods: 1. (00:15) Problem Set up 2. (01:48) IF Function 3. (05:57) IFS Function (Excel 2016 Insider Program) (Formula from XLarium at YouTube) 4. (08:47) SWITCH Function (Excel 2016 Insider Program) 5. (11:26) Names […]

Excel Magic Trick 1378: Average of Customer Total Sales by Product: Formulas, DAX or Array Formula?

About this tutorial: Video duration: 29:29 Download File: See how to calculate Average of Customer Total Sales by Product using Excel Formulas, Excel Array Formulas and DAX Measures in Excel Power Pivot: 1. (00:15) Introduction 2. (01:08) 2 Step Solution with SUMIFS and AVERAGEIF functions. 3. (07:21) 2 SUMIFS Array Formula 4. (11:26) FREQUENCY Array […]

Excel Magic Trick 1286: Excel 2016 IFS Functions (9 Examples: Compare & Contrast IF & IFS Functions)

About this tutorial: Video duration: 28:21 Download File: Learn all about the new Excel 2016 IFS Function for situations where you have three or more items to put in a cell and VLOOKUP will not work: 1. (00:10) Introduction to what IFS functions does. IFS is great for when you have 3 or more items […]

Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula

About this tutorial: Video duration: 10:35 Download File: See how to create a single cell formula that calculates the total hours worked for the week given a column of start times and a column of end times: 1. (00:10) Problem Setup 2. (01:00) Time Values in Excel are decimals between 0 and 1 that represent […]

Excel Magic Trick 1403: DAX Formulas: Explicit or Implicit Measures? Why Explicit is Better.

About this tutorial: Video duration: 11:22 Download File: Learn about the difference between Explicit and Implicit DAX Measures: 1. (00:10) Introduction to Explicit and Implicit DAX Measures 2. (01:33) Create Implicit Measures and see these six problems: 1) Number Field will use SUM Function, 2) Text Field uses COUNT, 3) You can’t add Number Formatting […]