An Excel video course from Deskbright:

Advanced Excel Functions

VLOOKUP, INDEX MATCH, conditionals, and more

So, you know the basics of Excel — from simple formulas like SUM and AVERAGE to cell references and hotkeys. You've got a solid baseline understanding of what spreadsheets are all about, and you're comfortable navigating the interface.

But, when you open up spreadsheets designed by more experienced users, you're a bit overwhelmed. There's a ton of functionality that you just don't have a solid handle on yet. Maybe you've used VLOOKUP before, but haven't yet committed the formula to memory. Maybe you've heard about INDEX MATCH, but haven't experimented much with different MATCH types or array functions. Or maybe you're not quite sure what the more advanced Excel formulas can do — like INDIRECT, OFFSET, and COUNTIFS.

There are a ton of formulas out there to learn, and sometimes knowing where to start can seem overwhelming — particularly when you're not sure which functions out there are the most useful.

So here's a little secret to help you out:

You only need to know about
15 advanced formulas to do 90% of your work.

It may seem crazy to say that, because Excel contains over 300 functions — and it can often seem like you need to learn them all to be effective. But the truth is that if you know the right things to study, you can learn everything you need to know to become an Excel expert in just a few hours.

With just 15 advanced formulas under your belt, you can:

  • Design spreadsheets that automate the manual work you've been doing and reduce your time spent in Excel by hours per day;
  • Perform complex lookups and references between virtually any two worksheets or workbooks;
  • Answer multi-faceted questions about a data set with just a few simple keystrokes;
  • Become the resident 'Excel Expert' in your office; and
  • Blow away your colleagues — and your boss — with your Excel skills.

But the best part is: knowing these Excel formulas is a very valuable skill. Employees who have mastered the art of the spreadsheet are extremely sought-after in the business world, and knowing the right stuff can net you a better job — with a higher salary.

And we've got an easy way to get started:

In under 5 hours, this course will teach you everything you need to know about Advanced Excel.

Forget boring lectures, hard-to-understand articles, and ambiguous instructions. We've done away with all the unnecessary fluff and distilled the core of what you need to know into an engaging video course that can be completed in under five hours.

In it, you'll learn 15 expert-level formulas that will put you in the top 95% of business analysts worldwide — and save you hours at work every day. Here's what you'll explore:

  • Using VLOOKUP, INDEX MATCH, and other advanced formulas to automate work in Excel
  • Performing advanced lookups based on both rows and columns at the same time
  • Employing array functions to create complex formulas that pull data based on multiple criteria from a table
  • Using INDIRECT and OFFSET with advanced string concatenation for the most automated lookups possible in Excel
  • Leveraging the IF statement to create advanced conditional spreadsheets
  • Using SUMIF, COUNTIF, and their cousins to mirror PivotTable functionality — without creating a messy spreadsheet
  • Saving hours of work every day by automating spreadsheets

When you're done, you'll be an Excel expert capable of constructing even the most complex formulas. Buy the course today and get ready for the next stage in your career.

Not convinced? Try out a free trial of the course and get a sample lesson on us!

Hands-on exercises

At Deskbright, we believe in learning by doing. That's why all of our courses come with a set of hands-on challenges that let you practice your newly-learned skills on your own. Each exercise comes with a comprehensive answer key so that you can check your work and learn through experience.

Your satisfaction is guaranteed.

We offer a 100%, unconditional, 30-day money-back guarantee on all of our courses. If you aren't satisfied for any reason, you can let us know within 30 days of your purchase to get your money back — no questions asked. This means that there's absolutely zero risk on your end!

Want to know more?

No problem! Check out the frequently asked questions below. There's a ton more details on the course; what you'll learn; and your instructor to read through.

What are the requirements?

Microsoft Excel 2007 or later — Windows or Mac!

Who is the target audience?

This course is for folks who have a baseline familiarity with Excel, but haven't yet had the chance to explore more powerful and advanced formulas and functions.

Intermediate Excel users should be able to dive into these lessons with no problem, but some familiarity with the program is a pre-requisite — including general knowledge of what a formula is and how it works; how to enter formulas into cells; absolute and relative cell references; and working with strings.

Towards the end of this course, we'll build on what we've learned to dive into some advanced lessons that will help even experienced Excel pros get the most out of their spreadsheets. This course is recorded on a Windows computer, but the formulas and functions we use are also applicable to Excel for Mac.

How long will it take?

You should be able to complete this course in 3-5 hours. We've saved you time by eliminating all the fluff and making sure you get the highest possible value out of this course per minute spent learning.

What will I learn?

Here's a full 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.

Your instructor

Photo of Sam

Sam McIntire

Founder, Deskbright

This course is taught by master Excel instructor Sam McIntire of Deskbright. Sam taught himself Excel after landing a job at a top management consultancy, and is passionate about sharing his spreadsheet knowledge with others.

With years of experience teaching students around the world advanced Excel skills, his easy-to-follow style is perfect for beginners to advanced learners alike. Sam:

  • Has helped thousands of students learn the Excel formulas and functions they need to succeed at work;
  • Is an experienced management consultant who learned Excel on-the-job at a top tier firm;
  • Has been featured in top business publications like Fast Company, Business.com, and Forbes;
  • Has received hundreds of positive reviews and 'thank you' e-mails from students whose work was transformed by his lessons.

Quoted in:

BBC
Forbes
Fast Company
 

Customer reviews

The perfect tutorial for a complex problem  

This was money very well-spent. In just about 2 hours, this course gave me a solid review of Excel lookup functions, and provided the solution I needed to solve a complex index and match problem with a very large dataset.