Advanced Excel

Module 1: Excel Basics Refresher

1. Understanding the Excel Environment

• Overview of the Ribbon, Quick Access Toolbar, and workbook display options
• Essential keyboard shortcuts to improve productivity

2. Core Formulas and Functions

• Functions: SUM, AVERAGE, COUNT, MIN, MAX
• Relative, Absolute, and Mixed cell references

3. Formatting and Presentation

• Cell formatting and custom number formats
• Conditional formatting techniques
• Creating data validation rules and dropdown menus

4. Productivity and Best Practices

• Time-saving shortcuts and efficiency techniques
• Identifying and preventing common errors

5. Ensuring Data Accuracy

• Data entry standards and validation methods
• Auditing formulas and tracking precedents/dependents

6. Workbook & Worksheet Management

• Understanding workbooks vs worksheets
• Adding, deleting, renaming sheets
• Hiding and unhiding worksheets

7. Worksheet Formatting Techniques

• Modifying fonts and styles
• Applying borders, colors, and themes
• Aligning content and formatting numbers
• Using basic conditional formatting
• Creating reusable templates

8. Editing and Organizing Worksheets

• Inserting, deleting, and resizing rows, columns, and cells
• Using Find and Replace tools

Module 2: Advanced Functions and Formulas

1. Logical Functions

• IF, Nested IF, AND, OR, NOT
• IFS and SWITCH for handling multiple conditions

2. Lookup and Reference Tools

• VLOOKUP and HLOOKUP
• INDEX and MATCH (including combination usage)
• XLOOKUP (for Excel 365 users only)

3. Text Manipulation Functions

• LEFT, RIGHT, MID, LEN, TRIM
• CONCATENATE and TEXTJOIN
• FIND, SEARCH, SUBSTITUTE, REPLACE

4. Date and Time Functions

• TODAY, NOW, DATE, DAY, MONTH, YEAR
• DATEDIF, NETWORKDAYS, WORKDAY

5. Mathematical & Statistical Functions

• SUMIF, SUMIFS, COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS
• ROUND, ROUNDUP, ROUNDDOWN
• RAND, RANDBETWEEN

6. Financial Calculations

• NPV, IRR, PMT, FV, PV
• Depreciation methods: SLN, DB, DDB

Module 3: Data Analysis and Management (2 Hours)

1. Sorting and Filtering Data

• Custom sorting techniques
• Advanced filters and slicers

2. Working with Excel Tables

• Creating and managing structured tables
• Using structured references in formulas

3. Data Cleaning Methods

• Removing duplicates
• Text-to-Columns and Flash Fill
• Managing blanks and error values

4. What-If Analysis Tools

• Goal Seek
• One-variable and Two-variable Data Tables
• Scenario Manager

Module 4: Data Visualization

1. Advanced Charting

• Combo charts, secondary axis, and trendlines
• Waterfall, Histogram, and Box & Whisker charts
• Using Sparklines for in-cell analysis

2. Advanced Conditional Formatting

• Rule-based formatting using formulas
• Data bars, color scales, and icon sets

3. Dashboard Creation

• Building dashboards using PivotTables, charts, and slicers
• Dashboard design principles and layout best practices

Module 5: Advanced Data Tools

1. Power Query

• Importing external data
• Transforming, merging, and appending datasets
• Data cleaning and shaping techniques

2. Power Pivot

• Introduction to data modeling
• Creating relationships between multiple tables
• Basics of DAX (Data Analysis Expressions)

3. Forecasting and Analytical Tools

• Using the Forecast Sheet feature
• Performing regression analysis and adding trendlines

Module 6: Collaboration and Sharing

1. Workbook Protection

• Applying passwords and permissions
• Locking specific cells and formulas

2. Sharing and Team Collaboration

• Tracking changes and adding comments
• Sharing via OneDrive or SharePoint
• Real-time co-authoring (Excel 365)

3. Exporting and Printing

• Converting files to PDF and other formats
• Print setup, headers, and footers customization

Module 7: Practical Applications & Student Projects

1. Real-World Case Studies

2. Final Capstone Project