Microsoft Office Excel Intermediate

Course Overview

Elevate your Excel skills beyond basics. Master powerful functions, dynamic analysis with PivotTables, and advanced data management techniques to transform raw data into actionable insights.

Target Audience:

Users comfortable with Excel basics (data entry, simple formulas, basic formatting) who need to analyze, manage, and present complex data sets.

Upon completion, students will be able to:

• Build complex formulas using logical (IF, AND/OR), lookup (VLOOKUP/XLOOKUP), and text functions.
• Apply and manage Conditional Formatting to visually analyze data trends and patterns.
• Create, modify, and filter PivotTables and PivotCharts to summarize large data sets.
• Use advanced filtering, sorting, and data tools to manage lists.
• Link data across multiple worksheets and workbooks.
• Implement Data Validation and protect worksheets and workbooks.

Outline

Module 1: Advanced Formulas and Functions

• Logical Functions: Using IF, nested IFs, AND, and OR to make conditional calculations.
• Lookup Functions: Using VLOOKUP (or XLOOKUP) to find and retrieve data from a table.
• Text & Date Functions: Using functions like LEFT, RIGHT, CONCAT, and DATE to manipulate text and date values.

Module 2: Conditional Formatting Techniques

• Beyond Basic Highlighting: Using top/bottom rules, data bars, color scales, and icon sets.
• Custom Rules: Creating new rules with custom formulas (e.g., format a cell if it is above the average of a range).
• Managing Rules: Editing, deleting, and controlling the order of multiple conditional formatting rules.

Module 3: Introduction to PivotTables

• PivotTable Basics: Creating a PivotTable from a data range, understanding the Field List.
• Arranging Data: Adding fields to Rows, Columns, Values, and Filters to shape your report.
• Grouping and Formatting: Grouping dates/numbers, applying number formatting, and changing summary calculations (Sum, Count, Average).

Module 4: Advanced Data Sorting and Filtering

• Custom Sorts: Sorting data by multiple columns (e.g., sort by Region, then by Sales descending).
• Advanced Filter: Using the Advanced Filter tool to extract data based on complex, multi-field criteria.
• Table Filters: Leveraging the powerful filtering capabilities of Excel Tables (slicers & timelines).

Module 5: Working with Multiple Sheets and Workbooks

• 3D Formulas:Referencing the same cell across multiple worksheets for summary calculations (e.g., SUM(Sheet1:Sheet3!B5)).
• Linking Workbooks: Creating formulas that reference cells in other open workbooks.
• Consolidating Data: Using the Consolidate tool to combine data from multiple sheets.

Module 6: Data Validation and Protection

• Data Validation: Setting rules to restrict data entry (e.g., whole numbers, dates, custom lists for drop-down menus).
• Input Messages and Error Alerts: Creating prompts and custom error messages for users.
• Protection: Locking cells, protecting a worksheet with a password, and protecting workbook structure.