Easy way to IT Job

Top 25 Excel Formulas you should know

Top 25 Excel Formulas you should know

Published On: April 29, 2022

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)

In other words, SUM(10,20,30)
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)

In other words AVERAGE(10,20,30)
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)

In other words COUNT(10,20,30)
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)

MOD(E5,2), MOD(E6, 3) and MOD(E7,4). In other words, MOD(10,2), MOD(20, 3) and MOD(30, 4).
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.

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
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)

Here significance is the multiple you want to round.
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)

Example: FLOOR(Number, Significance)
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.

FOR example: POWER(D5,2). In other words it is nothing but POWER(2, 2).
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
The CONCATENATE() function joins or merges several text strings into one string.
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.

For example: REPLACE(G18,1,1,”S”) and REPLACE(G19,2,1,”Y”)
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.

For example: SUBSTITUTE(G18,”nice”,”wonderful”) and SUBSTITUTE(G19,”chocolate”,”icecream”)
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.

VLOOKUP()
First Name Last Name Department 
Peter Paul Marketing
Adam Smith Sales
Kevin Stuart HR
Lisa West IT
=VLOOKUP(H10,H10:J10,2) After applying this formula, the following is returned.
Adam Smith
  • 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.
Left
Life is beautiful Life

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.
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
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.

Example:
Uppercase
The world is a stage THE 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
Lowercase
LIFE IS BEAUTIFUL life 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.
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.

For example:
Today() 07/03/2022
  • 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.
Day Day
=DAY(TODAY()) 12

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

  • MONTH
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.
Month Month
=MONTH(TODAY()) 3

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

  • YEAR
The function YEAR() returns the current year.
Year Year
=YEAR(TODAY()) 2022

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 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
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.
Hour Hour
=HOUR(NOW()) 20
MINUTE
Minute Minute
=MINUTE(NOW()) 20
SECOND
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.

For example:
First Name Peter Adam Kevin Lisa
Last Name Paul Smith Stuart West
Department  Marketing Sales HR IT
=HLOOKUP(K20,H20:L22,3). After applying this formula in the above table. The following data is retrieved.
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.

Just a minute!

If you have any questions that you did not find answers for, our counsellors are here to answer them. You can get all your queries answered before deciding to join SLA and move your career forward.

We are excited to get started with you

Give us your information and we will arange for a free call (at your convenience) with one of our counsellors. You can get all your queries answered before deciding to join SLA and move your career forward.