Contents

• Lecture 1 [Week 1] Introduction to Excel Analytics

  • 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 [Week 2] 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

• Lecture 3 [Week 3] Introduction to Python

  • 0_Introduction to Course
  • 1-1. Why Python
  • 1-2. Setting Up Python with Visual Studio Code
  • 1-3-1. Printing and Variable (Theory)
  • 1-3-2. Printing and Variables (practice)
  • 2-1-1. Numbers (Theory)
  • 2-1-2. Numbers (practice)
  • 2-2-1. Booleans (theory)
  • 2-2-2. Booleans (practice)
  • 2-3-1. Strings (theory)
  • 2-3-2. Strings (practice)
  • 3-1-1. Lists
  • 3-1-2. Lists (practice)
  • 3-2-1. Dictionaries (theory)
  • 3-2-2. Dictionaries (practice)
  • 3-3-1. Tuples and Sets
  • 3-3-2. Tuples and Sets (practice)
  • 4-1-1. if statements(theory)
  • 4-1-2. if statements(practice)
  • 4-2-1. for loops(theory)
  • 4-2-2. for loop (practice)
  • 4-3-1. while loops(theory)
  • 4-3-2. while loops(practice)
  • 5-1-1. Reading from files
  • 5-1-2. Reading from files
  • 5-2-1. Writing to files
  • 5-2-2. Writing to files (practice)
  • 6-1-1. Functions(theory)
  • 6-1-2. Functions(practice)
  • 6-2-1. Classes(theory)
  • 6-2-2. Classes(practice)
  • 7-1-1. numpy(theory)
  • 7-1-2. numpy(practice)
  • 7-2-1. matplotlib(theory)
  • 7-2-2. matplotlib(practice)
  • 7-3-1. pandas(theory)
  • 7-3-2. pandas(practice)
  • 8-1. final deep dive(introducing project)
  • 8-2. final deep dive(dataset)
  • 8-3. final deep dive(project part 1)
  • 8-4. final deep dive(project part 2)
  • 8-5. final deep dive(project part 3)
  • 8-6. final deep dive(project part 4)