Top 10 Intermediate Excel Functions

Most Excel users know the basic functions like SUM, COUNT, AVERAGE etc. Therefore, in this tutorial I’m going to cover the 10 most important intermediate level Excel functions every Excel user should know. Watch the video for an introduction to each function, and then dive deeper by following the links below.

Note: there’s technically 17 functions covered in this tutorial, but a few of them do the same thing as other functions, or are very similar, so I’ve only counted 10 😉

Watch the Video

Download Workbook

Intermediate Level Excel Functions

Function 1: IF, Nested IFs and IFS

The IF functions belong to the logic group of functions and are a must for every Excel user to know.

Get started with the IF function and nested IFs. And if you have Excel 2019 or later, or Microsoft 365, check out the newer IFS function.

Function 2: IF(AND and IF(OR

The AND and OR functions extend the number and type of logical tests you can specify when nested in the IF function. A lot of people struggle with them, but once you master these functions you won’t look back.

Still stuck? Download our IF formula builder and have your IF formulas written for you.

Function 3: SUMIFS

The SUMIFS function is one of the ‘IFS’ family of functions which enables you to aggregate data that matches criteria you specify. Once you know one of these functions, the others are easy because they all have the same syntax, as you can see below:

These functions treat all criteria as AND criteria, meaning each condition must be TRUE for the value to be included in the SUM/AVERAGE etc. If you need more flexibility, the SUMPRODUCT function can handle AND and OR criteria.

Function 4.1: SUBTOTAL

The SUBTOTAL function is great for working with data that contains…wait for it, subtotals, because it automatically ignores any SUBTOTAL formulas in the range being summed. Not only that, you can also use it to SUM, AVERAGE and more, as shown in the table below. And you can specify whether you want to include or ignore hidden rows.

Function 4.2: AGGREGATE

The AGGREGATE function is what I like to call the Swiss Army Knife of functions because it can do so much. Similar to SUBTOTAL, AGGREGATE has a range of different aggregation options:

And like SUBTOTAL you can tell it to ignore hidden or filtered rows, but you can also choose from the following behaviours:

Function 5: XLOOKUP (or VLOOKUP)

The XLOOKUP function, available in Excel 2021 onward and Microsoft 365, is the new improved version of the much loved and sometimes hated VLOOKUP function. XLOOKUP gets around the limitations of VLOOKUP and removes the risk of inexperienced users inadvertently building formula errors into their work.

Function 6: INDEX & MATCH

INDEX and MATCH formulas can be scary for many users, but they get around the limitations of VLOOKUP. If you don’t have the XLOOKUP function in your version of Excel, then I encourage you to master INDEX & MATCH.

I’ll also go as far to say that the INDEX function is probably the MOST IMPORTANT EXCEL FUNCTION! If you’re already familiar with INDEX, then check out this tutorial that covers 5 things most people don’t know about the INDEX function:

Function 7: FILTER

The FILTER function is new in Excel 2021 and Microsoft 365. It’s one of the most important new functions we have in Excel. FILTER enables you to extract rows from a table of data based on criteria you specify. Not only that, you can extract some of the columns or all, or even extract the columns in a different order.

Function 8: IFERROR

The IFERROR function is a must for all report builders as it automatically hides errors returned by formulas ensuring a polished finish.

Function 9: EOMONTH and EDATE

The majority of Excel users work with date and time at some stage, especially accountants. The EOMONTH function, short for End of Month, and the EDATE Function, which I’m guessing is short for Extend Date, are super handy.

EOMONTH returns the last day of the month, n months before or after the specified date. It can even return the start of the month with a simple trick.

EDATE returns the same date of the month, n months before or after the specified date.

If you’d like to learn more about working with dates and times in Excel, check out my Excel Date & Time 101 and grab a copy of my eBook and examples file.

Function 10: GETPIVOTDATA

The GETPIVOTDATA function is often misunderstood and harshly judged, but it’s the secret weapon of PivotTable users who want to create a custom styled report without the bulk of a PivotTable and is super robust.

More Excel Functions

Now that you’ve mastered the above functions, you can find more resources here:

Advanced Excel Formulas course – get up to speed with advanced formulas in 5 hours.

Excel Function Library – Looking for help on a specific Function? Check out our Excel Function Library.

Absolute References Explained – Absolute references are a cornerstone of writing formulas and are therefore a must have skill for every Excel user.

Absolute References for Tables – Tables have their own absolute reference language which is not as obvious as it should be.

Writing Formulas Efficiently – In this post I share how to write formulas in such as way that it’s quick to write, and quick to maintain.

Named Ranges – Unlock some more advanced techniques with named ranges and make your formulas easier to read, write and maintain.

Time Calculations – Working with dates and time in Excel is tricky if you don’t know the fundamentals of how Excel handles date-time values.

Excel Forum – Have a specific question you need help with? Our forum volunteers are eager to help you.