Excel Mastery

Module 1: Introduction to Excel

• Overview of Excel Interface
  • Workbook, Worksheets, and Cells
  • Ribbon, Tabs, and Quick Access Toolbar
  • Workbook Management (Open, Save, New, Close)
• Basic Excel Operations
  • Creating and Saving Workbooks
  • Navigating Cells, Rows, and Columns
  • Entering Data: Text, Numbers, and Dates
  • Formatting Cells: Fonts, Alignment, and Number Formats

Module 2: Basic Formulas and Functions

• Basic Formulas
  • SUM, AVERAGE, COUNT, COUNTA
  • Basic Arithmetic Operations: +, -, *, /
  • Using Parentheses for Formula Order
• Cell Referencing
  • Relative vs. Absolute Referencing (e.g., A1 vs. $A$1)
  • Mixed Referencing (e.g., $A1, A$1)

Module 3: Data Management and Formatting

• Data Types
  • Text, Number, Date, Time formats
  • Custom Formats
• Cell Formatting
  • Adjusting Column Width and Row Height
  • Borders, Backgrounds, and Color Formatting
  • Conditional Formatting (Basic Highlighting, Data Bars)
• Text Functions 
  • CONCATENATE, LEFT, RIGHT, MID
  • TEXT, LEN, FIND, REPLACE

Module 4: Data Analysis Tools

• Sorting and Filtering
  • Sorting Data in Ascending/Descending Order
  • Filtering Data with AutoFilter
  • Advanced Filters: Custom Criteria
• Data Validation
  • Setting Up Data Validation Rules
  • List Validation, Date Validation
  • Creating Drop-Down Lists
• Tables and Structured References
  • Creating and Formatting Tables
  • Sorting and Filtering within Tables
  • Using Structured References

Module 5: Advanced Functions

• Lookup Functions
  • VLOOKUP, HLOOKUP
  • INDEX, MATCH, and combining INDEX-MATCH for dynamic lookups
  • XLOOKUP (Excel 365 feature)
• IF Functions
  • Basic IF Statement
  • Nested IF Functions
  • IFERROR, IFS
• Logical Functions
  • AND, OR, NOT
  • Combining Logical Functions with IF statements
• Text Functions
  • TEXT, CONCAT, TEXTJOIN

Module 6: Working with Charts

• Introduction to Charts
  • Types of Charts (Bar, Line, Pie, etc.)
  • Inserting and Customizing Charts
  • Chart Formatting and Data Labels
• Advanced Charting Techniques
  • Combo Charts
  • Secondary Axes
  • Sparklines

Module 7: PivotTables and PivotCharts

• Creating PivotTables
  • Organizing Data with PivotTables
  • Grouping Data
  • Summarizing Data using Sum, Average, Count, etc.
  • Filtering and Sorting PivotTables
• PivotCharts
  • Creating and Customizing PivotCharts
  • Dynamic Filtering using Slicers

Module 8: Advanced Data Analysis Tools

• What-If Analysis
  • Goal Seek
  • Data Tables
  • Scenario Manager
• Solver
  • Introduction to Solver
  • Using Solver for Optimization Problems
• Power Query
  • Importing Data from External Sources (CSV, Web, etc.)
  • Transforming Data with Power Query Editor
  • Combining and Merging Data

Module 9: Excel Macros and VBA

• Introduction to Macros
  • Recording and Running Macros
  • Assigning Macros to Buttons
  • Simple Macro Editing (VBA editor basics)
• VBA Programming for Excel
  • Introduction to VBA Syntax
  • Writing Simple VBA Code
  • Variables, Loops, and Conditional Statements

Module 10: Excel Tips, Tricks, and Shortcuts 

• Keyboard Shortcuts
  • Navigating Excel Quickly
  • Data Entry Shortcuts
  • Formatting Shortcuts
• Excel Productivity Tips
  • Flash Fill
  • Using Templates
  • Freeze Panes and Split Screens
• Protecting and Sharing Workbooks
  • Workbook and Worksheet Protection
  • Password Protection
  • Sharing Workbooks for Collaboration