Advanced Excel

Course Outline

Module 1: Advanced Excel Functions for Data Analytics

• Text functions for LMS data cleaning (e.g., TEXTJOIN, LEFT, MID, RIGHT)
• Lookup functions (e.g., VLOOKUP, HLOOKUP, INDEX/MATCH) to connect student and course data
• Logical functions (e.g., IF, AND, OR, IFS) for creating data-based decisions
• Array formulas and dynamic arrays (e.g., UNIQUE, FILTER, SORT)
• Hands-on Practice: Cleaning and preparing LMS data for analysis

Module 2: PivotTables and PivotCharts for Dynamic Analysis

• Creating and customizing PivotTables to summarize LMS data
• Using PivotCharts to visualize engagement and completion rates
• Calculated fields and custom grouping in PivotTables
• Slicers and timelines for interactive filtering of LMS metrics
• Hands-on Practice: Analyzing course completion rates and student performance using PivotTables

Module 3: Power Query for Data Transformation

• Introduction to Power Query and its applications in LMS data
• Loading, combining, and transforming large datasets from multiple sources
• Data cleansing techniques in Power Query (handling missing data, splitting columns)
• Advanced transformations for customized LMS data preparation
• Hands-on Practice: Using Power Query to organize LMS data for analysis

Module 4: Power Pivot and Data Modeling

• Setting up data models in Power Pivot for relational data analysis
• Creating and managing relationships between LMS tables (e.g., students, courses)
• Using DAX (Data Analysis Expressions) to perform complex calculations
• Hands-on Practice: Building a data model to analyze LMS metrics across multiple tables

Module 5: Data Visualization with Excel Charts and Dashboards

• Best practices for creating educational data visualizations
• Advanced chart types (e.g., histograms, box plots, scatter plots) for LMS data insights
• Building interactive dashboards with Excel charts, slicers, and buttons
• Using conditional formatting to highlight trends and outliers
• Hands-on Project: Creating a dashboard to monitor student engagement and course progress

Module 6: Statistical Analysis and Forecasting in Excel

• Basic statistical functions (e.g., AVERAGE, MEDIAN, MODE) and their applications
• Data analysis toolpak: Descriptive statistics, correlation, and regression analysis
• Using Excel’s Forecast Sheet to predict student performance and trends
• Hands-on Practice: Conducting a regression analysis to understand factors impacting student grades

Module 7: Automating Reports with Macros and VBA (Optional Advanced Topic)

• Introduction to macros for automating repetitive tasks
• Recording and editing basic macros for LMS data processes
• Basics of VBA (Visual Basic for Applications) for creating custom functions
• Hands-on Project: Automating data refresh and report generation for LMS dashboards