Description

Total Contact period: 18 hours

Module 1: Data Preparation & Cleaning and Week 1: 4.5 hours of Lecture time

Lesson 1.1: Data Import & Connection

• Importing from CSV, TXT, and database files
• Power Query basics for data import
• Refreshable data connections
• Practice: Import Nigerian stock market data

Lesson 1.2: Data Cleaning Fundamentals

• Removing duplicates and blank rows
• Text-to-columns for data separation
• TRIM and CLEAN functions
• Practice: Clean messy customer dataset

Lesson 1.3: Data Type Standardization

• Converting text to numbers
• Date format standardization
• Handling inconsistent data formats
• Practice: Standardize survey response data

Lesson 1.4: Outlier Detection & Treatment

• Statistical methods for outlier identification
• Quartile analysis and box plots
• Decision rules for outlier treatment
• Practice: Sales data outlier analysis

Lesson 1.5: Data Validation & Quality Checks

• Consistency checks across datasets
• Completeness validation
• Accuracy verification methods
• Practice: Data quality scorecard

Module 2: Advanced Analysis Functions and Week 2: 4.5 hours of Lecture time

Lesson 2.1: Advanced Lookup Functions

• INDEX and MATCH combinations
• Multiple criteria lookups
• Approximate match scenarios
• Practice: Product performance lookup system

Lesson 2.2: Array Formulas & Functions

• Understanding array formulas
• SUMPRODUCT for complex calculations
• Array constants and operations
• Practice: Multi-dimensional sales analysis

Lesson 2.3: Statistical Functions

• STDEV, VAR for variability analysis
• CORREL for correlation analysis
• PERCENTILE and QUARTILE functions
• Practice: Employee performance statistical analysis

Lesson 2.4: Advanced Conditional Functions

• COUNTIFS and SUMIFS with multiple criteria
• AVERAGEIFS for conditional averaging
• Complex logical combinations
• Practice: Customer segmentation analysis

Lesson 2.5: Text Analysis Functions

• LEN, FIND, SEARCH for text analysis
• SUBSTITUTE and REPLACE functions
• Regular expression alternatives
• Practice: Social media sentiment keyword analysis

Module 3: Pivot Tables & Advanced Analysis and Week 3: 4.5 hours of Lecture time

Lesson 3.1: Pivot Table Fundamentals

• Creating and configuring pivot tables
• Row, column, and value field setup
• Basic filtering and sorting
• Practice: Sales performance pivot analysis

Lesson 3.2: Advanced Pivot Table Features

• Calculated fields and items
• Grouping by dates and numbers
• Show values as percentages and differences
• Practice: Year-over-year growth analysis

Lesson 3.3: Pivot Charts & Visualization

• Creating pivot charts from pivot tables
• Dynamic chart updates
• Chart formatting and customization
• Practice: Interactive sales dashboard

Lesson 3.4: Slicers & Timeline Controls

• Adding slicers for easy filtering
• Timeline controls for date filtering
• Connecting slicers to multiple pivot tables
• Practice: Multi-chart dashboard with controls

Lesson 3.5: Power Pivot Introduction

• When to use Power Pivot
• Data model creation basics
• Relationships between tables
• Practice: Multi-table data model setup

Module 4: Data Visualization & Reporting and Week 4: 4.5 hours of Lecture time

Lesson 4.1: Advanced Charting Techniques

• Combination charts for multiple metrics
• Dynamic chart ranges
• Custom chart formatting
• Practice: Executive dashboard charts

Lesson 4.2: Conditional Formatting for Analysis

• Data bars and color scales
• Icon sets for performance indicators
• Custom conditional formatting rules
• Practice: Performance heat map creation

Lesson 4.3: Dynamic Dashboards

• Dashboard design principles
• Interactive elements and controls
• Mobile-friendly dashboard layouts
• Practice: Sales performance dashboard

Lesson 4.4: Scenario Analysis & Modeling

• Data tables for sensitivity analysis
• Scenario Manager for what-if analysis
• Goal Seek for target-based planning
• Practice: Business case scenario modeling

Lesson 4.5: Reporting Automation

• Automated report generation
• Template-based reporting
• Data refresh and update procedures
• Practice: Monthly automated report system

Course 4 Final Project (2 hours) and Week 4: Students to complete and submit

• Complete data analysis project using Nigerian economic data
• From raw data import to final executive presentation
• Includes cleaning, analysis, visualization, and insights