Advanced Excel and Analytics Course - Excel With Wayne

My Total Course Time:

My Course completion time:

Course Overview:If you have been using Excel for a while and struggled to accomplish some things your job requires and/or you want to learn a lot about the “analytics revolution”, then this course is for you. We cover advanced functions such as GETPIVOTDATA, OFFSET, and the INDIRECT function.

With regards to analytics, we cover all Excel statistical functions, the Excel Solver including the advanced Evolutionary Solver and GRG Multistart Engines, Monte Carlo Simulation, Multiple Regression, Logistic Regression, Classification, and Forecasting.

Overview:

26 Hours
500+ Files
Nearly 250 videos
Includes content from 20+ other courses
Syllabus

Who Will Benefit?
Any professional that uses Microsoft Excel and wants to become more efficient with their time.

Course Content

Expand All

OVERVIEW

Sample Lessons Sample Lesson

COURSE CONTENT

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

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

Circular Reference

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

One Way Data Table

Two Way Data Table

Mortgage Data Table

Goal Seek Finance

Data Table Chart

Conditional Counts and Sums 11 Topics

Expand

Lesson Content

0% Complete 0/11 Steps

SUMIFS With Duplicates

Data Consolidate

Database Functions

Computed Criteria

Advanced Filter

Conditional Counts and Sums With Non-Blanks

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

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

Tables Introduction

Tables Extended

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

Analyzing Surveys and More Pivot Charts

Creating Pivot Tables From Multiple Ranges

Multiple Pivot Tables

Pivot Tables and the NBA Part One

Pivot Tables and the NBA Part Two

Putting it all Together

Descriptive Statistics 7 Topics

Expand

Lesson Content

0% Complete 0/7 Steps

Histogram Creation

Histogram Shapes

Descriptive Statistics

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

Estimating the Beta of a Stock

Regression to the Mean

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

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

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

Linest Function

Offset and Indirect Functions 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Offset Function

Left Hand Lookup

Most Recent Sales

Graphing the Last 6 Months of Sales

Indirect Function

Indirect Consolidate

Worksheet Names

Nested Drop Down

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

Capital Budgeting

Fantasy Sports Part One

Fantasy Sports Part Two

Financial Solver

NFL Point Spreads

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

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

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

Power View Tiles

Power View Multiples

Power View Movies

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

The Newsperson Problem

Poker Simulation

Simulating the NBA Finals

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

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

Supply Chain 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

Inventory on Time

Inventory Modeling With Shortages

Aggregate Planning

Economic Order Quantity

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

Inventory Turns

About Instructor

Wayne Winston

7 Courses

+12 enrolled

Not Enrolled

Take this Course

$147.00

Course Includes

25 Lessons
246 Topics

Excel With Wayne is an online Microsoft Excel Training Course taught by Dr. Wayne Winston. Dr. Winston is the world’s foremost Microsoft Excel Expert that teaches at some of the world’s largest organizations including Microsoft and government organizations such as the Navy and Army.

CONTACT INFO

Copyright © 2023 | Excel With Wayne