Excel TV

Academy course

Data Modelling in Excel

71 lessons across 7 modules · Szilvia Juhasz

Data Modelling in Excel

What’s in this course

Download The Files

  • Download the Files

Decision Modelling with Excel

  • Why Art and Science with Excel
  • Historical Perspectives: Art-Science
  • Art & Science of Excel Modelling in 9 Steps
  • Step 1: Defining the Problem
  • Step 2: Analyzing the Problem
  • Step 3: Knowing Your Stakeholder
  • Step 4: Understanding Your Role
  • Step 5: Planning Your Model
  • Step 6: Building Your Model
  • Step 7: Testing Your Model
  • Step 8: Delivering Your Model
  • Step 9: Maintaining Your Model
  • The 9 Steps Recap

Thinking Like Excel

  • Introduction to Thinking Like Excel
  • Why Think Like Excel
  • Cell Referencing is Everything
  • Relative Cell Referencing DEMO
  • Mixed Cell Referencing DEMO
  • Absolute Cell Referencing DEMO
  • Multiplication Matrix Example
  • R1C1 Mode DEMO
  • Notation Styles
  • Cell Notation Styles Compared
  • Excel and Chess

Advanced Excel Formula Mastery

  • Introduction to Advanced Excel Formula Mastery
  • Looking Things Up
  • VLookup DEMO
  • Match DEMO
  • VLookup with Match DEMO
  • VLookup with Error Trapping DEMO
  • VLookup Approximate Match DEMO
  • VLookup to the Left DEMO
  • Index DEMO
  • Offset
  • Offset DEMO
  • Conditional Calculations
  • SumIfs DEMO
  • Averageifs DEMO
  • Array Formulas DEMO
  • Countifs for Uniques DEMO

Anatomy of an Excel Model

  • Anatomy of an Excel Model Introduction
  • Create Names to Store Global Assumptions DEMO
  • Named Ranges Static vs Dynamic DEMO
  • Replace a Range Address with a Dynamic Range Name DEMO
  • Create Names to Retrieve Intersections
  • Create a Simple Custom Function Without VBA DEMO
  • Variables vs Scenarios Goal Seek Demo
  • Variables vs Scenarios - Scenario Manager DEMO
  • Workshop Introduction Rolling Forecast Planner
  • Workshop Rolling Forecast Planner DEMO
  • Workshop Retail Order Processing Model LAB INTRO

Upgrading to the Modern Excel Table

  • Introduction to Upgrading to the Modern Excel Table
  • What is a Modern Excel Table
  • 3 Ways to Create an Excel Table
  • Excel Tables Essential Techniques DEMO
  • Excel Tables Slicers and Graphs DEMO
  • Dynamic Charts with Tables DEMO
  • Excel Tables Advanced Formulas
  • Excel Tables and Data Models
  • Intro to Data Models DEMO
  • Modern Excel Tables RECAP

Next Level Excel + Database Solutions

  • Introduction to Next Level Excel Database Systems
  • Excel vs Database Debate
  • Demos Overview
  • DEMO 1 - SQL
  • DEMO 1 - Access Database Backend
  • DEMO 1 - The Excel Front End
  • DEMO 1 - Specify the Connection on the Excel Front End
  • DEMO 2 - Introduction
  • DEMO 2 - Process Automation

Curriculum

Download The Files 1 lesson
  1. 1. Download the Files
Decision Modelling with Excel 13 lessons
  1. 1. Why Art and Science with Excel
  2. 2. Historical Perspectives: Art-Science
  3. 3. Art & Science of Excel Modelling in 9 Steps
  4. 4. Step 1: Defining the Problem
  5. 5. Step 2: Analyzing the Problem
  6. 6. Step 3: Knowing Your Stakeholder
  7. 7. Step 4: Understanding Your Role
  8. 8. Step 5: Planning Your Model
  9. 9. Step 6: Building Your Model
  10. 10. Step 7: Testing Your Model
  11. 11. Step 8: Delivering Your Model
  12. 12. Step 9: Maintaining Your Model
  13. 13. The 9 Steps Recap
Thinking Like Excel 11 lessons
  1. 1. Introduction to Thinking Like Excel
  2. 2. Why Think Like Excel
  3. 3. Cell Referencing is Everything
  4. 4. Relative Cell Referencing DEMO
  5. 5. Mixed Cell Referencing DEMO
  6. 6. Absolute Cell Referencing DEMO
  7. 7. Multiplication Matrix Example
  8. 8. R1C1 Mode DEMO
  9. 9. Notation Styles
  10. 10. Cell Notation Styles Compared
  11. 11. Excel and Chess
Advanced Excel Formula Mastery 16 lessons
  1. 1. Introduction to Advanced Excel Formula Mastery
  2. 2. Looking Things Up
  3. 3. VLookup DEMO
  4. 4. Match DEMO
  5. 5. VLookup with Match DEMO
  6. 6. VLookup with Error Trapping DEMO
  7. 7. VLookup Approximate Match DEMO
  8. 8. VLookup to the Left DEMO
  9. 9. Index DEMO
  10. 10. Offset
  11. 11. Offset DEMO
  12. 12. Conditional Calculations
  13. 13. SumIfs DEMO
  14. 14. Averageifs DEMO
  15. 15. Array Formulas DEMO
  16. 16. Countifs for Uniques DEMO
Anatomy of an Excel Model 11 lessons
  1. 1. Anatomy of an Excel Model Introduction
  2. 2. Create Names to Store Global Assumptions DEMO
  3. 3. Named Ranges Static vs Dynamic DEMO
  4. 4. Replace a Range Address with a Dynamic Range Name DEMO
  5. 5. Create Names to Retrieve Intersections
  6. 6. Create a Simple Custom Function Without VBA DEMO
  7. 7. Variables vs Scenarios Goal Seek Demo
  8. 8. Variables vs Scenarios - Scenario Manager DEMO
  9. 9. Workshop Introduction Rolling Forecast Planner
  10. 10. Workshop Rolling Forecast Planner DEMO
  11. 11. Workshop Retail Order Processing Model LAB INTRO
Upgrading to the Modern Excel Table 10 lessons
  1. 1. Introduction to Upgrading to the Modern Excel Table
  2. 2. What is a Modern Excel Table
  3. 3. 3 Ways to Create an Excel Table
  4. 4. Excel Tables Essential Techniques DEMO
  5. 5. Excel Tables Slicers and Graphs DEMO
  6. 6. Dynamic Charts with Tables DEMO
  7. 7. Excel Tables Advanced Formulas
  8. 8. Excel Tables and Data Models
  9. 9. Intro to Data Models DEMO
  10. 10. Modern Excel Tables RECAP
Next Level Excel + Database Solutions 9 lessons
  1. 1. Introduction to Next Level Excel Database Systems
  2. 2. Excel vs Database Debate
  3. 3. Demos Overview
  4. 4. DEMO 1 - SQL
  5. 5. DEMO 1 - Access Database Backend
  6. 6. DEMO 1 - The Excel Front End
  7. 7. DEMO 1 - Specify the Connection on the Excel Front End
  8. 8. DEMO 2 - Introduction
  9. 9. DEMO 2 - Process Automation
Member access: Lesson videos and downloadable resources require academy login. Existing members get the same access they had on academy.excel.tv.