Get $1 credit for every $25 spent!

The A to Z Microsoft Excel Certification Training Bundle

Ending In:
Add to Cart - $49
Add to Cart ($49)
$1,600
96% off
wishlist
(80)
Courses
8
Lessons
688
Enrolled
800

What's Included

Product Details

Access
Lifetime
Content
6 hours
Lessons
101

Microsoft Excel: Advanced Formulas & Functions

Master 75+ Excel Formula & Functions with Hands-On Demos

By Excel Maven | in Online Courses

It's time to show Excel who's boss! Whether you're starting from square one or aspiring to become an absolute Excel wizard, this is the right place for you. This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Through hands-on, contextual examples, you'll learn why these formulas are awesome and how they can be applied in a number of ways. Upon completion of this course, and all courses included in the bundle, you'll also receive a certification of completion validating your new skills! This is especially useful for including in your portfolio or resume, so future employers can feel confident in your skill set.

  • Access 101 lectures & 6 hours of content 24/7
  • Easily build dynamic tools & Excel dashboards to filter, display, & analyze your data
  • Go rogue & design your own formula-based formatting rules
  • Join datasets from multiple sources in seconds w/ lookup, index, & match functions
  • Pull real-time data from APIs directly into Excel
  • Manipulate dates, times, text, & arrays
  • Automate tedious & time-consuming analytics tasks using cell formulas and functions in Excel
  • Includes a certification of completion

Instructor

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Getting Started
    • Course Structure & Outline - 4:12
    • Downloading the Homework Exercises - 1:42
  • Excel Formulas 101
    • Introduction - 1:06
    • The Formula Library & Auditing Tools - 4:37
    • DEMO: Evaluate Formula - 1:25
    • Basic Formula Syntax - 3:31
    • Fixed & Relative Cell References - 4:57
    • Common Errors & IFERROR - 7:25
    • Function, CTRL & ALT Shortcuts - 6:36
    • BONUS: Mac Shortcuts - 2:08
    • Data Validation Rules - 3:28
    • Fixed vs. Volatile Functions - 3:21
    • QUIZ: Formulas 101
    • HOMEWORK: Formulas 101 - 0:46
  • Conditional Statements & Logical Operators
    • Introduction - 1:23
    • Anatomy of the IF Statement - 4:01
    • Nested IF Statements - 4:53
    • AND/OR Operators - 8:47
    • NOT (<>) Operators - 3:17
    • Addressing Errors with IFERROR - 4:03
    • Common IS Statements - 4:00
    • QUIZ: Conditional Statements & Logical Operators
    • HOMEWORK: Conditional Statements & Logical Operators - 0:44
  • Statistical Functions
    • Introduction - 1:12
    • Basic Stats Functions - 5:17
    • SMALL/LARGE & RANK/PERCENTRANK - 6:19
    • RAND() & RANDBETWEEN - 2:12
    • The SUMPRODUCT Function - 3:55
    • COUNTIFS/SUMIFS/AVERAGEIFS - 4:49
    • DEMO: Basic Dashboards with Stats Functions - 8:09
    • QUIZ: Statistical Functions
    • HOMEWORK: Statistical Functions - 0:35
  • Lookup & Reference Functions
    • Introduction - 1:23
    • Named Arrays - 3:30
    • ROW/ROWS & COLUMN/COLUMNS - 3:18
    • VLOOKUP & HLOOKUP - 5:54
    • DEMO: Joining Data with VLOOKUP - 6:23
    • Combining IFERROR & VLOOKUP - 4:30
    • VLOOKUP Range Options - 6:18
    • The INDEX Function - 1:59
    • The MATCH Function - 2:32
    • Combining INDEX & MATCH - 6:08
    • Combining MATCH & VLOOKUP - 4:47
    • UPDATE: VLOOKUP Correction - 5:05
    • The OFFSET Function - 2:05
    • Combining OFFSET & COUNTA - 2:58
    • DEMO: Building a Scrolling Chart with OFFSET - 9:51
    • QUIZ: Lookup & Reference Functions
    • HOMEWORK: Lookup & Reference Functions - 0:54
  • Text Functions
    • Introduction - 0:57
    • UPPER, LOWER, PROPER & TRIM - 3:32
    • CONCATENATE (&) - 3:26
    • LEFT, MID, RIGHT & LEN - 3:31
    • TEXT & VALUE - 4:20
    • SEARCH & FIND - 5:04
    • Categorizing Data with IF(ISNUMBER(SEARCH)) - 5:39
    • Combining RIGHT, LEN & SEARCH - 5:43
    • The SUBSTITUTE Function - 2:21
    • QUIZ: Text Functions
    • HOMEWORK: Text Functions - 0:59
  • Date & Time Functions
    • Introduction - 2:13
    • Understanding DATEVALUE - 5:05
    • Date Formatting & Fill Series - 3:39
    • TODAY() & NOW() - 2:16
    • YEAR/MONTH/DAY & HOUR/MINUTE/SECOND - 3:04
    • The EOMONTH Function - 6:17
    • The YEARFRAC Function - 2:27
    • WEEKDAY, WORKDAY & NETWORKDAYS - 7:24
    • The DATEDIF Function - 4:23
    • DEMO: Budget Pacing Tool - 11:07
    • QUIZ: Date & Time Functions
    • HOMEWORK: Date & Time Functions - 0:35
  • Formula-Based Formatting
    • Introduction - 1:30
    • Creating and Managing Formula-Based Rules - 4:39
    • DEMO: Highlighting Rows with MOD - 3:50
    • DEMO: Formatting Cells Based on Values - 3:29
    • DEMO: Formatting Cells with Stats Functions - 5:28
    • DEMO: Formatting Cells with Text & Conditional Functions - 5:08
    • QUIZ: Formula-Based Formatting
    • HOMEWORK: Formula-Based Formatting - 0:34
  • Basic Array Formulas
    • Introduction - 1:26
    • Rules of Array Formulas - 3:24
    • Pros & Cons of Array Formulas - 2:15
    • Vertical, Horizontal & 2-D Array Constants - 6:26
    • Using Array Constants in Formulas - 3:38
    • Named Array Constants - 4:31
    • The TRANSPOSE Function - 4:21
    • Linking Data: Array vs. Non-Array Comparison - 2:36
    • DEMO: Returning the "X" Largest Values - 3:09
    • DEMO: Counting Characters Across Cells - 1:52
    • DEMO: Creating a "MAX IF" Function (Part 1) - 3:09
    • DEMO: Creating a "MAX IF" Function (Part 2) - 6:54
    • The Double Unary Operator ("--") - 3:49
    • QUIZ: Array Formulas
    • HOMEWORK: Array Formulas - 2:09
  • Badass Bonus Functions
    • Introduction - 1:41
    • The INDIRECT Function - 10:55
    • The HYPERLINK Function - 6:19
    • Real-Time Data with WEBSERVICE & FILTERXML - 7:16
    • QUIZ: Badass Bonus Functions
  • Wrapping Up
    • Resources & Next Steps - 0:41

