Complete Microsoft Excel Training Course

Basic Formulas – Index, Match, and Lookups 43 Topics

Expand

Lesson Content

0% Complete 0/43 Steps

Basic Formulas Part One

Basic Formulas Part Two

Basic Formulas Part Three

Computing a Production Function

Modeling Supply and Demand

Cost Curves

Retirement

Modeling Fish in a Pond

Amortization Schedule

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 Future of the United States Population

The CHOOSE Function

VLOOKUPS and Income Taxes

Math is Fun

Rule of 72

Bringing PDF Tables Into Excel

Multiplying 4 Consecutive Integers

Explaining Google’s Page Rank

The XLOOKUP Function

XLOOKUP and the TRUE Argument

XLOOKUP and the INDEX MATCH Combination

Using XLOOKUP to Reference a Cell Range

Filtering, Sorting, and Removing Duplicates 16 Topics

Expand

Lesson Content

0% Complete 0/16 Steps

Filtering a Column on Two Criteria

Basic Sorting

Sorting on Colors

Sorting A to Z

Case Sensitive Sorting

Sorting With a Custom List

Creating a Custom List

Sorting Across

Removing Duplicates

Filtering by Selection

Range Names 9 Topics

Expand

Lesson Content

0% Complete 0/9 Steps

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

Expand

Lesson Content

0% Complete 0/15 Steps

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

Special Text Formats

Using Flash Fill to Clean Up Messy Data

Date, Time, and Financial Functions 27 Topics

Expand

Lesson Content

0% Complete 0/27 Steps

Date Functions Part One

Date Functions Part Two

Static Date and Time

The EOMONTH and EDATE Functions

The WEEKNUM Function

Introduction to Time Functions

The TIMEVALUE function and Time Intervals

Time Sequences and Adding Up Times

Internal Rate of Return

Modified Internal Rate of Return

Modified Internal Rate of Return and XIRR

Present Value and Future Value Functions

Mortgage Functions

The FVSCHEDULE Function

Rate and NPER Functions

Percentage of Principle

Bond Pricing

Annual Percentage Rates on Mortgage Loans

Excel’s Depreciation Functions

Compound Interest

Growing Annuities

Adjustable Rate Mortgages (ARM’s)

Scheduling a Project

Auditing Tools, Circular References, and IF Functions 24 Topics

Expand

Lesson Content

0% Complete 0/24 Steps

Valuing an Acquisition

Combining Addresses

If Statements and Sales Commissions Calculations

If Statements and Sales Commissions Calculations Part Two

IF Statements and Option Investing

IF Statements Continued

Basic Shift Code

Rail Fence Code

Longest Winning Streak

The Ducci Sequence

The Stern Sequence

Waist to Height Ratio and Longevity

Combining the IF and IFERROR Functions

Inquire Add-In 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Introduction to the Inquire Add-In

Comparing Workbooks with the Inquire Add-In

Analyzing a Workbook

Analyzing Links Between Workbooks

Analyzing Links Between Worksheets

Analyzing Precedents and Dependents of a Given Cell

Cleaning Excess Formats

Data Tables, Goal Seek, and Scenario Manager 18 Topics

Expand

Lesson Content

0% Complete 0/18 Steps

Finding Time to Breakeven

Bill Belichick’s Bold Move

IF Statements and Data Tables

Data Table Chart

Hedge Funds

The Syracuse Problem

Relating Bond Price and Yield

Creating a Data Table Based on a Model in a Different Worksheet

Conditional Counts, Sums, Averages, and Subtotals 24 Topics

Expand

Lesson Content

0% Complete 0/24 Steps

COUNTIF and COUNTIFS Functions

COUNT, COUNTA, and COUNTBLANK Functions

SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS

SUMIFS With Duplicates

Subtotals

The SUBTOTAL Function

Using the SUBTOTAL Function to Count Cells With Colors

Sorting Subtotals

Data Consolidate

Summing Data in Different Worksheets

Showing Data in Different Worksheets Side by Side

Conditional Counts and Sums With Non-Blanks

Benford’s Law

Finding the N’th Match

Analyzing NFL Play Effectiveness

Using the SUMPRODUCT Function for Conditional Sums and Counts

How Many Winning Seasons?

Using OR Criteria with Conditional Counts, Sums, and Averages

Computing Sales

Ranked Choice Voting

Conditional Formatting and Data Validation 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

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

Using Data Validation to Prevent Entry of Duplicates

Circle Invalid Data

Highlighting New Hires

Conditional Formatting and Sports Part One

Conditional Formatting and Sports Part Two

Conditional Formatting and Fraud Detection

Tables and 3D Formulas 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Worksheet Templates

Three Dimensional Formula

Three Dimensional Formulas With Wildcards

Aggregate Function with Slicers

