Excel Formulas

Top 25 Excel Formulas you should know


Microsoft Excel is a very helpful tool mainly to work with data. Many organizations use Excel to help them work with data to generate reports. It is very popular among professionals and its simplicity makes it all the more popular. It has various built-in applications, which are less complicated to use, making it a hit among professionals.

In this blog, we will learn about excel formulas and functions, which are widely used in software applications by industries to carry out calculations thereby, making data analysis quicker. We will go through top 25 excel formulas that you must be aware of in the current technology era.

Excel Formulas and Functions

Excel formulas basically operate on values in a range of cells. They provide results after performing the operation on the values. It does not matter whether they right or wrong, it always gives the output. Excel formulas are widely used to perform various operations like addition, subtraction, multiplication, division, finding averages and percentages and also work on date and time values.

Also in this blog, we will learn about functions and their uses. There is marked difference between functions and formulas in Microsoft Excel. Formulas are usually typed and designed by the user whereas functions are predefined calculations used on the spreadsheet. They both are used to perform calculations used in complex situations right from solving engineering problems, mortgage payments and other math problems.

Here we will look at the top 25 excel formulas that you should be aware of. This will be a handy resource for students as well as professionals related to information technology. 

  • SUM

The SUM() function is used to find the total of a set of values by selecting the range of cells. This literally is the addition operation performed on a set of values.

Eg: =SUM(E5:E7)

In other words, SUM(10,20,30)
NameTotal Price

Here we are finding out the total price. The cell range is E5:E7. The function SUM() is used to find the total price. After we type the formula SUM(E5:E7). We get the total price as 60


The next function is AVERAGE() which calculates the average of a set of selected values.

Example: =AVERAGE(E5:E7)

In other words AVERAGE(10,20,30)
NameTotal Price
Average 20

Here we are finding out the average of the total price. The selected cell range is E5:E7. The function AVERAGE() is used to find the average of the total price. After we type the formula AVERAGE(E5:E7). We get the average of the total price as 20.


The function COUNT() is used to find the number of total price in the selected range of values.

Example: =COUNT(E5:E7)

In other words COUNT(10,20,30)
NameTotal Price
Count 3

Here we are finding out the count of the total price. The selected cell range is E5:E7. The function COUNT () is used to find the count of the total price. After we type the formula COUNT(E5:E7). We get the count of the total price i.e 3. Here the function COUNT() works only on numeric values.


The function MOD() returns the remainder of a selected number when it is divided by another number.

Example: – MOD(Number, Divisor)

MOD(E5,2), MOD(E6, 3) and MOD(E7,4). In other words, MOD(10,2), MOD(20, 3) and MOD(30, 4).
ModulusTotal Price

Here in MOD(10,2). The number 10 is divided by 2 and the remainder obtained after division is 0. So the modulus is 0. Similarly, MOD(20,3). The number 20 is divided by 3 and the remainder obtained after division is 2. So the modulus is 2. Likewise, MOD(30,4). The number 30 is divided by 4 and the remainder obtained after division is 2. The modulus is 2.

  • LEN

The LEN() function is applied on a string and it returns the total number of characters a string has. The overall characters, special characters and spaces are returned.

Example: LEN(G18) also LEN(“Sandra”) returns the length as 7, as there are 7 characters in the string. Another example is LEN(G19) also LEN(“Jessica”) returns the length as 8, as there are 8 characters in the string.
Name Length

The function CEILING() rounds a number to the nearest multiple of significance or the nearest integer.

For example: CEILING(number, significance)

CEILING( E12, 5)


CEILING(50.54, 5)

Here significance is the multiple you want to round.

Here the multiple is 5 and the nearest multiple of 50.54 is 55.


The function FLOOR() rounds number down to the nearest multiple of significance.

FLOOR(D5, 3)

Example: FLOOR(Number, Significance)

Here the multiple is 3 and number down to the nearest multiple of significance is 30.


The function POWER() returns the result after a particular number is raised to certain power.

FOR example: POWER(D5,2). In other words it is nothing but POWER(2, 2).

Here POWER(D5, 2) also POWER(2,2) will return the value 4 after the selected number 2 is raised to the power of 2. Similarly POWER(D6, 5) also POWER(4,5) will return the value 1024 after the selected number 4 is raised to the power of 5. Likewise, POWER(D7, 3) also POWER(8,3) will return the value 512 after the number 8 is raised to the power of 3.

The CONCATENATE() function joins or merges several text strings into one string.
Happy Birthday

Example: CONCATENATE(G9,H9) which is nothing but CONCATENATE(“Peter”,”Paul”) and the output obtained concatenating is PeterPaul. Similarly CONCATENATE(G11,H11), which is nothing but CONCATENATE(“Happy”,”Birthday”).


The function REPLACE() replaces a part of a string with a different string.

For example: REPLACE(G18,1,1,”S”) and REPLACE(G19,2,1,”Y”)

In the above example, REPLACE(G18,1,1,”S”) also REPLACE(R20, 1,1,”S”). Here the first character of “R20” i.e. is R is replaced with S. Here you can clearly see that one character and the first character is replaced.

Again, in the second example, REPLACE(G19,2,1,”Y”). Here the second character of “H17” i.e 1 is replaced with Y. Here you can clearly see that the second character and only one character is replaced.