View Full Curriculum


Access
Lifetime
Content
4 hours
Lessons
61

Microsoft Excel: Data Visualization with Excel Charts & Graphs

Master 20+ Charts & Graphs in Excel 2016 with Hands-On Demos

By Excel Maven | in Online Courses

Ask people what comes to mind when they think of Excel and odds are they'll say, "spreadsheets". However, Excel is an incredibly powerful, robust, and dynamic data visualization platform. This course will give you a deep, comprehensive understanding of Excel's latest data visualization tools and techniques. You'll learn when, why, and how to use each chart type, discover key data visualization best practices, and learn through interactive, hands-on demos and exercises every step of the way.

  • Access 61 lectures & 4 hours of content 24/7
  • Explore each of the 20+ chart types that Excel 2016 has to offer
  • Dive into a series of 12+ advanced Excel demos guaranteed to turn you into a data visualization pro

Instructor

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Getting Started
    • Course Structure & Outline - 2:03
    • Bonus Homework Exercises
  • Data Visualization Best Practices
    • Key Principles & The 10-Second Rule - 2:45
    • The Good, The Bad & The Ugly - 3:44
    • Three Key Questions - 1:49
  • Chart Customization
    • Setting Expectations - 1:02
    • Chart Elements, Layouts & Styles - 6:20
    • Chart Formatting Options - 5:26
    • Changing Chart Types & Adding a Secondary Axis - 3:03
    • Creating, Modifying & Applying Custom Templates - 4:06
    • QUIZ: Chart Customization
  • Mastering Basic Charts & Graphs
    • Bar & Column Charts - 8:49
    • HOMEWORK: Bar & Column Charts
    • Histogram & Pareto Charts - 5:58
    • HOMEWORK: Histogram & Pareto Charts
    • Line Charts & Trendlines - 5:16
    • HOMEWORK: Line Charts & Trendlines
    • Area Charts - 4:41
    • HOMEWORK: Area Charts
    • Pies, Donuts & Race Tracks - 12:04
    • HOMEWORK: Pies, Donuts & Race Tracks
    • Scatter Plots - 7:30
    • Bubble Charts - 6:36
    • HOMEWORK: Scatter Plots & Bubble Charts
    • Box & Whisker Charts - 6:05
    • HOMEWORK: Box & Whisker Charts
    • Tree Maps & Sunbursts - 6:36
    • HOMEWORK: Tree Maps & Sunbursts
    • Waterfall Charts - 3:17
    • Funnel Charts - 3:42
    • HOMEWORK: Waterfall & Funnel Charts
    • Radar Charts - 7:56
    • HOMEWORK: Radar Charts
    • Stock Charts - 7:18
    • HOMEWORK: Stock Charts
    • Heat Maps - 4:41
    • HOMEWORK: Heat Maps
    • Surface & Contour Charts - 7:35
    • HOMEWORK: Surface & Contour Charts
    • Geospatial Maps with Power Map - 5:28
    • HOMEWORK: Power Map
    • Basic Combo Charts - 7:57
    • HOMEWORK: Combo Charts
    • Sparklines - 2:07
    • HOMEWORK: Sparklines
    • QUIZ: Basic Charts & Graphs
  • Next-Level Data Viz Demos
    • Setting Expectations - 1:40
    • DEMO: Custom Image Overlay Charts - 6:01
    • DEMO: Adding Binary Values to Highlight Ranges - 5:02
    • DEMO: Automation with OFFSET & COUNTA - 7:08
    • DEMO: Adding Interactive Elements with Form Controls - 12:30
    • DEMO: Animating Changes Over Time - 14:20
    • DEMO: Building a Dynamic Dashboard (Part 1) - 16:10
    • DEMO: Building a Dynamic Dashboard (Part 2) - 12:08
    • DEMO: Dynamic Value-Based Formatting - 10:09
    • DEMO: Dynamically Highlighting a Data Series - 10:26
    • DEMO: Building a Custom Pacing Chart - 10:22
    • DEMO: Building a Custom Gauge Chart - 9:58
    • DEMO: Visualizing Percentages with Arrays - 7:16
    • QUIZ: Next-Level Data Viz
  • Wrapping Up
    • Resources & Next Steps - 2:38

View Full Curriculum


Access
Lifetime
Content
7 hours
Lessons
90

Microsoft Excel: Intro to Power Query, Power Pivot & DAX

Get Up & Running with Excel's Data Modeling & Business Intelligence Tools

By Excel Maven | in Online Courses

This course will introduce you to Microsoft Excel's powerful data modeling and business tools Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're looking to become a power Excel user and supercharge your analytics, then this is the A-Z guide that you're looking for.

  • Access 90 lectures & 7 hours of content 24/7
  • Use sample data from a fictional supermarket chain to get hands-on w/ Power Query
  • Practice shaping, blending, & exploring project files
  • Cover the fundamentals of database design & normalization
  • Explore & analyze your data model using Power Pivot & DAX
  • Cover basic DAX syntax & learn some of the most powerful and commonly used functions