Conditional Formatting Tables

Copying Formulas Across a Table

The Vignere Code

Filtering Charts With Table Slicers

Pivot Tables 31 Topics

Expand

Lesson Content

0% Complete 0/31 Steps

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

Using Solve Order to Change Order Calculations

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 Congress

Pivot Tables and Dating

Simpsons Paradox

Putting it all Together

Analyzing Election Data

Editing the Pivot Table Default Settings

Using Multiple Slicers

Conditional Formatting and PivotTables

Pivot Tables and Unrecognized Dates

Summarizing Bitcoin Data

More on Calculated Fields

Using Custom Groups to Compute Net Promoter Score

Formatting a Single Pivot Table Cell and Moving a Pivot Table

Descriptive Statistics 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

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

Chinese Accidental Deaths and Histograms

Describing Baseball Salaries

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

Expand

Lesson Content

0% Complete 0/13 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

Converting ACT scores to SAT scores

Multiple Regression 19 Topics

Expand

Lesson Content

0% Complete 0/19 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

Logistic Regression for Grouped Data

The Windchill Index

Predicting Wine Prices

One Way ANOVA

Randomized Blocks

Two Way ANOVA Without Interaction

Two Way ANOVA With Interaction

ANOVA With More Than Two Factors

Probability 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

Introduction to Probability

Addition Rule of Probability

Rule of Complements

Independent Events

Conditional Probability

Total Probability

Bayes Theorem

Theoretical Basis For Naive Bayes

Calculating Likelihoods and Prior Probabilities for Naive Bayes

Using Naive Bayes to Compute Posterior Probabilities

Random Variables 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Random Variables

Binomial Random Variables

Binomial Mean Variance

The Hypergeometric Random Variable

The Negative Binomial Random Variable

Poisson Random Variables

The Exponential Random Variable

Normal Random Variables

Central Limit Theorem

Z Scores

The Weibull Random Variable

The Beta Random Variable

Analyzing a Plane Crash

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’s Little Theorem

Offset and Indirect Functions 21 Topics

Expand

Lesson Content

0% Complete 0/21 Steps

Graphing the Last 6 Months of Sales

Indirect Function

Indirect Consolidate

Using The INDIRECT Function With Spaces

Combining the INDIRECT and ADDRESS Functions

Indirect Range

Worksheet Names

Nested Drop Down

The INDIRECT Function and Stock Return Data

Income Distribution Part One

Income Distribution Part Two

One Man One Vote?

Average Points Scored

Technical Analysis

Using the INDEX Function to Return a Cell Address

Optimization and the Excel Solver 27 Topics

Expand

Lesson Content

0% Complete 0/27 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

Final Grade Point Average

Using Solver for Negotiation

Forecasting 17 Topics

Expand

Lesson Content

0% Complete 0/17 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

Using The Brier Score To Evaluate Forecasters

Using the Excel 2016 Forecast Sheet Capability

Using the Excel 2016 Forecast Functions

Forecasting Candy Bar Sales

Downloading Trend Data from Google Trends and Forecasting Future Searches

Evaluating Forecast Bias

Forecast Sheet Statistics

Charting 32 Topics

Expand

Lesson Content

0% Complete 0/32 Steps

Combo and Secondary Axis Charts

Handling Missing Data

Handling Hidden Data

Adding Pictures to Column Graphs

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

Adding a Picture to a Column Graph

Stacking Pictures in a Column Graph

Creating a Chart With a Secondary X and Y Axis

Pie Charts With a Bar Chart Breakout

Deleting Points From a Chart

Highlighting Best and Worst Months of Sales

Keystroke Shortcuts and Hyperlinks 9 Topics

Expand

Lesson Content

0% Complete 0/9 Steps

Keystroke Formatting Shortcuts

Worksheet Movement

Keystroke Formulas

Cell Selection Shortcuts

Hyperlinks

The HYPERLINK Function

Linking Excel Data

Protecting a Worksheet

Format Painter and the F4 Key

Power View 5 Topics

Expand

Lesson Content

0% Complete 0/5 Steps

Introduction to Power View

Monte Carlo Simulation 15 Topics

Expand

Lesson Content

0% Complete 0/15 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’s Ruin

Stock Problem

Winning Streak

Analyzing the Neurotic Foul Shooter

Advanced Solver 24 Topics

Expand

Lesson Content

0% Complete 0/24 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

Fast Food Scheduling

Solving Logic Puzzles Part One

Facility Layout

Solving Logic Puzzles Part Two

Pi Day Puzzle

Introduction to Neural Networks

Fitting a Neural Network

Statistical Quality Control 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

Process Capabilities

Six Sigma

P-Charts and Defective Units

X-Bar Charts and Process Control

Ranges with R-Charts

Moving Range Charts and Process Control

Deming’s Funnel

