Get $1 CAD credit for every $25 CAD spent!

The Premium A to Z Microsoft Excel Bundle Ft. Alan Jarvis & Chris Dutton

Ending In:
Add to Cart - $52.99 CAD
Add to Cart ($52.99 CAD)
$66.99 CAD
$2,288 CAD
Price Drop!
wishlist
Courses
10
Lessons
760
Enrolled
2,571

What's Included

Product Details

Access
Lifetime
Content
1.0 hours
Lessons
21

Excel Pro Tips: Productivity Tools

Save Time & Get More Done with Excel's Most Powerful Productivity Tools

By Excel Maven | in Online Courses

The Excel Pro Tips: Productivity Tools course will improve your current productivity. This 2-hour course will cover a range of topics to help you save time and work more efficiently, including keyboard shortcuts, autofill and flash fill, data validation, cell protection, named ranges, multi-level sorting, advanced filters, and much more. By the end of this course, you'll be ready to supercharge your productivity and absolutely revolutionize your Excel workflow.

63,046 positive reviews from 248,923 students

  • Access 21 lectures & 1 hour of content 24/7
  • Learn & practice Excel's productivity tools using keyboard shortcuts, autofill & flash fill, and data validation
  • Easily sort your data using cell protection, named ranges, multi-level storing, advanced filters & more
  • Supercharge your productivity & improve your Excel workflow

Note: Software NOT included.

"I must say that I was extremely impressed with how well the course was presented. I was pleasantly surprised to see just how much was covered in this one course by an exceptional instructor. " – Daulton Brent Cotten

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 150,000+ students in more than 190 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 & mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Updates included
  • Experience level required: all levels

Requirements

  • Microsoft Excel 2007-2019 or Office 365 recommended
  • Designed for Windows users (some tools may not be available for Mac)

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:41
    • DOWNLOAD: Course Resources
    • Setting Expectations - 1:45
  • Productivity Tips
    • Introduction - 0:29
    • Customizing the Footer - 4:03
    • CTRL Shortcuts - 8:01
    • ALT Key Tips - 6:53
    • "Go To" Special Options - 4:36
    • Removing Blank Rows - 3:17
    • Data Validation Drop-Downs - 7:11
    • Autofill & Flash Fill - 8:26
    • Customizing the Ribbon - 6:53
    • Splitting Text to Columns - 7:39
    • Synchronous Scrolling - 2:58
    • Extracting Unique Values - 4:06
    • Named Range & Table References - 8:12
    • Protecting Cells & Formulas - 8:09
    • Multi-Level Row & Column Sorting - 10:54
    • Advanced Filter Criteria - 9:27
    • QUIZ: Productivity Tips
  • Wrapping Up
    • Resources & Next Steps

View Full Curriculum


Access
Lifetime
Content
1.0 hours
Lessons
18

Excel Pro Tips: Data Visualization

Think Outside the Pie Chart & Explore Some of Excel’s Most Powerful Visualization Tools

By Excel Maven | in Online Courses

This Excel Data Visualization course is all about bringing data to life, using some of Excel's most powerful visualization tools. The 18 lectures of this course won't be talking about your basic pie charts and line graphs. You will be introduced to Excel's unique features like filled maps, sparklines, custom templates, dynamic visuals, interactive form controls, and much more. If you want to create clear and effective charts and graphs and interactive reports and dashboards, this course is for you.

63,046 positive reviews from 248,923 students

  • Access 18 lectures & 1 hour of content 24/7
  • Explore Excel's most powerful visualization tools
  • Know about Excel's unique features like filled maps, sparklines, custom templates. dynamic visuals, interactive form controls & more
  • Create clear & effective charts and graphs as well as interactive reports and dashboards
Note: Software NOT included.

"Yes, it was a good match for me. I enjoyed being able to practice applying what I was learning on the spreadsheets provided and that I had the ability to go at my own pace replaying sections if needed." – Jodi Ming

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 150,000+ students in more than 190 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 & mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Updates included
  • Experience level required: all levels

Requirements

  • Microsoft Excel 2007-2019 or Office 365 recommended
  • Designed for Windows users (some tools may not be available for Mac)

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:41
    • DOWNLOAD: Course Resources
    • Setting Expectations - 1:45
  • Visualization Tips
    • Introduction - 0:24
    • Chart Properties (Move & Size) - 5:08
    • Hidden Chart Source Data - 2:43
    • Filled Maps (Excel 2016+) - 5:10
    • Customizing Charts to Tell a Story - 8:53
    • Adding Sparklines - 7:44
    • Custom Chart Templates - 6:06
    • Heat Maps with Color Scales - 7:11
    • Analyzing Distribution with Histograms - 9:28
    • Goal Pacing with Gauge Charts - 10:26
    • Highlighting Time Periods - 8:05
    • Dynamic Source Ranges - 12:08
    • Interactive Form Controls - 11:48
    • QUIZ: Visualization Tips
  • Wrapping Up
    • Resources & Next Steps

View Full Curriculum


Access
Lifetime
Content
1.0 hours
Lessons
20

Excel Pro Tips: PivotTables

Explore & Analyze Your Data with Advanced PivotTable Tools and Techniques

By Excel Maven | in Online Courses

This Excel Pro Tips: PivotTables course will be focusing on one of Excel's most powerful and flexible analytic tools—PivotTables. The 1-hour course will start with some common topics like slicers, table layouts, and field list options, then dive into more unique use cases like grouping values, enabling multiple filters, reviving source data from a cache, defining custom sort lists, and more. This course will help you work smarter and not harder. By the end of this course, you'll be ready to slice, dice, and filter your data like a PivotTable pro.

63,046 positive reviews from 248,923 students

  • Access 20 lectures & 1 hour of content 24/7
  • Understand the use of Excel slicers, table layouts & field list options
  • Learn grouping values, enabling multiple filters, reviving source data from the cache, defining custom sort lists & more
  • Slice, dice & filter data like a PivotTable pro
  • Practice & master PivotTables techniques used by professionals
Note: Software NOT included.

"Everything you need to get started using Excel pivot tables (a little bit of pivot charts as well). The amount of content and pacing was perfect for me. The real gem is the last session where you use what you learned. " – Craig Day

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 150,000+ students in more than 190 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 & mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Updates included
  • Experience level required: all levels

