Let’s now look at the top Excel formulas you must know. In this article, we have categorized Excel formulas based on their operations. Let’s start with the first Excel formula on our list.
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)”
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)
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)
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)
POWER
The function “Power()” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:
Power =POWER (A2,3)
CEILING
Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance.
CEILING(‘31.458’,5)
The nearest highest multiple of 5 for 35.316 is 40.
FLOOR
Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.
FLOOR( ‘31.458’,5 )
The nearest lowest multiple of 5 for 35.316 is 35.
CONCATENATE
This function merges or joins several text strings into one text string. Given below are the different ways to perform this function.
- In this example, we have operated with the syntax:
CONCATENATE =CONCATENATE(A25, ” “, B25)
“=CONCATENATE(A27&” “&B27)”
LEN
The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.
LEN(A7)
REPLACE
As the name suggests, the REPLACE() function works on replacing the part of a text string with a different text string.
The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicate the number of characters you want to replace.
Let’s have a look at the ways we can use this function.
- Here, we are replacing A101 with B101 by typing
REPLACE =REPLACE(A15,1,1,”B”)
SUBSTITUTE
The SUBSTITUTE() function replaces the existing text with a new text in a text string.
The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.
Here, [instance_num] refers to the index position of the present texts more than once.
Given below are a few examples of this function:
- Here, we are substituting “I like” with “He likes” by typing:
“=SUBSTITUTE(A20, “I like”,”He likes”)”
UPPER, LOWER, PROPER
The UPPER() function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the other will be in lowercase.
Let’s understand this better with the following examples:
- Here, we have converted the text in A6 to a full uppercase one in A7.
UPPER(A6)
NOW()
The NOW() function in Excel gives the current system date and time.
TODAY()
The TODAY() function in Excel provides the current system date.
The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month. =day(today())
The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December.
The YEAR() function, as the name suggests, returns the year from a date value.
HOUR, MINUTE, SECOND
The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.
The function MINUTE(), returns the minute from a time value as a number from 0 to 59. =MINUTE(NOW())
DATEDIF
The DATEDIF() function provides the difference between two dates in terms of years, months, or days.
Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.
IF Formula
The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.
In the below example, we want to check if the value in cell A2 is greater than 5. If it’s greater than 5, the function will return “Yes 4 is greater”, else it will return “No”.
=IF(A1>5,”Yes”,”No”)
COUNTIF
The function COUNTIF() is used to count the total number of cells within a range that meet the given condition.
Below is a coronavirus sample dataset with information regarding the coronavirus cases and deaths in each country and region.
Let’s find the number of times India is present in the table.
=countif(a1:a21119,”India”)
SUMIF
The SUMIF() function adds the cells specified by a given condition or criteria.