Advanced Excel and Power BI Course

Lesson 1 • Navigating the Excel Interface

Covers the Ribbon, Quick Access Toolbar, and worksheet navigation shortcuts. Establishes spatial fluency needed for all subsequent Excel tasks.

Lesson 2 • Formatting Cells and Worksheets

Applies number formats, fonts, borders, and conditional formatting to structure data visually. Professional formatting improves readability and report quality.

Lesson 3 • Workbook Organization and Protection

Covers sheet grouping, freezing panes, and workbook protection settings. Organized, protected workbooks prevent errors in shared environments.

Lesson 1 • Logical and Conditional Functions

Teaches IF, AND, OR, NOT, and nested logic to build decision-driven formulas. Conditional logic enables dynamic outputs based on changing data.

Lesson 2 • Text and Date Functions

Applies CONCATENATE, TEXT, LEFT, RIGHT, MID, and date functions to manipulate strings and dates. Text and date handling is critical for data cleaning and reporting.

Lesson 3 • Lookup and Reference Functions

Covers VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP for cross-table data retrieval. Lookup functions eliminate manual searching and link datasets efficiently.

Lesson 4 • Formula Syntax and Cell References

Explains absolute, relative, and mixed references alongside formula auditing tools. Correct referencing is the foundation of reusable, error-free formulas.

Lesson 1 • Data Cleaning with Functions and Tools

Applies TRIM, CLEAN, PROPER, and Remove Duplicates to fix common data quality issues. Clean data ensures accurate formula results and trustworthy analysis.

Lesson 2 • Sorting, Filtering, and Advanced Filters

Teaches multi-level sorting, AutoFilter, and Advanced Filter with criteria ranges. Filtering isolates relevant subsets without altering the underlying dataset.

Lesson 3 • Importing and Connecting External Data

Covers importing CSV, text, and database files using Excel's data connection tools. External data connections reduce manual re-entry and keep reports current.

Lesson 1 • PivotCharts and Visual Summaries

Creates PivotCharts linked to PivotTables for synchronized visual analysis. Charts update automatically when PivotTable filters or data change.

Lesson 2 • Slicers, Timelines, and Interactivity

Adds slicers and timelines to enable one-click filtering across multiple PivotTables. Interactive controls transform static summaries into dynamic dashboards.

Lesson 3 • What-If Analysis and Scenario Tools

Applies Goal Seek, Scenario Manager, and Data Tables for sensitivity analysis. What-if tools support decision-making by modeling multiple outcome scenarios.

Lesson 4 • Calculated Fields and Custom Grouping

Adds calculated fields, items, and custom groupings to extend PivotTable analysis. Custom calculations surface metrics not present in the source data.

Lesson 1 • Dynamic Array Functions

Covers FILTER, SORT, UNIQUE, SEQUENCE, and RANDARRAY for spill-range outputs. Dynamic arrays eliminate helper columns and simplify multi-step formula chains.

Lesson 2 • LAMBDA and Custom Functions

Defines reusable custom functions with LAMBDA, LET, and helper functions. Custom functions standardize logic across workbooks and reduce formula duplication.

Lesson 3 • Advanced Lookup and Aggregation

Combines XLOOKUP, SUMPRODUCT, and array logic for multi-condition aggregation. These patterns handle complex reporting requirements without helper columns.

Lesson 1 • Query Optimization and Best Practices

Covers query folding, disabling unnecessary loads, and structuring queries for performance. Optimized queries reduce refresh times and prevent memory issues in large datasets.

Lesson 2 • Transforming and Shaping Data

Applies column splitting, pivoting, unpivoting, and data type changes to reshape tables. Proper shaping ensures data is structured correctly for analysis and modeling.

Lesson 3 • Power Query Interface and Connections

Introduces the Power Query Editor, query pane, and source connection types. Understanding the interface is prerequisite to building any transformation pipeline.

Lesson 4 • Custom Columns and M Language Basics

Creates custom columns using the M formula language for advanced transformations. M language unlocks transformations unavailable through the graphical interface.

Lesson 1 • Filters, Slicers, and Report Interactivity

Applies page filters, visual-level filters, and slicers to control report interactivity. Proper filter configuration ensures users explore data without seeing incorrect results.

Lesson 2 • Data Modeling and Relationships

Defines table relationships, cardinality, and cross-filter direction in the Model view. A well-structured model is the foundation of accurate DAX calculations.

Lesson 3 • Introduction to DAX Formulas

Covers DAX syntax, calculated columns, and basic measures using SUM, COUNT, and AVERAGE. DAX measures enable dynamic calculations that respond to report filters.

Lesson 4 • Building Core Visuals and Reports

Creates bar, line, pie, and table visuals with proper field assignments and formatting. Core visuals communicate data trends and comparisons to business audiences.

Lesson 1 • Dashboard Layout and UX Design

Applies layout grids, color themes, tooltips, and bookmarks for professional dashboards. Good UX design reduces cognitive load and guides users to key insights.

Lesson 2 • Publishing, Sharing, and Row-Level Security

Publishes reports to Power BI Service, configures workspaces, and sets row-level security. Secure sharing ensures the right users see only the data they are authorized to view.

Lesson 3 • Advanced Visuals and Custom Formatting

Uses scatter plots, waterfall charts, decomposition trees, and conditional formatting. Advanced visuals reveal patterns and root causes not visible in standard charts.

Lesson 4 • Advanced DAX Patterns and Time Intelligence

Covers CALCULATE, USERELATIONSHIP, and time intelligence functions for period comparisons. Time intelligence enables year-over-year, month-to-date, and rolling calculations.