Advanced Excel and Analytics Course

Sample Lessons Sample Lesson

Range Names 2 Topics

Expand

Lesson Content

0% Complete 0/2 Steps

Range Name Tricks

Implicit Intersection

Text Functions 4 Topics

Expand

Lesson Content

0% Complete 0/4 Steps

Clean Function

Flash Fill

Control + E and Flash Fill

The TEXT Function

Auditing Tools, Circular References, and IF Functions 5 Topics

Expand

Lesson Content

0% Complete 0/5 Steps

If Statements and Sales Commissions Calculations Part Two

Data Tables, Goal Seek, and Scenario Manager 6 Topics

Expand

Lesson Content

0% Complete 0/6 Steps

Conditional Counts and Sums 11 Topics

Expand

Lesson Content

0% Complete 0/11 Steps

SUMIFS With Duplicates

Conditional Counts and Sums With Non-Blanks

Benford

Finding the N

Analyzing NFL Play Effectiveness

Conditional Formatting and Data Validation 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Formula Option Part One

Formula Option Part Two

Custom Option

Circle Invalid Data

Highlighting New Hires

Conditional Formatting and Sports Part One

Conditional Formatting and Sports Part Two

Tables 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

Worksheet Templates

Three Dimensional Formula

Aggregate Function with Slicers

Conditional Formatting Tables

Copying Formulas Across a Table

Pivot Tables 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

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

Putting it all Together

Descriptive Statistics 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Histogram Creation

Histogram Shapes

Descriptive Statistics

Rule of Thumb

More Statistical Functions

Taking a Random Sample

Multiple Objective Decision Making

Trend Curve, Correlation, and Regression to the Mean 12 Topics

Expand

Lesson Content

0% Complete 0/12 Steps

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 12 Topics

Expand

Lesson Content

0% Complete 0/12 Steps

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 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Introduction to Probability

Addition Rule of Probability

Rule of Complements

Independent Events

Conditional Probability

Total Probability

Bayes Theorem

Random Variables 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Random Variables

Binomial Random Variables

Binomial Mean Variance

Poisson Random Variables

Normal Random Variables

Central Limit Theorem

Z Scores

Array Formulas 17 Topics

Expand

Lesson Content

0% Complete 0/17 Steps

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

Fermat

Offset and Indirect Functions 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Graphing the Last 6 Months of Sales

The INDIRECT Function and Stock Return Data

Optimization and the Excel Solver 25 Topics

Expand

Lesson Content

0% Complete 0/25 Steps

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

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 11 Topics

Expand

Lesson Content

0% Complete 0/11 Steps

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 6 Topics

Expand

Lesson Content

0% Complete 0/6 Steps

Check Boxes and Chart Dashboards

List Boxes and Chart Dashboards

Spinners

Creating Charts With GETPIVOTDATA

Sparklines Part One

Sparklines Part Two

Power View 5 Topics

Expand

Lesson Content

0% Complete 0/5 Steps

Introduction to Power View

Monte Carlo Simulation 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

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

Stock Problem

Winning Streak

Advanced Solver 17 Topics

Expand

Lesson Content

0% Complete 0/17 Steps

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

Advanced Learning Curve Models

Bin Packing Problem

Final Exam Scheduling

Discriminant Analysis

Cluster Analysis

Resolving Process Bottlenecks

The Toyota Manufacturing Sequence Algorithm

Advanced Charts 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

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

Macros 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

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

Supply Chain 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

Inventory on Time

Inventory Modeling With Shortages

Aggregate Planning

Economic Order Quantity

Power of Two

Multiple Product Economic Order Quantity

Quantity Discounts

Cycle Service Level

Fillrate, Reorder Point, and Safety Stock

Finding Distances Between 100 Cities

Locating Four Warehouses

Jobshop Scheduling

Queuing

Inventory Turns

Advanced Excel and Analytics Course

Course Content

OVERVIEW

About Instructor

Wayne Winston

Course Includes

CONTACT INFO

CONTACT INFO