Course

## Complete Course Outline

See below for a complete list of lessons. Click the drop down for a list of topics associated with each lesson. To sign up for our FREE 21 day course, click here.

#### Basic Formulas – Index, Match, and Lookups

Basic Formulas Part One
Basic Formulas Part Two
Basic Formulas Part Three
Diving Score
Gini Index
Body Mass Index
Big Mac Index
Modeling Supply and Demand
Cost Curves
Retirement
Modeling Fish in a Pond
Amortization Schedule
Depreciation
Gravity Model
Butterfly Effect
Product Line Pricing
Variance Analysis
Vertical Lookup
Horizontal Lookup
Product Prices Based On Date
Vertical Lookup From Multiple Worksheets
Vertical Lookup With Multiple Columns
Index Function
Index With Rows and Columns
Match Function
Combining Match and Index
Match, Index, and Large Functions
Finding Most Recent Date of Sale
Cost of Living
Currency Exchange
Predicting the US Population

#### Range Names

Name Box
Create From Selection
F3 Trick
Name Entire Row or Column
Apply Names
Worksheet and Workbook Names
Range Name Tricks
Implicit Intersection
Lookup With Range Names

#### Text Functions

Left, Right, Mid, Len and Concatenate Functions
Find Function and Text to Columns
Histograms
Clean Function
Flash Fill
Control + E and Flash Fill
Find Last Word
Two Column Lookup
Social Security Numbers
Unicode
Creating an Email List With Text Functions
The TEXT Function
The ROUND, ROUNDUP, ROUNDDOWN, and INT Functions

#### Date and Financial Functions

Date Functions Part One
Date Functions Part Two
Static Date and Time
Custom List
MLK Day
Net Present Value
XNPV
Internal Rate of Return
Modified Internal Rate of Return and XIRR
Present Value and Future Value Functions
Mortgage Functions
Rate and Nper Functions
Percentage of Principle

#### Auditing Tools, Circular References, and IF Functions

Auditing
Circular Reference
If Statements
Odds and Evens
Annual Revenue
Product Life Cycle
Flatten Table
Error Trap
NFL Scores
The ISERROR Function
Divide by 7
Valuation
If Statements and Sales Commissions Calculations
IF Statements and Option Investing
If Statements and Sales Commissions Calculations Part Two
IF Statements Continued
Basic Shift Code
Rail Fence Code
Longest Winning Streak

#### Data Tables, Goal Seek, and Scenario Manager

One Way Data Table
Two Way Data Table
Mortgage Data Table
Customer Value
Goal Seek
Goal Seek Finance
Scenario Manager
Down Payment
Project Management
Hedging
Bill Belichick’s Bold Move
IF Statements and Data Tables
Data Table Chart
Hedge Funds

#### Conditional Counts and Sums

COUNTIF and COUNTIFS Functions
COUNT, COUNTA, and COUNTBLANK Functions
SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS
SUMIFS With Duplicates
Subtotals
Data Consolidate
Database Functions
Computed Criteria
DGET Function
Conditional Counts and Sums With Non-Blanks
Benford’s Law
Finding the N’th Match
Analyzing NFL Play Effectiveness

#### Conditional Formatting and Data Validation

Top and Bottom Rules
Highlight Cells Rules
Data Bars, Color Scales, and Icon Sets
Formula Option Part One
Formula Option Part Two
Stop if True
Data Validation
Custom Option
Circle Invalid Data
Highlighting New Hires
Conditional Formatting and Sports Part One
Conditional Formatting and Sports Part Two

#### Tables

Worksheet Templates
Three Dimensional Formula
Paste Special
Tables Introduction
Table Graphs
Table Slicers
Tables Extended
Aggregate Function with Slicers
Conditional Formatting Tables
Copying Formulas Across a Table
The Vignere Code

#### Pivot Tables

Introduction to Pivot Tables
Analyzing Pivot Table Data
Text Filters
Grouping and Pivot Charts
Value Field Settings and Show Values As
Calculated Fields
Slicers and Filters
GETPIVOTDATA Function
Calculated Items
Timelines
Analyzing Surveys and More Pivot Charts
Creating Pivot Tables From Multiple Ranges
Multiple Pivot Tables
Data Model
Pivot Tables and the NBA Part One
Pivot Tables and the NBA Part Two
Birth Rate
Republican Control
PivotTables and Dating
Putting it all Together
Election Demographics

#### Descriptive Statistics

Histogram Creation
Histogram Shapes
Descriptive Statistics
Rule of Thumb
More Statistical Functions
Taking a Random Sample
Congressional Apportionment
Matchmaker
Multiple Objective Decision Making
Analyzing Changes in US Family Income
Descriptive Statistics for Grouped Data

#### Trend Curve, Correlation, and Regression to the Mean

Linear Regression Part One
Linear Regression Part Two
Exponential Growth
Power and Demand Curves
Polynomial Demand Curves
Learning Curves
Correlation
Estimating the Beta of a Stock
Regression to the Mean
Moving Average
Good R Squared
Finding the Best Curve to Fit Data

#### Multiple Regression

Multiple Regression Part One
Multiple Regression Part Two
Dummy Variables
Introduction to Conjoint Analysis
Conjoint Analysis and Regression
Value Based Pricing
Nonlinearities and Interactions
What Makes NBA Teams Win
Quarterback Rating
Maximum Likelihood Estimation
Logistic Regression
The Windchill Index

#### Probability

Introduction to Probability
Rule of Complements
Independent Events
Conditional Probability
Total Probability
Bayes Theorem

#### Random Variables

