Excel Guru: The Only Excel Formulas Course You’ll Ever Need

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.

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

Learn about Excel's ribbon.

Learn about the Quick Access and Formula toolbars.

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

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

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

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.

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.

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

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

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

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

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.

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

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

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

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.

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

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.

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.

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

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)

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

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.

Recap the key concepts you learned in Module 2.1.

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

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

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

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.

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

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

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

Recap the key concepts you learned in Module 2.2.

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.

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

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.

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.

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.

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.

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.

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.

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

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.

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

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

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

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

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

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

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.

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

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

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.

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.

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.

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.

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

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.

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

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

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.

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

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

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

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

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

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

Learn how to use the transpose function with array formulas.

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

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.

Recap the key concepts you learned in Module 2.3.

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

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.

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.

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.

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

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.

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.

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

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.

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.

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.

Recap the key concepts you learned in Module 2.4.