# Introduction

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)

 Name Total Price Books 10 Pens 20 Bags 30 Total 60

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

• AVERAGE

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

Example: =AVERAGE(E5:E7)

 Name Total Price Books 10 Pens 20 Bags 30 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.

• COUNT

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

Example: =COUNT(E5:E7)

 Name Total Price Books 10 Pens 20 Bags 30 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.

• MODULUS

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

Example: – MOD(Number, Divisor)

 Modulus Total Price 10 0 20 2 30 2

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.

 Name Length Sandra 7 Jessica 8
• CEILING

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

For example: CEILING(number, significance)

CEILING( E12, 5)

Or

CEILING(50.54, 5)

 CEILING 50.54 55

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

• FLOOR

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

FLOOR(D5, 3)

 FLOOR 30.46 30

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

• POWER

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

 Power 2 4 4 1024 8 512

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.

• CONCATENATE
 Names Peter Paul PeterPaul Happy Birthday 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”).

• REPLACE

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

 Replace R20 S20 H17 HY7

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.

• SUBSTITUTE

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

 Substitute Today is a nice day Today is a wonderful day She likes chocolate She 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 Name Last Name Department Peter Paul Marketing Adam Smith Sales Kevin Stuart HR Lisa West IT
• LEFT, MID
 Left Life is beautiful Life

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

 Mid Life is beautiful Is

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.

 Right Life is beautiful Beautiful

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

• UPPER, LOWER, PROPER

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.

 Uppercase The world is a stage THE WORLD IS A STAGE
 Lowercase LIFE IS BEAUTIFUL life is beautiful
 Propercase today is a nice day Today 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.

• TODAY

The function TODAY() returns the current system date.

 Today() 07/03/2022
• DAY
 Day Day =DAY(TODAY()) 12

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

• MONTH
 Month Month =MONTH(TODAY()) 3

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

• YEAR
 Year Year =YEAR(TODAY()) 2022

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

• TIME
 Time Time =TIME(10,20,30) 10:20 AM

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

• HOUR
 Hour Hour =HOUR(NOW()) 20
 Minute Minute =MINUTE(NOW()) 20
 Second Second =SECOND(NOW()) 32
• HLOOKUP

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.

 First Name Peter Adam Kevin Lisa Last Name Paul Smith Stuart West Department Marketing Sales HR IT
 Kevin HR

## Conclusion

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.