How to Master Excel Formulas - The Ultimate Guide | MyExcelOnline

120+ Lessons of Free Microsoft Excel Formula Training Videos & Articles

Absolutely FREE! | No Sign-up Needed | Scroll Down & Start Learning!

Excel Formulas & Functions are a must know feature in Microsoft Excel that allows you to quickly analyze your data in many forms.  It is an indispensable skill that will make you stand out from the crowd in the Excel world.

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!

YouTube video


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!

YouTube video


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!

YouTube video


Lesson 4: LEN Function

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.

YouTube video

Lesson 5: FILTER Function

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.

YouTube video


Lesson 6: SUBSTITUTE Function

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.

YouTube video


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.

YouTube video


Lesson 8: MATCH Function

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.

YouTube video


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.

YouTube video


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).

YouTube video


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.

YouTube video

Free Excel Formulas & Functions Tutorials and Exercise Workbooks


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!




Array, Lookup 






Convert, Values 


Data Cleansing, Trim 




Extract, Find, Left 



Find & Select 



If, And 


Iferror, Vlookup 


Index, Match 



Len, Length 


Mathematical Formulas 


Multiple Criteria, Vlookup 



Pivot Table, GetPivotData 








Substitute, Trim 


Sum, Lookup 






Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn