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 some Basic Excel formulas required when we use Excel in our daily lives.

Below let us take a look at some basic, but important formulas in Excel like SUM, COUNT, AVERAGE, MIN, MAX, and so on.

Basic Excel Formulas

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.

Basic Excel Formulas Download