Random Variables
Binomial Random Variable
Binomial Mean Variance
Poisson Random Variables
Normal Random Variables
Central Limit Theorem
Z Scores

#### Array Formulas

Introduction to Array Formulas and Functions
Simple Examples of Array Formulas
Array Formulas and Text Functions
Finding Smallest Numbers in a Range
Emulating SUMIF or DSUM
Emulating SUMIFS
Emulating the OR Operator With Arrays
Creating the MEDIANIF Function
Creating the STDEVIF Function
Conditional Products
Finding Unique Entries in an Array
Transpose Function
Frequency Function
Matrix Array Functions
Trend Function
Linest Function

#### Offset and Indirect Functions

Offset Function
Left Hand Lookup
Offset Cost
Offset Account
Most Recent Sales
Dynamic Ranges
Graphing the Last 6 Months of Sales
Indirect Function
Indirect Consolidate
Indirect Range
Worksheet Names
Nested Drop Down
Indirect and Stocks

#### Optimization and the Excel Solver

Introduction to Solver Part One
Introduction to Solver Part Two
Introduction to Solver Part Three
Transportation
Scheduling
Capital Budgeting
Fantasy Sports Part One
Fantasy Sports Part Two
Financial Solver
NFL Totals
Pricing Model
Warehouse Location
Finding Distances
Demand Sensitivity to Price Change
Saving For 1 Year Salary
Sampling Plan For Attributes
The Assignment Problem
The Transshipment Problem
Inventory Model
The Shortest Route Problem
Hypothesis Testing For a Population Mean
Asset Allocation
Network Flow Models
Cheapest Diet

#### Forecasting

Trend and Seasonality
Ratio to Moving Average Part One
Ratio to Moving Average Part Two
Winters Method of Forecasting Introduction
Winters Method – Equations
Winters Method – Initialization
Winters Method – Implementation
Credit Union Forecasting Part One
Credit Union Forecasting Part Two
Predicting Software Sales
Forecasting Candy Bar Sales

#### Charting

Combo and Secondary Axis Charts
Handling Missing Data
Handling Hidden Data
Data Labels
Data Tables
Labels From Cells
Creating Icon Sets
Thermometer Charts
Band Charts
Dynamic Chart Labels
Check Boxes and Chart Dashboards
List Boxes and Chart Dashboards
Spinners
Gantt Charts
Dynamic Histograms
Charts Based on Sorted Data
Adding Conditional Colors to a Chart
Waterfall Charts
Creating Charts With GETPIVOTDATA
Pareto Charts
Vertical Line
Bubble Charts
Sparklines Part One
Sparklines Part Two

#### Keystroke Shortcuts

Keystroke Formatting Shortcuts
Worksheet Movement
Keystroke Formulas
Cell Selection Shortcuts
Protecting a Worksheet
Format Painter and the F4 Key

#### Power View

Introduction to Power View
Power View Tiles
Power View Map
Power View Multiples
Power View Movies

#### Monte Carlo Simulation

Introduction to Monte Carlo Simulation
The RAND and RANDBETWEEN Functions
Simulating a Discrete Random Variable
Simulating a Normal Random Variable
Simulating a Baseball Inning
The Birthday Problem
Craps
The Newsperson Problem
Bidding
Poker Simulation
Simulating the NBA Finals
Gambler’s Ruin
Stock Return Problem
Winning Streak

Simplex LP Solver Model
GRG Solver Model
Evolutionary Solver Model
Locating One Warehouse
Locating Two Warehouses
Assigning Workers to Jobs Part One
Assigning Workers to Jobs Part Two
Scheduling John Deere
Traveling Salesperson
Estimating Elasticities
Bin Packing Problem
Final Exam Scheduling
Discriminant Analysis
Cluster Analysis
Resolving Process Bottlenecks
The Toyota Manufacturing Sequence Algorithm

#### Excel 2016 Charts

Histograms
Pareto Charts
Boxplots One Variable
Boxplots More Than One Variable
Waterfall Chart – Time Series Data
Waterfall Chart – Revenue to Profit
Treemap Chart – Bookstore
Sunburst Chart – Bookstore
Sunburst Chart – Monthly Breakdown
Treemap Chart – Restaurant

#### Interview Questions

Uber Interview Question Part One
Uber Percent of Calls Each Hour
Uber Fraction of Calls Each Hour and Date
Uber – Drivers Per Shift
Changing Company Names
Opening Lockers
Magic Square
Wall Street Clock Interview Question
Company Names

#### Macros

Inserting the Developer Tab
Inserting a List of Names
Relative Reference With Macros
Using Macros to Format Cells
Formatting a Range of Variable Size
Using a Macro to Freeze Values
Using Macros For Reporting
Creating a Border With a Macro
Personal Macro
Sorting Macro

### Complete Course

Our complete Microsoft Excel training course covers virtually every topic in Excel. From more basic formulas such as sum and vertical lookups to array formulas and Excel solver models, we have built our course around Dr. Wayne Winston’s lifetime of experience helping multinational corporations around the world learn Microsoft Excel.

### Freedom to Choose

No matter what your skill level is, our Microsoft Excel Training Course has everything that you need! More experienced? Then feel free to skip around to any of our 300 videos. Our course is setup so that when you complete a video and move onto the next one, this video will be “marked complete”. This ensures that you will never lose your spot and can watch the whole course in whichever order you prefer.

### Learn From The Best

We may sound like a broken record, but we can not stress enough the importance of being able to learn from the absolute best in any subject matter, let alone in Microsoft Excel, a program that you and your organization relies on daily.