101 Advanced Excel Formulas & Functions Examples

Bryan

See how easy it is to learn the Top Excel Formulas here…

Formulas VS Functions

You most probably have heard the words Formulas & Functions both being used in Excel. What is the difference between them?

A Formula is an expression which calculates the value of a cell and a Function is a predefined formula that is made available for you to use in Excel. Below is an example of both but we use both terms (Formulas & Functions) interchangeably:

Table of Contents

List of 101 Advanced Excel Formulas & Functions Examples

DATE & TIME
DateDif
EndOfMonth
Time
Weekday
Weeknum
Workday
Year

LOGICAL
And
If
If, And
Iferror

LOOKUP
Array, Lookup
Hlookup
Iferror, Vlookup
Index
Index, Match
Indirect
Match
Multiple Criteria, Vlookup
Offset
Pivot Table, GetPivotData
Sum, Lookup
Vlookup
Xlookup

MATH
Average
Count
CountA
CountBlank
CountIf
CountIfs
Mathematical Formulas
Mod
Percentage
Rand
Randbetween
Round
Subtotal
Sumifs
Sumproduct

OTHER
3D
Array
Chart
Convert, Values
Evaluate
Find & Select
FV
Reference
Show, Hide
Transpose
Type
Value

TEXT
Between
Clean
Concatenate
Data Cleansing, Trim
Extract, Find, Left
Left
Len, Length
Proper
Remove
Remove, Substitute
Replace
Replace, Cleanup
Right
Substitute
Substitute, Trim
Text
Upper

OFFICE 365
Filter
Sort
Sortby
Unique

Click on any Excel formula & function link below and it will take you to the advanced Excel formulas with examples in Excel sheet free download for you to practice!

Click Here To 👉 Join Our Free Formulas & Functions Webinar Training on VLOOKUP, IF, SUMIF & INDEX/MATCH & Advance Your Excel LEVEL!

3D

Consolidate with 3D Formulas in Excel

And

AND Formula in Excel

Array

Calculate Total Sales With An Array Formula

Excel Array Formulas Explained

Array, Lookup

Total Bonus Due With An Array Lookup Formula

Average

Get the Average of Values with Excel’s AVERAGE Formula

Between

BETWEEN Formula in Excel

Chart

In-Cell Bar Charts with the REPT Function

Clean

Cleaning Data with Excel’s CLEAN Formula

Concatenate

Concatenate With A Line Break

Convert, Values

How to Convert Formulas to Values

Count

Count Formula in Excel

CountA

CountA Formula in Excel

CountBlank

CountBlank Formula in Excel

CountIf

CountIf Formula in Excel

CountIfs

CountIfs Formula in Excel

Data Cleansing, Trim

Cleaning Data with Excel’s TRIM Formula

DateDif

DATEDIF function: Introduction

EndOfMonth

Excel´s EndOfMonth function

Evaluate

Evaluate Formulas Step By Step in Excel

Extract, Find, Left

Extract First Name From Full Name in Excel

Filter

FILTER Formula in Excel

FV

Calculate the Compound Interest with Excel’s FV Formula

Calculate your Monthly Investment with Excel’s FV Formula

Find & Select

Highlight All Excel Formula Cells

Hlookup

HLOOKUP Function in Excel: Introduction

If

IF Function: Introduction

If, And

IF Function Combined With The AND Function

Iferror

IFERROR Function: Introduction

Iferror, Vlookup

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

Index

INDEX Function Introduction

INDEX Function with Data Validation

Sum a Range Using the INDEX Function

Index, Match

How To Use INDEX-MATCH Formula

Index Match 2 Criteria with Data Validation

INDEX-MATCH Maximum Sales

Indirect

INDIRECT Function Using Sheet References

INDIRECT Function in Excel

INDIRECT Function for Dependent Dropdown Lists in Excel

Left

Extracting Data with Excel’s LEFT Formula

Len, Length

Getting the length of text with Excel’s LEN Formula

Match

MATCH Function Intro

Match Two Lists With The MATCH Function

Mathematical Formulas

Subtraction Formula in Excel

Division Formula in Excel

Addition Formula in Excel

Mod

Getting the remainder with Excel’s MOD Formula