Instructor

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:47
    • IMPORTANT: Versions & Compatibility - 3:15
    • Exploring the Course Project Files - 1:16
    • Setting Expectations - 1:53
  • Intro to "Power Excel"
    • The "Power Excel" Workflow - 3:50
    • "Best Thing to Happen to Excel in 20 Years" - 8:18
    • When to use Power Query & Power Pivot - 0:44
    • QUIZ: Intro to "Power Excel"
    • HOMEWORK: Intro to "Power Excel"
  • Power Query
    • Introduction - 0:27
    • Meet Power Query (aka "Get & Transform") - 2:40
    • The Query Editor - 3:17
    • Data Loading Options - 1:40
    • IMPORTANT: Regional Date Formatting
    • Basic Table Transformations - 8:06
    • Text-Specific Tools - 10:17
    • Number-Specific Tools - 7:41
    • Date-Specific Tools - 7:39
    • PRO TIP: Creating a Rolling Calendar - 5:02
    • Adding Index & Conditional Columns - 7:44
    • Grouping & Aggregating Data - 7:10
    • Pivoting & Unpivoting - 8:52
    • Modifying Workbook Queries - 4:18
    • Merging Queries - 7:43
    • Appending Queries - 5:49
    • Connecting to a Folder of Files - 9:32
    • Power Query Best Practices - 2:44
    • QUIZ: Power Query
    • HOMEWORK: Power Query
  • Data Modeling 101
    • Introduction - 0:28
    • Meet Excel's "Data Model" - 3:38
    • Data vs. Diagram View - 2:33
    • Database Normalization - 4:14
    • Data Tables vs. Lookup Tables - 5:40
    • Relationships vs. Merged Tables - 2:07
    • Creating Table Relationships - 6:54
    • Modifying Table Relationships - 2:06
    • Active vs. Inactive Relationships - 3:55
    • Relationship Cardinality - 5:46
    • Connecting Multiple Data Tables - 5:59
    • Filter Direction - 5:08
    • Hiding Fields from Client Tools - 4:59
    • Defining Hierarchies - 3:53
    • Data Model Best Practices - 1:38
    • QUIZ: Data Modeling 101
    • HOMEWORK: Data Modeling 101
  • Power Pivot & DAX 101
    • Introduction - 0:29
    • Creating a "Power" PivotTable - 5:27
    • "Power" Pivots vs. "Normal" Pivots - 2:48
    • Intro to Data Analysis Expressions (DAX) - 1:12
    • Calculated Columns - 8:39
    • DAX Measures = Supercharged Calculated Fields - 2:24
    • Creating Implicit Measures - 1:59
    • Creating Explicit Measures (AutoSum) - 7:08
    • Creating Explicit Measures (Power Pivot) - 7:42
    • Understanding Filter Context - 9:18
    • Step-by-Step Measure Calculation - 12:06
    • RECAP: Calculated Columns vs. Measures - 2:29
    • Power Pivot Best Practices - 1:50
    • QUIZ: Power Pivot & DAX 101
    • HOMEWORK: Power Pivot & DAX 101
  • Common DAX Functions
    • Introduction - 0:27
    • DAX Formula Syntax & Operators - 4:05
    • Common DAX Function Categories - 3:54
    • Basic Math & Stats Functions - 12:45
    • COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS - 10:35
    • HOMEWORK: Math & Stats Functions
    • Logical Functions (IF/AND/OR) - 16:49
    • SWITCH & SWITCH(TRUE) - 10:33
    • Text Functions - 10:53
    • HOMEWORK: Logical & Text Functions
    • The CALCULATE Function - 14:39
    • Adding Filter Context with FILTER (Part 1) - 9:20
    • Adding Filter Context with FILTER (Part 2) - 14:23
    • Removing Filter Context with ALL - 9:06
    • HOMEWORK: CALCULATE, FILTER & ALL
    • Joining Data with RELATED - 3:39
    • Iterator ("X") Functions: SUMX - 12:20
    • Iterator ("X") Functions: RANKX - 5:33
    • HOMEWORK: Iterator Functions
    • Basic Date & Time Functions - 4:58
    • Time Intelligence Formulas - 17:02
    • HOMEWORK: Time Intelligence
    • Speed & Performance Considerations - 3:02
    • DAX Best Practices - 2:03
    • QUIZ: Common DAX Functions
  • Wrapping Up
    • Introduction - 0:53
    • Data Visualization Options - 4:35
    • SNEAK PEEK: PowerBI - 5:55
    • Resources & Next Steps - 2:25

View Full Curriculum


Access
Lifetime
Content
5 hours
Lessons
83

Microsoft Excel: Data Analysis with Excel Pivot Tables

Master Excel Pivot Tables & Pivot Charts with Hands-On Demos

By Excel Maven | in Online Courses

Pivot Tables are an essential tool for anyone who works with data in Excel. Pivots allow you to explore and analyze raw data quickly, revealing powerful insights and trends otherwise buried in the noise. In other words, they give you answers. Whether you're exploring product sales, analyzing marketing tactics, or wonder how Boston condo prices have trended over the past 15 years, Excel Pivot Tables provide fast, accurate, and intuitive solutions to even the most complicated questions. This course will give you a deep, 100% comprehensive understanding of Pivot Tables and Pivot Charts.

  • Access 83 lectures & 5 hours of content 24/7
  • Cover raw data structure, table layouts & styles
  • Explore design & formatting options
  • Understand sorting, filtering, & grouping tools
  • Anaylze data from a number of real-world case studies including San Diego burrito ratings, shark attack records, MLB team statistics, & more

Instructor

Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.

He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Getting Started
    • Course Structure & Outline - 3:26
    • Getting to Know the IMDb Movie Database - 2:21
    • Setting Expectations - 1:14
  • PivotTable 101
    • Why PivotTables? - 4:10
    • Structuring the Source Data - 2:00
    • Inserting Your First PivotTable - 3:13
    • Navigating the Field List - 7:43
    • Analyze & Design Options - 1:33
    • Selecting, Clearing, Moving & Copying Pivots - 5:14
    • Refreshing & Updating Pivots - 7:30
    • PRO TIP: Dealing with Growing Source Data - 6:49
    • How PivotTables ACTUALLY Work - 3:39
    • QUIZ: PivotTable 101
    • HOMEWORK: PivotTable 101
  • PivotTable Formatting
    • Number Formatting - 2:12
    • PRO TIP: Formatting Empty Cells - 1:41
    • Table Layouts & Styles - 5:30
    • PRO TIP: Using Tabular Layouts to Create New Tables - 2:20
    • Customizing Headers & Labels - 1:16
    • Adding Conditional Formats - 6:51
    • PRO TIP: Data Bars with Invisible Text - 2:13
    • QUIZ: PivotTable Formatting
    • HOMEWORK: PivotTable Formatting
  • Sorting, Filtering & Grouping
    • Basic Sorting Options - 5:01
    • PRO TIP: Incorrect Alphabetical Sorting - 2:01
    • Label Filters & Manual Selections - 8:15
    • PRO TIP: Label Filters with Wildcards - 3:15
    • Value Filters - 3:10
    • PRO TIP: Enabling Multiple Filters - 2:42
    • Grouping Data - 3:23
    • Automatic Date Grouping - 4:25
    • Filtering with Slicers & Timelines - 4:51
    • Breaking Out Report Filter Pages - 4:13
    • QUIZ: Sorting, Filtering & Grouping
    • HOMEWORK: Sorting, Filtering & Grouping
  • Calculated Values & Fields
    • Summarizing Values - 4:45
    • PRO TIP: Avoiding the "Count Of" Trap - 3:49
    • "Show Values As" Calculations - 3:35
    • Show Values As: % of Column/Row - 3:47
    • Show Values As: % of Parent - 3:02
    • Show Values As: Difference From - 4:41
    • Show Values As: Running Total - 2:44
    • Show Values As: Rank - 2:36
    • Show Values As: Index - 8:06
    • Inserting Calculated Fields - 6:30
    • Calculations in Pivots vs. Raw Data - 4:32
    • Calculating Using Counts (Part 1) - 7:20
    • Calculating Using Counts (Part 2) - 4:19
    • Calculated Items (NOT RECOMMENDED) - 6:11
    • Solve Order & List Formula Tools - 4:54
    • QUIZ: Calculated Values & Fields
    • HOMEWORK: Calculated Values & Fields
  • PivotCharts
    • Intro to PivotCharts - 2:26
    • DEMO: Column Chart - 4:13
    • DEMO: Pie & Donut Charts - 5:46
    • DEMO: Clustered Bar Chart - 3:25
    • PRO TIP: Prevent Charts from Resizing with Cells - 4:45
    • Changing Chart Types on the Fly - 3:18
    • DEMO: Stacked Area Chart - 5:46
    • PivotChart Layouts & Styles - 4:03
    • Moving PivotCharts to New Sheets - 2:09
    • Applying Slicers & Timelines to Multiple Charts - 5:01
    • DEMO: Building a Dynamic Dashboard - 13:22
    • QUIZ: PivotCharts
    • HOMEWORK: PivotCharts
  • PivotTable Case Studies
    • Setting Expectations - 1:45
    • U.S. Voters (2012) - 10:33
    • HOMEWORK: U.S. Voters (2012)
    • San Francisco Salaries - 13:06
    • HOMEWORK: San Francisco Salaries
    • Shark Attack Records - 10:31
    • HOMEWORK: Shark Attack Records
    • Stock Market Data - 12:25
    • HOMEWORK: Stock Market Data
    • Baseball Team Statistics - 14:16
    • HOMEWORK: Baseball Team Statistics
    • San Diego Burrito Ratings - 16:40
    • HOMEWORK: San Diego Burrito Ratings
    • Daily Weather Conditions - 12:41
    • HOMEWORK: Daily Weather Conditions
    • Spartan Race Facebook Posts - 14:37
    • HOMEWORK: Spartan Race Facebook Posts
  • Wrapping Up
    • Resources & Next Steps - 2:27

View Full Curriculum


Access
Lifetime
Content
1 hours
Lessons
11

Create Data Models & Relationships in Excel

Develop Your Skills & Knowledge On How to Create Data Models for Your Business

By GreyCampus | in Online Courses

This course enables you to develop your own data models to gather accurate, complete and timely reports from your enterprise system. Whether you are new to Excel or an advanced user, this fast-paced course will cover what you need to know to become an Excel Power BI user who can work with data like the best of them.

  • Access 11 lectures & 1 hour of content 24/7
  • Learn how to publish Excel Spreadsheets using SharePoint
  • Understand ER diagrams
  • Design a layout for an Excel-based data model
  • Discover how to use a Power Query to create data models
  • Explore Power Pivot, design & manage the newly built data model
  • Develop Interactive Dashboards using Power Views

Instructor

GreyCampus transforms careers through skills and certification training. We are a leading provider of training for working professionals in the areas of Project Management, Big Data, Data Science, Service Management and Quality Management. We offer live-online (instructor-led online), classroom (instructor-led classroom) and e-learning (online self-learning ) courses. Our growing suite of accredited courses is constantly upgraded to address the career enhancement goals of working professionals.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Introduction
    • Introduction - 3:26
  • Publishing Excel Spreadsheets using SharePoint
    • Publishing Excel Spreadsheets using SharePoint - 10:13
  • Study the existing ER diagram, metadata of the existing database schema
    • Study the existing ER diagram metadata of the existing database schema-HD - 8:38
  • Identify cardinality and design a layout for Excel based data model
    • Identify cardinality and design a layout for Excel based - 19:15
  • Using Power Query to create data models
    • Using Power Query to create data models - 13:20
  • Using Power Pivot, design and manage the newly built data model
    • Using Power Pivot, design and manage the newly built - 8:04
  • Define entity relationships and check cardinality
    • Define entity relationships and check cardinality - 9:20
  • Develop Interactive Dashboards using Power Views
    • Develop Interactive Dashboards using Power Views - 9:42
  • Using Timelines and Tab views for Dashboards
    • Using timelines Part 1 - 16:16
    • Using timelines Part 2 - 12:13
  • Mock Test
    • Mock Test

View Full Curriculum


Access
Lifetime
Content
6 hours
Lessons
96

Microsoft Excel 2016 Master Class: Beginner to Advanced

Learn Today's Most Important Office Skill

By Joe Parys | in Online Courses

In today's workplace, knowing how to use Excel is the number one skill that everyone needs to have. It's easy to get started with Excel but it's a lot tougher to become an expert. This course will help you go from beginner to expert in the most efficient way possible.

  • Access 96 lectures & 6 hours of content 24/7
  • Learn new Excel techniques by performing them yourself
  • Become an effective Excel expert fast

Instructor

