Back To Home Page

Highline College Excel 2016 Class: Busn 218, Spreadhseet Construction
Comprehensive Excel Class: Effective & Efficient Calculations & Data Analysis



Syllabus (click to view pdf, right-click to download file)
Introductory Video (click to view video)

 

Videos, Written Handouts, Homework and Homework Solutions:

Week 1:

Video 01: Highline Excel 2016 Class 01: Excel Fundamentals: Efficiency, Data, Data Sets, Formatting (1 hour 21 min)
Excel File: Busn218-Week01.xlsm (for Video and Homework)
Handout: Highline Excel 2016 Class 01 Excel Fundamentals Efficiency, Data, Data Sets, Formatting.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to #6

Video 02:  Highline Excel 2016 Class 02: Excel Fundamentals: Comprehensive Formula Lesson (15 Examples) (55 min)
Excel File: Busn218-Video02.xlsm (for Video and Homework)
Handout: Highline Excel 2016 Class 02 Excel Fundamentals Formulas, Formula Types, Formula Elements, Functions & Excel's Golden Rule.pdf (click to view pdf, right-click to download file) Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to #11
Project to complete for points toward a grade: Busn218Project01.xlsm

 

Week 2:

Video 03:  Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model (36 Examples)  (1 hour 43 min)
Excel File: Busn218-Video03-Start.xlsm and Busn218-Video03-Finished.xlsm (for Video and Homework)
Zipped Files for Big Data Power Query / Power Pivot Data Model Example: Video03TextFilesForImport.zip
Handout: Highline Excel 2016 Class 03 Excel Fundamentals Data Analysis Sort, Filter, PivotTables, Power Query, Power Pivot.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to #7
Project to complete for points toward a grade: Busn218Project02.xlsm

 

Week 3:

Video 04:  Highline Excel 2016 Class 04: References: Relative, Absolute, Mixed, Sheet, Workbook, 3-D, Table and more (24 Examples) (1 hour 43 min)
Excel File: Busn218-Video04Start.xlsm and Busn218-Video04Finished.xlsm (for Video and Homework)
Excel File for Workbook Reference: May Net Income.xlsx
Handout: Highline Excel 2016 Class 04 Cell References Defined Names Table Formula Nomenclature.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to #13
Project to complete for points toward a grade: Busn218Project03.xlsm    Zipped Folder needed for Project 3: TextFiles.zip

 

Week 4:

Video 05:  Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples  (52 min)
Excel File: Busn218-Video05.xlsm (for Video and Homework)
Handout: Highline Excel 2016 Class 05 Introduction To Array Formulas.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to #4
No Project This Week.

Video 06:  Highline Excel 2016 Class 06: Conditional Calculations with Excel Formulas: Comprehensive Lessons   (1 hour 23 min)
Excel File: Busn218-Video06Start.xlsx   and   Busn218-Video06StartFinished.xlsx  (for Video and Homework)
Handout: Highline Excel 2016 Class 06 Conditional Calculations with Excel Formulas AND & OR Criteria.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 9
No Project This Week.

Video #7 Not Required For Winter 2017 Class:
Video 07:
  Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS & IFS Functions for Conditional Calculations    (11 min)
Excel File: Busn218-Video07.xlsx (for Video)
Handout: Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS & IFS Functions for Conditional Calculations.pdf (click to view pdf, right-click to download file)
Homework to practice: No Homework
No Project This Week.

 

Week 5:

Video 08:  Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data    (30 min)
Excel File: Busn218-Video08.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 08 Text Functions.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 2

Video 09:  Highline Excel 2016 Class 09: Date Formulas and Date Functions, including Fiscal Quarter & Year    (22 min)
Excel File: Busn218-Video09.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 09 Date Functions.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 6

Video 10:  Highline Excel 2016 Class 10: Excel Data Validation (List, Custom & More), Comprehensive Lesson   (24 min)
Excel File: Busn218-Video10.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 10 Data Validation.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 2

Video 11:  Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples    (1 hour 18 mins)
Excel File: Busn218-Video11.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 11 Lookup Functions.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 10
Project to complete for points toward a grade: Project04.xlsm

 

Week 6:

Video 12: Highline Excel 2016 Class 12: Two Lookup Values in VLOOKUP? Helper Column or Array Formula.   (8 mins)
Excel File: Busn218-Video12-14Start.xlsx (for Video and Homework #12, 13 and 14)
Handout: Highline Excel 2016 Class 12 Two Lookup Values in VLOOKUP.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1

Video 13:  Highline Excel 2016 Class 13: One Lookup Value to Return Multiple Items: INDEX & AGGREGATE   (9 mins)
Excel File: Busn218-Video12-14Start.xlsx (for Video and Homework #12, 13 and 14)
Handout: Highline Excel 2016 Class 13  One Lookup Value Return Multiple Items.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #2

Video 14:  Highline Excel 2016 Class 14: VLOOKUP as Relationship in Power Pivot Data Model & Vice Versa   (8 mins)
Excel File: Busn218-Video12-14Start.xlsx and Busn218-Video12-14Finished.xlsx (for Video and Homework #12, 13 and 14)
Handout: Highline Excel 2016 Class 14 VLOOKUP as Relationship and Vice Versa.pdf (click to view pdf, right-click to download file)
Homework to practice: None.

Video 15:  Highline Excel 2016 Class 15: Excel Charts to Visualize Data: Comprehensive Lesson 11 Chart Examples   (52 mins)
Excel File: Busn218-Video15.xlsm (for Video and Homework)
Handout: Highline Excel 2016 Class 15 Excel Charts.pdf (click to view pdf, right-click to download file)
PowerPoint: Busn218-Charts.pptx
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 5

 

Week 7:

Video 16:  Highline Excel 2016 Class 16: Conditional Formatting to Visualize Data: Built-in & Logical Formulas   (44 mins)
Excel File: Busn218-Video16.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 16 Conditional Formatting to Visualize Data - Copy.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 13
Project to complete for points toward a grade: Project05.xlsm

BEFORE watching Video 17, watch this video for explanation about Microsoft's Update of the Combine Binary "Expand Button":

Excel Magic Trick 1379: Power Query Combine Binary "Expand Button" January 2017 Update  (7 mins)

Video 17:  Highline Excel 2016 Class 17: How to Build Excel Dashboard PivotTable, Chart, Conditional Formatting from Data Model, See First DAX Formula    (36:15 mins)
Excel File: Busn218-Video17Start.xlsx and Busn218-Video17Finished.xlsx (for Video)
Zipped Files for Power Query / Power Pivot Data Model Example:Video17ImportTextFiles-05.zip
Handout: Highline Excel 2016 Class 17 Dashboards.pdf (click to view pdf, right-click to download file)
Homework to practice: No Homework
Project to complete for points toward a grade: Busn218Project06.xlsm

 

Week 8:

Video 18:  Highline Excel 2016 Class 18: Clean & Transform Data: Replace, Flash Fill, Text To Columns, Formulas    (24 mins)
Excel File: Busn218-Video18.xlsx (for Video and Homework)
Handout: Highline Excel 2016 Class 18 Clean Transform Data Replace Flash Fill TTC Formulas.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 4

Video 19:   Highline Excel 2016 Class 19: Transform Data Sets using Advanced Filter (8 Examples)   (34 mins)
Excel File: Busn218-Video19Start.xlsx  and Busn218-Video19Finished.xlsx  (for Video and Homework)
Handout: Highline Excel 2016 Class 19 Transform Data Sets Advanced Filter.pdf (click to view pdf, right-click to download file)
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 3
Project to complete for points toward a grade: Project 07.xlsm

Video 20:   Highline Excel 2016 Class 20: Power Query (Get & Transform ) Import Multiple Excel Files, 1 Sheet Each & PivotTable Show Values As   (24 mins)
Excel File: Busn218-Video20Start.xlsx  and Busn218-Video20Finished.xlsx  (for Video)
Zipped Files for Power Query / Power Pivot Data Model Example: Video20-3ExcelFilesImport.zip
Handout: Highline Excel 2016 Class 20 Clean Transform & Import Excel Workbook Data with Power Query.pdf (click to view pdf, right-click to download file)
Homework: None

Video 21:  Highline Excel 2016 Class 21: Power Query Import Multiple Excel Workbooks with Multiple Sheets     (9 mins)
Excel File: Busn218-Video21Start.xlsx and Busn218-Video21Finished.xlsx  (for Video)
Zipped Files for Power Query: Video21-3ExcelWorkbooks.zip
Handout: Highline Excel 2016 Class 21 PQ to Import Multiple Excel Workbooks with Multiple Sheets.pdf (click to view pdf, right-click to download file)
Homework: None
Project to complete for points toward a grade: Project 08.xlsm & Zipped Folder: 3ExcelFilesForProject08.zip 

 

Week 9:


Video 22:  Highline Excel 2016 Class 22: How To Build Data Model & DAX Formulas in Power Pivot     (1 hour 5 mins)
Excel File: Busn218-Video22Start.xlsm and Busn218-Video22Finished.xlsm  (for Video)
Zipped Files for Power Query: Video22-ImportExcelFiles.zip
Handout: Highline Excel 2016 Class 22 Data Modling & DAX Formulas.pdf (click to view pdf, right-click to download file)
Homework: None
 

Extra Video About Making Automatic Calendar Table
Video 22.5:
   Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016    (3 mins)
Excel File: EMT1299Start.xlsx and EMT1299Finished.xlsx  (for Video)
Handout: None
Homework: None
 

Video 23:  Highline Excel 2016 Class 23: Intro to Power BI Desktop to Create Data Model & Visualization      (24 mins)
Excel File: Busn218-Video23.xlsm (for Video)
Finished Power BI Desktop File: Video22-Busn218-Finished.pbix
Zipped Files to use in import into Power BI Desktop: Video23-Import5ExcelFiles.zip
Handout: Highline Excel 2016 Class 23 Power BI Desktop.pdf (click to view pdf, right-click to download file)
Homework: None

 

Week 10:

Video 24:  Highline Excel 2016 Class 24: Financial Functions: PMT, RATE, NPER and FV 12 Examples     (41 mins)
Excel File: Busn218-Video24-OldFileName-Busn214-Week10.xlsm
Handout: Highline Excel 2016 Class 24 Finance Functions in Excel.pdf
Homework to practice: Sheets at end of downloaded Excel Workbook: HW #1 to 4
  

Video 25:  Highline Excel 2016 Class 25: Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more…       (9 mins)
Excel File: Busn218-Video25-OldFileName-Busn214-Week03.xlsx
Handout: None.
Homework: None
   

Video 26: Highline Excel 2016 Class 26: Macro Recorder Basics & Copying VBA Code From Internet       (40 mins)
Excel File: Busn218-Video26Start.xlsm and Busn218-Video26Finished.xlsm (Video)
Handout: Highline Excel 2016 Class 26 Recorded Macros.pdf
Homework: None
    

Class over! That was fun!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Link to Old Excel 2013 Class