Multiple Criteria, Vlookup

VLOOKUP with Multiple Criteria in Excel

Offset

Create a Dynamic Data Range with the OFFSET function

OFFSET Function Introduced

Return the Last Value in a Column with the Offset Function

Sum the Last 7 Transactions with the Offset Function

Percentage

How to Calculate Percentage in Excel

Pivot Table, GetPivotData

GETPIVOTDATA Function

Proper

Cleaning Data with Excel’s PROPER Formula

Rand

Create a Random List with Excel’s RAND Formula

Randbetween

RANDBETWEEN Function

RANDBETWEEN Function for Excel Dates

Reference

Jump To A Cell Reference Within An Excel Formula

Remove

How to Remove Formulas in Excel

Remove, Substitute

Remove Second Hyphen with Excel’s SUBSTITUTE Formula

Replace

Change Phone Area Codes with Excel’s REPLACE Formula

Cleaning Data with Excel’s REPLACE Formula

Extract Last Name with Excel’s REPLACE Formula

Replace, Cleanup

Clear Everything Before the Hyphen with Excel’s REPLACE Formula

Right

Extracting Data with Excel’s RIGHT Formula

Round

Round Off Numbers with Excel’s ROUND Formula

10 + 1 Ways to Round Numbers in Excel

Show, Hide

How to Show & Hide Formulas in Excel

Sort

SORT Formula in Excel

Sortby

SORTBY Formula in Excel

Substitute

Add Comma After the Surname with Excel’s SUBSTITUTE Formula

Cleaning Data with Excel’s SUBSTITUTE Formula

Count Text Occurrences with Excel’s SUBSTITUTE Formula

Substitute, Trim

How to Remove Leading and Trailing Spaces in Excel

Subtotal

Autosum an Array of Data in Excel

Excel Subtotal Function – Avoid Double Counting

Excel Subtotal Function – Filtered or Visible Values

Excel Subtotal Function – Include Hidden Values

Summarize Data With Dynamic Subtotals

Sum, Lookup

Two Way Lookup Using The SUM Intersect Function

Sumifs

SUMIF Function: Introduction

SUMIF Function: One Criteria & Sum Range

SUMIFS Function: Introduction

SUMIFS function: Wildcards *

Sumproduct

Advanced SUMPRODUCT Function: Conditional Date

Advanced SUMPRODUCT Function: Conditional Sum

Advanced SUMPRODUCT Function: Count

Advanced SUMPRODUCT Function: Maximum Sales

Advanced SUMPRODUCT Function: Sum Multiple Criteria

Advanced Sumproduct Function: Sum the Top 3 Sales with an Array Formula

Sumproduct & Weighted Averages

Text

Excel´s TEXT Function

Add Leading Zeros in Excel

Time

Calculate Elapsed Time in Excel

Transpose

TRANSPOSE Formula in Excel

Type

TYPE Formula in Excel

Upper

UPPER Formula in Excel

Unique

UNIQUE Formula in Excel

Value

VALUE Formula in Excel

Vlookup

Named Ranges with Vlookup Formula

Vlookup Approximate Match in Excel

VLOOKUP Example: Vlookup with a Drop Down List

VLOOKUP Function: Introduction

Vlookup in an Excel Table

Vlookup in Multiple Excel Sheets

VLOOKUP Multiple Columns

Xlookup

How to use the XLOOKUP function in Excel with 7 Examples!

Weekday

WEEKDAY function: Introduction

Weeknum

WEEKNUM Formula in Excel

Workday

Workday Formula in Excel

Year

YEAR Formula in Excel

If you like this Excel tip, please share it

X Facebook LinkedIn Copy Email Print Reddit

Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

$1 Trial for 30 days!

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-introduction.svg" alt="Introduction" class="" width="48" height="48" /> Introduction

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-formulas.svg" alt="Formulas" class="" width="48" height="48" /> Formulas

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-formatting.svg" alt="Formatting" class="" width="48" height="48" /> Formatting

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-charts.svg" alt="Charts" class="" width="48" height="48" /> Charts

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-printing.svg" alt="Printing" class="" width="48" height="48" /> Printing

$1 Trial for 30 days!

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-formulas.svg" alt="Formulas" class="" width="48" height="48" /> Formulas

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-pivot-tables.svg" alt="Pivot Tables" class="" width="48" height="48" /> Pivot Tables

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-macros-vba.svg" alt="Macros & VBA" class="" width="48" height="48" /> Macros & VBA

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-data-analysis.svg" alt="Data & Analysis" class="" width="48" height="48" /> Data & Analysis

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-charts.svg" alt="Charts" class="" width="48" height="48" /> Charts

$1 Trial for 30 days!

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-formulas.svg" alt="Formulas" class="" width="48" height="48" /> Formulas

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-pivot-tables.svg" alt="Pivot Tables" class="" width="48" height="48" /> Pivot Tables

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-macros-vba.svg" alt="Macros & VBA" class="" width="48" height="48" /> Macros & VBA

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-power-query.svg" alt="Power Query" class="" width="48" height="48" /> Power Query

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-power-pivot.svg" alt="Power Pivot" class="" width="48" height="48" /> Power Pivot

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-data-analysis.svg" alt="Data & Analysis" class="" width="48" height="48" /> Data & Analysis

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-charts.svg" alt="Charts" class="" width="48" height="48" /> Charts

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-power-bi.svg" alt="Power BI" class="" width="48" height="48" /> Power BI

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-dashboards.svg" alt="Excel Dashboards" class="" width="48" height="48" /> Excel Dashboards

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-financial-modeling.svg" alt="Financial Modeling" class="" width="48" height="48" /> Financial Modeling

$1 Trial for 30 days!

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-word.svg" alt="Word" class="" width="48" height="48" /> Word

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-powerpoint.svg" alt="PowerPoint" class="" width="48" height="48" /> PowerPoint

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-outlook.svg" alt="Outlook" class="" width="48" height="48" /> Outlook

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-onenote.svg" alt="OneNote" class="" width="48" height="48" /> OneNote

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-teams.svg" alt="Teams" class="" width="48" height="48" /> Teams

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-access.svg" alt="Access" class="" width="48" height="48" /> Access

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/courses/icon-sharepoint.svg" alt="SharePoint" class="" width="48" height="48" /> SharePoint

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-project.svg" alt="Project" class="" width="48" height="48" /> Project

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-visio.svg" alt="Visio" class="" width="48" height="48" /> Visio

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-automated.svg" alt="Power Automate" class="" width="48" height="48" /> Power Automate

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-powerapps.svg" alt="Power Apps" class="" width="48" height="48" /> Power Apps

<img src="https://www.myexcelonline.com/wp-content/themes/meo-theme/web/assets/img/menu/icon-forms.svg" alt="Forms" class="" width="48" height="48" /> Forms

$1 Trial for 30 days!

101 Best Excel Tips And Tricks

Anne Train

Jonathan Group

Sue Koenig

Debbie Trenholm

101 Most Popular Excel Formulas

101 Ways to Master Excel Pivot Tables

101 Ready To Use Excel Macros

101 Advanced Excel Formulas & Functions Examples

Advanced Excel Formulas & Functions are a must know feature in Microsoft Excel that allows you to quickly analyze your data in many forms. Once you learn how to construct Excel Formulas & Functions, your Excel skills & level will increase exponentially.

See how easy it is to learn the Top Excel Formulas here…

Formulas VS Functions

List of 101 Advanced Excel Formulas & Functions Examples

3D

And

Array

Array, Lookup

Average

Between

Chart

Clean

Concatenate

Convert, Values

Count

CountA

CountBlank

CountIf

CountIfs

Data Cleansing, Trim

DateDif

EndOfMonth

Evaluate

Extract, Find, Left

Filter

FV

Find & Select

Hlookup

If

If, And

Iferror

Iferror, Vlookup

Index

Index, Match

Introduction

Formulas

Formatting

Charts

Printing

Formulas

Pivot Tables

Macros & VBA

Data & Analysis

Charts

Formulas

Pivot Tables

Macros & VBA

Power Query

Power Pivot

Data & Analysis

Charts

Power BI

Excel Dashboards

Financial Modeling

Word

PowerPoint

Outlook

OneNote

Teams

Access

SharePoint

Project

Visio

Power Automate

Power Apps

Forms

Steps To Follow