MS Excel SpecialistFull Course Curriculum

Section 01

Getting Comfortable

Speed first. The shortcuts and view settings that stop you doing things the slow way.

1. 01Introduction to Excel, Shortcuts, Fill Handle, Flash Fill
2. 02Freeze Panes, Split Window, Gridlines, Headings, Comments, Hyperlinks
3. 03Sorting, Filtering, Grouping, Custom Sort, Hiding and Deleting

Section 02

Finding and Fixing Data Fast

Four tools that save more time than any formula you will ever write.

1. 04Find and Replace, Wildcards, and Cleaning up Comments
2. 05Go to Special, Paste Special, and Horizontal Sort
3. 06Excel Tables and Structured References, and Why Every Dataset Should Be One

Section 03

Formulas and Referencing

Why one dollar sign decides whether your formula survives a copy-paste.

1. 07Mathematical Calculation, Absolute, Relative and Mixed Referencing
2. 08Function Syntax, Formula Nesting, and Pulling Values from Other Sheets

Section 04

Core Maths and Statistics

The everyday functions. Boring to list, impossible to work without.

1. 09SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, MIN, MAX
2. 10ABS, PRODUCT, ROUND, ROUNDUP, ROUNDDOWN, LARGE, SMALL, RANK.EQ, RANK.AVG
3. 11SUBTOTAL and AGGREGATE with Filters, Plus SUMPRODUCT

Section 05

Named Ranges

Formulas that read like sentences instead of coordinates.

1. 12Name Manager: Naming Ranges and Using Them Inside Your Functions

Section 06

Text Functions

Splitting, joining and fixing messy text without touching a single cell by hand.

1. 13LEFT, RIGHT, MID, FIND, LEN, SEARCH
2. 14REPLACE, SUBSTITUTE, TRIM, UPPER, LOWER, PROPER, REPT
3. 15CONCAT, TEXTJOIN, Text to Columns, VALUE, FORMULATEXT

Section 07

Date and Time Functions

Date maths in Excel behaves oddly until someone explains it once.

1. 16DATE, DAY, MONTH, YEAR, EDATE, EOMONTH, TODAY, TEXT
2. 17WEEKDAY, WORKDAY.INTL, NETWORKDAYS.INTL, TIME, HOUR, MINUTE, SECOND

Section 08

Logical Functions

Teaching a spreadsheet to make decisions.

1. 18Comparison Operators, IF, Nested IF, Order of Execution
2. 19IFS, SWITCH, IFERROR, EXACT
3. 20AND, OR, and Mixed AND-OR Logic
4. 21LET and LAMBDA: Naming Your Steps and Writing Your Own Functions

Section 09

Conditional Analysis

Counting and summing only what matches your conditions. Analysis really starts here.

1. 22COUNTIFS for Data Analysis
2. 23SUMIFS, AVERAGEIFS, MINIFS, MAXIFS and Sparklines

Section 10

Lookup Functions

Pulling the right value out of another table, five different ways.

1. 24LOOKUP, VLOOKUP, HLOOKUP and MATCH
2. 25INDEX with MATCH, and XLOOKUP
3. 26OFFSET, INDIRECT and ADDRESS for Ranges That Move on Their Own

Section 11

Dynamic Arrays

The biggest change to Excel formulas in twenty years. One formula, many answers.

1. 27How Spilling Works, Then FILTER, SORT and SORTBY
2. 28UNIQUE, SEQUENCE, RANDARRAY, and Combining Them into Summary Reports
3. 29Searchable and Dependent Dropdown Lists Built on Spill Ranges

Section 12

Validation and Conditional Formatting

Stop bad entries at the source, and make numbers tell their own story.

1. 30Data Validation: Whole Number, Date, Text Length, List, Dependent Lists
2. 31Conditional Formatting, from Built-In Rules to Formula-Driven Formatting

Section 13

Formula Auditing and Protection