Joe Parys is a certified life coach, professional motivational speaker, entrepreneur, licensed psychology teacher and basketball coach. His main areas of expertise are teaching, coaching, public speaking, personal development, personal transformation, the human mind, maximizing human potential, motivation and goal setting.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Section 2: - Worksheet Basics
    • S2L1 - Excel 2016 Parts of the Screen - 5:48
    • S2L2 - Excel 2016 Using the Worksheet
    • S2L3 - Ecxel 2016 Navigating Worksheets - 4:40
    • S2L4 - Excel 2016 Multiple Worksheets - 6:46
    • S2L5 - Excel 2016 The Ribbon - 7:21
    • S2L6 - Excel 2016 Saving and Opening Files - 6:30
  • Section 3: Manipulating Columns and Rows
    • S3L2 - Excel 2016 Insert and Delete Columns and Rows - 6:19
    • S3L1 - Excel 2016 Resizing Columns and Rows - 7:28
  • Section 4: Entering Data
    • S4L1 - Excel 2016 Entering Text - 6:33
    • S4L2 - Excel 2016 Editing Data - 5:24
    • S4L3 - Excel 2016 Undo Changes
    • S4L4 - Excel 2016 Excel Data Entry Helps - 4:01
    • S4L5 - Excel 2016 Entering Numbers
    • S4L6 - Excel 2016 Entering Dates - 5:08
    • S4L7 - Excel 2016 Entering Symbols - 4:57
  • Section 5: Selecting Cells and Ranges
    • S5L1 - Excel 2016 Selecting With Mouse
    • S5L2 - Excel 2016 Selecting With Keyboard - 4:34
    • S5L3 - Excel 2016 The Extend Key - 3:00
  • Section 6: Formulas
    • S6L1 - Excel 2016 Basic Formulas - 7:30
    • S6L2 - Excel 2016 Rule of Order - 2:42
    • S6L3 - Excel 2016 Formulas on Dates
    • S6L4 - Excel 2016 Formulas with Text - 4:55
    • S6L5 - Excel 2016 Multi Sheet Formulas - 3:31
  • Section 7: Copying and Moving
    • S7L1 - Excel 2016 Copying Cells or Ranges - 5:44
    • S7L2 - Excel 2016 Move Cells or Ranges - 3:08
    • S7L3 - Excel 2016 Copy Move Columns or Rows
    • S7L4 - Excel 2016 Copying Formulas
    • S7L5 - Excel 2016 Using the Clipboard Pane - 4:14
    • S7L6 - Excel 2016 Move or Copy Sheets - 4:30
  • Section 8: The Fill Handle
    • S8L1 - Excel 2016 Fill Handle Basics - 4:46
    • S8L2 - Excel 2016 Fill Series - 5:03
    • S8L3 - Excel 2016 Custom Fill Lists - 4:30
  • Section 9: Naming Ranges
    • S9L1 - Excel 2016 Overview of Naming Ranges - 3:36
    • S9L2 - Excel 2016 Multi Sheet Range Names - 6:40
    • S9L3 - Excel 2016 Using Rang Names Like Bookmarks - 2:43
  • Section 10: Functions
    • S10L1 - Excel 2016 SUM Function - 7:22
    • S10L2 - Excel 2016 Quick Analysis - 5:09
    • S10L3 - Excel 2016 The If Function - 10:27
    • S10L4 - Excel 2016 If Multiple Criteria
    • S10L5 - Excel 2016 Vlookup1 - 7:19
    • S10L6 - Excel 2016 Vlookup2 - 2:22
    • S10L7 - Excel 2016 Data Validation - 6:34
    • S10L8 - Excel 2016 Multiple VLookups
    • S10L9 - Excel 2016 More Functions
    • S10L10 - Excel 2016 Proper Round and Other Functions - 5:36
  • Section 11: Formatting Worksheet Contents
    • S11L1 - Excel 2016 Basic Formatting - 5:19
    • S11L2 - Background Fill Patterns and Effects - 4:30
    • S11L3 - Excel 2016 Aligning Cell Contents - 5:38
    • S11L4 - Excel 2016 Borders - 2:40
    • S11L5 - Excel 2016 Formatting Numbers - 3:37
    • S11L6 - Excel 2016 Custom Number Formats - 5:00
    • S11L7 - Excel 2016 Custom Number Format Exercise
    • S11L8 - Excel 2016 Date Formats - 4:27
    • S11L9 - Excel 2016 Time Formats
    • S11L10 - Excel 2016 Conditional Formatting 1 - 4:46
    • S11L11 - Excel 2016 Conditional Formatting 2 - 6:22
  • Section 13: Bringing it All Together Print Exercise
    • S13L1 - Excel 2016 Start and Finish - 6:04
    • S13L2 - Excel 2016 Format and Quick Analysis - 4:44
    • S13L3 - Excel 2016 Print Area and Titles - 4:40
    • S13L4 - Excel 2016 6 Month Totals - 3:46
    • S13L5 - Excel 2016 Modify Print Titles Header Footer - 6:16
    • S13L6 - Excel 2016 Final Adjustments
  • Section 14: Payment Function and What If Analysis
    • S14L1 - Excel 2016 Using the PMT Function - 3:14
    • S14L2 - Excel 2016 Using Goal Seek - 3:32
    • S14L3 - Excel 2016 Using Scenario Manager - 5:35
    • S14L4 - Excel 2016 Using Data Table - 5:01
  • Section 15: Finding and Fixing Errors
    • S15L1 - Excel 2016 Using Error Checking - 6:00
    • S15L2 - Excel 2016 Fixing Circular References - 2:02
    • S15L3 - Excel 2016 Tracing Precedents and Dependents - 2:53
    • S15L4 - Excel 2016 Evaluate Formulas - 4:47
  • Section 16: More on Working With Tables
    • S16L1 - Excel 2016 Sorting Tables - 3:28
    • S16L2 - Excel 2016 Creating Subtotals - 5:59
    • S16L3 - Excel 2016 Adding Records to a Table - 4:54
    • S16L4 - Excel 2016 Using a Form for Data Entry - 5:13
    • S16L5 - Excel 2016 Removing Duplicates - 4:17
  • Section 17: Protecting Your Work
    • S17L1 - Excel 2016 Protect Sheet - 4:44
    • S17L2 - Excel 2016 Highlight Unlocked Cells - 2:45
    • S17L3 - Excel 2016 Allow Commands
    • S17L4 - Excel 2016 Protect Workbook Structure - 1:59
    • S17L5 - Excel 2016 Share and Mark as Final - 3:56
    • S17L6 - Excel 2016 Protect Workbook - 2:17
  • Section 18: Pivot Tables
    • S18L1 - Excel 2016 Create a Pivot Table - 4:45
    • S18L2 - Excel 2016 Multiple Rows and Columns - 3:45
    • S18L3 - Excel 2016 Printing Pivot Tables - 3:34
    • S18L4 - Excel 2016 Change Display Totals - 6:17
    • S18L5 - Excel 2016 Pivot Table Filters - 4:17
    • S18L6 - Excel 2016 Filtering Pivot Tables with Timelines - 3:52
    • S18L7 - Excel 2016 Changing Pivot Table Appearance - 5:40
    • S18L8 - Excel 2016 Recommended Pivot Tables - 4:22
  • Section 19: Viewing Large Worksheets and Multiple Workbooks
    • S19L1 - Excel 2016 Freezing Panes and Split - 4:05
    • S19L2 - Excel 2016 Using the Watch Window - 2:01
    • S19L3 - Excel 2016 Viewing Multiple Workbooks - 6:40
  • Section 20: Working with Charts
    • S20L1 - Excel 2016 Prep Data and Make Chart - 5:55
    • S20L2 - Excel 2016 Formatting Chart 1 - 5:54
    • S20L3 - Excel 2016 Formatting Chart 2 - 8:01
    • S20L4 - Excel 2016 Sunburst and Treemap - 5:14

View Full Curriculum


Access
Lifetime
Content
14 hours
Lessons
189

Microsoft Excel from Beginner to Advanced

Accelerate Your Career with This 4-Part, A-Z Excel Course

By Kyle Pew | in Online Courses

Microsoft Excel is the most widely used office software in the United States and, as such, is a basic requirement for many jobs. If you're great at it, it may even make the difference in earning a promotion. This single course comprises four courses covering the absolute basics through to advanced Excel techniques. By the completion of the course, you'll have mastered the most popular Excel tools and have the confidence to complete any Excel task with efficiency and grace.

  • Access 189 lectures & 14 hours of content 24/7
  • Create effective spreadsheets
  • Manage large sets of data
  • Master the use of some of Excel's most popular & highly sought-after functions
  • Create dynamic reports w/ Excel PivotTables
  • Unlock the power & versatility of Microsoft Excel's AddIn and PowerPivot
  • Automate your day to day Excel tasks by mastering the power of Macros & VBA

