M365 Excel Desktop: Formulas, Functions, and Tables

Level: Beginner to Intermediate

Duration: 1 day

Prerequisites: M365 Excel Desktop: Getting Started or equivalent

About:  Build dependable calculations and well-structured data using references, essential functions, Excel Tables, validation, and protection.

Learning Objectives

• Build and troubleshoot formulas
• Apply mathematical, logical, conditional, date, and text functions
• Use Tables and structured references
• Improve data quality with validation
• Protect formulas appropriately

Course Topics

Formula Design

• Syntax, operators, order of operations
• Relative, absolute, mixed, cross-sheet, and 3-D references
• Named ranges, Formula AutoComplete, errors, and auditing

Essential Functions

• SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK, ROUND, SUBTOTAL
• IF, IFS, AND, OR, NOT, IFERROR
• COUNTIF(S), SUMIF(S), AVERAGEIF(S)
• TODAY, NOW, DATE, WORKDAY, NETWORKDAYS
• LEFT, RIGHT, MID, LEN, TRIM, CLEAN, CONCAT, TEXTJOIN, TEXT, VALUE

Excel Tables

• Create, name, style, sort, and filter Tables
• Total Row, structured references, calculated columns, expansion, and duplicate removal

Validation and Protection

• Dropdown lists, input messages, alerts, and custom rules
• Lock cells; protect worksheets and workbook structure
• Protection versus security