Discover the Simplicity of Mastering Top Excel Formulas Here…
Formulas vs. Functions
You've likely encountered the terms Formulas and Functions in Excel. What sets them apart?
A Formula is an expression that computes the value of a cell, whereas a Function is a pre-built formula readily available for use in Excel. Here's an example of each, though we often use the terms 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 to access advanced Excel formulas with examples, available for free download in an Excel sheet for practice!
Click Here To ? Join Our Free Formulas & Functions Webinar Training on VLOOKUP, IF, SUMIF & INDEX/MATCH & Advance Your Excel LEVEL!
3D
Consolidate data using 3D Formulas in Excel And
Utilize the AND Formula in Excel Array
Compute Total Sales with an Array Formula in Excel Understand Array Formulas in Excel Array, Lookup
Calculate Total Bonus Due with an Array Lookup Formula Average
Obtain the Average of Values using Excel’s AVERAGE Formula Between
Utilize the BETWEEN Formula in Excel Chart
Create In-Cell Bar Charts with the REPT Function Clean
Clean Data with Excel’s CLEAN Formula Concatenate
Concatenate Text with a Line Break Convert, Values
Convert Formulas to Values in Excel Count
Use the Count Formula in Excel CountA
Apply the CountA Formula in Excel CountBlank
Implement the CountBlank Formula in Excel CountIf
Utilize the CountIf Formula in Excel CountIfs
Employ the CountIfs Formula in Excel Data Cleansing, Trim
Clean Data with Excel’s TRIM Formula DateDif
Introduction to the DATEDIF function EndOfMonth
Utilize Excel’s EndOfMonth function Evaluate
Evaluate Formulas Step by Step in Excel Extract, Find, Left
Extract First Name from Full Name in Excel Filter
Use the FILTER Formula in Excel FV
Calculate Compound Interest with Excel’s FV Formula Determine your Monthly Investment using Excel’s FV Formula Find & Select
Highlight All Excel Formula Cells Hlookup
Introduction to the HLOOKUP Function in Excel If
Introduction to the IF Function If, And
Combine IF Function with the AND Function Iferror
Introduction to the IFERROR Function Iferror, Vlookup
Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel Index
Introduction to the INDEX Function Use INDEX Function with Data Validation Sum a Range Using the INDEX Function Index, Match
Utilize the INDEX-MATCH Formula Index Match 2 Criteria with Data Validation Use INDEX-MATCH to Find Maximum Sales Indirect
Use INDIRECT Function with Sheet References Implement INDIRECT Function in Excel Create Dependent Dropdown Lists with INDIRECT Function Left
Extract Data with Excel’s LEFT Formula Len, Length
Determine the Length of Text with Excel’s LEN Formula Match
Introduction to the MATCH Function Match Two Lists Using the MATCH Function Mathematical Formulas
Perform Subtraction in Excel with a Formula Perform Division in Excel with a Formula Perform Addition in Excel with a Formula Mod
Find the Remainder with Excel’s MOD Formula Multiple Criteria, Vlookup
Use VLOOKUP with Multiple Criteria in Excel Offset
Create a Dynamic Data Range with the OFFSET function Introduction to the OFFSET Function Return the Last Value in a Column using the Offset Function Sum the Last 7 Transactions with the Offset Function Percentage
Calculate Percentage in Excel Pivot Table, GetPivotData
Utilize the GETPIVOTDATA Function Proper
Clean Data with Excel’s PROPER Formula Rand
Generate a Random List with Excel’s RAND Formula Randbetween
Utilize the RANDBETWEEN Function Use RANDBETWEEN Function for Excel Dates Reference
Navigate to a Cell Reference Within an Excel Formula Remove
Remove Formulas in Excel Remove, Substitute
Remove the Second Hyphen with Excel’s SUBSTITUTE Formula Replace
Change Phone Area Codes with Excel’s REPLACE Formula Clean 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
Extract Data with Excel’s RIGHT Formula Round
Round Off Numbers with Excel’s ROUND Formula Discover 10 1 Ways to Round Numbers in Excel Show, Hide
Show & Hide Formulas in Excel Sort
Use the SORT Formula in Excel Sortby
Utilize the SORTBY Formula in Excel Substitute
Add a Comma After the Surname with Excel’s SUBSTITUTE Formula Clean Data with Excel’s SUBSTITUTE Formula Count Text Occurrences with Excel’s SUBSTITUTE Formula Substitute, Trim
Remove Leading and Trailing Spaces in Excel Subtotal
Autosum an Array of Data in Excel Avoid Double Counting with Excel’s Subtotal Function Use Excel’s Subtotal Function for Filtered or Visible Values Include Hidden Values with Excel’s Subtotal Function Summarize Data with Dynamic Subtotals Sum, Lookup
Perform a Two-Way Lookup Using The SUM Intersect Function Sumifs
Introduction to the SUMIF Function Use SUMIF Function with One Criteria & Sum Range Introduction to the SUMIFS Function Use SUMIFS function with Wildcards * Sumproduct
Use Advanced SUMPRODUCT Function for Conditional Date Use Advanced SUMPRODUCT Function for Conditional Sum Use Advanced SUMPRODUCT Function for Count Use Advanced SUMPRODUCT Function for Maximum Sales Use Advanced SUMPRODUCT Function for Sum Multiple Criteria Use Advanced Sumproduct Function to Sum the Top 3 Sales with an Array Formula Use Sumproduct for Weighted Averages Text
Utilize Excel’s TEXT Function Add Leading Zeros in Excel Time
Calculate Elapsed Time in Excel Transpose
Use the TRANSPOSE Formula in Excel Type
Use the TYPE Formula in Excel Upper
Use the UPPER Formula in Excel Unique
Use the UNIQUE Formula in Excel Value
Use the VALUE Formula in Excel Vlookup
Use Named Ranges with Vlookup Formula Perform Vlookup Approximate Match in Excel Use VLOOKUP with a Drop Down List Introduction to the VLOOKUP Function Use Vlookup in an Excel Table Use Vlookup in Multiple Excel Sheets Use VLOOKUP Across Multiple Columns Xlookup
Learn to Use the XLOOKUP Function in Excel with 7 Examples! Weekday
Introduction to the WEEKDAY function Weeknum
Use the WEEKNUM Formula in Excel Workday
Use the Workday Formula in Excel Year
Use the YEAR Formula in Excel
The above is the detailed content of 101 Advanced Excel Formulas & Functions Examples. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
What is the meeting time limit for the free version of Teams?
Jul 04, 2025 am 01:11 AM
MicrosoftTeams’freeversionlimitsmeetingsto60minutes.1.Thisappliestomeetingswithexternalparticipantsorwithinanorganization.2.Thelimitdoesnotaffectinternalmeetingswhereallusersareunderthesameorganization.3.Workaroundsincludeendingandrestartingthemeetin
how to group by month in excel pivot table
Jul 11, 2025 am 01:01 AM
Grouping by month in Excel Pivot Table requires you to make sure that the date is formatted correctly, then insert the Pivot Table and add the date field, and finally right-click the group to select "Month" aggregation. If you encounter problems, check whether it is a standard date format and the data range are reasonable, and adjust the number format to correctly display the month.
How to Fix AutoSave in Microsoft 365
Jul 07, 2025 pm 12:31 PM
Quick Links Check the File's AutoSave Status
How to change Outlook to dark theme (mode) and turn it off
Jul 12, 2025 am 09:30 AM
The tutorial shows how to toggle light and dark mode in different Outlook applications, and how to keep a white reading pane in black theme. If you frequently work with your email late at night, Outlook dark mode can reduce eye strain and
how to repeat header rows on every page when printing excel
Jul 09, 2025 am 02:24 AM
To set up the repeating headers per page when Excel prints, use the "Top Title Row" feature. Specific steps: 1. Open the Excel file and click the "Page Layout" tab; 2. Click the "Print Title" button; 3. Select "Top Title Line" in the pop-up window and select the line to be repeated (such as line 1); 4. Click "OK" to complete the settings. Notes include: only visible effects when printing preview or actual printing, avoid selecting too many title lines to affect the display of the text, different worksheets need to be set separately, ExcelOnline does not support this function, requires local version, Mac version operation is similar, but the interface is slightly different.
How to Screenshot on Windows PCs: Windows 10 and 11
Jul 23, 2025 am 09:24 AM
It's common to want to take a screenshot on a PC. If you're not using a third-party tool, you can do it manually. The most obvious way is to Hit the Prt Sc button/or Print Scrn button (print screen key), which will grab the entire PC screen. You do
How to change your name in Microsoft Teams?
Jul 03, 2025 am 02:01 AM
To change the name in Microsoft Teams, take different steps based on the account type. For personal Microsoft accounts, you can go to account.microsoft.com to modify your name and sync it to Teams; for work or school accounts, the administrator needs to update it in the Microsoft 365 Admin Center; in addition, the display name can be temporarily changed before joining the meeting, but it will only take effect for the current meeting. All changes may take hours to sync and may be due to cache displaying old names in chat history or meeting playback.
Where are Teams meeting recordings saved?
Jul 09, 2025 am 01:53 AM
MicrosoftTeamsrecordingsarestoredinthecloud,typicallyinOneDriveorSharePoint.1.Recordingsusuallysavetotheinitiator’sOneDriveina“Recordings”folderunder“Content.”2.Forlargermeetingsorwebinars,filesmaygototheorganizer’sOneDriveoraSharePointsitelinkedtoaT