Instructor

Kyle Pew is a Microsoft Certified Trainer (MCT) and a certified Microsoft Office Master Instructor and has been teaching and consulting for the past 10+ years on various computer applications, including:

  • Microsoft Office Suite 1997, 2000, XP, 2003, 2007, 2010, 2013
  • Excel, Word, PowerPoint, Outlook, Access and Visio
  • SharePoint End-User 2007, 2010, 2013
  • VBA (Excel and Access)
  • Adobe Suite
  • Photoshop, Illustrator, InDesign
  • Maya (Modeling and Animation)
  • Unity3d (Game Design)
  • HTML, CSS and JavaScript
  • Crystal Reports

Kyle is a graduate of the San Francisco Art Institute in the Media Arts and Animation Program. He has worked as a Game Designer for Electronic Arts, designing on games such as Nerf N-Strike, Nerf N-Strike Elite, both for the Wii, and a Sims 3 Expansion Pack for the PC.

Kyle has facilitated courses that range from 1-on-1 interactions to large scale groups of 100+ participants, including; live in person classes, webinar style classes online and live online full courses. He is consistently ranked top in reviews for each of the courses he teaches.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion not included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: beginner

Requirements

  • Internet required
  • Microsoft Excel 2010, 2013, or 2016

Course Outline

  • Microsoft Excel Course Introduction
    • Welcome to the Ultimate Microsoft Excel Course - 7:12
    • Course Exercise Files - 1:09
  • Microsoft Excel Fundamentals
    • Launching Excel - 2:34
    • Introduction to the Excel Interface - 4:50
    • Customizing the Excel Quick Access Toolbar - 3:53
    • Understanding the Structure of an Excel Workbook - 5:02
    • Saving an Excel Document - 2:44
    • Opening an Existing Excel Document - 2:02
  • Entering and Editing Text and Formulas
    • Entering Text to reate Spreadsheet Titles - 5:35
    • Working With Numeric Data in Excel - 3:31
    • Entering Date Values in Excel - 4:44
    • Working with Cell References - 3:55
    • Creating Basic Formulas in Excel - 7:02
    • Relative Versus Absolute Cell References in Formulas - 10:38
    • Understanding the Order of Operation - 3:20
  • Working with Basic Excel Funtions
    • The structure of an Excel Function - 3:40
    • Working with the SUM() Function - 4:50
    • Working with the MIN() and MAX() Functions - 3:47
    • Working with the AVERAGE() Function - 2:38
    • Working with the COUNT() Function - 2:15
    • Using the AutoSum Command - 4:55
    • Using the AutoFill Command to Copy Formulas - 3:09
  • Modifying an Excel Worksheet
    • Moving and Copying Data in an Excel Worksheet - 4:34
    • Inserting and Deleting Rows and Columns - 5:27
    • Changing the Width and Height of Cells - 5:06
    • Hiding and Unhiding Excel Rows and Columns - 2:51
    • Deleting an Excel Worksheet - 2:53
    • Renaming an Excel Worksheet - 1:58
    • Moving and Copying and Excel Worksheet - 4:12
  • Formatting Data in an Excel Worksheet
    • Working with Font Formatting Commands - 3:53
    • Changing the Background Color of a Cell - 2:50
    • Adding Borders to Cells - 3:33
    • Formatting Data as Currency Values - 5:21
    • Formatting Percentages - 2:17
    • Using Excel's Format Painter - 2:47
    • Creating Styles to Format Data - 8:12
    • Merging and Centering Cells - 2:50
    • Using Conditional Formatting - 4:34
  • Inserting Images and Shapes into an Excel Worksheet
    • Inserting Images - 4:09
    • Inserting Excel Shapes - 3:55
    • Formatting Excel Shapes - 4:39
    • Working with Excel SmartArt - 6:46
  • Creating Basic Charts in Excel
    • Creating an Excel Column Chart - 4:53
    • Working with the Excel Chart Ribbon - 5:23
    • Adding and Modifying Data on an Excel Chart - 3:08
    • Formatting an Excel Chart - 4:40
    • Moving a Chart to another Worksheet - 2:31
    • Working with Excel Pie Charts - 5:06
  • Printing an Excel Worksheet
    • Viewing your Document in Print Preview - 2:11
    • Changing the Margins, Scaling and Orientation - 8:06
    • Working with Page Layout View - 3:49
    • Adding Header and Footer Content - 4:35
    • Printing a Specific Range of Cells - 2:58
  • Working with Excel Templates
    • Intro to Excel Templates - 1:31
    • Opening an Existing Template - 5:33
    • Creating a Custom Template - 4:36
  • Congratulations! You've Built a Solid Microsoft Excel Foundation
    • Congratulations! - 0:52
  • Excel 102 Exercise Files
    • Download Excel 102 Exercise Files
  • Working with an Excel List
    • Understanding Excel List Structure - 5:39
    • Sorting a List Using Single Level Sort - 3:46
    • Sorting a List Using Multi-Level Sorts - 3:46
    • Using Custom Sorts in an Excel List - 4:33
    • Filter an Excel List Using the AutoFilter Tool - 4:56
    • Creating Subtotals in a List - 6:36
    • Format a List as a Table - 7:30
    • Using Conditional Formatting to Find Duplicates - 3:52
    • Removing Duplicates - 4:10
  • Excel List Functions
    • Excel Function: DSUM() - 6:35
    • Excel Function: DAVERAGE() - 2:47
    • Excel Function: DCOUNT() - 3:02
    • Excel Function: SUBTOTAL() - 6:15
  • Excel Data Validation
    • Understanding the Need for Data Validation - 5:13
    • Creating a Validation List - 5:37
    • Adding a Custom Validation Error - 4:37
    • Dynamic Formulas by Using Validation Techniques - 4:24
  • Importing and Exporting Data
    • Importing Data from Text Files - 5:34
    • Importing Data from Microsoft Access - 4:14
    • Exporting Data to a Text File - 4:13
  • Excel PivotTables
    • Understanding Excel PivotTables - 3:44
    • Creating an Excel PivotTable - 6:20
    • Modifying Excel PivotTable Calculations - 6:37
    • Grouping PivotTable Data - 4:43
    • Formatting PivotTable Data - 4:24
    • Drilling Down into PivotTable Data - 3:31
    • Creating PivotCharts - 4:40
    • Filtering PivotTable Data - 3:47
    • Filtering with the Slicer Tool - 3:44
  • Working with Excel's PowerPivot Tools
    • Why PowerPivot? - 4:50
    • Activating the Excel PowerPivot AddIn - 2:26
    • Creating Data Models with PowerPivot - 6:37
    • Creating PivotTables based on Data Models - 3:51
    • PowerPivot Calculated Fields - 3:51
    • PowerPivot KPIs - 7:06
  • Working with Large Sets of Excel Data
    • Using the Freeze Panes Tool - 3:39
    • Grouping Data (Columns and/or Rows) - 4:17
    • Print Options for Large Sets of Data - 7:32
    • Linking Worksheets (3D Formulas) - 4:08
    • Consolidating Data from Multiple Worksheets - 5:30
  • Congratulations! You're an Intermediate Excel User
    • Congratulations! - 0:42
  • Excel 103 Exercise Files
    • Download Excel 103 Exercise Files
  • Working with Excel's Conditional Functions
    • Working with Excel Name Ranges - 5:10
    • Using Excel's IF() Function - 6:09
    • Nesting Functions - 7:43
    • Using Excel's COUNTIF() Function - 3:55
    • Using Excel's SUMIF() Function - 5:40
    • Using Excel's IFERROR() Function - 6:05
  • Working with Excel's Lookup Functions
    • Using Excel's VLOOKUP() Function - 7:39
    • Using Excel's HLOOKUP() Function - 4:22
    • Using Excel's INDEX() and MATCH() Functions - 13:20
  • Working with Excel's Text Based Functions
    • Using Excel's LEFT(), RIGHT() and MID() Functions - 6:18
    • Using Excel's LEN() Function - 6:15
    • Using Excel's SEARCH() Function - 12:19
    • Using Excel's CONCATENATE() Function - 3:10
  • Auditing an Excel Worksheet
    • Tracing Precendents in Excel Formulas - 4:01
    • Tracing Dependents in Excel Formuals - 2:52
    • Working with the Watch Window - 4:19
    • Showing Formulas - 2:46
  • Protecting Excel Worksheets and Workbooks
    • Protecting Specific Cells in a Worksheet - 8:05
    • Protecting the Structure of a Workbook - 4:12
    • Adding a Workbook Password - 3:15
  • Mastering Excel's "What If?" Tools
    • Working with Excel's Goal Seek Tool - 7:41
    • Working with Excel's Solver Tool - 9:32
    • Building Effective Data Tables in Excel - 6:17
    • Creating Scenarios in Excel - 7:51
  • Automating Repetitive Tasks in Excel with Macros
    • Understanding Excel Macros - 3:50
    • Activating the Developer Tab in Excel - 1:42
    • Creating a Macro with the Macro Recorder - 7:39
    • Editing a Macro with VBA - 5:16
    • Creating Buttons to Run Macros - 3:11
  • Congratulations!
    • Congratulations on Competing the Microsoft Excel 103 - 0:58
  • Microsoft Excel Macros and VBA Course Introduction
    • Welcome to the Course - 5:50
    • Understanding the Why and How Behind Excel Macros - 5:15
    • Course Exercise Files - Download these First!
  • Project #1: Using Excel's Macro Recorder Tool
    • Introduction to Project #1: Inserting and Formatting Text - 2:01
    • Project #1: Start Recording! - 7:40
    • Running A Macro - 2:43
    • Project #1: Running a Macro with a Button - 3:35
    • Project #1: Opps! I Need to Edit My Macro - 4:27
    • Practical Uses of Excel Macros - 2:16
  • Excel VBA Concepts
    • New Lecture
    • Excel VBA Concepts - 3:32
    • The Visual Basic Editor (VBE) - 3:25
    • Excel VBA Modules - 2:01
    • Creating an Excel VBA Procedure - 8:17
    • Adding Code to a VBA Procedure - 4:04
    • Understanding Excel VBA Variables - 7:01
    • Building Logic with an IF Statement - 6:18
    • Working with Excel VBA Loops to Repeat Blocks of Code - 9:42
  • Project #2: Moving Beyond the Basics and into VBA
    • Introduction to Project #2: Interacting with the User - 2:56
    • Project #2: Breaking Down the VBA Code - 3:34
    • Project #2: Prompting the User for Information - 6:54
    • Continue Excel VBA InputBox - 7:20
    • Project #2: Building Logic into Your Macros - 4:31
    • Project #2: Alerting the User of Errors - 10:29
    • Other Practical Uses of Message and Input Boxes - 3:17
  • Project #3: Preparing and Cleaning Up Data with a Little VBA
    • Introduction to Project #3: Cleaning Up and Formatting Data - 3:06
    • Project #3: Preparing to the Use the Macro Recorder - 2:54
    • Project #3: Inserting the Headers - 3:26
    • Project #3: Formatting the Headers - 2:21
    • Project #3: Putting it all Together with a Little VBA - 10:47
    • Running the Loop - 2:54
  • Project #4: Using VBA to Automate Excel Formulas
    • Introduction to Project #4: Automate Excel Formulas - 2:10
    • Project #4: Automate the SUM() Function through Code - 8:01
    • Continue Automate SUM Function - 6:28
    • Test the SUM Function - 3:45
    • Project #4: Loop the SUM() Function Over Multiple Worksheets - 6:17
  • Project #5: Bringing it All Together and a Weekly Report
    • Introduction to Project #5: Creating the Final Report - 2:59
    • Project #5: Creating the Final Report Loop - 7:45
    • Project #5 - Copying and Pasting Data with VBA - 9:26
    • Project #5 - Running the Final Report Procedure - 3:14
  • Project #6: Working with Excel VBA User Forms
    • Introduction to Project #6: Working with Excel VBA User Forms - 3:01
    • Project #6: Creating an Excel User Form - 5:05
    • Project #6: Adding Controls to the Form - 4:01
    • Project #6: Adding VBA Code to the Initialize Event - 8:49
    • Project #6: Adding VBA Code to the ComboBox Change Event - 4:32
    • Project #6: Adding VBA Code to the Add Worksheet Button - 3:28
    • Change Worksheet Name - 3:58
    • Project #6: Adding VBA Code to the Create Report Button - 8:52
    • Project #6: Showing the Form - 4:02
  • Project #7: Importing Data from Text Files
    • Project #7: Opening a Text File for Import - 2:42
    • Project #7: Opening a Text File for Import - 6:45
    • Project #7: Get Data from Text File - 5:30
    • Project #7: Importing Multiple Text Files with the GetOpenFilename Method - 9:32
    • Project #7: Creating a Loop to Read Each File - 7:26
    • Project #7: Adding a New Sheet for Imported Data - 2:36
    • Project #7: Clear the Clipboard - 3:38
    • Project #7: Working with the ScreenUpdating Property - 3:16
    • Project #7: Creating Reusable Code with a VBA Function - 7:44
  • Course Wrap Up
    • Congratulations! You're an Excel Macro/VBA Master - 1:02

