Excel Formulas and Examples

This guide provides an overview of the Excel Formulas and Functions from the ground up. Learn the basics through useful examples. So, let’s start with the basics! There are two ways to perform calculations in Excel; using Formulas and Functions.

Table of contents:

Part 1: The Basics:

  1. What is a formula? What is a function?
  2. The Parts of an Excel formula
  3. Insert a formula
  4. Edit a formula
  5. Copy and Paste formulas
  6. Basic Excel Formulas: SUM, IF, AVERAGE, TEXTJOIN, MIN & MAX, ROUND

Part 2: Advanced Formulas: (coming soon)

Formulas and Functions

As first, take a closer look at the differences between them.

What is a Formula? In Excel, Formulas are math statements that can perform calculations and manipulate the values of other cells. Also, it can test conditions and frequently uses logical operators. Let’s see a simple example: =B1 + B2 + B3 + B4 finds the sum of the cell values between cell B1 and cell B4.

What is a function? Functions are built-in presets, so there are hard-coded formulas in Excel. With its help, you can avoid manual data entry, and it has small, user-friendly names. For example =SUM(B1:B4). This function sums all the values from cell B1 to B4.

The Parts of an Excel Formula

All formulas contain one or more built-in functions, and the formula contains the following parts:

  1. Functions: SUM
  2. References: B1:B4 returns all the numbers in a range of cells
  3. Operators: The * (asterisk) operator multiplies numbers
  4. Constants: numbers or text that you add to the formula, such as 20%
parts of an excel formula

Insert a Formula

Follow these steps to enter a formula:

  1. Select the cell
  2. Select Formula Bar
  3. Type and equal sign (=)
  4. Type the formula, for example, =SUM(B1:B4)
  5. Press Enter to execute the formula
how to insert a formula

Tip: There are some ways to insert a new formula. If you want to avoid mistyped words, use the Insert Function Button on the Formulas Tab. To do that: Select the cell > Click Formulas Tab > Click Insert Function > Select the function from the list. Finally, click OK.

excel formulas tab

Edit a Formula

Editing formulas is not rocket science. Let’s see the steps:

  1. Select the cell that contains a formula
  2. Click on the Formula bar to change the formula
  3. Press enter

Tip: If you are in formula editing mode, Excel highlights the given range with a blue border.

edit a formula

Copy and Paste Formulas

In the example, you want to use the entered formula for a different range. Should you type the formula again and again? No, you need only seconds to copy formulas to another location. To copy a formula, follow the steps below:

Example 1: Adjacent cells

  1. Select the cell that contains a formula
  2. Rest your cursor in the lower-right corner while it turns into a plus sign (+)
  3. Drag the fill handle right
copy a formula

Example 2:

What if you want to copy the formula to another location and there are non-adjacent cells?

Let’s see the steps:

  1. Select the cell that contains a formula
  2. Press Ctrl + C to copy the formula
  3. Right-click on the target cell
  4. Select Paste Formulas
copy and paste formulas

The most used Basic Excel Formulas to Boost your Productivity

In Excel, there are simple and more complex formulas. In the first part of our guide, you will learn about the most popular and easiest formulas to boost productivity and speed up your work.

SUM

=SUM is the most used, must-have function in some basic Excel formulas. This formula summarizes values from a selection of columns or rows that contain numbers.

SUM

Example:

=SUM(B1:B5) sums the values of column B.
=SUM(A1:H1) sums the values in the first row between column A and column H.
=SUM(B3:B10, B13, B15:B20) is a bit tricky. First, it sums values from range B3 to B10. After that, it skips the values in cells B11 and B12. Next, it adds B13 and skips cell B14. Then, finally, aggregate the values from cell B15 to B20.

=SUM(B1:B5)+1 expression shows how to turn your function into a formula.

IF

The IF formula runs a logical (true or false) test and returns 1 (if the test is TRUE) or 0 (if the test is FALSE).

if

Example:

=IF(B4>C4, TRUE, FALSE) – Checks if the value at B4 is greater than the value in cell C4. If the logic is true, the result is 1. If not, 0.

Use descriptions to make the formula easy to readable: =IF(B4>C4, “B4 is greater than C4”, “C4 is greater than B4”). Because the B4>C4, the formula returns with the text: “B4 is greater than C4”

Or get the greater value using the formula below: =IF(B4>C4, B4, C4). In this case, the result is 65 in cell E6.

Example 2:

=IF(AVG(B5:B8) > AVG(C5:C8), SUM(B5:B8), SUM(C5:C8))

First, the formula calculates the average from B5 to B8, then C5 to C8. After that, it compares the two values. If the average of the range B5:B8 is greater than the average of C5 to C8, the result is TRUE. You will get the SUM(B5:B8) as a result. On the other hand, if the result is FALSE, Excel gets the SUM(C5:C8) due to the formula.

If you are talking about excel formulas, IF is an absolute must-have function.

AVERAGE

To calculate the average of numbers in a range of cells, use the AVERAGE function.

Example:

=AVERAGE(A2:A7) shows a simple average of six numbers in a range.

average

Tip: the AVERAGE function ignores logical values, empty cells, and cells that contain any text.

TEXTJOIN

In Excel, concatenate multiple text values is a daily routine. TEXTJOIN concatenates multiple values using a delimiter (or without it).

You need only three required arguments:

  • delimiter
  • ignore empty
  • text1
textjoin

Example:

=TEXTJOIN(“, “TRUE, A3:A12) will return “The sun always shines on TV.” and ignore the blank cells.

=TEXTJOIN(“, “FALSE, A3:A12) will return “The sun always shines on TV.” and concatenate the blank cells too.

MIN and MAX

Use the MIN function to get the smallest number in a range of cells. The MAX function will find the largest number in a given range.

min and max

Good to know: You can use either MIN or MAX with multiple ranges: =MIN(A1:A10, B5:B10) will find the min value in range A1:A10 AND B5:B10.

ROUND

The ROUND function rounds a number to N digits, where N is an integer. For example, if cell B2 contains 99.146. To round that value to one decimal place, use the following formula:

=ROUND(B2, 1)

The result is 99.1.

The ROUND function has two required arguments:

  • number, that you want to round.
  • num_digits are the number of digits

Take a closer look at the examples below:

round formula

Advanced Excel Formulas and Examples

You’ll find hundreds of formulas and examples very soon! Stay tuned.

Additional resources: