**Basic Excel Formulas**

MS Excel features functions and formulas which make various calculations (be it mathematical, financial, or logical), easy and thus help us complete our tasks quickly. While it is not practical to remember every formula, it is best to

learn basic excel formulasrequired for our daily activities.

##### Table of contents

Here Is The List Of The Top 10 Basic Formulas And Functions In Excel.

Let us understand the **list of basic excel formulas** by learning **how to do basic excel formulas** using the below **Students’ Unit Test Marks** table:

### #1 – **SUM**

**Description**

One of the **10 basic excel formulas** is the **SUM()** excel function. It performs the mathematical operation of addition, calculating the sum of the chosen cell range values. The selected cells can be contiguous or non-contiguous.

The formula is:

where,

**number1**: It is the mandatory argument and is the first number we need to add.**number2**: It is an optional argument and is the second number we want to add.

Also, we can provide a maximum of 255 arguments in the **SUM()**, which can be values, excel cell references, arrays, ranges, or even other functions’ return values.

**Example**

We need to determine the overall aggregate for the students in the **Students’ Unit Test Marks** table.

**Step 1: **Choose cell J3 and enter the **SUM()**. The formula is** =SUM(B3:I3)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle downwards to copy the formula in cells J4:J17. The final output will be:

**#2 – COUNT **

**Description**

The **COUNT() **excel function determines the number of cells in a selected range that contains only a numeric value. The selected cells can be contiguous or non-contiguous.

The formula is:

where,

**value1**: It is the mandatory argument and denotes the first cell reference or range for which we want to count the numeric values.**value2**: It is an optional argument and indicates the second cell reference or range for which we want to count the numeric values.

Remember, we can enter a maximum of 255 arguments in the **COUNT** formula. Also, the function ignores blank cells and data in any format other than numerical values.

**Example**

Suppose we want to determine how many students appeared for the unit test in each subject in the **Students’ Unit Test Marks** table.

**Step 1: **Choose cell B18 and enter the **COUNT()**. The formula is **=COUNT(B3:B17)**.

**Step 2: **Press **Enter**.

**Step 3: **Now, drag the fill handle to the right to copy the formula in cells C18:I18. The output will be:

Thus, row 18 shows the number of students who appeared for the unit test in each subject using the **COUNT()**.

### #3 – **COUNTA**

**Description**

The **COUNTA() **excel function calculates the total count of cells containing a value, which could be numerical, text, or data in any format. The selected cell range can be contiguous or non-contiguous.

The formula is:

where,

**value1**: This is a mandatory argument and denotes the first cell reference or range for which we want to count the values.**value2**: This is an optional argument and is the second cell reference or range for which we want to count the values.

We can enter a maximum of 255 arguments in the **COUNTA** formula. Also, the function ignores blank cells.

**Example**

We need to determine how many students appeared for the unit test in each subject in the **Students’ Unit Test Marks** table. We mark those students present who give valid reasons for their absence. And if they are absent and do not provide the reason, we leave the individual cells empty.

**Step 1: **Choose cell B18 and enter the **COUNTA()**.

The formula is** =COUNTA(B3:B17)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle to the right to copy the formula in cells C18:I18.

The output will be:

Thus we can see the required attendance details for each subject in row 18 using the **COUNTA()**.

**#4 – COUNTBLANK**

**Description**

The **COUNTBLANK()** returns the count of blank or empty cells in the chosen cell range.

The formula is:

where,

**range**: It is the cell range in which we want to count the empty cells.

The function has only one argument and does not consider a cell with a value of **0** as a blank cell. On the other hand, it counts cells containing empty text strings or functions returning blank text strings.

**Example**

Suppose we want to update the absentees’ data for each subject in the **Students’ Unit Test Marks** table.

**Step 1: **Choose cell B18 and enter the **COUNTBLANK()** as:

**=COUNTBLANK(B3:B17)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle to the right to copy the formula in the cell range C18:I18. The output will be:

Thus, we can see the absentee details in row 18 using **COUNTBLANK()**.

### #5 – **AVERAGE**

**Description**

The **AVERAGE()** excel function evaluates the average of values in the chosen cell range, where the range can be contiguous or non-contiguous.

The formula is:

where,

**number1**: It is a mandatory argument and is the first value of a cell reference or range, for which we have to calculate the average.**number2**: It is the second value of a cell reference or range for which we have to calculate the average.

We can provide up to 255 arguments in the **AVERAGE()**. Also, the function ignores empty cells and non-numerical values.

**Example**

Assume we have to calculate the average marks scored by each student in the **Students’ Unit Test Marks** table.

As the **AVERAGE()** ignores blank cells, let us insert **0** in cells without a value to get the correct output.

**Step 1: **Choose cell J3 and enter the **AVERAGE()** as **=AVERAGE(B3:I3).**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle downwards to copy the formula in cell range J4:J17. The output will be:

Thus, we can see the average marks of each student in column J, using the **AVERAGE().**

### #6 – **MIN Excel**

**Description**

The **MIN() excel function **determines the least value in the chosen cell range, with the range being adjacent or non-adjacent.

The formula is:

where **number1 **is the mandatory argument, and the subsequent arguments are optional.

We can provide up to 255 arguments, and the function counts constants, cell references, and ranges while ignoring empty cells, text, and logical values. However, if we directly provide logical values or text representations of numerical values as arguments, the **MIN()** counts them.

**Example**

Suppose we need to determine the least score in each subject in the **Students’ Unit Test Marks** table.

**Step 1: **Choose cell B18 and enter the **MIN()** as **=MIN(B3:B17)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle to the right to copy the formula in cells C18:I18. The output will be:

Thus, we can now see the least marks scored in each subject in row 18, using the **MIN()**.

### #7 – **MAX Excel**

**Description**

The **MAX()** excel function determines the highest value in the chosen cell range, with the selected cell range being adjacent or non-adjacent.

The formula is:

where **number1 **is the mandatory argument, and the subsequent arguments are optional.

We can enter a maximum of 255 arguments, and the function counts constants, cell references, and ranges while ignoring empty cells, text, and logical values.

However, if we directly provide logical values or text representations of numerical values as arguments, the **MAX()** counts them.

**Example**

Suppose we need to find the top score in each subject in the **Students’ Unit Test Marks** table.

**Step 1: **Choose cell B18 and enter the **MAX()** as **=MAX(B3:B17)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle to the right to copy the formula in cells C18:I18. The output will be:

### #8 – **LEN Excel**

**Description**

The **LEN() **excel function calculates the number of characters in a cell, including spaces and special characters.

The formula is:

The argument can be a number, text, or a reference to the cell for which we want to calculate the number of characters.

**Example**

Consider column A (**Student Name**) in the **Students’ Unit Test Marks** table. Suppose we need to determine the length of each student’s name.

**Step 1: **Choose cell B2 and enter the **LEN() **as **=LEN(A2)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle downwards to copy the formula in the range B3:B16, and the output will be:

### #9 – **TRIM Excel**

The **TRIM() **excel function eliminates unwanted empty spaces in a cell. Thus, it ensures other functions do not result in errors due to unnecessary spaces.

The formula is:

The argument, **text, **denotes a value or cell content from which we want to remove unnecessary spaces.

The function leaves a single space gap between words in texts and removes the extra spaces at a text’s starting and ending positions.

**Example**

Consider that we concatenated the first and last names of each student while filling column A of the **Students’ Unit Test Marks** table:

But when we use the student names in another function, it throws an error. We then realize there is an additional space between the first and last names. Below are the steps to remove the extra space using the **TRIM()** excel function.

**Step 1: **Choose cell D2 and enter the **TRIM()** as **=TRIM(C2)**

**Step 2: **Press **Enter**.

**Step 3: **Drag the fill handle downwards to copy the formula in the cell range D2:D16.

Thus, we will get the student names without unnecessary spaces in column D, using the **TRIM()**.

**#10 – IF Excel**

**Description**

Of the **list of basic excel formulas**, **IF() **is really important. It checks a logical condition and returns a value if the criterion holds; otherwise, it returns another value when the logical test is **FALSE**.

The formula is:

where,

**logical_test**: The logical condition, based on which the**IF()**returns a value.**[value_if_true]**: It is the value the**IF()**returns if the**logical_test**is**TRUE**.**[value_if_false]**: It is the value the**IF()**returns if the**logical_test**is**FALSE**.

The **logical_test** will include logical operators, such as **=**, **>**, **<**, **>=**, **<=**, and **<>**.

**Example**

Suppose we want to find the **Top Achiever** among the students in the **Students’ Unit Test Marks** table based on the **Average Aggregate**.

**Step 1: **Choose cell K3 and enter the **IF()** as **=IF(MAX($J$3:$J$17)=J3,A3,””)**

**Step 2: **Press **Enter**.

**Step 3: **Now drag the fill handle downwards to copy the formula in range K4:K17. The output will be:

The **MAX()** excel function first determines the top score in column J, and then the **IF()** excel function checks if each cell in column J contains the highest score. Once it finds the match, it displays the respective student’s name in column K.

**Conclusion**

Now, we have learned **how to do basic excel formulas**. All the discussed **10 basic excel formulas** will greatly help Excel users. We can directly type the required formula inside the target cell, use **Formulas** -> **Insert Function**, or choose the needed function from the **Formulas** tab. Rest assured, we can achieve our desired results quickly and error-free.

**Download Template**

This article must be helpful to understand **Basic Excel Formulas**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Basic Excel Formulas. Here we discuss the list of top 10 excel formulas, functions & examples with downloadable excel template. You can learn more from the following articles –

## Leave a Reply