Requirements

  • Microsoft Excel 2007-2019 or Office 365 recommended
  • Designed for Windows users (some tools may not be available for Mac)

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:41
    • DOWNLOAD: Course Resources
    • Setting Expectations - 1:45
  • PivotTable Tips
    • Introduction - 0:18
    • Customizing the Field List - 4:07
    • Autofitting Column Width - 4:21
    • Outline & Tabular Layouts - 7:46
    • Counting Non-Numerical Fields - 6:05
    • Grouping Dates - 8:03
    • Enabling Multiple Filters - 5:00
    • Grouping Values - 6:50
    • Adding Value Calculations - 9:01
    • Showing Empty Items - 6:22
    • Configuring Slicers & Timelines - 10:23
    • Conditional Formatting - 8:23
    • Removing & Reviving Source Data - 8:19
    • Adding Custom Sort Lists - 5:23
    • Solve Order & List Formulas - 6:16
    • QUIZ: PivotTable Tips
  • Wrapping Up
    • Resources & Next Steps

View Full Curriculum


Access
Lifetime
Content
6.0 hours
Lessons
103

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.

63,046 positive reviews from 248,923 students

  • Access 103 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

Note: Software NOT included.

"In general the course is well organized in my opinion, the topics bring together all the necessary functions, and if one has willingness to learn deeper after the course all the other alternative functions will be quite easy to understand and apply." – Abuzar Talibov

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 150,000+ students in more than 190 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 & mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Updates included
  • Experience level required: all levels

Requirements

  • Microsoft Excel 2007 - 2019 or Office 365 (Some features may not be available in earlier versions)

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:28
    • Downloading the Homework Exercises - 1:42
    • Setting Expectations - 3:36
  • 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
  • Extra Bonus Functions
    • Introduction - 1:41
    • The INDIRECT Function - 10:55
    • The HYPERLINK Function - 6:19
    • WEBSERVICE & FILTERXML: Weather Underground - 7:16
    • WEBSERVICE & FILTERXML: Open Weather Map - 9:14
    • QUIZ: Badass Bonus Functions
  • Wrapping Up
    • Resources & Next Steps - 0:41

View Full Curriculum


Access
Lifetime
Content
7.0 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.

63,046 positive reviews from 248,923 students

  • 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

Note: Software NOT included.

"This has been an excellent course - Chris knows his stuff, and presents it well. I never knew there was so much power in Excel — I considered myself pretty good at Excel, but Power Query and the Data Model has blown me away." – Ian Watts

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 150,000+ students in more than 190 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 & mobile streaming
  • Certification of completion included
  • Redemption deadline: redeem your code within 30 days of purchase
  • Updates included
  • Experience level required: all levels

Requirements

  • Version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)
  • Designed for PC users (Power Pivot is currently unavailable with Excel for Mac)

Course Outline

  • Getting Started
    • Course Structure & Outline - 1:22
    • 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"
  • Connecting & Shaping Data with 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 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 & Normalization
    • 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
3.0 hours
Lessons
56

Microsoft Excel Essentials, Level 1 Basics: Excel Made Simple

Build a Solid Foundation on the Excel's Basic Functions, Tools & Formula

By Alan Jarvis | in Online Courses

Excel is the most commonly used and in-demand tool for making sense of data. When you can harness that power, you will be a sought after employee. If you run a business, being proficient in Excel can help you see your business in a completely different light. This is the level for Excel beginners, and all the Excel basics will be covered here. Getting to grips with Excel will give you a distinct advantage in whatever work you currently do, and can open the door to new opportunities.

14,230 positive reviews from 81,504 students

  • Access 56 lectures & 3 hours of content 24/7
  • Understand the most widely used business software in the world
  • Build your own simple to complex formulas & use some of Excel's built-in functions
  • Build your own spreadsheets from scratch & actually understand how they work

Note: Software NOT included.

"I needed Excel immediately for work and this course is about as perfect as can be as a beginner." – Sharon Goldzweig

Alan Jarvis has almost thirty years of experience in teaching various subjects and has held senior management positions at several blue-chip and Times Top 100 companies. He now specializes in consultancy, interim work, and teaching on Udemy. Alan Jarvis has 80,000 happy students across 194 countries enrolled in his courses.

Important Details

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

Requirements

  • Any device with basic specifications
  • Copy of Excel 2007, 2010, 2013, or 2016

Course Outline

  • Your First Program
  • Section 1 - Start Here - All The Basics
    • Welcome To The Course! - 2:06
    • Opening Excel, And Creating A Shortcut - 3:45
    • The Anatomy Of A Workbook - 15:19
    • A Quick Review Of What's Where - 6:11
    • Always Do This First! - Save Your New Workbook - 2:00
    • Let's Enter Some Data - 1:41
    • Editing Data - 1:42
    • Ooops. I Made A Mistake - Undo and Redo - 1:20
    • Changing Appearance Of Text With Formatting - Fonts - 9:03
    • A Quick Word On Formatting - 1:20
    • Formatting Text - Alignment - 3:07
    • Saving Time With AutoFilling Sequences - 4:40
    • POWER USER - Adding Your Own Lists To Autofill - 4:23
    • Saving Time with Format Painter - 2:48
    • Changing The Column Width - 3:33
    • Tidy Large Titles With Merge And Centre - 3:01
    • A Couple Of Shortcuts For Entering Data - 3:57
  • Section 2 - Doing The (Simple) Math
    • Sums - The Old Fashioned Way! - 3:11
    • Sums - Using Autosum - 5:32
    • Copying Formulas - 3:16
    • SUMming Horizontally - 3:16
    • Basic Formulas - Subtraction - 2:28
    • Basic Formulas - Multiplication - 1:32
    • Basic Formulas - Division - 1:21
    • Average Function - 2:41
    • The Order Of Mathematical Operation - 6:38
    • POWER USER - Evaluate Formula - 4:50
  • Section 3 - Rearranging Things
    • Inserting New Columns And Rows - 6:21
    • Moving Existing Columns And Rows - 2:27
    • Cutting, Copying, Inserting And Deleting - 6:07
    • Hiding Columns And Rows - 2:41
  • Section 4 - Formulas Learning The Clever Stuff!
    • ROUNDing Functions - 5:55
    • Formatting Numbers - 2:12
    • A Primer In Building Complex Formulas - 3:10
    • Building a Complex Formula - 4:19
  • Section 5 - A Few More Essentials
    • 5000 Sorting - 4:16
    • Wrapping Text And Soft Enter - 3:59
    • Adding A New Worksheet - 1:07
    • Creating A Simple Chart - 4:20
    • Adding Borders - 2:36
    • Customizing the Quick Access Toolbar - 2:11
    • Simple Printing - 3:34
    • Freezing For An Easier View - 4:06
    • Getting Help - 1:39
    • Filters - 6:21
    • Highlighting Cells - 2:03
    • Closing - 1:29
  • Section 6 - Bonus Section - The Keyboard Shortcuts
    • Bonus 1 Whizzing Around Excel Revision - 6:45
    • Bonus 2 Keyboard Shortcuts - 11:18
  • Section 7 - Bonus Section Absolute and Relative Cell References
    • Bonus 3 - A1 Style - Relative Relative - 2:15
    • Bonus 3 - $A$1 Style - Absolute Absolute - 2:44
    • Bonus 3 - $A1 Style - Absolute Relative - 2:16
    • Bonus 3 - A$1 Style - Relative Absolute - 2:01
  • Section 8 - Student Questions Answered
    • Level 1 SQA 01 - Reverse Engineering A Sample Spreadsheet - 20:55
    • Level 1 SQA 02 - Casing And Text Functions - 6:35
    • Level 1 SQA 03 - Charts From A Filtered List - 2:54

