Excel Formulas Tutorial: What are Formulas and How to Use Them

Formulas are what make Excel, Excel. They’re how we perform both simple and complex calculations, and the possibilities are nearly endless as far as what they can do. Today, we’re going back to the basics with an overview of what formulas are and how to use them, as well as sharing our Excel formulas tutorial list that helps you put it all into practice.

If you’re a pro Excel user already, don’t worry – we’ve also included tutorials for some of our more advanced formulas that you will love! Plus, a good refresher never hurts.

Excel Formulas Tutorial – The Basics of Creating a Simple Excel Formula

In Excel, formulas are used to perform calculations. The equal sign (=) is typed in the cell first, followed by your expression. It’s important to remember that Excel formulas follow the order of operations (PEMDAS, just like we learned in math class): parentheses, exponents, multiplication/division, and addition/subtraction.

In addition to basic operators (such as + and -), formulas can include functions. Functions are typically expressed in UPPERCASE and followed by parentheses () which contain the function arguments or parameters. These terms (argument, parameter) refer to the values that the function operates on in order to perform its calculation.

These are some easy functions you can use in a formula.

• SUM: Returns the sum of the argument values.
• AVERAGE: Returns the simple average of the argument values.
• COUNT: Returns the count of cells with numerical values.
• MAX and MIN: Returns the maximum and minimum number.

While you can also create basic formulas with constants (values typed into the formula) like =2+2, =2*2, etc., their real power comes from their ability to calculate values using cells as the input.

So, in addition to operators, functions, and constants, formulas can also include references to specific cells or a range of cells. When you use the cells, that’s known as a cell reference.  Because you can modify the value of referenced cells without having to update the formula, they help to ensure that your calculations are accurate.

Inserting Functions

The distinction between a formula and a function is that a formula is defined as the calculation statement or expression. These formulas can be simple or complex, but they always begin with an equal sign (=). A function returns a computed value (or values) based on its arguments.

Think of a function as being able to perform one task. A formula can use one or more functions to perform multiple tasks, and formulas are totally customizable.

The structure of each function is the same. Take the SUM function, for example. It’ll look something like this when used in a formula: =SUM(A1:B2). The part within the parentheses (arguments) indicates the range of cells we’re providing to that function. The SUM function is designed to add up the numerical values found in its arguments, in this case, any numbers found in the range A1:B2.

While you can only use one formula in a single cell, you can nest several functions within it to express a series of calculations.

How Do You Use Formulas?

You can use formulas to compute just about anything in Excel. And you don’t have to be afraid of getting creative! With a little experimentation, you can write formulas that have all types of neat applications.

While formulas frequently operate on and return numbers, some formulas are designed to manipulate text values. Formulas can even operate on and return date values.

If you’re a new Excel user, you can learn the basic formulas in our free Excel 100 class before moving on to practicing with the formulas below.

Some of our Most Popular Excel Formula Tutorials:

Extract URL from Hyperlink with an Excel Formula 

Want to learn how to create a custom function and use it to extract a URL? This Excel formulas tutorial is for you! 

While there’s no built-in Excel function that can extract the link, that’s where our custom function comes into play. This formula tutorial walks you through the steps of creating the URL function and then inserting it into the formula that extracts the hyperlink.

Create a Dynamic PivotTable Style Report with One Formula 

In the past, there were two fundamental methods for creating reports in Excel. We could either enter the report labels and compute the report values using formulas, or we could utilize a PivotTable. Both options had advantages and disadvantages. The report type would have to be chosen based on the context of our workbook. 

In this Excel formulas tutorial, you’ll learn an entirely new option that uses a dynamic array (DA) to construct a dynamic PivotTable-style report that automatically updates data and grows to include any new data items. We accomplish this by using the new VSTACK and HSTACK functions in a DA formula. Now, there won’t be any need for a manual refresh!

Excel Calendar with One Formula 

By the end of this lesson, you’ll know how to make an Excel calendar with just one formula. We’ll create a formula that displays the days of any month in a graphical calendar format. Our graphical calendar will include a row for each week and display the days of the given month in 7 columns (Sunday through Saturday).

You’ll be using the SEQUENCE function in this formula, so it’s a great opportunity to get some practice with it if you’re unfamiliar!

Excel Month List

In this Excel formulas tutorial, you’ll learn how to use a single Excel formula to construct a list of months. To make it a little more interesting, we’ll let the user select the number of months they want to see, and add formulas to show the last day of each month, the number of days in each month, the month number, the year, and a structured monthly label.

LET Function

LET isn’t a formula – it’s a function – but it is a huge help when your formulas start getting longer and more complex (as they do in our Dynamic PivotTable Style Report tutorial). 

Often, formulas are pretty simple. They’ll look like the SUM example we mentioned previously: =SUM(A1:B2). However, as our workbooks become more complicated, our formulas tend to become more complicated as well. We occasionally wind up with cumbersome formulas that are tough to understand and manage, and look more like this:

=IF(VLOOKUP(B8,Table1,2,0)>100, VLOOKUP(B8,Table1,2,0), VLOOKUP(B8,Table1,2,0)*C8)

This LET function tutorial teaches you how to assign a name and its matching value (or expression) and then reference the name as needed throughout the rest of the formula. Using LET in the formula above transforms it into this:

=LET(Rate, VLOOKUP(B8,Table1,2,0), IF(Rate>100,Rate,Rate*C8))

It’s a lifesaver when you start creating more advanced formulas!

This is only a short list of the many Excel formulas we cover throughout the Excel University blog and training programs. We’re always finding new, unexpected ways to use formulas and make our spreadsheets easier to maintain.

Do you have any favorite Excel functions or formulas that you use all the time? Let us know in the comments!