View Full Curriculum


Access
Lifetime
Content
2 hours
Lessons
57

Master Microsoft Excel Macros & VBA

Use 6 Simple Projects to Become a Master Working with Excel Macros & VBA

By Kyle Pew | in Online Courses

During this course, you will build a foundation of working with Excel Macros and VBA. You'll engage in several projects that will build upon one another as you progress through the basic building blocks of automating tasks within Excel. Soon you'll be ready to enter the world of Excel VBA, where you will experience a more dynamic, robust experience for yourself and your peers.

  • Access 57 lectures & 2 hours of content 24/7
  • Automate placing & formatting a title on a worksheet
  • Interact w/ the users of your Macros through input & message boxes
  • Clean up multiple Excel worksheets & prepare the data to be reported on
  • Create a report based on multiple sets of data found within multiple Excel worksheets
  • Automate the creation of Excel Formulas to SUM data up in your report

Instructor

Kyle Pew is a Microsoft Certified Trainer (MCT) and a certified Microsoft Office Master Instructor and has been teaching and consulting for the past 10+ years on various computer applications, including:

  • Microsoft Office Suite 1997, 2000, XP, 2003, 2007, 2010, 2013
  • Excel, Word, PowerPoint, Outlook, Access and Visio
  • SharePoint End-User 2007, 2010, 2013
  • VBA (Excel and Access)
  • Adobe Suite
  • Photoshop, Illustrator, InDesign
  • Maya (Modeling and Animation)
  • Unity3d (Game Design)
  • HTML, CSS and JavaScript
  • Crystal Reports


  • Kyle is a graduate of the San Francisco Art Institute in the Media Arts and Animation Program. He has worked as a Game Designer for Electronic Arts, designing on games such as Nerf N-Strike, Nerf N-Strike Elite, both for the Wii, and a Sims 3 Expansion Pack for the PC.

    Kyle has facilitated courses that range from 1-on-1 interactions to large scale groups of 100+ participants, including; live in person classes, webinar style classes online and live online full courses. He is consistently ranked top in reviews for each of the courses he teaches.

