How to Master Excel Formulas – The Ultimate Guide

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!

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!

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.

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.

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.

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.

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.

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

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.