Table of Contents: Advanced Excel Functions
Lost? No problem! Here's a complete table of contents for this course:
Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.
Before diving into our course, there are a couple of important prerequisite lessons we'll need to learn. Don't worry - they're minimal!
What is a function?
What is a function in Excel? Our helpful tutorial will walk you through the basics of what a function is and how to use one in your spreadsheet.
Excel has two types of cell references: Relative and absolute. Relative references shift as you copy and paste cells. Absolute references do not.
The Paste Special command has many uses, but we'll use it to convert formulas to values before we create our charts.
VLOOKUP, one of Excel's most powerful functions, is used to look up data from a foreign table. Learn how to do a VLOOKUP in this handy tutorial!
Can you create a basic VLOOKUP function to pull data from an external table?
Ever wondered whether it's possible to do a VLOOKUP horizontally rather than vertically? It's easy with the HLOOKUP function!
Use your newly-learned HLOOKUP function to pull data from a table that's oriented horizontally rather than vertically.
INDEX MATCH, one of Excel's most useful functions, is an improved alternative to VLOOKUP.
INDEX MATCH exercise
Let's try using INDEX MATCH in a real-world scenario and explore why it's a better alternative to VLOOKUP for many applications.
What exactly does that 'match_type' argument mean? Here, we'll explore the different MATCH types available in Excel and explain when each of them is most often used.
Experiment with different MATCH types in an INDEX MATCH formula to solve a distinct set of analytical problems.
The INDEX function isn't just one-dimensional: it can accept a 'row' as well as a 'column' argument for more complex lookups in two dimensions.
Two-way lookup exercise
Try your hand at using the INDEX MATCH MATCH formulation to dynamically pull values from a two-dimensional data set.
INDEX MATCH MATCH works well for data sets that are well laid-out - but what if our data is all in one column? Good news: we can still perform lookups based off of multiple criteria. But things get a bit more difficult!
Expert lookup exercise
Let's bring everything we've learned together by using array formulas to complete an ultra-complex lookup based on multiple criteria.
The INDIRECT function is a helpful add-on to INDEX MATCH and VLOOKUP's functionality. It enables us to choose lookup tabs in real time.
Use the INDIRECT function to select data from multiple tabs - at the same time.
The OFFSET function moves a selected data range by a specified number of cells vertically or horizontally.
Can you use the OFFSET function to select a lookup table in real-time based on a dynamic criterion?
The IF function
The IF function serves as the backbone of many of Excel's more advanced conditional formulas. Let's learn how to use it before moving on to our expert-level functions.
Can you use your knowledge of the IF function to construct conditional formulas that evaluate dynamic criteria within a workbook?
Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.
Let's take what we've learned about SUMIF and apply it to a real-world data table.
SUMIFS is very similar to Excel's SUMIF function, but it allows you to take the sum of a range based on multiple criteria rather than using a single condit
Can you modify our SUMIF formula slightly to accommodate multiple criteria within our data table?
The COUNTIF function allows you to count the number of occurrences of a specified criteria within a given cell range.
Let's try using conditional formulas with a COUNT rather than a SUM as the result.
COUNTIFS allows you to count the number of rows in a table that satisfy multiple criteria across as many columns as you want.
Can you add multiple criteria to our COUNTIFS formula to accommodate multiple columns within our data set?
AVERAGEIF, like its cousins SUMIF and COUNTIF, allows us to take the average of a range based on a specified criteria.
Let's modify our conditional SUM and COUNT formulas to take the AVERAGE of values within our data set, isntead.
AVERAGEIFS is the multi-criteria version of the AVERAGEIF function, allowing us to AVERAGE a range based on multiple criteria.
Finally, we'll try our hand at using the AVERAGEIFS formula to take the AVERAGE of rows in a dataset, contingent upon multiple criteria being satisfied.
In this culminating exercise, we'll bring together everything we've learned in this course to solve a complex data organization and analytical challenge for our manager.
This marks the conclusion of our course! Check in here to review what we've learned.