How to Master Excel Formulas – The Ultimate Guide
Table of Contents
Over 120 Lessons on Microsoft Excel Formula Training Videos & Articles
Completely FREE! | No Registration Required | Scroll Down to Begin Learning!
Excel Formulas & Functions are an essential feature in Microsoft Excel that enable you to analyze your data in various ways. Mastering these skills will distinguish you in the Excel community.
Below, you'll discover over 120 examples of Excel formulas & functions, covering key formulas like XLOOKUP, VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and more. Start your free learning journey now!
Free Excel Formulas & Functions Video Lessons
Lesson 1: TOP Excel Formulas and Functions Lesson 2: Mastering the VLOOKUP formula in 4 simple steps Lesson 3: Exploring 6 new functions in Office 365 Lesson 4: Understanding the Len Function Lesson 5: Utilizing the Filter Function Lesson 6: Applying the Substitute Function Lesson 7: Using the Concatenate Function Lesson 8: Working with the Match Function Lesson 9: Understanding Absolute and Relative Functions Lesson 10: Exploring Left, Mid, and Right Functions Lesson 11: XLOOKUP – A Superior Alternative to VLOOKUP Lesson 1: TOP Excel Formulas and Functions
Excel Formulas & Functions enable you to analyze your data in various ways and significantly enhance your Excel proficiency.
Upon completing this tutorial, you will become proficient in the TOP Excel Formulas and Functions: VLOOKUP, INDEX, MATCH, SUMIF, & IF in just 1 HOUR! You will also learn:
- A proven step-by-step method to create Excel Formulas in SECONDS!
- The drawbacks of using VLOOKUP and why INDEX & MATCH is superior!
- 3 TIPS used by Excel experts & MVPs to boost your Formula knowledge!
Lesson 2: Mastering the VLOOKUP formula in 4 simple steps
Excel's VLOOKUP function is widely recognized as the most utilized and discussed function in Excel. It's perfect for retrieving values from a table for your custom reports and analysis.
In this tutorial, we'll guide you through using VLOOKUP in Excel with 4 simple steps!
- Input the VLOOKUP formula
- Specify the value you're searching for
- Indicate the table you're referencing
- Identify the column number with the result
Watch this Tutorial to become proficient with the VLOOKUP function and use it confidently!
Lesson 3: Exploring 6 new functions in Office 365
This is your comprehensive guide to learning all the new Excel formulas in Office 365:
- Filter Formula – Use an Excel Formula to filter your table data
- Randarray Formula – Create random numbers in an array
- Sequence Formula – Generate a series of numbers with an Excel Formula
- Sort Formula – Sort a table based on a specified column and order
- Sortby Formula – Sort a table based on specified column(s)
- Unique Formula – Extract the unique values from a list
Watch this video lesson to delve into these formulas in detail!
Lesson 4: Understanding the Len Function
Sometimes, you need to count the number of characters in a cell in Excel. Fortunately, this is straightforward with Excel’s LEN (Length) formula!
You can use the LEN formula in Excel:
- To count all characters in a cell, including letters, numbers, special characters, and spaces.
- As a data cleansing tool to detect leading or trailing spaces or combine with other TEXT functions.
- To identify errors like extra spaces in data entry.
Lesson 5: Utilizing the Filter Function
Did you know you can filter your table data using an Excel Formula? Watch this free Excel training to find out how.
- Filter is a new formula introduced in Office 365 in 2018!
- It allows you to filter a table array based on your specified conditions and extract matching records.
- It's a dynamic function that automatically updates results when the data sources change.
- It's perfect for sorting and selecting data and can enhance the presentation of your reports.
Lesson 6: Applying the Substitute Function
When you need to replace specific text in each word following a pattern, Excel has the perfect formula for you.
Substitute Function can be used to:
- Clean Data by replacing any leading, trailing, or unwanted text.
- Replace every occurrence of old text with new text.
- Replace multiple texts simultaneously using nested Substitute functions.
Lesson 7: Using the Concatenate Function
Excel’s CONCATENATE function combines two or more text strings into one string. The items can be text values, numbers, or cell references.
- A useful formula for extracting text from data.
- Combine multiple cells to produce a consolidated result, particularly useful for merging names and other details.
- You can even integrate concatenate with other functions for advanced outcomes.
Lesson 8: Working with the Match Function
The MATCH function in Excel returns the position of an item within a list or range of cells.
- It can detect irregularities as easily as it can extract data.
- The match function is a dynamic tool to optimize your Excel performance.
- It's mainly used with the INDEX function to retrieve values from a table based on horizontal and vertical criteria.
Lesson 9: Understanding Absolute and Relative Functions
Learn the distinctions between these two and how to use them effectively in Excel Formulas! This Online Excel Training will help you understand the differences between Relative and Absolute References in Excel.
- Use Absolute Reference – When you want to keep the cell reference unchanged when filling cells.
- Use Relative Reference – When you want the cell reference to adjust automatically when filling cells.
Lesson 10: Exploring Left, Mid, and Right Functions
Ever needed to extract text from the left, middle, or right side? You can do this with the LEFT, MID, and RIGHT text formulas in Excel.
- Three of the most useful and powerful text-based functions.
- Extracts a specified number of characters from the left, middle, or right of a text.
- Also helpful when different parts of text contain different information (like social security numbers).
Lesson 11: XLOOKUP – A Superior Alternative to VLOOKUP
XLOOKUP is a versatile and superior alternative to the previously mentioned Excel functions. It enables you to quickly lookup values in a data set (similar to VLOOKUP) with added flexibility and benefits.
- It can search values both horizontally and vertically,
- Perform an exact or approximate match,
- Use wildcards,
- Return a custom text when no result is found,
- Doesn’t have the limitation of the return array being on the right of the lookup array.
Free Excel Formulas & Functions Tutorials and Exercise Workbooks
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 in an Excel sheet available for free download to practice!
3D
Consolidate Data with 3D Formulas in Excel And
Use the AND Formula in Excel Array
Calculate Total Sales Using An Array Formula Excel Array Formulas Explained Array, Lookup
Calculate Total Bonus Due With An Array Lookup Formula Average
Calculate the Average of Values with Excel’s AVERAGE Formula Between
Use 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
Use the CountA Formula in Excel CountBlank
Use the CountBlank Formula in Excel CountIf
Use the CountIf Formula in Excel CountIfs
Use the CountIfs Formula in Excel Data Cleansing, Trim
Clean Data with Excel’s TRIM Formula DateDif
Introduction to the DATEDIF function EndOfMonth
Use 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 Calculate Monthly Investment with 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
How To Use INDEX-MATCH Formula Index Match 2 Criteria with Data Validation INDEX-MATCH Maximum Sales Indirect
Use INDIRECT Function with Sheet References Use INDIRECT Function in Excel Use INDIRECT Function for Dependent Dropdown Lists in Excel Left
Extract Data with Excel’s LEFT Formula Len, Length
Get the Length of Text with Excel’s LEN Formula Match
Introduction to the MATCH Function Match Two Lists With The MATCH Function Mathematical Formulas
Use Subtraction Formula in Excel Use Division Formula in Excel Use Addition Formula in Excel Mod
Get 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 with the Offset Function Sum the Last 7 Transactions with the Offset Function Percentage
Calculate Percentage in Excel Pivot Table, GetPivotData
Use the GETPIVOTDATA Function Proper
Clean Data with Excel’s PROPER Formula Rand
Create a Random List with Excel’s RAND Formula Randbetween
Use the RANDBETWEEN Function Use RANDBETWEEN Function for Excel Dates Reference
Jump To A Cell Reference Within An Excel Formula Remove
Remove Formulas in Excel Remove, Substitute
Remove 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 10 1 Ways to Round Numbers in Excel Show, Hide
Show & Hide Formulas in Excel Sort
Use the SORT Formula in Excel Sortby
Use the SORTBY Formula in Excel Substitute
Add 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 Use Excel Subtotal Function to Avoid Double Counting Use Excel Subtotal Function for Filtered or Visible Values Use Excel Subtotal Function to Include Hidden Values Summarize Data With Dynamic Subtotals Sum, Lookup
Perform 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 & Weighted Averages Text
Use 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 Use Vlookup for Approximate Match in Excel VLOOKUP Example: 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 for Multiple Columns Xlookup
How 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 How to Master Excel Formulas – The Ultimate Guide. 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)
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 insert a picture into a cell in Excel
Jul 21, 2025 am 12:09 AM
To embed an image into a cell in Excel, you need to set the position attribute and resize the cell. First, right-click and select "Size and Properties" after inserting the picture, and check "Change position and size with the cell"; secondly, adjust the cell row height or column width to adapt to the picture, or crop the picture to maintain the proportion; finally, you can use "As Image (Fill Cells)" in "Paste Special" to achieve the background filling effect.
How to get the last value in a column in Excel
Jul 26, 2025 am 08:03 AM
To obtain the last value of a column in Excel, you can choose different methods according to the data characteristics: 1. Use the LOOKUP function to quickly find the last non-null value, which is suitable for situations where there may be empty rows in the data. The formula is =LOOKUP(2,1/(A:A""), A:A); 2. Use the INDEX COUNTA combination to process continuous data, which is suitable for data columns without empty rows, and the formula is =INDEX(A:A,COUNTA(A:A)); 3. Use the INDEX MATCH combination to obtain the last numeric value, which is suitable for data columns containing only numbers, and the formula is =INDEX(A:A,MATCH(9.99E 307,A:A)). this
6 Excel Hacks to Elevate Your Spreadsheet Game
Jul 22, 2025 pm 12:02 PM
Quick links can also add bar charts without inserting graphics. Create and use custom lists to create and use custom lists to save time using navigation panes to fix dynamic snapshots of data captured by percentage format errors Whether you want to improve spreadsheet efficiency, enhance data analysis capabilities, or want to improve your career competitiveness, mastering new tips from Microsoft Excel can help you. Add these six practical Excel tips to your skill library now! 6 You can add bar charts without inserting graphics The first thing we learned in the school’s Excel course is how to create charts with data. Although this is a necessary skill, sometimes, removing the complex effects of the chart and visualizing data in a simple way is more efficient.
How to link a chart title to a cell in Excel
Jul 21, 2025 am 12:48 AM
In Excel, linking cells to the chart title through formulas can achieve dynamic updates. The specific operation is to click on the chart title and enter "=Sheet1!A1" (or the corresponding cell address) in the formula bar and press Enter; if you need to splice text and cell content, you can use formulas such as "="Sales Trend-"&A1" or combined with functions such as "="Data Update Time: "&TEXT(B1,"yyyy-mm-dd"); when using this function, you need to pay attention to ensuring that the cell content is valid, the path is correct and the link has not been deleted. When copying the chart to other workbooks, you should also check the validity of the link.
how to add different headers and footers in word
Jul 26, 2025 am 04:17 AM
To set different headers and footers in Word, you must first insert the section break character. 1. Click the starting position of the new section, 2. Select "Next Page" or "Continuous" in the "Delimiter" in "Layout", 3. After inserting, double-click the header footer area, cancel "Link to Previous" to disconnect the link, so that each section can be set independently; if it cannot be modified, it may be that the link is not cancelled or the section break character is deleted by mistake, you can set the cover page to "None" header footer, and hide it by setting the height to 0 or enabling "Different Home Page".
how to use styles in word
Jul 23, 2025 am 02:16 AM
Using Word styles can effectively unify formats, improve layout efficiency and generate directories. Styles are shortcuts for a set of formatting, which can be applied to text with one click, and the whole article will be automatically updated when modified; use title styles (such as titles 1~9) to build a document structure and achieve quick jumps with the navigation pane; text styles are used to unify the text format, and other styles such as "emphasis" and "code" are suitable for specific scenarios; modifying styles can adjust the format by right-clicking "Modify". Create a new style and set the format in the management style; copy and paste can reapply the correct style to solve the problem when the style is confused, and custom styles support exporting to other documents.
How to count cells with text in Excel
Jul 22, 2025 am 12:59 AM
To count the number of cells containing text in Excel, you can use COUNTIF function or combine ISTEXT and SUMPRODUCT to achieve more accurate statistics. 1. Use the COUNTIF function: =COUNTIF (range, "*") to quickly count cells containing any character, but the empty string will be counted; 2. Use =SUMPRODUCT (--ISTEXT(A1:A10)) to exclude non-text content, ensuring that only cells that are truly text are counted. Be careful to avoid selecting whole columns, checking blanks and data formats, and selecting appropriate methods according to actual needs.