Important Details

  • Length of time users can access this course: lifetime
  • Access options: web streaming, mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Experience level required: all levels
  • Microsoft Excel required

Requirements

  • Internet required

Course Outline

  • Course Introduction and Overview
    • Welcome to the Course - 4:08
    • Something to THINK About and SHARE - 2:01
    • Understanding the Why and How Behind Excel Macros - 3:18
  • Download Exercise Files
    • Download this ZIP file
  • Project #1: Using Excel's Macro Recorder Tool
    • Introduction to Project #1 - Inserting and Formatting Text - 1:39
    • Project #1 - Start Recording! - 7:14
    • Project #1 - Running a Macro with a Button - 2:12
    • Project #1 - Opps! I Need to Edit My Macro - 4:04
    • Practical Uses of Excel Macros - 2:17
    • Project #1 - Quiz - Test Your Knowledge on Using the Macro Recorder
    • Project #1 - Exercise - Try it Yourself and Practice
  • Excel VBA Concepts
    • Excel VBA Concepts - 0:30
    • The Visual Basic Editor - 2:06
    • Excel VBA Modules - 1:14
    • Creating Excel VBA Procedures - 2:05
    • Understanding Excel VBA Variables - 5:16
    • Working Excel VBA Loops to repeat blocks of code - 4:11
    • Building Logic with an IF Statement - 2:39
    • VBA Concepts - Quiz - Test Your Knowledge on VBA Programming Concepts
  • Project #2: Moving Beyond the Basics and Into VBA
    • Introduction to Project #2 - Interacting with the User - 2:42
    • Project #2 - Breaking Down the VBA Code - 3:05
    • Project #2 - Prompting the User for Information - 5:35
    • Project #2: Building Logic in Your Macros - 4:57
    • Project #2 - Alerting the User of Errors - 7:05
    • Other Practical Uses of Message Boxes and Input Boxes - 3:18
    • Project #2 - Quiz - Test Your Knowledge on Editing Your Macros
    • Project #2 - Exercise - Try it Yourself and Practice
  • Project #3: Preparing and cleaning up Data with a little VBA
    • Introduction to Project #3 - Cleaning Up and Formatting Multiple Worksheets - 2:59
    • Project #3 - Using the Macro Recorder to Lay the Foundation - 1:53
    • Project #3: Preparing to use the Macro Recorder - 2:28
    • Project #3 - Inserting the Headers - 2:40
    • Project #3 - Formatting the Headers - 5:07
    • Project #3 - Putting it all Together with alittle VBA Magic - 9:54
    • Project #3 - Quiz - Test Your Knowledge on VBA
    • Project #3 - Exercise - Try it Yourself and Practice
  • Project #4: Using VBA to automate Excel Formulas
    • Introduction to Project #4 - Automate Microsoft Excel Formulas with VBA - 2:23
    • Project #4: Automate the SUM Function through Code - 9:30
    • Project #4: Loop the SUM Function over Multiple Worksheets - 5:57
    • Project #4 - Quiz - Test Your Knowledge Automating a Function with VBA
    • Project #4 - Exercise - Try it Yourself and Practice
  • Project #5: Bringing it all togther and create a weekly report
    • Introduction to Project #5 - Creating a Final Report - 2:56
    • Project 5: Putting it all together to create a Final Report - 9:57
    • Project #5 - Quiz - Test Your Knowledge of Creating the Final Report
    • Project #5 - Exercise - Try it Yourself and Practice
  • Bonus Project #6: Working with VBA Forms
    • Introduction to Bonus Project #6 - Working with Excel VBA Forms - 3:14
    • Bonus Project #6: Creating an Excel VBA Form - 2:48
    • Bonus Project #6: Adding Controls to the Form - 2:42
    • Bonus Project #6: Changing Control and Form Properties - 4:56
    • Bonus Project #6: Adding VBA Code to the Initialize Event of the Form - 7:26
    • Project 6: Adding VBA Code to the ComboBox Change Event - 4:20
    • Project #6: Adding VBA Code to the Add Worksheet Button Click Event - 6:15
    • Project #6: Adding VBA Code to the Create Report Button Click Event - 2:03
    • Bonus Project #6: Showing the Form - 3:27
    • Bonus Project #6 - Quiz - Test Your Knowledge on Creating Excel VBA Forms
    • Bonus Project #6 - Exercise - Try it Yourself and Practice
  • Course Wrap Up
    • Congratulations! You're an Excel Macro/VBA Master - 1:02
    • BONUS! - Special Offer! Any of my courses for only $10

View Full Curriculum



Terms

  • Unredeemed licenses can be returned for store credit within 15 days of purchase. Once your license is redeemed, all sales are final.