I made these materials in Summer of ’20 for an undergrad class — you may find them useful with respect to some of the “mechanics” of Excel. Nothing in here about how to build a 2-period moving weighted average forecast, but stuff that might help — like what does =SUMPRODUCT() do?
Start with the List of Excel Expected Competencies (for MIST 2610) to see which workbook(s) go with which video(s). Work your way through the list. There is a “solution” spreadsheet (usually) in addition to the “starter” spreadsheets. Enjoy!
List of Excel Expected Competencies…
1-List_of_Competencies.pdf
The first few (BASIC) Excel Competencies… A – E
[VIDEO] A gentle introduction to Excel
[XLSX] This Hogwarts Potions Class spreadsheet goes with the above “gentle introduction” video: 1_HogwartsPotionsExam.xlsx
[XLSX] First Five Excel Competencies (A to E)
2A_Competencies_A_to_E.xlsx
[VIDEO] Tab 0 • Basic Excel Navigation
[VIDEO] Tab A • Spreadsheet “Engineering” best practices
[VIDEO] Tab B • Relative, Absolute, and Mixed cell references
[VIDEO] Tab C • Lookup & Reference Functions
[VIDEO] Tab D • Logical Functions (=IF(), =AND(), =OR())
[VIDEO] Tab E • “Information” Functions (=ISERROR(), etc.)
[XLSX] First Five Excel Competencies (A to E) completed
2B_Competencies_A_to_E_Completed.xlsx
The next few (Intermediate) Excel Competencies… F – J
[XLSX]Next Five Excel Competencies (F to J)
3A_Competencies_F_to_J.xlsx
[VIDEO] Tab F • Text Functions (=LEFT() ,=MID(), =RIGHT(), etc.)
[VIDEO] Tab G • Date & Time Functions
[VIDEO] Tab H • Loan Payment Functions
[VIDEO] Tab I • Conditional Functions (=SUMIF(), etc.)
[VIDEO] Tab J • Basic Statistical Functions in Excel
[XLSX] Next Five Excel Competencies (F to J) File completed
3B_Competencies_F_to_J_Completed.xlsx
Excel Standards Appendix
[XLSX] Excel spreadsheet for “Excel Standards Appendix”
4A_Appendix.xlsx
[VIDEO] Video for “Excel Standards Appendix“
[XLSX] Excel spreadsheet for “Excel Standards Appendix” completed
4B_Appendix_Completed.xlsx
The next few (Advanced) Excel Competencies… K – M
[XLSX] Excel Competency “K” • Nested Functions…
5A_CompK.xlsx
[VIDEO] Excel Competency “K” • Nested Functions…
[XLSX] Excel Competency “K” • Nested Functions completed âś…
5B_CompK-Complete.xlsx
Excel Competency L – Data Visualization
[LINK] Numberphile: The Greatest Ever Infographic Ever Made! (YouTube)
[VIDEO] Excel Competency “L” • Data Visualization (Part I)
[LINK] Data Visualization Infographic: How to Make Charts and Graphs
[VIDEO] Excel Competency “L” • Data Visualization (Part II)
[LINK] Easy BI: Data Visualization & Chart Types
[LINK] The Simpsons meets data visualization…
[VIDEO] Excel Competency “L” • Data Visualization (Part III)
Excel Competency M – Pivot Tables
[LINK] Parliamentarians in Ottawa (parl.ca)
[PDF] Hacking Pivot Tables
From: Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets, David Hawley & Raina Hawley, pp. 124-139, O’Reilly Media, 2007
[CSV] Excel Competency “M” • Pivot Tables starter file
6A_ListOfMPs.xlsx
[VIDEO] Excel Competency “M” • PivotTables…
[XLSX] Excel Competency “M” • Pivot Tables completed âś…
6B_CompL-PivotTable.xlsx
[EXCEL] Hogwarts Database (using Excel)
7A_ExcelHogwarts.xlsx
[VIDEO] Hogwarts Database (using Excel) • Tables + Pivot Tables (Part I)
[VIDEO] Hogwarts Database (using Excel) • Tables + Pivot Tables (Part II)
[EXCEL] Hogwarts Database (using Excel) completed
7B_ExcelHogwarts-Video.xlsx
Future expansion: competencies N…Q