Acceptance Sampling Part One

Acceptance Sampling Part Two

The Taguchi Loss Function

Negotiation 9 Topics

Expand

Lesson Content

0% Complete 0/9 Steps

Two Person Negotiation Part One

Two Person Negotiation Part Two

Why Maximize POP (Proportion of Potential)

Store Contractor Negotiation Part One

Store Contractor Negotiation Part Two

Store Contractor Negotiation Part Three

Three Player Negotiation

Six Player Negotiation

How to Determine Reservation Levels

Advanced Charts 11 Topics

Expand

Lesson Content

0% Complete 0/11 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

Funnel Chart

Interview Questions 15 Topics

Expand

Lesson Content

0% Complete 0/15 Steps

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

Putting Data in One Row Part One

Putting Data in One Row Part Two

Summarizing Trends in the Summer Olympics

Formulas for Microsoft Competition

More Fun with Gymnastic Formulas

Using PivotTables to Analyze a 4x400M Relay

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

Expand

Lesson Content

0% Complete 0/48 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

Transportation

The Assignment Problem

The Shortest Route Problem

The Transshipment Problem

Network Flow Models

Locating One Warehouse

Locating Two Warehouses

Finding Distances Between 100 Cities

Locating Four Warehouses

Assigning Workers to Jobs Part One

Assigning Workers to Jobs Part Two

Scheduling John Deere

Traveling Salesperson

The Vehicle Routing Problem

Vehicle Routing with Time Windows

Jobshop Scheduling

The Toyota Manufacturing Sequence Algorithm

Queuing

Inventory Turns

Resolving Process Bottlenecks

Introduction to Monte Carlo Simulation

The RAND and RANDBETWEEN Functions

Simulating a Discrete Random Variable

Simulating a Normal Random Variable

The Newsperson Problem

Facility Layout

Location and Warehouse Shipments

Maximizing Shipments Close to a Warehouse

How Many Warehouses are Needed to Provide Adequate Service?

Solving Warehouse Location Problems With Limited Warehouse Capacity

Supplier Selection

Dynamic Capacity Planning

Estimating the Cost of Adding Capacity

Assembly Line Balancing

Flexible Capacity

Project Management

The Math Behind The Goal

The Bullwhip Effect

Estimating Censored Demand

Office 365 Functions 6 Topics

Expand

Lesson Content

0% Complete 0/6 Steps

The TEXTJOIN Function

The MAXIFS and MINIFS Functions

The IFS Function

The SWITCH Function

Downloading Currency and Cryptocurrency Data

Creating Custom Functions With Lambda!

Power Pivot 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Introduction to PowerPivot

Creating PivotTables from a Database

DAX Functions

The DISTINCTCOUNT Function

Comparing Forecasted Sales to Actual Sales

The RELATED DAX Function

Pivot Tables Based on Power Pivot Calculated Columns

Calculated Measures

KPI (Key Performance Indicators)

Using Power Pivot to Draw a Random Sample

Using Power Pivot to Sample From a Subset of a Database

Median and Date Intelligence DAX functions

Intelligence Functions Part Two

Filled and 3D Maps 6 Topics

Expand

Lesson Content

0% Complete 0/6 Steps

World Filled Maps

US Filled Maps

Building a 3D Energy Map

3D Time Animation

Mapping the 2016 US Election

Mapping NYC Subway Lines

Dynamic Arrays 11 Topics

Expand

Lesson Content

0% Complete 0/11 Steps

The Unique Function

The UNIQUE Function and Excel Tables

The SORT and SORTBY Functions

The SORT and SORTBY Functions With Ranges and Tables

The FILTER Function

The SEQUENCE Function

The RANDARRAY Function

Referring to Dynamic Arrays in Other Formulas

Using Dynamic Arrays to Create Dynamic Dropdown Boxes

Using Dynamic Arrays to Emulate a Pivot Table

The STOCKHISTORY Function

Data Types 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Introduction to Geography Data Types

Geography Types and Excel Tables

The FIELDVALUE Function

Resolving Ambiguity

Stock Data Types

New Excel Data Types That Will Wow You

Getting Historical Weather Data Into Excel

Power Query 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Downloading Bitcoin Prices and Volume

Downloading the Population of US Cities

Flattening a Table

Turning a Flat File Into a Table

Using a Formula to Add a Column

How to Append Queries

Combining All Workbooks in a Folder

Filling Blanks in a Column

Transforming Data Using Delimiters

Grouping Data with Power Query

Using Power Query to Summarize Data Across Multiple Columns

Creating Custom Columns

Pivoting Columns with Power Query

Full Course Certificate Assessment

Complete Microsoft Excel Training Course

Course Content

About Instructor

Wayne Winston

Course Includes

CONTACT INFO

CONTACT INFO