Let us understand the function better

REPLACE(oldText,startNum, numChars, newText).

In this syntax, startNum is the index position where you need to start replacing the characters. numChars is the number of characters you wish to replace.


The function SUBSTITUTE() is used to replace the existing text with a new text in a string.

For example: SUBSTITUTE(G18,”nice”,”wonderful”) and SUBSTITUTE(G19,”chocolate”,”icecream”)
Today is a nice dayToday is a wonderful day
She likes chocolateShe likes icecream

Here in the example SUBSTITUTE(G18,”nice”,”wonderful”), the word “nice” in the string text “Today is a nice day” is replaced with “wonderful” and the output is “Today is a wonderful day”.

In the next example SUBSTITUTE(G19,”chocolate”,”icecream”) the word “chocolate” in the string text “She likes chocolate” is replaced with “icecream” and the output is “She likes icecream”.

The function VLOOKUP() looks for a particular value in the leftmost column in a table. It is known are vertical lookup.

We will take a look at the arguments of the VLOOKUP() function.

lookup_value – this value is the value that you have to look in the first column in the table.

table – this denotes the table from which the value is obtained.

col_index – mentions the column in the table from where the value is to be obtained.

range_lookup – it is an optional argument. TRUE = approximate match. FALSE = exact match.

First NameLast NameDepartment 
=VLOOKUP(H10,H10:J10,2) After applying this formula, the following is returned.
  • LEFT, MID 
The function LEFT() returns the number of characters from the beginning of a string. The function MID() returns the characters from the middle of the string when you give the length and starting position. The function RIGHT() returns the number of characters from the end of the string.
Life is beautifulLife

Here the LEFT() function is used to extract the word “Life” from the beginning of the text string.

LEFT(G22,4) – this is nothing but LEFT(“Life is beautiful”,4). Here the first four characters are extracted from the text string.
Life is beautifulIs

MID(G22,5,3) – Here the function MID(“Life is beautiful”, 5,3). The start number and the number of characters to be returned are mentioned. So applying this formula “is” is the string that is returned.

Life is beautifulBeautiful

RIGHT(G28,9) – Here the function RIGHT(“Life is beautiful”,9). The number of characters 9 is mentioned and the word “beautiful” is returned.


Any string can be converted to uppercase when the function UPPER() is used. Similarly the function LOWER() can be used to convert any string to lowercase. The PROPER() function converts any string of text with the first letter of each word in uppercase and the rest in lowercase.

The world is a stageTHE WORLD IS A STAGE
UPPER(G32) – Here this also is UPPER(“The world is a stage”). The function converts the whole text string into upper case
LIFE IS BEAUTIFULlife is beautiful
The above example is for lowercase. LOWER(G35). This is actually LOWER(“LIFE IS BEAUTIFUL”). The function converts the whole text string into lower case.
today is a nice dayToday Is A Nice Day

In the above example, the function PROPER(G38) is PROPER(“today is a nice day”) is converted to “Today Is a Nice Day”.

  • NOW

The function NOW() gives the current system date and time.

NOW() – 06/03/2022 21:26. Here the function returns the current system time and date, but it is highly volatile and keeps changing.


The function TODAY() returns the current system date.

For example:
  • DAY
The function DAY() basically returns the day of the month. It is normally between 1 to 31. The number 1 and 31 are the first day and last day of the month.

In the above example, current day number is returned i.e. 12.

The function MONTH() returns the month of the year. For example the number 1 to 12 will be displayed. The number 1 and 12 are the first month January and 12 i.e the last month is December.

In the above example, the current month i.e. March is returned.

  • YEAR
The function YEAR() returns the current year.

In the above example, the current year is displayed i.e. 2022

  • TIME
TIME() function converts hours, minutes, and seconds which are mentioned as numbers to an Excel serial number. It is formatted with a time format.
=TIME(10,20,30)10:20 AM

In the above example TIME(hours, minutes, seconds) is mentioned and the corresponding time is displayed.

  • HOUR
The function HOUR() returns the hour in the form of a number between 0 and 23. 0 being 12 a.m. and 23 being 11 p.m.

The function HLOOKUP() also known as horizontal lookup. This function looks for a value on the top row of a table. It gives the value in the same column from the row you mention.

Here lets look at the arguments.

lookup_value – this denotes the value to look up.

table – this denotes the table from where you have to extract the data.

row-index – this denotes the row number from where to extract data.

range_lookup – this is optional. This denotes the exact match or approximate match. It is Boolean in nature.

For example:
First NamePeterAdamKevinLisa
Last NamePaulSmithStuartWest
Department MarketingSalesHRIT
=HLOOKUP(K20,H20:L22,3). After applying this formula in the above table. The following data is retrieved.


Excel is a spreadsheet application that is mainly used for analysing data and for reporting purposes. It is a very efficient and powerful tool. In the above blog, you must have understood the various formulas and functions used, which are applicable to perform on various tasks for better performance. You can always master the use of Excel applications to make transitions in career.

This blog will be useful to students who are studying information technology and also for professionals who are working in the IT industry. It gives you a list of commonly as well important formulas relevant to Excel applications. This blog has given you explanation for each function so that it is easily comprehendible to even a beginner who has no experience with spreadsheet applications and programming.

Leave a Comment