Duration: 3 days – 21 hrs
Overview
This hands-on program levels up power users on Microsoft 365/Office 2021 apps—Excel, Word, PowerPoint, and Outlook—focusing on real-world productivity, automation, data analysis, and professional document/report creation. Participants will master advanced features (dynamic arrays, Power Query, slide masters, long-document controls, automation helpers, and collaboration) and apply them in end-to-end case studies.
Objectives
- Build robust Excel workbooks using advanced formulas, dynamic arrays, PivotTables, and Power Query for repeatable data prep.
- Design publication-ready Word documents using styles, sectioning, templates, references, and controlled layouts.
- Create compelling, on-brand PowerPoint decks using slide masters, layouts, storytelling flows, and data-driven visuals.
- Streamline email and time management with Outlook rules, Quick Steps, search, and shared calendars.
- Collaborate securely with OneDrive/SharePoint, manage versions, and co-author across apps.
- Apply best practices for performance, auditing, accessibility, and maintainability across Office files.
Audience
- Analysts, specialists, managers, project leads, trainers, and power users who work extensively with Office files
- Teams producing reports, dashboards, client deliverables, or executive presentations
Pre- requisites
- Comfortable with basic Office usage (Excel formatting & simple formulas, basic Word/PowerPoint editing, sending email in Outlook)
- Laptop with Microsoft 365/Office 2021 installed; organization access to OneDrive/SharePoint recommended
Course Content
Day 1 Excel Analytics & Data Preparation
Module 1: Excel Power Foundations (Advanced)
- Structured references & named ranges
- Data validation, error trapping, audit & trace precedents/dependents
- Tables as data models (sorting, filtering, totals, relationships basics)
Module 2: Advanced Formulas & Dynamic Arrays
- XLOOKUP vs. VLOOKUP; INDEX/MATCH patterns
- FILTER, UNIQUE, SORT, SEQUENCE, LET, LAMBDA patterns
- Date/time, text, conditional & aggregation functions (IFS, SUMIFS, XMATCH)
Module 3: Pivot Power
- PivotTables & PivotCharts: grouping, calculated fields, slicers, timelines
- Scenario analysis with What-If tools (Goal Seek, Solver intro)
Module 4: Power Query (Get & Transform)
- Importing CSV/Excel/SharePoint/web data
- Cleaning & shaping (split/merge, unpivot, fill, data types)
- Query parameters & refresh strategies for repeatable pipelines
Lab & Checkpoint
- Build a refreshable report: automate data cleanup with Power Query; analyze with PivotTables & dynamic arrays
Day 2 Word for Professional Docs + Outlook Productivity
Module 5: Word Mastery for Long & Branded Documents
- Styles architecture (paragraph, character, linked styles) & multi-level numbering
- Section breaks, headers/footers, page numbering variants
- Templates & themes; building blocks, Quick Parts, and content controls
- Tables, images, icons, and layout alignment best practices
- References: table of contents, captions, cross-references, citations, bibliography
- Mail Merge (Excel/CSV/Outlook contacts), envelopes/labels
- Review tools: Track Changes, Compare, Restrict Editing, document protection
- Accessibility: headings, alt text, reading order, export to PDF
Module 6: Outlook for Focus & Flow
- Mail triage systems (rules, Quick Steps, categories, search folders)
- Calendar mastery, shared calendars, meeting polls & booking etiquette
- Advanced search, flags, follow-ups; delegation and mailbox cleanup
Lab & Checkpoint
- Create a branded report template with automatic TOC and styles; mail-merge a stakeholder update; set Outlook automations for triage
Day 3 PowerPoint Storytelling + Cross-App Collaboration
Module 7: Slide Masters & Visual Systems
- Master slides, layouts, themes, and color/typography governance
- Grids, alignment, white space, and visual hierarchy; consistency checks
- Advanced animations & transitions (incl. Morph) used sparingly
Module 8: Data-Driven Storytelling
- Choosing the right chart; linking Excel ranges/charts; updating without breakage
- Infographics with shapes/icons; image treatment & compression
- Presenter tools, rehearsal, and export options (PDF, video, PPTX packaging)
Module 9: Collaboration & Governance (Microsoft 365)
- OneDrive/SharePoint basics: sharing, permissions, version history
- Real-time co-authoring across Word/Excel/PowerPoint
- Comments/mentions, tasks, and review workflows
- File hygiene: naming, metadata, and archival strategies
Capstone Lab (End-to-End)
- Team builds a mini “business review pack”:
- Excel: refreshable analysis with PQ + Pivot + dynamic arrays
- Word: executive brief using the team’s template & references
- PowerPoint: 7–10 slide narrative with linked charts & slide master
- Publish to SharePoint, set permissions, and simulate a review cycle
