Basic Excel Curriculum (20 Hours)


Module 1: Introduction to Excel (2 hours)

  • Topics Covered:
    • Excel Interface Overview
    • Basic Navigation and Operations
    • Understanding Workbooks and Worksheets
    • Cell Basics: Selecting, Editing, and Formatting

Module 2: Data Entry and Basic Functions (2 hours)

  • Topics Covered:
    • Entering Data: Text, Numbers, and Dates
    • Basic Formulas and Functions: SUM, AVERAGE, MIN, MAX
    • AutoFill and Flash Fill
    • Basic Error Checking

Module 3: Data Formatting (2 hours)

  • Topics Covered:
    • Formatting Cells: Fonts, Colors, and Borders
    • Number Formatting: Currency, Percentages, and Dates
    • Conditional Formatting
    • Aligning and Merging Cells

Module 4: Managing Worksheets (2 hours)

  • Topics Covered:
    • Inserting and Deleting Worksheets
    • Renaming and Moving Worksheets
    • Grouping and Ungrouping Worksheets
    • Freezing Panes and Split Windows

Module 5: Advanced Formulas and Functions (3 hours)

  • Topics Covered:
    • Logical Functions: IF, AND, OR
    • Lookup Functions: VLOOKUP, HLOOKUP, and INDEX-MATCH
    • Text Functions: CONCATENATE, LEFT, RIGHT, MID, LEN
    • Date and Time Functions

Module 6: Data Visualization (3 hours)

  • Topics Covered:
    • Creating and Customizing Charts: Column, Line, Pie, and Bar
    • Sparklines
    • Chart Elements: Titles, Labels, and Legends
    • Data Analysis with PivotTables and PivotCharts

Module 7: Data Analysis Tools (3 hours)

  • Topics Covered:
    • Sorting and Filtering Data
    • Data Validation
    • What-If Analysis: Goal Seek and Data Tables
    • Using Solver for Optimization

Module 8: Advanced Data Management (3 hours)

  • Topics Covered:
    • Importing Data from External Sources
    • Using Tables for Data Management
    • Advanced Filtering and Sorting
    • Removing Duplicates

Capstone Project 1: Personal Budget Tracker (2 hours)

  • Objective:
    • Create a comprehensive personal budget tracker using Excel.
    • Use various functions to calculate total income, expenses, and savings.
    • Implement data visualization techniques to present financial insights.
  • Skills Applied:
    • Data Entry and Formatting
    • Basic and Advanced Functions
    • Charts and Conditional Formatting

Capstone Project 2: Sales Data Analysis (2 hours)

  • Objective:
    • Analyze sales data to generate insights for a fictional company.
    • Use PivotTables and PivotCharts to summarize sales performance.
    • Apply advanced functions to identify trends and outliers.
  • Skills Applied:
    • Data Analysis Tools
    • Advanced Functions
    • Data Visualization
    • PivotTables and PivotCharts.

Advanced Excel Curriculum (30 Hours)


Module 1: Advanced Formulas and Functions (4 hours)

  • Topics Covered:
    • Array Formulas and Dynamic Arrays
    • Advanced Logical Functions: SUMIF, COUNTIF, SUMIFS, COUNTIFS
    • Advanced Lookup Functions: XLOOKUP, INDEX-MATCH with multiple criteria
    • Complex Text Functions: FIND, SEARCH, REPLACE, SUBSTITUTE

Module 2: Data Analysis Techniques (4 hours)

  • Topics Covered:
    • Advanced PivotTables and PivotCharts
    • Data Slicers and Timelines
    • Power Query for Data Transformation
    • Power Pivot for Data Modeling

Module 3: Advanced Data Visualization (4 hours)

  • Topics Covered:
    • Advanced Chart Types: Waterfall, Funnel, Histogram, Pareto
    • Interactive Dashboards
    • Conditional Formatting with Formulas
    • Using Sparklines for Data Trends

Module 4: Automation with Macros and VBA (4 hours)

  • Topics Covered:
    • Recording and Editing Macros
    • Introduction to VBA Programming
    • Writing VBA Code for Automation
    • Debugging and Error Handling in VBA

Module 5: Financial Modeling and Analysis (4 hours)

  • Topics Covered:
    • Building Financial Models
    • Scenario and Sensitivity Analysis
    • Discounted Cash Flow (DCF) Analysis
    • Monte Carlo Simulation

Module 6: Data Validation and Advanced Data Management (3 hours)

  • Topics Covered:
    • Advanced Data Validation Techniques
    • Using Form Controls for Data Entry
    • Managing Large Datasets
    • Protecting and Securing Workbooks

Module 7: Collaborative Features and Integration (3 hours)

  • Topics Covered:
    • Sharing and Co-authoring Workbooks
    • Using Excel with Microsoft Teams and SharePoint
    • Integrating Excel with Other Office Applications
    • Importing and Exporting Data

Module 8: Advanced Excel Tips and Tricks (2 hours)

  • Topics Covered:
    • Customizing the Excel Ribbon and Quick Access Toolbar
    • Using Named Ranges for Efficiency
    • Dynamic Range Names
    • Tips for Faster Excel Performance

Capstone Project 1: Business Financial Dashboard (3 hours)

  • Objective:
    • Create an interactive business financial dashboard using advanced Excel features.
    • Use data from multiple sources and apply complex data analysis techniques.
    • Implement advanced data visualization and interactivity.
  • Skills Applied:
    • Advanced Data Visualization
    • PivotTables and PivotCharts
    • Power Query and Power Pivot
    • Macros and VBA

Capstone Project 2: Comprehensive Sales Forecast Model (3 hours)

  • Objective:
    • Develop a comprehensive sales forecast model for a fictional company.
    • Use advanced formulas, financial modeling techniques, and scenario analysis.
    • Automate parts of the model using VBA.
  • Skills Applied:
    • Financial Modeling and Analysis
    • Advanced Formulas and Functions
    • Macros and VBA
    • Data Validation and Management.