Microsoft Excel: From Excel Beginner to Excel Formula Master

Transforming Students into Microsoft Excel Wizards by Solving Real World Problems in MS Excel. (Can Use Excel 2003-2016)
4.5 (31 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,480 students enrolled
$19
$200
90% off
Take This Course
  • Lectures 181
  • Length 16.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 5/2016 English

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!

What are the 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.

What am I going to get from this course?

  • Become a 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.
  • Creatively write formulas to address complex practical problems by combining functions together.
  • Save time by learning tips, tricks, and traps to look out for as I share advice that I’ve acquired over the years.

What is the target audience?

  • This course is for individuals who have a desire to be phenomenal at writing formulas and problem solving in 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 basics needed to get newbies up to speed so that they can benefit from this course as well.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Module 1: Introduction to Excel
01:37

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

Section 2: 1.1 The What, Why, and How of Excel
04:25

In this lesson, we'll take a look at 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.

Section 3: 1.2 Excel Components and User Interface
02:36

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

04:55

Learn about Excel's ribbon.

04:06

Learn about the Quick Access and Formula toolbars.

03:58

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

Section 4: 1.3 Introduction to Formulas and Functions
02:57

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

4 questions

Show off your your newly acquired knowledge of formulas and functions!

06:48

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

13:18

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.

Section 5: 1.4 Overview of Cell References
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.

2 questions

Test your skills on cell references!

Section 6: Module 2: Process and Transform Data
02:44

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

Section 7: 2.1 Performing Data Aggregation
01:34

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

12:00

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

4 questions

Show off your knowledge of the simple aggregation functions!

08:13

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

08:59

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.

10:18

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

03:39

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

12 questions

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

10:11

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

04:40

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.

04:13

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

06:42

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.

08:17

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.

5 questions

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

05:03

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

03:39

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)

10:03

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

05:30

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.

4 questions

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

03:05

Recap the key concepts you learned in Module 2.1.

Section 8: 2.2 Performing Logical Operations
01:17

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

08:11

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

03:41

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

07:44

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.

06:29

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

02:52

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

03:02

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

5 questions

Show off your knowledge of logical operations in Excel.

01:42

Recap the key concepts you learned in Module 2.2.

Section 9: 2.3 Mastering Lookups and References
02:41

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.

03:35

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

08:54

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.

07:00

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.

05:42

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.

4 questions

Show off your knowledge of the choose function.

03:41

Lear 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.

10:09

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.

06:30

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.

06:50

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

05:26

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.

08:07

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

02:53

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

06:39

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

6 questions

Show off your knowledge about the vlookup and hlookup functions.

03:31

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

04:16

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

05:56

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

06:39

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.

05:20

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

03:01

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

06:29

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.

10 questions

Show off your knowledge about the index and match functions.

04:44

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.

06:41

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.

08:46

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.

04:06

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

06:59

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.

04:07

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

07:50

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

05:22

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.

06:21

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

5 questions

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

07:30

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

05:50

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

4 questions

Show off your knowledge about defined names!

06:19

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

08:23

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

04:19

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

05:34

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

05:13

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

3 questions

Show off your knowledge about the offset function!

05:27

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.

05:45

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.

03:47

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.

03:51

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.

3 questions

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

13:09

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.

07:35

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Jarrod Tanton, 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!

Ready to start learning?
Take This Course