View Full Curriculum


Access
Lifetime
Content
8.0 hours
Lessons
97

Microsoft Excel Essentials, Level 2: Intermediate/Advanced

Be an Excel Power User by Working on Projects that Showcase the Range of Excel's Capabilities

By Alan Jarvis | in Online Courses

So, you have an understanding of the basics, but would you like to use Excel like the professionals? If so, this is the course for you. What you will learn in this course will supercharge your skills and your career. You will learn powerful and little known techniques to enable you to build your own powerful spreadsheet solutions. You will do this by creating multiple projects together, step-by-step, and in-depth to guarantee your understanding. Each project builds on the last, so your skills develop cumulatively. Each project in the course is designed to introduce a range of concepts that showcase the true power of Excel, and show you what is really possible.

14,230 positive reviews from 81,504 students

  • Access 97 lectures & 8 hours of content 24/7
  • Learn the fastest way to create powerful dynamic templates
  • Build a fully functional complex relational database
  • Create dynamic named ranges
  • Create Macros & use them to automate your work
  • Make project management simple w/ Gantt charting

Note: Software NOT included.

"The course covers from basics to advanced material. Explanations are clear and easy to understand, despite the level of Excel one has." – Paula Yankova

Alan Jarvis has almost thirty years of experience in teaching various subjects and has held senior management positions at several blue-chip and Times Top 100 companies. He now specializes in consultancy, interim work, and teaching on Udemy. Alan Jarvis has 80,000 happy students across 194 countries enrolled in his courses.

Important Details

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

Requirements

  • Any device with basic specifications
  • Copy of Excel 2007, 2010, 2013 or 2016

Course Outline

  • First Section
    • First Lecture
    • Welcome To The Course! - 2:05
  • Project 1 - Creating A Data Entry Screen To Populate Multiple Templates
    • 1000 - Proof Of Concept - 4:49
    • Planning Ahead - 2:13
    • Creating Our Data Entry Screen - 4:08
    • (Custom) Formatting Dates And Time - 6:02
    • Simple Calculations With Time - 2:35
    • More (Useful) Calculations With Time - 8:06
    • It's About Time (And Dates) - 8:28
    • Adding With Time - 3:39
    • Creating A Template From An Image - 11:34
    • Importing A Template From An Existing Excel File - 2:21
    • Converting Time To A Decimal - 5:41
    • A Little Bit Of Simple Data Entry - 3:11
    • Simple Conditional Formatting For A Cleaner View - 4:42
    • Calculating Time Out Of House Using Travel Time - 4:46
    • Simple Logical Testing And Nested Logical Testing - 8:28
    • Building Text Strings With A Formula - 12:10
    • Before We Move On...Accessing The Developer Ribbon - 1:05
    • A Tick Box Exercise (Of Sorts) - 7:11
    • Auto-populating Check Boxes - 11:48
    • PRACTICE EXERCISE 1 - Time To Add A New Entry - 1:40
    • Defining A Working Area, And Protecting Your Work - 4:40
    • PRACTICE EXERCISE 2 - Set Up A Working Area, And Limit User Entry - 1:06
  • Project 2 - Building A Database With Excel
    • Simple VLOOKUPs - 4:16
    • Step 1 - Get Some Data In, And Split It - 3:54
    • Using Data Validation To Get The Right Input - 3:47
    • Let's Build Our Database! - 6:12
    • Importing Data From A Text File - 2:01
    • Importing Data From A Word File - 2:37
    • Pulling Data From Multiple Sources - 3:33
    • Using OTHER Look-Ups To Look Up! - 5:25
    • LOOKUP From A LOOKUP With No Intermediary Step - 2:19
    • Data Arrays Don't Have To Start At A1 - 2:58
    • Some Common Reasons VLook-Ups Fail - 6:02
    • One Inherent Flaw In VLOOKUP - 1:10
    • POWER USER - A Breakdown Of Looking Up Backwards - 6:44
    • The Other Way Of Looking Up Backwards - 6:49
    • Backwards Look-Ups In Action - 3:47
    • POWER USER - Dealing With Inconsistencies In User Entry - 8:19
    • POWER USER - Fuzzy VLOOKUPs - 3:33
    • Power User - VLOOKUPs With Multiple Inputs - 9:39
    • Power User - Looking Up Multiple Inputs Using An Array Formula - 4:45
    • VLOOKUP's Brother...HLOOKUP - 5:04
    • POWER USER - The Holy Grail - How To Return Multiple Values From A Single Look Up - 13:33
    • What To Look For When THAT Formula Didn't Work - 4:05
    • The Fastest Way To Modify Your Column Numbers - 7:24
    • POWER USER - Vlook-Ups With Moving Columns - 3:05
    • Putting It All Together - 9:52
    • The Finishing Touch - How Many Records Did I Find - 4:28
  • Project 3 - Named Ranges
    • A Simple Static Named Range Using A Single Cell - 3:44
    • Creating A Named Range Using A Range Of Cells - 2:53
    • Using Row Labels To Name Multiple Ranges - 2:34
    • POWER USER - A Magic Trick Using Row And Column Labels - 4:55
    • POWER USER - Dynamic Named Ranges - 8:06
    • POWER USER - What To Do With Dynamic Names Ranges With Titles - 5:22
    • POWER USER - Dynamic Charts - 10:27
    • Horizontal Dynamic Named Ranges - 11:09
  • Project 4 - What Can I Have For Dinner
    • Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for? - 1:25
    • Hyperlinking To A Different Sheet In The Same Workbook - 3:32
    • Creating Our First Macro - 5:37
    • Assigning A Macro To A Button - 4:13
    • Creating A List For Our Dropdown Using A Dynamic Named Range - 1:39
    • Using A Conditional Format To Know When A Value Is Missing - 4:34
    • Copying Conditional Formats And Creating Our Drop-Downs - 2:50
    • Building Our Formula...INDIRECT Function - 2:55
    • Building Strings For Indirect Sheet And Cell References - 7:16
    • It's A One Or A Zero - 2:35
    • Working The Percentages And Adding Traffic Lights - 4:06
    • POWER USER - The HYPERLINK Function (And Problem) - 3:16
    • PRACTICE EXERCISE 1 - Fill In The Blanks - 0:59
    • PRACTICE EXERCISE 2 - Pretty It Up (With A Macro) - 1:45
    • PRACTICE EXERCISE 3 - Create A VLOOKUP Using A Built String With INDIRECT - 2:13
  • Project 5 - Using Excel For Gantt Charts...Timelines And Project Plans!
    • Creating A Gantt Chart Using A Worksheet - 7:39
    • Building The First Part Of Our Logical Test - 4:20
    • Multiple Logical Tests At Once Using AND - 8:28
    • Conditional Formatting...Where The Magic Happens - 5:32
    • Gantt Charts Using The Built In Charting Tools - 4:51
    • Gantt Charts With Different Colors For Different Criteria - 7:47
  • Project 6 - Bonus Section - Just For Fun
    • Bonus - How I Created Randomly Generated License Plate Numbers! - 7:14
  • Student Questions Answered!
    • Level 2 SQA 01 - Calls Text Data - Or How To Return a Column Title If Value is 1 - 6:42
    • Level 2 SQA 02 - Calls Text Data 2 - This Time Using Text! - 9:02
    • Level 2 SQA 03 - Extracting Phone Numbers From A Cell - 2:39
    • Level 2 SQA 04 - What Is The CHOOSE Function Really Used For - 14:47
    • Level 2 SQA 05 - Casing And Text Functions - 6:35
    • Level 2 SQA 06 - Dynamic Charting From A Drop Down - 10:02
    • Level 2 SQA 07 - Extracting a Unique List, And Summing The Money! - 2:25
    • Level 2 SQA 08 - SUMIF With Dynamic Sum Range - 5:55
    • Level 2 SQA 09 - VLOOKUPs With Pictures! - 4:30
    • Level 2 SQA 10 - Data Validation With Dependent Dropdowns - 3:42
    • Level 2 SQA 11- Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround) - 8:30
    • Level 2 SQA 12 - Using 2 Labels As A Lookup From Drop-downs - 6:47
    • Level 2 SQA 13 - Tiered Pricing - 9:00
    • Level 2 SQA 14 - Worksheet Protection - 4:39
    • Level 2 SQA 15 - The Middle Name Problem...and Solution! - 9:03
    • Level 2 SQA 16 - Finding Matches, And Counting Entries - 8:06
    • Level 2 SQA 17 - Fee Calculator, or LOOKUPs That Are True, Not False - 5:42

View Full Curriculum


Access
Lifetime
Content
14.0 hours
Lessons
181

Microsoft Excel Essentials, Level 3: Macros & VBA Programming

Turbocharge Excel with VBA! From Simple Helper Programs to Complete Solutions with Excel Visual Basic for Applications

By Alan Jarvis | in Online Courses

If you really want to harness the full power of Excel, then learning how to program in VBA (Visual Basic for Applications), the powerhouse, is essential. In this course, you will learn everything you need to know about coding in VBA, even if you've never looked at a line of code before! You will learn how to streamline your code for efficiency from the beginning. You'll then go on to create 6 separate projects using those very tools and techniques, where you will pull together all of your earlier learning, and give you the confidence to program your own solutions as soon as you finish the course!

14,230 positive reviews from 81,504 students

  • Access 181 lectures & 14 hours of content 24/7
  • Learn to program in VBA from scratch
  • Fully automate Excel using Visual Basic for Applications
  • Understand coding & the thought process behind it
  • Develop complex full-blown applications from scratch

Note: Software NOT included.

"Good, practical examples. Gaining experience through making mistakes. Real-life examples added from participants questions" – Andrew Hubbard

Alan Jarvis has almost thirty years of experience in teaching various subjects and has held senior management positions at several blue-chip and Times Top 100 companies. He now specializes in consultancy, interim work, and teaching on Udemy. Alan Jarvis has 80,000 happy students across 194 countries enrolled in his courses.

Important Details

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

Requirements

  • Any device with basic specifications
  • Copy of Excel 2007, 2010, 2013, or 2016

