Below you will find 120+ Excel formulas & functions examples for key formulas & functions like XLOOKUP, VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more. Let us start learning for free!
Free Excel Formulas & Functions Video Lessons
Lesson 1: MOST POPULAR Excel Formulas and Functions
Excel Formulas & Functions will allow you to quickly analyze your data in many forms and will increase your Excel skill exponentially.
After completing this tutorial, you will Master the Most POPULAR Excel Formulas and Functions: VLOOKUP, INDEX, MATCH, SUMIF, & IF in only 1 HOUR! You will also learn:
- A proven step-by-step guide to creating Excel Formulas in SECONDS!
- The limitations of using a VLOOKUP and why INDEX & MATCH is better!
- 3 TIPS that the Excel experts & MVPS use that will enhance your Formula knowledge!
Lesson 2: Learn VLOOKUP formula in 4 easy steps
Excel`s VLOOKUP function is arguably the most used function and the most talked-about function in Excel. It can be used to extract values from a table and use them in your custom reports and analysis.
In this tutorial, we will cover how to use VLOOKUP in Excel using 4 easy steps!
- Enter VLOOKUP formula
- Enter the value you are looking for
- Enter the table where you going
- Enter the column number containing the result
Watch this Tutorial to Master the VLOOKUP function and use it with confidence!
Lesson 3: 6 brand new functions in Office 365
This is your one-stop-shop for learning all the new Excel formulas in Office 365:
- Filter Formula – Filter your table data with an Excel Formula
- Randarray Formula – Generate random numbers in an array
- Sequence Formula – Generate a series of numbers with an Excel Formula
- Sort Formula – Sorts a table based on a column and order specified
- Sortby Formula – Sorts a table based on the column(s) specified
- Unique Formula – Gets the unique values of a list
Watch this video lesson to learn the following formulas in detail!
There are times when you need to get the number of characters within a cell in Excel. Thankfully this is very easy to do 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 all spaces.
- As a data cleansing technique to find leading or trailing spaces or can be nested with other TEXT functions.
- To find out errors like additional spaces in data entry.
Did you know that you can now filter your table data with an Excel Formula? Watch this free Excel training to learn how.
- Filter is a new formula introduced in Office 365 released in 2018!
- It can be used to filter a table array based on conditions you specify and extract matching records.
- It is a dynamic function i.e. when the values in the data sources change the result from this formula will update automatically.
- It’s an ideal function for sorting and picking out data and can even be used to improve the presentation of your reports.
When you needed to replace a specific text in each word, and there is a pattern, Excel has just the formula for you.
Substitute Function can be used to :
- Clean Data by substituting any leading, trailing, or unwanted text.
- Replace every instance of old text with new text.
- Replace multiple texts at one time using the nested Substitute function.
Lesson 7: CONCATENATE Function
Excel’s CONCATENATE functions join two or more text strings into one string. The item can be a text value, number, or cell reference.
- A helpful formula for extracting text from data.
- Join several cells to give a consolidated result, especially useful for combining names and other details.
- You can even combine concatenate with other functions for advanced results.
The MATCH function in Excel returns the position of an item within a list or a range of cells.
- It can find irregularities just as easily as it can extract data.
- The match function is a dynamic tool to help make your Excel work at optimum levels.
- Primarily combined with INDEX function to help user result value from a table based on horizontal and vertical criteria.
Lesson 9: ABSOLUTE and RELATIVE Function
Learn the differences between these two and how you can properly use them in Excel Formulas! You can easily compare the differences between Relative and Absolute References in Excel with this Online Excel Training.
- Use Absolute Reference – When you do not want cell reference to change when filling cells.
- Use Relative Reference – When you want cell reference to automatically adjust when filling cells.
Lesson 10: Left, Right & Mid Function
Ever wanted to get something on the left, middle, or right side of your text? You can with the LEFT, MID, and RIGHT text formulas in Excel.
- Three most useful and powerful text-based functions.
- Extracts a specific number of characters from the left, middle, or right of a text.
- Also, helpful in cases where different parts of text contain different information (like social security numbers).
Lesson 11: XLOOKUP – Outstanding Replacement of VLOOKUP
XLOOKUP is a versatile and outstanding replacement for the above-mentioned Excel functions. It allows you to quickly lookup values in a data set (just like VLOOKUP Excel) with additional flexibility and advantages.
- It can search the value both horizontally and vertically,
- Perform an exact or approximate match,
- Use wildcards,
- Return a custom text when no result is found,
- Doesn’t even have the restriction of the return array to be 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
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!
3D
And
Array
Array, Lookup
If
If, And
Iferror
Iferror, Vlookup
Index
Index, Match
Indirect
Left
Len, Length
Match
Mathematical Formulas
Mod
Multiple Criteria, Vlookup
Offset
Percentage
Pivot Table, GetPivotData
Proper
Rand
Randbetween
Reference
Sort
Sortby
Substitute
Substitute, Trim
Subtotal
Sum, Lookup
Sumifs
Sumproduct
Text
Time
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.