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