Microsoft Excel: From Excel Beginner to Excel Formula Master
4.0 (91 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,775 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel: From Excel Beginner to Excel Formula Master to your Wishlist.

Add to Wishlist

Microsoft Excel: From Excel Beginner to Excel Formula Master

Transforms Students into Microsoft Excel Wizards by Solving Practical Problems in Excel. Can Use Excel 2003 - Excel 2016
4.0 (91 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,775 students enrolled
Created by Jarrod Tanton
Last updated 5/2016
English
Current price: $10 Original price: $200 Discount: 95% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 16.5 hours on-demand video
  • 18 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Become an Excel formula master who is proficient in reading and writing even the most complex formulas in Excel.
  • Understand the majority of Excel’s built in functions and how to use them to efficiently solve a wide range of problems with Excel.
  • Creatively write formulas in Excel to address complex practical problems by combining Excel's functions together.
  • Save time by learning Excel tips, tricks, and traps to look out for as I share advice that I’ve acquired from using Excel over the years.
View Curriculum
Requirements
  • Students will need to have access to Microsoft Excel. The course is taught in Excel 2010. For the best experience students should have Excel 2010 or later. However, many of the concepts taught in this course are related to the core functions in Excel, which have not changed much since Excel 2003. Therefore, if you only have access to Excel 2003, you will still be able to follow along and will get quite a bit of value from the course.
Description

The Excel Apprenticeship Series is focused on transforming students into Excel Wizards to give them a distinct competitive advantage over their peers. This installment of the series is about mastering the art and science of problem solving with formulas in Excel.

The course takes a unique approach that is rooted in solving real world problems. This gives students an apprenticeship experience as opposed to a traditional lecture approach. By the end of the course, students will have worked nearly 170 examples and will have learned how to leverage Excel to solve just about any problem they might face in practice. Students will cover just under 100 functions in the course and will be proficient in reading and writing even the most complex formulas in Excel.

The course is broken down into two modules. The first module provides an introduction to Excel. This module is intended to get everyone on the same page, so even those who have never used Excel can feel comfortable starting with this course.

The second module teaches students how to become formula masters and expert problem solvers with Excel. This module is broken down into seven sections that are mapped to functional areas, once again putting an emphasis on how Excel is used in the real world.

This course offers students:

  • Over 16 hours of valuable video instruction that is uniquely structured to share the lessons learned from over a decade of professional experience
  • Over 125 quiz questions strategically placed throughout the course to help test your understanding and reinforce what you’ve learned
  • A Practical Application Workbook and solution manual for each section
  • An Excel Wizard’s Guide to Critical Skills
  • A Comprehensive Data Aggregation Reference Sheet

This course is intended for individuals who have a desire to become phenomenal at writing formulas in Excel. This includes:

  • Recent or soon to be college graduates
  • Individuals who work in analyst, consulting, management, or supervisory roles
  • Individuals looking to gain a tangible skill on their resume
  • Individuals looking to gain a competitive advantage over their peers


Recent studies show it pays to know Excel. Get started today!

Who is the target audience?
  • This course is for individuals who have a desire to be phenomenal at writing formulas and problem solving in Microsoft Excel. The people that will benefit from this course the most are recent or soon to be college grads, individuals working in analyst, consulting, management, or supervisory roles, individuals looking to gain a tangible skill on their resume, or individuals who currently use Excel in their daily job and would like to better understand how they can leverage Excel to help them with their work.
  • This course is not for casual students who are looking to learn just the basics of Excel. Also, because this course focuses on how to process and transform data using Excel’s built-in functions in depth, it does not cover pivot tables or the visualization of data (charting). These topics are covered separately in their own course. If you already know how to use Excel’s reference functions, can write your own array formulas to aggregate data conditionally, and have plenty of experience working with strings, dates, and times, this course is probably not for you.
  • As a bonus, this course has an entire module that is dedicated to covering the Excel basics needed to get newbies up to speed so that they can benefit from this course as well.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
181 Lectures
16:18:41
+
Module 1: Introduction to Excel
1 Lecture 01:37

Provides an overview of the material covered in Module 1: Introduction to Excel

Preview 01:37
+
1.1 The What, Why, and How of Excel
1 Lecture 04:25

In this lesson, we'll take a high level look at Excel and review a few real world examples to understand why Excel is so popular and how it is used in industry to solve problems.

The What, Why, and How of Excel
04:25
+
1.2 Excel Components and User Interface
4 Lectures 15:35

Learn how workbooks, worksheets, ranges, and cells relate to one another and about the most common file extensions for Excel.

Excel Components and File Extensions
02:36

Learn about Excel's ribbon.

Excel's User Interface: The Ribbon
04:55

Learn about the Quick Access and Formula toolbars.

Excel's User Interface: Quick Access and Formula Toolbars
04:06

Learn how to add/remove, move/copy, rename and navigate worksheets.

Excel's User Interface: The Worksheet
03:58
+
1.3 Introduction to Formulas and Functions
3 Lectures 23:03

Learn the order of operations and structure of formulas and functions.

Introduction to Formulas and Functions
02:57

Show off your newly acquired knowledge of formulas and functions!

Formulas and Functions
4 questions

Learn how to write simple formulas and how to apply the order of operations in Excel.

Order of Operations and Formulas
06:48

Learn how to write formulas using Excel's built in functions. We'll review how to use function tool tips, discuss the different types of arguments, and demonstrate how to provide functions hard-typed values, as well as, values using references.

Using Excel's Built in Functions
13:18
+
1.4 Overview of Cell References
1 Lecture 12:02

Learn why cell references are like street addresses and understand the impact copy/paste, insert, delete, and move operations have on relative and absolute cell references.

Understanding Cell References
12:02

Test your skills on cell references!

Cell References
2 questions
+
Module 2: Process and Transform Data
1 Lecture 02:44

Provides an overview of the material covered in Module 2: Process and Transform Data

Preview 02:44
+
2.1 Performing Data Aggregation
16 Lectures 01:46:06

Get an overview of the material covered in Module 2.1 - Performing Data Aggregation. The workbooks used in this module are attached as resources.

Module 2.1 Overview
01:34

Learn how to perform simple data aggregations and make use of data with the sum, count, counta, min, max, and average functions.

Simple Aggregation Functions
12:00

Show off your knowledge of the simple aggregation functions!

Simple Aggregation Functions
4 questions

Learn how to use the countif, sumif, and averageif functions to aggregate data conditionally (i.e., data that meets a specific criteria).

Single Criteria Conditional Aggregation: Static Criteria
08:13

Using the countif, sumif, and averageif functions, build a sales report that dynamically aggregates car sales data to display metrics about a day's sales.

Single Criteria Conditional Aggregation: Dynamic Criteria
08:59

Expand your data aggregation capabilities by learning how to test greater than, less than, and not equal to test conditions.

Single Criteria Conditional Aggregation: >, <, and <> Conditions
10:18

Build a management reporting tool that shows the percentage complete for a selected project using the single criteria conditional aggregation functions.

Single Criteria Conditional Aggregation: Combining Aggregations
03:39

Show off your knowledge of the single criteria conditional aggregation functions.

Single Criteria Conditional Aggregation Functions
12 questions

Get an overview of the multiple criteria conditional aggregation functions and build a dynamic table using the countifs function that powers a dynamic chart.

Multiple Criteria Conditional Aggregation
10:11

Get more practice using the averageifs function to aggregate data on multiple conditions when all criteria need to be true ("and testing"). We'll see how the multiple criteria conditional aggregation functions can be used on both vertical and horizontal data sets.

Multiple Criteria Conditional Aggregation: AND Testing
04:40

Learn how to creatively perform "OR testing" by combining two or more single criteria conditional aggregation functions together.

Multiple Criteria Conditional Aggregation: OR Testing
04:13

Review an array formula cheat sheet to get a small taste of array formulas and then learn how to combine arrays with the single criteria conditional aggregation functions to perform or testing.

Multiple Criteria Conditional Aggregation: Using Arrays for OR Testing
06:42

Work an additional problem where you get to use array formulas to perform OR testing on a single range. Then expand your OR testing capabilities by learning how to perform OR testing on criteria that applies to more than one row or column.

Preview 08:17

Show off your knowledge of the multiple criteria conditional aggregation functions!

Multiple Criteria Conditional Aggregation Functions
5 questions

Review an example of how array multiplication can be used to aggregate the data from many columns at once.

Bonus: Using Array Multiplication to Aggregate Data from Many Columns
05:03

Obtain a high level overview of the 6 database aggregation functions we will be covering in this course. (DCOUNT, DCOUNTA, DSUM, DMIN, DMAX, and DAVERAGE)

Database Aggregation Functions: Overview
03:39

Learn how to build a dynamic reporting tool, using the database aggregation functions, that allows you to quickly answer questions about a data set.

Database Aggregation Functions: Building a Reporting Tool
10:03

Learn how to limit data on a range of values and learn how to use Excel's wildcards ("?" and "*") to increase your database aggregation capabilities.

Database Aggregations: Advanced Searches
05:30

Show off your knowledge of the multiple criteria conditional aggregation functions!

Database Aggregation Functions
4 questions

Recap the key concepts you learned in Module 2.1.

Performing Data Aggregation: Wrap Up
03:05
+
2.2 Performing Logical Operations
8 Lectures 34:58

Get an overview of the material covered in Module 2.2 - Performing Logical Operations. The workbooks used in this module are attached as resources.

Module 2.2 Overview
01:17

Learn how to answer questions about your data by using the AND / OR functions to evaluate a series of logical tests or values.

Logical Operators
08:11

Learn how to use the if and iferror functions to conditionally control the outcome of formula.

Conditional Functions Overview
03:41

Use the if function to build a tool that reports on the adherence to a monthly budget. Also, learn how to expand the power of the if function by combining it with the and / or functions.

More on Conditional Functions: The If Function
07:44

Learn how if functions can be nested together to handle situations that require more than two possible outcomes.

Handling more than two Outcomes: Nesting If Functions
06:29

Learn how to make your tools more robust by using the iferror function to gracefully handle errors.

Simple Error Handling with the Iferror Function
02:52

Learn how to use the not function to inverse logical values.

Logical Value Functions: True, False, and Not
03:02

Show off your knowledge of logical operations in Excel.

Logical Operations
5 questions

Recap the key concepts you learned in Module 2.2.

Performing Logical Operations: Wrap Up
01:42
+
2.3 Mastering Lookups and References
53 Lectures 05:15:13

Get an overview of the material covered in Module 2.3 - Mastering Lookups and References. The workbooks used in this module are attached as resources.

Preview 02:41

Get an overview of the choose function and understand what makes it unique among its lookup counterparts.

Preview 03:35

Learn how to leverage the choose function to provide range inputs to other functions dynamically based on a user input. As a bonus, learn another method (combo box) to create a drop down menu in Excel and learn why this style of drop down complements the choose function so well.

Preview 08:54

Learn how the choose function handles decimal values and how to use that knowledge to transform quantitative values into qualitative ones. Also, learn how the choose function can be used to build dynamic tools by allowing users to select a calculation to perform.

Choose Function: Transforming Quality Scores and Selecting a Forecast Model
07:00

Learn how nesting functions within the choose function can be helpful. You will learn how to return the quarter given a date and you will learn how to dynamically perform different calculations based on user input.

Choose Function: Nesting Functions Inside Choose
05:42

Show off your knowledge of the choose function.

The Choose Function
4 questions

Learn about Excel's most popular lookup functions - vlookup and hlookup. We'll discuss the difference between vlookup and hlookup and review an example of how they work.

Vlookup and Hlookup: An Overview
03:41

Learn some very important concepts about the vlookup function by working a simple example. Here you will learn why its important to lock down the range for the table you are searching, what causes the vlookup function to return the value not found and reference errors, some common reasons why those errors can be misleading, and what happens when the table you are searching contains duplicate values.

Vlookup: Key Concepts
10:09

Work a more realistic example and learn how to use the vlookup function to make a report meaningful by translating a set of classification codes into their descriptions.

Vlookup: Adding Meaning to Classifications
06:30

Learn a tactic to help you lookup information even when you don't have a unique field.

Vlookup: Making it Unique
06:50

Learn how to use vlookup's approximate match by building a tool to calculate the commission percentage due based on a multi-tier sales structure.

Vlookup: Understanding Approximate Match
05:26

Put your knowledge of the approximate match to use and build a federal income tax calculator.

Vlookup: A Federal Income Tax Calculator
08:07

Learn how the choose function can be combined with the vlookup function to let you dynamically lookup information from different tables.

Vlookup: Searching Multiple Tables
02:53

Learn how to use the hlookup function. Recognize that everything you just learned about vlookup applies to hlookup as well

Hlookup: More of the Same, Kind of
06:39

Show off your knowledge about the vlookup and hlookup functions.

The Vlookup and Hlookup Functions
6 questions

Learn about Excel's most powerful lookup functions - index and match.

Index and Match: An Overview
03:31

Learn how to use the match function to return the relative position of an item from a list.

Match: Finding the Position
04:16

Learn how the greater than and less than approximate match types work with the match function.

Match: Understanding the Approximate Match
05:56

Learn how to use the index function to lookup information from a one-dimensional array (data in a single column or single row) and learn a time saving tip for copying and pasting values inside of formulas.

Index: A Special Case with 1D Arrays
06:39

Learn how to use the index function to retrieve data from a table.

Index: Getting Values from a Table
05:20

Learn how to use the other version of the index function to lookup information from more than one table.

Index: Getting Values from Multiple Tables
03:01

Learn how to use the index function to return a reference instead of a value. Also, learn how the index function can be combined with the counta function to construct a dynamic range that can save you time.

Index: Returning a Reference Instead of a Value
06:29

Show off your knowledge about the index and match functions.

The Index and Match Functions
10 questions

Learn how the index and match functions can be combined together to create a powerful lookup machine and review the benefits that this type of lookup has over the vlookup and hlookup functions.

Index and Match: Benefits of Index / Match Lookup
04:44

Learn how an index / match lookup can be used to perform left lookups and how it is resilient to columns being inserted into the lookup range.

Index and Match: Left lookups and Reference Stability
06:41

Learn how to perform a truly dynamic lookup by using the match function to supply both a row and column number to the index function.

Index and Match: Building a Dynamic Order Report
08:46

Learn how to perform an approximate lookup using the index and match functions.

Index and Match: Mastering the Approximate Lookup
04:06

Learn how the approximate index / match lookup can be used to help you solve a common problem in business. Here, we'll build a tool to find the discount for a product that has price breaks based on the quantity a customer orders.

Index and Match: Putting the Approximate Match to Use
06:59

Learn how to perform an index / match lookup that offsets from the located value as we revisit our sales commission problem.

Index and Match: The Power of Offsetting Lookups
04:07

Learn how to perform an index / match lookup that searches multiple tables.

Index and Match: Searching Multiple Tables - Revisited
07:50

Learn a tactic for how to find the first occurrence of a tested condition in a list by using an index / match lookup in conjunction with an array formula.

Index and Match: Finding the First Occurrence
05:22

Learn how to perform dynamic data aggregation by returning the entire row or column when performing an index / match lookup.

Index and Match: Returning the Entire Row or Column
06:21

Show off your knowledge about how the index and match functions can be combined to perform a powerful lookup.

Combining the Index and Match Functions
5 questions

Learn what defined names are, as well as, why and how you would use them.

Defined Names: Overview and Simple Example
07:30

Learn how to use defined names to store ranges and calculations on ranges.

Defined Names: More than Constants - Storing Ranges and Calculations
05:50

Show off your knowledge about defined names!

Defined Names
4 questions

Learn how the offset function works and how it can be used to return a value, as well as, a reference.

Offset: Understanding the Offset Function
06:19

Learn how to use the offset and counta functions to create dropdown lists that automatically update.

Offset: Creating a Dynamic Dropdown List
08:23

Learn how to perform dynamic calculations by using the offset function to provide a dynamic reference to other functions.

Offset: Creating a Dynamic Calculation
04:19

Get more practice performing dynamic calculations by using the offset function to provide a dynamic reference to other functions.

Offset: Another Dynamic Calculation
05:34

Learn how to combine the offset and match functions to build a dynamic table that is the foundation for a dynamic chart.

Offset: Dynamic Charting
05:13

Show off your knowledge about the offset function!

The Offset Function
3 questions

Learn how to use the row and column functions to return the row and column numbers of references. Also, learn how to use the indirect function to indirectly point to a reference that is typed into a cell.

Row and Column: An Overview
05:27

Learn how to find the last row or column in a range and how to automatically number rows and columns by using the row and column functions in conjunction with multi-cell array formulas.

Row and Column: Finding the Last Row and Automatic Numbering
05:45

Learn how to use the rows and columns functions to return the total number of rows or columns in a reference.  Also, learn how to use the indirect function to indirectly point to a reference that is typed into a cell.

Rows and Columns: An Overview
03:47

Learn how to combine the rows and index functions to help you clean up a data set by selecting the values from every other row.

Rows and Columns: Cleaning Up Bad Data
03:51

Show off your knowledge about the row, column, rows, and columns functions!

Row, Column, Rows, and Columns
3 questions

Learn how to build a tool that enables you to lookup information from a table that contains duplicates and return the requested information for each occurrence by combining the index, row, and small functions together in an array formula.

Lookups, References, and Arrays: Returning More than the First Occurrence
13:09

We'll start building a tool here that helps manage the enrollment and scheduling for a series of training events. Learn how to combine the countif, index, and match functions together to dynamically count based on user input.

Lookups, References, and Arrays: Managing Schedules - Part 1
07:35

We'll continue building a tool that helps manage the enrollment and scheduling for a series of training events. Learn how to combine the index, match, row, and small functions together in an array formula to lookup information from a table that contains duplicates and return the requested data for each occurrence that meets the lookup criteria.

Lookups, References, and Arrays: Managing Schedules - Part 2
10:52

We'll finish building a tool that helps manage the enrollment and scheduling for a series of training events. Practice using vlookup along with an index / match lookup to pull information from a table and perform a calculation on it.

Lookups, References, and Arrays: Managing Schedules - Part 3
06:14

Learn how to use the index, match, row, small, and if functions together in an array formula to apply filters to a dataset and return the matching records.

Lookups, References, and Arrays: Searching Data to Return Records
14:24

Learn how to use the address function to return a reference to the cell at the intersection of a given row and column.

Address: An Overview
04:26

Learn how to combine the address function with the row, column, counta, and offset functions to return the last cell in a dynamic list.

Address: Finding the Last Cell of A Dynamic List
02:54

Learn how to save time by combining the address and indirect functions together to access information from different worksheets quickly. Also, get a brief introduction to how Excel's Autofill functionality can save you time.

Address: Getting Data From Templated Worksheets Quickly
05:38

Learn how to use the transpose function to change the orientation of a range of data. Also, learn the difference between the transpose function and the copy and paste special transpose option.

Transpose: An Overview
04:09

Learn how to use the transpose function with array formulas.

Transpose: Transposing Data Inside Array Formulas
04:17

Get an overview of the hyperlink function an learn how to create a link to a web page in a workbook.

Hyperlink: An Overview
02:42

Learn how to use the hyperlink function to create links to different locations within a worksheet or workbook and to link to external workbooks and files. Also, learn how to use relative file paths with the hyperlink function.

Hyperlink: Workbook and File Navigation
07:13

Show off your knowledge about the address, transpose, and hyperlink functions!

Address, Transpose, and Hyperlink Functions
3 questions

Recap the key concepts you learned in Module 2.3.

Mastering Lookups and References: Wrap up
01:27
+
2.4 Working with Informationals
12 Lectures 54:33

Get an overview of the material covered in Module 2.4 - Working with Informationals. The workbooks used in this module are attached as resources.

Module 2.4 Overview
01:31

Get an overview of the istext, isnontext, isnumber, and isblank functions, learn how they handle different types of cell values, and understand how formatting changes impact these functions. Also, learn a trick for using relative references when the ranges you are referencing need to move in a transposed direction.

Value Testers: An Overview
08:28

Learn how to use the isblank and if functions together to handle blank cells conditionally. Also, understand the difference between weighted averages vs the average function.

Value Testers: Special Treatment for Blanks
06:00

Learn how to combine the isnumber and if functions together to build a tool that decides what calculation to perform based on the data type of a user provided value.

Value Testers: Conditionally Controlling Action Based on Input Data Type
04:48

Learn how the iserror, iserr, and isna functions handle each of Excel's error messages.

Error Testers: An Overview
03:19

Learn how to combine the isna, if, and text functions together with the lookup functions to extend their capability to perform lookups when the underlying data types don't match.

Error Testers: Enhancing Lookup Functions
07:38

Learn how to use the iserror and if functions together in an array formula to pre-process data containing errors before it is provided to other functions to prevent errors from bubbling up in subsequent calculations.

Error Testers: Preventing Errors from Bubbling Up
04:44

Get an overview of the cell, info, and type functions and learn how the cell function can be used to prevent errors from occurring.

Core Informationals: An Overview
06:07

Learn how to use the cell function with the format and filename parameters to return the formatting details of a cell and the directory that a workbook is saved in.

Core Informationals: Formatting Details and File Names
04:20

Learn how the cell function works when a reference is not specified and learn how to use the info function to return information about the Excel environment. Also, understand the importance of recognizing that you have many options available when designing solutions in Excel and that it's your job as an Excel Wizard to evaluate the pros and cons of each option before selecting an implementation.

Core Informationals: Last Active Cell and Environment Information
03:26

Learn how to use the type function to identify the type of data stored in a cell and learn how to combine it with the if function to prevent errors in calculations.

Core Informationals: The Type Function
02:55

Show off your knowledge of the informational functions!

Informationals
3 questions

Recap the key concepts you learned in Module 2.4.

Working with Informationals: Wrap Up
01:17
3 More Sections
About the Instructor
Jarrod Tanton
4.0 Average rating
91 Reviews
1,775 Students
1 Course
Founder XLEssentials.com / CEO ReAppDev, Inc.

Hi, I’m Jarrod. I have over a decade of professional experience leveraging Microsoft Excel to solve practical business problems for companies of all sizes and I’m passionate about sharing what I’ve learned over the years with others.

I studied Industrial Engineering at Texas A&M University. After school, I worked as an Industrial Engineer at American Express, spent four years as a project manager at an engineering firm, and then spent three years as a Business Analytics consultant for IBM - where I worked on projects like the Deepwater Horizon oil spill in 2010, automating financial reporting capabilities for one of the world’s largest investment management companies, and partnering with a leading pharmaceutical company to help evaluate the state of the mental healthcare ecosystem.

I eventually left consulting to get more experience in operations management, which led me to spend two years in a finance management role at a large industrial supply company before I decided to launch my own company. While I worked in various industries and sectors over the past ten years, my knowledge of Microsoft Excel consistently gave me a competitive advantage over my peers and helped me advance my career.

A few years into my career, several of my coworkers made a comment about how they thought I was an “Excel Wizard”. That tag stuck with me ever since. In each of my roles, I became the go to person for transforming data sets into actionable information and for helping others with questions they had about Excel. I’ve taught countless coworkers and clients how to leverage Excel effectively and efficiently to solve their problems, as well as tips and tricks to save them hours of time. Now, I’d love the opportunity to share what I’ve learned over the years with you to give you the same advantage I have!