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
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
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 andOFFSET 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
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.
In addition to teaching Excel, Sam has founded a number of businesses — most recently, Mosaic, a food company that helps busy professionals eat well.
Quoted in:
Customer reviews