Course Aims

• Enhance existing Excel skills to handle more complex data and analysis.

• Teach learners to use logical, text, and lookup functions.

• Introduce data management tools, advanced charts, and better presentation techniques.

Course Outline

Session 1: Review & Advanced Formatting Techniques, Logical & Text Functions

• Quick review of essential Excel skills (basic formulas, formatting, charts)

• Using Format as Table for structured data

• Custom number formats (e.g., thousands separator, custom date formats)

• Conditional formatting basics (highlight cells, data bars, color scales)

• Using IF for basic decision-making

• Combining IF with AND / OR

• Introduction to text functions: LEFT, RIGHT, MID, LEN, CONCAT / TEXTJOIN

• Practical applications: cleaning phone numbers, formatting IDs

Session 2: Lookup Functions & Dynamic Data, Advanced Charting & Visualization

• Understanding the need for lookups

• Using VLOOKUP and HLOOKUP

• Introduction to XLOOKUP (if available)

• Basics of MATCH and INDEX

• Creating combo charts (e.g., columns + line)

• Adding secondary axes

• Inserting and customizing sparklines

• Using chart elements and design tools for professional reports

Session 3: Data Management Tools, PivotTables & Wrap-Up

• Sorting by multiple levels (e.g., by department, then by score)

• Using basic filters and slicers

• Data validation: creating drop-down lists

• Removing duplicates

• Understanding PivotTables: what they are and when to use them

• Creating a simple PivotTable

• Using basic filters and slicers with PivotTables

• Refreshing data and changing field layout

• Final mini-project: combine functions, formatting, and charts into a summary report