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

## Formulas and Functions

Differences between Formulas and Functions:

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 formula uses the SUM function to sum 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 includes 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%

## 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

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.

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

## 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:

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

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

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

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

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.

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

### TEXTJOIN

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

You need only three required arguments:

• delimiter
• ignore empty
• text1

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.

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

But, first, take a closer look at the examples below:

## Best practices for using Excel Formulas

Switch between relative and absolute reference. First, select the formula’s cell and use the F2 shortcut to enter the cell edit mode. Then, press F4 to switch between the reference types.

Apply autocomplete formula function. First, type an equal sign (=) and type the first character of the function. Excel shows a dynamic list of built-in functions. Next, select the function that you want to use.

Use Function ScreenTips. Type an equal sign and enter the function name. To add arguments, type opening parenthesis. A screen tip will appear and show your formula’s corresponding argument.