Course Outline

  • Introduction To The Course
    • Why Should I Learn How To Code - 10:28
    • The 15 Golden Rules Of Coding - 6:24
  • Introducing Your Personal Built In Translator...the Macro Recorder
    • Introducing The Visual Basic Editor, & Recording Our First Macro - 11:10
    • Saving Macro-enabled Workbooks, And Security Settings - 3:16
    • Moving Code Around - 4:38
    • Stepping Out. Well, In Actually - Debugging Made Easy - 5:50
    • With And End With - 12:34
    • Streamlining You Code, Or, Get Rid Of What You Don't Need - 7:53
    • Combining Your Code - 6:17
    • A Little Privacy Please - 3:18
    • Keyboard Shortcuts, And Why I Don't Use Them - 2:03
    • Why You Can't Get By With Just Recording Macros - 10:52
  • The Building Blocks Of Coding...Your Dictionary & Phrase Book Of Success!
    • Introduction To The Coding Section - 5:35
    • Getting All The Code For This Section - 6:15
    • Changing Your VBE Settings - 2:47
    • Protecting Your Code - 2:10
    • Objects, Methods And Properties - 6:27
    • Understanding The Hierarchy - 3:36
    • The Range Object - 3:03
    • The Cells Object - 2:42
    • The Activecell Property - 2:15
    • The Offset Property - 2:18
    • The End Property - 2:49
    • Dynamic Range Selection - 2:33
    • The CurrentRegion Property - 2:05
    • ACTIVATE vs. SELECT - 1:43
    • Between The Sheets - 2:40
    • Calling A Sheet By Its VB Name - 2:54
    • Sheets Vs. Worksheets - 2:02
    • Getting Around The Workbooks - 3:10
    • The Value Property - Writing Data - 5:26
    • The Value Property - Reading And Writing Data - 2:35
    • Copy and Paste - 2:58
    • Commonly Used Properties - 2:52
    • Coding Exercise - The Rainbow - 2:20
    • The Address Property - 4:04
    • The Row and Column Properties - 1:34
    • Capturing The Column Letter - 2:01
    • More Useful Properties - 3:05
    • Even More Useful Properties - 2:55
    • Opening Another Workbook Programmatically - 6:20
    • Closing Workbooks Programmatically - 3:41
    • Coding Exercise - Open Write Close - 3:41
  • The Programmers Toolbox...The Techie Stuff, Made Easy (Honest!)
    • Introduction To The Programmers Toolbox - 0:50
    • Variables - Local Variables - 6:46
    • Variables - Local Variables With A Twist - 3:40
    • Variables - Module Level Variables - 3:43
    • Variables - Project Level Variables - 3:02
    • Bonus - Calling A Sub Stored In A COMPLETELY DIFFERENT WORKBOOK - 4:18
    • A Neat Trick To Force Variable Declaration - 1:09
    • Variables - All The Techie Bits - 9:29
    • An Introduction To Looping - 1:07
    • Looping With Do Loop - 5:01
    • Looping With For Next - 2:33
    • Looping With A Stepped For Next - 2:02
    • Looping With While Wend - 7:10
    • An Introduction To Logical Testing - 1:41
    • Logical Testing - A Simple If Test - 6:43
    • Logical Testing - A Simple If Test Using Cells - 5:12
    • Logical Testing - If Then Else - 3:17
    • Logical Testing - If Then Else Using Cells - 3:41
    • Logical Testing - Testing Multiple Criteria - 3:33
    • Logical Testing - Testing If One Is True, And One Is False - 2:19
    • Logical Testing - Testing If Either Value Is True - 3:21
    • Logical Testing - Select Case - 6:16
    • Maths - Doing Simple Maths In Code - 2:42
    • Maths - Writing Formulas To Single Cells - 6:56
    • Maths - Writing Formulas To Ranges Of Cells - 5:19
    • Maths - Using Excel's Built-in Functions - 3:55
    • Maths - Built-in Functions With Defined Ranges - 3:25
    • InputBox - Getting User Input Using The InputBox Function - 5:29
    • Manipulating The User Input With Casing - 4:20
    • InputBox - Getting User Input Using The InputBox Method - 5:54
    • Message Boxes - Simple Message Boxes - 4:06
    • Message Boxes - Testing Which Button Was Pressed - 4:47
    • Arrays - An Introduction - 2:37
    • Arrays - A Simple One Dimensional Static Array - 5:56
    • Arrays - A Simple One Dimensional Dynamic Array - 4:44
    • Arrays - A Simple Two Dimensional Static Array - 5:33
    • Arrays - The Most Efficient Way To Capture An Array - 4:55
    • Arrays - Extracting Useful Data Based On User Input - 7:41
    • Using An Array As A Data Source For A VLookup - 2:55
  • Automating All Your Reports!
    • Introduction To Monthly Reporting - 2:28
    • Automating All Your Reports - 2010 Users - 1:20
    • Recording The Bones Of The Code - 4:25
    • Streamlining The Add New Sheet Code - 6:11
    • Deconstructing The Profit By Day Code2 - 2:09
    • Building Source Data Strings Dynamically At Runtime - 7:53
    • Creating Run Order and Data Capture Subs - 3:47
    • Solving That Naming Problem - 3:04
    • POWER USER - Sizing Your Charts Precisely - 3:17
    • Changing The Chart Title (And Why We Do It Separately) - 2:37
    • Deconstructing The Pivot Tables (It's Slightly Different) - 7:36
    • Titles, Money And Sorting - 4:46
    • Butchering One Table, To Create Another - 4:33
    • Adding The Commentary - Building Strings Dynamically At Runtime - 7:31
    • Adding The Commentary Using Data From The Sheet We're On - 3:56
    • POWER USER - How DO You Make Specific Words Bold - 4:36
    • POWER USER - INSTR...A Very Useful Function - 5:03
    • INSTR And Paying Attention To Detail - 2:54
    • Tidy Up The Title - 3:01
    • Easy As Pie (Chart) - 5:53
    • Pretty Up Our Pie Chart - 2:50
    • Putting It All Together - 4:50
  • Super Web Query - The Data Is Out There...On The Internet, That Is
    • Introduction To The Web Query section - 1:38
    • Pulling Data From The Internet - Capturing The Data For Rome - 4:18
    • Getting To Cancun And London From Rome - 5:59
    • Data Clean-Up - 4:11
    • A Simple Find And Replace - 2:07
    • Getting Our Formulas Right - 3:51
    • Streamlining The Formulas Code - 4:16
    • POWER USER - Displaying Messages In The Status Bar (Cool) - 3:06
    • Putting It All Together - 1:55
  • Workbook Events You Don't Have To Run Code To Have Code Run!
    • Intro To The Events Section - 2:06
    • WorkBook SheetActivate - 4:09
    • WorkBook BeforePrint - 2:47
    • WorkBook SheetChange - 1:14
    • WorkBook Open - Creating An Auto-Back Up - 5:17
    • WorkBook Open - Creating A Splash Screen - 3:55
    • WorkBook Open - Calling Other Code - 5:41
    • WorkBook BeforeClose - 3:09
    • WorkSheet Activate - You Can't Pick This - 1:44
    • WorkSheet Activate - You Might Pick This - 2:44
    • WorkSheet Change - 2:32
    • WorkSheet Change - A More Useful Use - 4:57
    • WorkSheet Activate - Top Secret Classified Information - 3:31
    • BONUS - WorkSheet Events - 4 New Things To Try - 8:34
  • User Defined Functions...What To Do If The Function You Need Isn't In Excel!
    • User Defined Functions...What They Are, And How You Make Them - 5:39
    • Using A UDF To Return Information - 1:41
    • Creating A Countdown Timer With A UDF - 8:30
    • A Custom UDF For Calculating Volume Discount - 4:07
    • A UDF For Getting All Your Sheet Names - 3:05
    • Calling A UDF From A Different Workbook - 1:47
    • SuperCountIf and SuperSumIf UDF's - 10:18
  • Bonus Section Controlling Windows - Folder Creation Gizmo
    • Introduction To The Folder Creation Gizmo - 2:24
    • Creating A New Folder With A Single Line Of Code - 0:55
    • A Single Level Folder Structure - 5:27
    • Folders Within Folders - 5:36
  • Bonus Section eMail Automation With Outlook...Why WRITE emails!
    • Introduction To The Emailing Section - 3:10
    • Understanding The eMail Routine - 4:57
    • Capturing All The Data - 10:14
    • The eMail Loop - 5:06
  • Bonus Section Word Automation - Controlling Word From Excel
    • Introduction To The Word Section - 0:45
    • Understanding The Word Routine - 2:17
    • Deconstructing How We Capture All The Data - 3:02
    • Formula Modifications With Unique Values - 3:18
    • Efficient Sorting - 2:56
    • Building The Text And Wrap Up - 5:16
  • Bonus Section PowerPoint Automation - Create Your Presentation In Seconds!
    • Introduction To The PowerPoint Section - 1:12
    • A Run Through The PowerPoint Base Code - 9:23
    • Setting Up The Shell Of The Code - 3:13
    • Who's Presenting This... - 2:31
    • Adding A Slide With A Logo And Text - 8:20
    • Prettying Up The Formatting (More Lego Coding!) - 2:03
    • Using Slide 1 To Create Slide 2 - 4:33
    • Adding A Chart As A Picture - 6:43
    • Adding Pivot Tables (And Another Chart) - 9:12
    • Final Slide, And Wrap Up - 2:12
  • Importing Specific Data From Multiple Files
    • Introduction To Importing Data From A Folder Full Of Files - 3:26
    • Looping Through All Excel Files In A Folder - 3:56
    • The Folder Picker - 3:11
    • A More Useful Loop Through Files - 3:20
    • The Data Grabber(er) - 13:17
  • Student Questions Answered
    • Level 3 SQA 01 - Get Rid Of Rows In An Array - 5:49
    • Level 3 SQA 02 - Emailing Routine Adding a Specific Attachment Based On a Criteria - 3:34
    • Level 3 SQA 03 - Adding A Date Stamp, And Going To The Insertion Point AUTOMATICALLY - 7:25
    • Level 3 SQA 04 - Saving An Individual Sheet To A Specific Folder - 11:41
    • Level 3 SQA 05 - Saving Multiple Sheets To A Single Workbook In A Specific Folder - 27:29
    • Level 3 SQA 06 - Animated Charts...With A Little Something Extra! - 6:23
    • Level 3 SQA 07 - Extracting Specific Data From A Big File, To A Bunch Of Little Ones - 7:25
    • Level 3 SQA 08 - Finding A Search String in Another Workbook With Multiple Sheets - 4:54
    • Level 3 SQA 09 - Extracting Unique Tables to Unique Sheets From A Big Data Set - 13:06
    • Level 3 SQA 10 - Protecting Specific Cells and Data Validation - 11:34
    • Level 3 SQA 11 - Dynamically Populating A Reusable Array While Looping Through A Table - 5:24
    • Level 3 SQA 12 - Sequential PDF Creation With Pictures - 17:02
    • Level 3 SQA 13 - File Picker, And Report Generator With Intelligent Filing - 13:09
    • Level 3 SQA 14 - Why Is My Workbook So Big - 7:26
    • Level 3 SQA 15 - Finding Updated Values In One Workbook, And Adding Them To Another Workbook - 9:10
    • Level 3 SQA 16 - Gantt Charts With A Little More Sophistication - 10:30
    • Level 3 SQA 17 - Vlookups (Or Any Application.WorksheetFunction) Over Ranges - 12:20
    • Level 3 SQA 18 - Adding A Date Stamp If A Change Has Been Made - 5:36

View Full Curriculum


Access
Lifetime
Content
1.0 hours
Lessons
96

Intermediate Microsoft Excel Training

Advance Your Skill Set with MS Excel 2019 & Work with Charts and Advanced Formatting

By Webucator | in Online Courses

This Intermediate Microsoft Excel training class is meant for students using Excel 2019 or Excel 365 who want to advance their skill set by learning to work with advanced formulas, lists, and illustrations. Students will also work with charts and advanced formatting including styles. Learn to streamline and analyze data and to keep data organized by managing large data sets with lists and tables. Learn to use filters to save time, as well as great ways to group and summarize data.

9.3/10 rating based on 30,389 reviews

  • Access 96 lectures & 1 hour of content 24/7
  • Learn to use formulas & functions
  • Convert, sort, filter & manage lists
  • Insert & modify illustrations in a worksheet
  • Learn to use conditional formatting & styles

Note: Software NOT included.

"I have done an "at your own pace" and a live "on-line" course - both are excellent and I would highly recommend. Great instructors! – Debbi Doris

Webucator is a global training company founded in 2003 that has trained over 60,000 students. They provide onsite and online training on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Webucator has trained more than 63,677 students from over 11,907 organizations are a Microsoft Certified Partner for Learning Solutions (CPLS).

Important Details

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

Requirements

  • Basic Excel
  • Pre-requisite course: Introduction to Microsoft Excel 2019 Training

Course Outline

  • Setup
    • Setup for Your Class
  • Lesson 1 - Advanced Formulas
    • Activity 1 - Using Named Ranges in Formulas - Reading
    • Activity 2 - Review of Using Named Ranges in Formulas - Presentation - 1:14
    • Activity 3 - Using Named Ranges in Formulas - Exercise
    • Activity 4 - Exercise Solution - Using Named Ranges in Formulas - Presentation - 3:00
    • Activity 5 - Using Formulas That Span Multiple Worksheets - Reading
    • Activity 6 - Entering a Formula Using Data in Multiple Worksheets - Exercise
    • Activity 7 - Exercise Solution - Entering a Formula Using Data in Multiple Worksheets - Presentation - 3:10
    • Activity 8 - Using the IF Function - Reading
    • Activity 9 - Using the IF Function - Exercise
    • Activity 10 - Exercise Solution - Using the IF Function - Presentation - 3:39
    • Activity 11 - Using the PMT Function - Reading
    • Activity 12 - Using the PMT Function - Exercise
    • Activity 13 - Exercise Solution - Using the PMT Function - Presentation - 5:02
    • Activity 14 - Using the LOOKUP Function - Reading
    • Activity 15 - Using the VLOOKUP Function - Reading
    • Activity 16 - Using the VLOOKUP Function - Exercise
    • Activity 17 - Exercise Solution - Using the VLOOKUP Function - Presentation - 4:01
    • Activity 18 - Using the HLOOKUP Function - Reading
    • Activity 19 - Using the CONCAT Function - Reading
    • Activity 20 - Using the CONCAT Function - Exercise
    • Activity 21 - Exercise Solution - Using the CONCAT Function - Presentation - 2:28
    • Activity 22 - Using the TRANSPOSE Function - Reading
    • Activity 23 - Using the PROPER, UPPER, and LOWER Functions - Reading
    • Activity 24 - Using the PROPER Function - Exercise
    • Activity 25 - Exercise Solution - Using the PROPER Function - Presentation - 1:31
    • Activity 26 - Using the LEFT, RIGHT, and MID Functions - Reading
    • Activity 27 - Using the LEFT and RIGHT Functions - Exercise
    • Activity 28 - Exercise Solution - Using the LEFT and RIGHT Functions - Presentation - 2:48
    • Activity 29 - Using Date Functions - Reading
    • Activity 30 - Using the YEAR, MONTH, and DAY Functions - Exercise
    • Activity 31 - Exercise Solution - Using the YEAR, MONTH, and DAY Functions - Presentation - 2:22
    • Activity 32 - Creating Scenarios - Reading
    • Activity 33 - Advanced Formulas Quiz - Quiz
  • Lesson 2 - Working with Lists
    • Activity 1 - Converting a List to a Table - Reading
    • Activity 2 - Converting a List to a Table - Exercise
    • Activity 3 - Exercise Solution - Converting a List to a Table - Presentation - 2:15
    • Activity 4 - Removing Duplicates from a List - Reading
    • Activity 5 - Removing Duplicates from a List - Exercise
    • Activity 6 - Exercise Solution - Removing Duplicates from a List - Presentation - 1:33
    • Activity 7 - Sorting Data in a List - Reading
    • Activity 8 - Review of Sorting Data in a List - Presentation - 2:06
    • Activity 9 - Sorting Data in a List - Exercise
    • Activity 10 - Exercise Solution - Sorting Data in a List - Presentation - 2:09
    • Activity 11 - Filtering Data in a List - Reading
    • Activity 12 - Filtering Data in a List - Exercise
    • Activity 13 - Exercise Solution - Filtering Data in a List - Presentation - 3:20
    • Activity 14 - Adding Subtotals to a List - Reading
    • Activity 15 - Adding Subtotals to a List - Exercise
    • Activity 16 - Exercise Solution - Adding Subtotals to a List - Presentation - 1:49
    • Activity 17 - Working with Lists Quiz - Quiz
  • Lesson 3 - Working with Illustrations
    • Activity 1 - Working with Clip Art - Reading
    • Activity 2 - Working with Clip Art - Exercise
    • Activity 3 - Exercise Solution - Working with Clip Art - Presentation - 1:41
    • Activity 4 - Using Shapes - Reading
    • Activity 5 - Adding Shapes - Exercise
    • Activity 6 - Exercise Solution - Adding Shapes - Presentation - 1:09
    • Activity 7 - Working with Icons - Reading
    • Activity 8 - Working with SmartArt - Reading
    • Activity 9 - Using Office Ink - Reading
    • Activity 10 - Working with Illustrations in Excel Quiz - Quiz
  • Lesson 4 - Visualizing Your Data
    • Activity 1 - Using Charts - Reading
    • Activity 2 - New Charts - Reading
    • Activity 3 - Inserting Charts - Reading
    • Activity 4 - Review of Inserting Charts - Presentation - 3:09
    • Activity 5 - Using the Chart Recommendation Feature - Reading
    • Activity 6 - Inserting Charts - Exercise
    • Activity 7 - Exercise Solution - Inserting Charts - Presentation - 2:16
    • Activity 8 - Editing Charts - Reading
    • Activity 9 - Review of Editing Charts - Presentation - 1:55
    • Activity 10 - Using Chart Tools - Reading
    • Activity 11 - Using the Quick Analysis Tool - Reading
    • Activity 12 - Editing Charts - Exercise
    • Activity 13 - Exercise Solution - Editing Charts - Presentation - 3:06
    • Activity 14 - Add and Format Objects - Reading
    • Activity 15 - Create a Custom Chart Template - Reading
    • Activity 16 - Visualizing Your Data Quiz - Quiz
  • Lesson 5 - Working with Tables
    • Activity 1 - Format Data as a Table - Reading
    • Activity 2 - Formatting Your Data as a Table - Presentation - 1:29
    • Activity 3 - Move between Tables and Ranges - Reading
    • Activity 4 - Modify Tables - Reading
    • Activity 5 - Define Titles - Reading
    • Activity 6 - Creating and Modifying a Table in Excel - Exercise
    • Activity 7 - Exercise Solution - Creating and Modifying a Table in Excel - Presentation - 1:37
    • Activity 8 - Working with Tables in Excel Quiz - Quiz
  • Lesson 6 - Advanced Formatting
    • Activity 1 - Applying Conditional Formatting - Reading
    • Activity 2 - Review of Applying Conditional Formatting - Presentation - 2:48
    • Activity 3 - Using Conditional Formatting - Exercise
    • Activity 4 - Exercise Solution - Using Conditional Formatting - Presentation - 5:51
    • Activity 5 - Working with Styles - Reading
    • Activity 6 - Review of Working with Styles - Presentation - 1:29
    • Activity 7 - Working with Styles - Exercise
    • Activity 8 - Exercise Solution - Working with Styles - Presentation - 2:19
    • Activity 9 - Creating and Modifying Templates - Reading
    • Activity 10 - Advanced Excel Formatting Quiz - Quiz
  • Lesson 7 - Intermediate Microsoft Excel 2019 Exam
    • Activity 1 - Intermediate Microsoft Excel 2019 Exam - Exam

View Full Curriculum


Access
Lifetime
Content
1.0 hours
Lessons
78

Advanced Microsoft Excel Training

Use Excel 2019 to Your Advantage & Work on More Critical Functions Including Data Tools, Macros, Pivot Tables, and More

By Webucator | in Online Courses

Already comfortable using Microsoft Excel 2019 or Excel 365 and ready to become an Excel expert? Take your skills to the next level in this Advanced Excel course. Learn Excel's most powerful and advanced tools, including PivotTables, PivotCharts, Macros, and more. You will gain skills for better collaboration, including password protection and tracking changes, learn to audit worksheets, validate data to ensure consistency, and use other essential business tools in Microsoft Excel.

9.3/10 rating based on 30,389 reviews

  • Access 78 lectures & 1 hour of content 24/7
  • Create pivot tables & charts
  • Learn to trace precedents & dependents
  • Convert text & validate and consolidate data
  • Collaborate w/ others by protecting worksheets & workbooks
  • Create, use, edit & manage macros
  • Import & export data

Note: Software NOT included.

"The class was very practical and relatable. The instructor created a positive atmosphere." – Jennifer Araujo-Bray

Webucator is a global training company founded in 2003 that has trained over 60,000 students. They provide onsite and online training on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Webucator has trained more than 63,677 students from over 11,907 organizations are a Microsoft Certified Partner for Learning Solutions (CPLS).

Important Details

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

Requirements

  • Basic & intermediate Excel experience

Course Outline

  • Setup
    • Setup for Your Class
  • Lesson 1 - Using Pivot Tables
    • Activity 1 - What Are Pivot Tables_ - Reading
    • Activity 2 - Creating Pivot Tables - Reading
    • Activity 3 - Review of Creating Pivot Tables - Presentation - 2:21
    • Activity 4 - More PivotTable Functionality - Reading
    • Activity 5 - Inserting Slicers - Reading
    • Activity 6 - Multi-Select Option in Slicers - Reading
    • Activity 7 - Review of Inserting Slicers - Presentation - 2:14
    • Activity 8 - PivotTable Enhancements - Reading
    • Activity 9 - Working with Pivot Tables - Reading
    • Activity 10 - Inserting Pivot Charts - Reading
    • Activity 11 - More Pivot Table Functionality - Reading
    • Activity 12 - Working with Pivot Tables - Exercise
    • Activity 13 - Exercise Solution - Working with Pivot Tables - Presentation - 4:35
    • Activity 14 - Using Pivot Tables Quiz - Quiz
  • Lesson 2 - Auditing Worksheets
    • Activity 1 - Formatting Auditing Commands - Reading
    • Activity 2 - Tracing Precedents - Reading
    • Activity 3 - Tracing Precedents - Exercise
    • Activity 4 - Exercise Solution - Tracing Precedents - Presentation - 2:44
    • Activity 5 - Tracing Dependents - Reading
    • Activity 6 - Tracing Dependents - Exercise
    • Activity 7 - Exercise Solution - Tracing Dependents - Presentation - 1:26
    • Activity 8 - Showing Formulas - Reading
    • Activity 9 - Auditing Worksheets Quiz - Quiz
  • Lesson 3 - Data Tools
    • Activity 1 - Converting Text to Columns - Reading
    • Activity 2 - Review of Converting Text to Columns - Presentation - 2:09
    • Activity 3 - Converting Text to Columns - Exercise
    • Activity 4 - Exercise Solution - Converting Text to Columns - Presentation - 2:09
    • Activity 5 - Linking to External Data - Reading
    • Activity 6 - Controlling Calculation Options - Reading
    • Activity 7 - Data Validation - Reading
    • Activity 8 - Review of Data Validation - Presentation - 2:53
    • Activity 9 - Using Data Validation - Exercise
    • Activity 10 - Exercise Solution - Using Data Validation - Presentation - 3:53
    • Activity 11 - Consolidating Data - Reading
    • Activity 12 - Consolidating Data - Exercise
    • Activity 13 - Exercise Solution - Consolidating Data - Presentation - 2:33
    • Activity 14 - Goal Seek - Reading
    • Activity 15 - Using Goal Seek - Exercise
    • Activity 16 - Exercise Solution - Using Goal Seek - Presentation - 4:48
    • Activity 17 - Data Tools Quiz - Quiz
  • Lesson 4 - Working with Others
    • Activity 1 - Protecting Worksheets and Workbooks - Reading
    • Activity 2 - Password Protecting a Workbook - Exercise
    • Activity 3 - Exercise Solution - Password Protecting a Workbook - Presentation - 1:30
    • Activity 4 - Password Protecting a Worksheet - Exercise
    • Activity 5 - Exercise Solution - Password Protecting a Worksheet - Presentation - 1:33
    • Activity 6 - Password Protecting Ranges in a Worksheet - Exercise
    • Activity 7 - Exercise Solution - Password Protecting Ranges in a Worksheet - Presentation - 1:26
    • Activity 8 - Marking a Workbook as Final - Reading
    • Activity 9 - Working with Others Quiz - Quiz
  • Lesson 5 - Recording and Using Macros
    • Activity 1 - Recording Macros - Reading
    • Activity 2 - Review of Recording Macros - Presentation - 1:42
    • Activity 3 - Recording a Macro - Exercise
    • Activity 4 - Exercise Solution - Recording a Macro - Presentation - 2:03
    • Activity 5 - Running Macros - Reading
    • Activity 6 - Editing Macros - Reading
    • Activity 7 - Adding Macros to the Quick Access Toolbar - Reading
    • Activity 8 - Adding a Macro to the Quick Access Toolbar - Exercise
    • Activity 9 - Exercise Solution - Adding a Macro to the Quick Access Toolbar - Presentation - 1:04
    • Activity 10 - Recording and Using Macros Quiz - Quiz
  • Lesson 6 - Random Useful Items
    • Activity 1 - Sparklines - Reading
    • Activity 2 - Review of Using Sparklines - Presentation - 2:32
    • Activity 3 - Inserting and Customizing Sparklines - Exercise
    • Activity 4 - Exercise Solution - Inserting and Customizing Sparklines - Presentation - 3:26
    • Activity 5 - Using Microsoft Translator - Reading
    • Activity 6 - Preparing a Workbook for Internationalization and Accessibility - Reading
    • Activity 7 - Importing and Exporting Files - Reading
    • Activity 8 - Importing Text Files - Exercise
    • Activity 9 - Exercise Solution - Importing Text Files - Presentation - 2:32
    • Activity 10 - Copying Data from Excel to Word - Exercise
    • Activity 11 - Exercise Solution - Copying Data from Excel to Word - Presentation - 1:25
    • Activity 12 - Copying Charts from Excel to Word - Exercise
    • Activity 13 - Exercise Solution - Copying Charts from Excel to Word - Presentation - 1:07
    • Activity 14 - Excel Random Useful Items Quiz - Quiz
  • Lesson 7 - Cloud
    • Activity 1 - Using the Cloud - Reading
    • Activity 2 - Using the Cloud - Exercise
    • Activity 3 - Cloud Review - Presentation - 2:07
  • Lesson 8 - Advanced Microsoft Excel 2019 Exam
    • Activity 1 - Advanced Microsoft Excel 2019 Exam - Exam

View Full Curriculum



Terms

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