Contents

• Lecture 1 [Week1] Introduction to Excel Analysis

  • 0. Intro
  • 1-1. Introduction to the Excel Interface
  • 1-2. Basic Navigation and Shortcuts
  • 1-3. Cell Formatting(Font, Borders, Alignment, Colors)
  • 1-4. Data Entry, Auto-Fill, and Basic Calculations
  • 1-5. Final Wrap-up Practice
  • 2-1. Overview of Basic Excel Formulas(SUM, AVERAGE, COUNTA)
  • 2-2. Logical Functions(IF, AND, OR, NOT, COUNTIF)
  • 2-3. Lookup Functions (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP)
  • 2-4. Text Functions(CONCATENATE, LEFT, RIGHT, MID, TRIM, UPPER, LOWER)
  • 2-5. Date and Time Functions (TODAY, NOW, DATEDIF, TEXT)
  • 2-6. Error-handling Functions (IFERROR, ISBLANK, ISNUMBER)
  • 2-7. Final Wrap-up Practice
  • 3-1. Working with Tables (Creating, Formatting, Managing)
  • 3-2. Conditional Formatting (Highlighting Pattern and Outliers)
  • 3-3. Sorting and Filtering Data
  • 3-4. Removing Duplicates and Handling Missing Data
  • 3-5. Final Wrap-up Practice
  • 4-1. Creating and Customizing Charts (Bar, Pie, Line, Scatter)
  • 4-2. Advanced Charts (Histograms, Waterfall, Sparklines, Heat Maps)
  • 4-3. Best Practices for Visualizing Data for Business Analysis
  • 4-4. Final Wrap-up Practice
  • 5-1. Creating and Customizing Pivot Tables
  • 5-2. Grouping, Sorting, and Filtering Pivot Data
  • 5-3. Calculated Fields and Items
  • 5-4. Advanced Pivot Table Techniques(Slicers, Timelines)
  • 5-5. Pivot Charts for Dynamic Visualization
  • 5-6. Final Wrap-up Practice
  • 6-1. Recording and Running Macros
  • 6-2. Editing VBA Code for Custom Automation
  • 6-3. Debugging and Troubleshooting Macros
  • 6-4. Writing Simple VBA Scripts
  • 7-1. Data Cleaning and Preparation
  • 7-2. Applying Formulas and Analysis Techniques with Pivot Tables
  • 7-3. Visualizing Insights with Charts and Pivot Tables

• Lecture 2 [Week2] Introduction to SQL

  • 1-1. SQL intro
  • 1-2. SQL functions(DDL, DML, DCL, DQL, Datatypes)
  • 1-3. Download SQL
  • 1-4. Setting SQL Environment
  • 2-1. SELECT&FROM
  • 2-2. JOIN
  • 2-3. Chapter 2 quiz
  • 3-1. ORDERBY, GROUPBY
  • 3-2. WHERE&HAVING
  • 4-1. AND, OR, BETWEEN
  • 4-2. LIKE, IN, IS NULL
  • 4-3. NULL Value
  • 5-1. Data Aggregation(SUM, AVG, MIN, MAX, COUNT)
  • 5-2. Chapter 5 quiz
  • 6-1. final project part 1
  • 6-2. final project part 2