Excel Formulas and Functions
There are plenty of Excel formulas and functions depending on what kind of operation
you want to perform on the dataset. We will look into the formulas and functions on
mathematical operations, character-text functions, data and time, sumif-countif, and few
lookup functions.
Let’s now look at the top 25 Excel formulas you must know. In this article, we have
categorized 25 Excel formulas based on their operations. Let’s start with the first Excel
formula on our list.
1. SUM
The SUM() function, as the name suggests, gives the total of the selected range of cell
values. It performs the mathematical operation which is addition. Here’s an example of it
below:
Sum "=SUM(C2:C4)"
Fig: Sum function in Excel
As you can see above, to find the total amount of sales for every unit, we had to simply
type in the function “=SUM(C2:C4)”. This automatically adds up 300, 385, and 480. The
result is stored in C5.
2. AVERAGE
The AVERAGE() function focuses on calculating the average of the selected range of
cell values. As seen from the below example, to find the avg of the total sales, you have
to simply type in:
AVERAGE =AVERAGE(C2, C3, C4)
Fig: Average function in Excel
It automatically calculates the average, and you can store the result in your desired
location.
3. COUNT
The function COUNT() counts the total number of cells in a range that contains a
number. It does not include the cell, which is blank, and the ones that hold data in any
other format apart from numeric.
COUNT =COUNT(C1:C4)
Fig: Microsoft Excel Function - Count
As seen above, here, we are counting from C1 to C4, ideally four cells. But since the
COUNT function takes only the cells with numerical values into consideration, the
answer is 3 as the cell containing “Total Sales” is omitted here.
If you are required to count all the cells with numerical values, text, and any other data
format, you must use the function ‘COUNTA()’. However, COUNTA() does not count
any blank cells.
To count the number of blank cells present in a range of cells, COUNTBLANK() is
used.
4. SUBTOTAL
Moving ahead, let’s now understand how the subtotal function works. The SUBTOTAL()
function returns the subtotal in a database. Depending on what you want, you can
select either average, count, sum, min, max, min, and others. Let’s have a look at two
such examples.
Fig: Subtotal function in Excel
In the example above, we have performed the subtotal calculation on cells ranging from
A2 to A4. As you can see, the function used is
SUBTOTAL =SUBTOTAL(1, A2: A4)
In the subtotal list “1” refers to average. Hence, the above function will give the average
of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,
“=SUBTOTAL(4, A2: A4)”
This selects the cell with the maximum value from A2 to A4, which is 12. Incorporating
“4” in the function provides the maximum result.
Fig: Count function in Excel
5. MODULUS
The MOD() function works on returning the remainder when a particular number is
divided by a divisor. Let’s now have a look at the examples below for better
understanding.
In the first example, we have divided 10 by 3. The remainder is calculated using the
function
MODULUS =MOD(A2,3)
The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the
same answer.
Fig: Modulus function in Excel
Similarly, here, we have divided 12 by 4. The remainder is 0 is, which is stored in
B3.
Fig: Modulus function in Excel
