Table of Contents: Advanced Excel Functions
Lost? No problem! Here's a complete table of contents for this course:
Introduction
Introduction
Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.
Prerequisites
Prerequisites
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.
Cell references
Excel has two types of cell references: Relative and absolute. Relative references shift as you copy and paste cells. Absolute references do not.
Paste special
The Paste Special command has many uses, but we'll use it to convert formulas to values before we create our charts.
Basic lookups
VLOOKUP
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!
VLOOKUP exercise
Can you create a basic VLOOKUP function to pull data from an external table?
HLOOKUP
Ever wondered whether it's possible to do a VLOOKUP horizontally rather than vertically? It's easy with the HLOOKUP function!
HLOOKUP exercise
Use your newly-learned HLOOKUP function to pull data from a table that's oriented horizontally rather than vertically.
INDEX MATCH
INDEX MATCH
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.
MATCH types
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.
MATCH exercise
Experiment with different MATCH types in an INDEX MATCH formula to solve a distinct set of analytical problems.
Advanced lookups
Two-dimensional lookups
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.
Multi-criteria lookups
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.
Lookup helpers
INDIRECT
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.
INDIRECT exercise
Use the INDIRECT function to select data from multiple tabs - at the same time.
OFFSET
The OFFSET function moves a selected data range by a specified number of cells vertically or horizontally.
OFFSET exercise
Can you use the OFFSET function to select a lookup table in real-time based on a dynamic criterion?
Conditional functions
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.
IF exercise
Can you use your knowledge of the IF function to construct conditional formulas that evaluate dynamic criteria within a workbook?
SUMIF
Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.
SUMIF exercise
Let's take what we've learned about SUMIF and apply it to a real-world data table.
SUMIFS
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
SUMIFS exercise
Can you modify our SUMIF formula slightly to accommodate multiple criteria within our data table?
COUNTIF
The COUNTIF function allows you to count the number of occurrences of a specified criteria within a given cell range.
COUNTIF exercise
Let's try using conditional formulas with a COUNT rather than a SUM as the result.
COUNTIFS
COUNTIFS allows you to count the number of rows in a table that satisfy multiple criteria across as many columns as you want.
COUNTIFS exercise
Can you add multiple criteria to our COUNTIFS formula to accommodate multiple columns within our data set?
AVERAGEIF
AVERAGEIF, like its cousins SUMIF and COUNTIF, allows us to take the average of a range based on a specified criteria.
AVERAGEIF exercise
Let's modify our conditional SUM and COUNT formulas to take the AVERAGE of values within our data set, isntead.
AVERAGEIFS
AVERAGEIFS is the multi-criteria version of the AVERAGEIF function, allowing us to AVERAGE a range based on multiple criteria.
AVERAGEIFS exercise
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.
Culminating exercise
Culminating exercise
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.
Conclusion
Conclusion
This marks the conclusion of our course! Check in here to review what we've learned.