Finding out why a formula is wrong, and stopping others from breaking it.

1. 32Error Checking, Trace Precedents and Dependents, Evaluate Formula, Protecting Sheets

Section 14

Charts and Graphs

Picking the chart that answers the question, not the one that looks busiest.

1. 33Column, Bar, Line, Area, Histogram and Pareto Charts
2. 34Pie, Donut, Tree Map, Sunburst, Waterfall and Funnel Charts
3. 35Scatter, Bubble, Radar, Box and Whisker, Heat Maps and Combo Charts

Section 15

Advanced Chart Techniques

The tricks that make an Excel chart look like it came from somewhere else.

1. 36Form Controls: Check Boxes, Combo Boxes, Radio Buttons, Scroll Bars
2. 37Gauge Charts, Pacing Charts, Dynamic Highlighting and Image Overlays

Section 16

Interactive Excel DashboardProject

Six classes building one dashboard from raw data to a screen a manager would actually open.

1. 38Data Analytics and Measuring KPIs
2. 39Advanced Data Analytics on the Same Dataset
3. 40Top and Worst Performers, Time Series Analysis
4. 41KPI Charts and Auto-Updating Bar Charts
5. 42Line Charts and Combo Charts
6. 43Final Design, Layout and Polish

Section 17

What-If Analysis

Answering the question every manager asks: what happens if this number changes?

1. 44Goal Seek, Data Tables and Scenario Manager
2. 45Solver, and Running What-If Analysis Inside a Financial Model

Section 18

Financial Functions

The functions accountants actually use, explained by someone who is one.

1. 46FV, PV, PMT, and Building a Loan Amortisation Schedule
2. 47NPV, IRR, and Depreciation with SLN, SYD and DDB

Section 19

Data Analysis ToolPak

Statistics without leaving Excel, and without pretending to be a statistician.

1. 48Descriptive Statistics, Histograms, Correlation and Covariance
2. 49Regression Analysis, Sampling and Hypothesis Testing

Section 20

Pivot Tables

The fastest tool in Excel, from your first pivot to the parts nobody explains.

1. 50Structuring Source Data, Your First Pivot, the Field List and Refreshing
2. 51How the Pivot Cache Actually Works, and Dealing with Growing Data
3. 52Layouts, Styles, Number Formats and Conditional Formatting Inside Pivots
4. 53Sorting, Filtering, Grouping, Slicers and Timelines

Section 21

Pivot Calculations and Charts

Where pivot tables stop summarising and start calculating.

1. 54Value Calculations: Percent of Parent, Difference from, Running Total, Rank, Index
2. 55Calculated Fields, Calculated Items, and the Count of Trap
3. 56Pivot Charts, and Building a Dashboard Driven Entirely by Pivots

Section 22

365 Creative Inc.Project

Real dataset, real questions, answered entirely with pivot tables.

1. 57Data Analysis with Pivot Tables
2. 58Turning the Analysis into a Dashboard

Section 23

365 Coffee CafeProject

A two-part case study on cafe sales. Messy input, clean output.

1. 59Coffee Cafe Project, Part One
2. 60Coffee Cafe Project, Part Two

Section 24

Power Query Foundations

Where it lives, what it replaces, and the parts that break reports if you skip them.

1. 61Why Power Query, and When It Beats Writing Formulas
2. 62The Editor: Applied Steps, Formula Bar, and Your First Look at M
3. 63Importing from Excel, CSV and Text Files; Load Destinations and Refresh
4. 64Data Types, Nulls and Errors, and Why They Cause Trouble Later

Section 25

Cleaning and Shaping

The daily-driver transformations. Most of your manual cleanup dies in this section.

1. 65Text Transformations, Extracting and Merging Columns
2. 66Fill, Replace, Sort, and Removing Duplicates Properly
3. 67Number Transformations and Filters with AND / OR Conditions
4. 68Column from Examples, Conditional Columns, Sorting Data into Buckets

Section 26

Reshaping Data

Turning a report layout back into a proper dataset, and the errors everyone hits doing it.

1. 69Group by on Multiple Levels, and Group by All Rows
2. 70Unpivot and Pivot Columns, Plus the Common Mistakes
3. 71Splitting Columns by Delimiter and into Rows, and Working with Nested Queries

Section 27

Dates, Time and Custom Columns

Date maths that behaves, and your first custom formulas inside the editor.

1. 72Date Transformations, Extracting Parts, and Locale Errors When Importing
2. 73Time Transformations and Calculating Hours Worked
3. 74Custom Columns, Drill-Down, and Filters That Read a Cell Value

Section 28

Bringing Data Together

Stop copy-pasting between files. Point at a folder and let it do the work.

1. 75Append: Combining Workbooks, Every File in a Folder, Every Sheet in a File
2. 76Merge and Every Join Kind, Including Fuzzy Match
3. 77Pulling from Web Pages, Google Sheets, SharePoint and OneDrive

Section 29

HR Data ReportProject

A full cleanup project. Years in each role, years at the company, names split out properly.

1. 78HR Project: Build a Clean, Refreshable Report from Raw Employee Data

Section 30

The M Language

You do not need to code. But reading M turns you from a clicker into someone who can fix anything.

1. 79How M Thinks: Let Expressions, Lists and Records
2. 80Custom Functions, Parameters, and Invoking Them Across Files
3. 81Error Handling, Table.Buffer, and Query Folding for Speed

Section 31

Power Pivot and the Data Model

Millions of rows, several tables, one report. No VLOOKUP in sight.

1. 82Why Power Pivot: What Pivot Tables Simply Cannot Do
2. 83Building a Model: Fact Tables, Lookup Tables, Star Schema
3. 84Relationships, Hierarchies, and How Filters Travel Between Tables

Section 32

DAX Fundamentals

Formulas that look like Excel but behave nothing like it, and why that trips people up.

1. 85Measures: Implicit vs Explicit, Syntax, and the Functions You Will Use Daily
2. 86Filter Context, the Idea Everything Else in DAX Rests On
3. 87Calculated Columns vs Measures, and When Each One Is the Wrong Choice

Section 33

Calendars and Time Intelligence

Year to date, last year, running totals, moving averages. This is what managers ask for.

1. 88Calendar Tables, and Sorting Month and Weekday Columns Correctly
2. 89YTD, MTD, QTD, Previous Period Comparison, Running Totals, Moving Averages

Section 34

Mastering CALCULATE

Two classes on one function, because DAX genuinely revolves around it.

1. 90CALCULATE Part One: Overriding Filters with ALL and KEEPFILTERS
2. 91CALCULATE Part Two: ALLEXCEPT, ALLSELECTED, Variables, Threshold Measures

Section 35

Advanced DAX

The concepts that separate people who copy DAX from people who write it.

1. 92Context Transition, and the Wrong Results It Quietly Causes
2. 93RELATED, RELATEDTABLE, and Working Across Multiple Fact Tables
3. 94RANKX, TOPN and CONCATENATEX for Ranked Reports
4. 95Awkward Relationships: Inactive, USERELATIONSHIP, Many to Many

Section 36

365 Retail Inc. DashboardProject

A regional sales dashboard driven entirely by the data model and DAX measures.

1. 96Model Setup, Measures, Slicers and Chart Logic
2. 97Latest Period Logic, Linked Shapes, CUBE Formulas, Final Layout

Section 37

From Excel into Power BIProject

Same model, same DAX. New tool. This class is your bridge to Power BI.

1. 98Move the Model into Power BI, Build the Report, Publish It

Section 38

365 Corporation Inc.Capstone

Everything from every class before it, in one project you do start to finish on your own.

1. 99Capstone: Raw Data in, Finished Report Out