| 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:
(Only watch video #3 until the 01:03:20 minute mark, then watch the Excel Magic Trick 1468 listed next) This is because Microsoft updated how the Power Query feature works and video #3 was made before the update)
Video 03:
Highline Excel 2016
Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model
(36 Examples) (1 hour 3 min)
(Only watch video #3 until the 01:03:20
minute mark, then watch the Excel Magic Trick 1468 listed next)
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
Watch this video
after finishing video #3 at the 01:03:20 minute mark. This video and files show the same examples as our #3 video, but I use the
newer User Interface of Excel 2016 Data Ribbon Tab:
Video:
Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic
Trick 1468) ( 50 min)
Files that go with Video:
EMT1468.xlsm
EMT1468Finished.xlsm
EMT1468TextFiles.zip
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 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
(This project not due until next week - see schedule)
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!