Technology has simplified our work, and with the right knowledge of digital tools, one can do things in jiffy. MS Excel is one such popular data management tool used for multiple purposes, such as analyzing vast data, performing quick calculations, organizing a plethora of data, generating analytical reports, etc.
What is most amazing about MS Excel is that it has plenty of shortcuts—formulas, and functions to perform a range of tasks without any errors and complications. So, if you really want to use MS Excel efficiently, knowing the most-used Excel formulas and functions is a must!
Over the next few minutes, you will be introduced to a set of Excel formulas and functions that you will use in your day-to-day operations. Here are the topics covered:
Microsoft Excel is one of the most popular and powerful spreadsheets, and it is used widely in every industry. It is mainly used to perform mathematical calculations in financial modeling, time management, invoice creation, inventory management, etc. To perform these massive and fragile calculations, we use Excel formulas and functions.
Excel formulas are a set of customized mathematical expressions that are used to perform those calculations. The main advantage of using a formula is that it makes the calculation dynamic, i.e. if there is a change in the source of the calculation, the result automatically changes. There is no manual intervention out there.
To understand how to use Excel formulas and functions, we have to understand the syntax of it first.
1. Structure of a Function in Excel:
Here is how you can use functions in Excel:
- Always start with an ‘=’ sign.
- Give the name of the function (e.g., SUM, IF, IFS)
- Give open parenthesis.
- Provide all the arguments required by the function.
- Give the closing parenthesis.
- Press Enter to execute.
Here is how you can use formulas in Excel:
- Always start with an ‘=’ sign.
- Provide with a customized mathematical expression that you want to calculate.
- Press Enter to execute
Here is an Excel formulas list representing the most used MS Excel formulas and functions that everyone should know.
Microsoft Excel doesn’t have a preset function for calculating percentages. However, you can calculate percentages by dividing the numbers and then converting them to percentages by using the Percentage function in the number group of the home tab.
For example, assume there are columns containing marks for students in math and science. You want to find the percentage of marks scored by each student.
To find the percentage,
- Start with an “=” sign
- Add the cells C3 and D3.
- Assuming total marks are 100 for each subject, divide by 200.
- Click on the percentage symbol in the number group of the home tab.
VLOOKUP is a famous Excel formula used for analysing reports and data in the range of the table, selected cell, or entire spreadsheet. It is used to find the value across columns with respect to the lookup value in the leftmost column of the table or selected column vertically.
You can use the syntax
=VLOOKUP(lookup_value, table, row_index, range_lookup) to find a value.
For example, assume you have huge data containing names, marks of students in math and science, and percentages. You are required to lookup the percentage of a student named “Sayan.” You would use VLOOKUP in this case.
Here are the steps to follow:
- Start with an “=” sign
- Give the name of the function VLOOKUP
- Provide the lookup_value, basically what you are looking for (in this case, “Sayan”)
- Provide the table where you want to look for (in this case, B2:E6)
- Provide the row_index (starts from 1), which row data you want to return (here, percentage column row_index is 4)
- Press Enter to Execute
The SUM function is one of the most-used functions in MS Excel. The function is symbolised by =SUM. This function is used to add numbers or to find out the aggregate values of selected cells. Instead of writing commands for multiple cells, you can simply use the SUM formula and write the cell addresses along with it. Here is the syntax
For example, assume there are columns containing marks for students in math and science. You want to find the total marks scored by each student.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function SUM.
- Provide the parameters, i.e., the marks you want to add.
- Press Enter to execute.
You can subtract two numbers on an Excel spreadsheet by using the SUM function itself. It is just that the second parameter has to be prefixed by a ‘-’ sign.
For example, assume we need to find the difference in marks scored in math by two students.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function SUM.
- Provide the parameters, i.e., the marks you want to subtract, but make sure the second parameter is prefixed with a ‘-’ sign.
- Press Enter to execute.
The IF formula is used to get an output based on a certain condition evaluated. With respect to the condition being TRUE or FALSE, the designated output will be returned.
The syntax for the same is:
=IF(logical_test, value_if_true, [value_if_false])
For example, assume columns containing percentages of marks scored by students in the class. You are required to find out if the student has passed or failed the examination. Let’s say a student scoring more than 40% passes and the rest fails.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function IF.
- Provide the condition based on which student passes or fails.
- Provide the output to show if the condition is true.
- Provide the output to show if the condition is false.
- Press Enter to execute.
The average function calculates the average values (arithmetic mean) of the selected cells.
The syntax for the Average If function is
=AVERAGE(number1, [number2], ...)
For example, assume a table containing the marks of students with their respective names. You are required to find out the average marks scored by the class.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function AVERAGE.
- Provide the range for which the average needs to be calculated.
- Press Enter to execute.
7. COUNTIF Function in Excel
The CountIf function is very helpful for counting the total number of cells in the given range in a given condition. It completes the calculation only if it meets the criteria.
The syntax for the CountIf function is
=COUNTIF(range, criteria)
For example, assume a table containing the marks of students with their respective names. You are required to find out the count of students with marks > 70.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function COUNTIF.
- Provide the entire range from the marks column
- Provide the condition that “marks > 70”
- Press Enter to execute.
8. Excel RANK Function
The rank function is used to provide rank to a number based on the size of it as compared to the others in the column.
Here is the syntax for the rank function:
=RANK(number,ref,[order])
For example, assume a table containing the marks of students with their respective names. You are required to find out the rank of students in the class.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function RANK.
- Provide the cell address for which rank is to be calculated.
- Provide the columns where the marks will be compared and the rank will be declared.
- Provide in which order you want the rank to be 0 for descending and 1 for ascending.
- Press Enter to execute.
9. INDEX Function Excel
The index function is often used for advanced lookup activity. It is used to return a value for a specified cell often referred to by using the row number and column number.
=INDEX(array, row_num, [column_num])
For example, assume a table containing the marks of students with their respective names. You are required to find out the marks of Ankit.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function INDEX.
- Provide the row number where the name is present
- Provide the column index, i.e., the value that you want.
- Press Enter to execute.
10. LOOKUP Function Excel
The lookup function is used to return the value of a row or column based on a lookup value in a column or row.
Here is the syntax:
=LOOKUP(lookup_value, lookup_vector, [result_vector])
For example, assume a table containing the marks of students with their respective names. You are required to find out the marks of Ankit.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function LOOKUP
- Provide the key that you want to search for.
- Provide the range where the key should be searched
- Provide the range from which the value should be returned.
- Press Enter to execute.
11. HLOOKUP in Excel
The hlookup formula in Excel is used for lookup activities where the value to be searched is on the top row and the value to be returned is in the same column.
Here is the syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
For example, assume a table containing the names of the employees and the same column containing their basic information. You are given a search term, let’s say a name, and you are required to find the gender and employee ID of that employee.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function HLOOKUP
- Provide the key that you want to search for.
- Provide the range where the key should be searched
- Provide the range from which the value should be returned.
- Press Enter to execute.
12. MATCH Function Excel
The match function is used to retrieve the positional index of the value based on the search key. It is often clubbed with the INDEX function for better and easier lookup activities.
Here is the syntax:
=MATCH(lookup_value, lookup_array, [match_type])
For example, assume a table containing the marks of students with their respective names. You are required to find out the positional index of Sayan which will be further used in INDEX function.
Here are the steps given:
- Start with an “=” sign.
- Give the name of the function MATCH
- Provide the key that you want to search for.
- Provide the range where the key should be searched
- Provide the match type (0 for exact match, 1 for closest value less than, -1 for smallest value greater than). Here 1 is the default value.
- Press Enter to execute.
13. SUMIF Function in Excel
This function is used to add numbers or to find out the aggregate values of selected cells based on a certain condition.
Here is the syntax:
=SUMIF(range, criteria, [sum_range])
For example, assume there are columns containing percentages scored by students. You want to find the total marks scored by the class, but with students who have passed, i.e., marks > 40.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function SUMIF.
- Provide the parameters, i.e., the marks you want to add.
- Provide the condition “> 40.”
- Press Enter to execute.
14. ROUND Function in Excel
The round function will round off the decimal number to the required decimal place.
Here is the syntax:
=ROUND(number,num_digits)
For example, assume you have a dataset containing percentages of marks and the name of the students. The percentage contains 5 decimal places. You are required to round off the percentages to 2 decimal places.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function ROUND.
- Provide the number to be rounded off.
- Provide the number of decimal places required.
- Press Enter to execute.
15. CEILING Function in Excel
The ceiling function in Excel is used to return the rounded-off number to the nearest largest multiple of a value provided in the parameter.
Here is the syntax:
=CEILING(number, significance)
For example, Here are the steps to use the ceiling function
16. FLOOR Function in Excel
The floor function is just the opposite of the ceiling function. It is used to return the rounded number to the nearest smallest multiple of the value provided in the parameter.
Here is the syntax:
=FLOOR(number, significance)
17. LEN Function in Excel
The len function is used to return the count of the number of characters in the text.
Here is the syntax:
=LEN(text)
18. CONCAT in Excel
The concat function is used to concatenate or join multiple texts together without a delimiter. If you intend to use a delimiter, you can use the join function.
Here is the syntax:
=CONCAT(text1, [text2],…)
For example, assume you have a dataset containing the first names and last names of students in a class. You are required to join the first and last names and create a column named names.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function CONCAT.
- Provide the first text to join.
- Provide the second text to join.
- Press Enter to execute.
NOTE: There is no provision to add a delimiter while using the CONCAT function.
19. UPPER Function in Excel
The upper function is used to transform a text in any case to all uppercase text.
Here is the syntax:
=UPPER(text)
The lower function is used to transform a text in any case to all lowercase text.
Here is the syntax:
=LOWER(text)
The proper function is used to transform a text in any case to camelcase text, i.e., every first character of the word is capital; others are lowercase.
Here is the syntax:
=PROPER(text)
22. NOW Function in Excel
The now function is used to show the current time and date of the system on Excel. It might differ with different timezones and systems.
Here is the syntax:
=NOW()
The time function is used to convert numbers into Excel time format. It takes input in the form of hours, minutes, and seconds in 24-hour format and returns the time with formatting in Excel.
Here is the syntax:
=TIME(hour, minute, second)
24. TODAY Function in Excel
The today function is used to print the current date on the system in a cell. It is usually combined with other functions such as DAY, MONTH, and YEAR to further get just the date, month, or year. The date is subject to change with systems and timezones.
Here is the syntax:
=TODAY()
Now if we want to granularize the date, we can use the DAY, MONTH and YEAR functions as shown below:
25. TRIM Function in Excel
The trim function in Excel is used to trim the extra amount of space that gets added mistakenly to the data and might result in inappropriate results.
Here is the syntax:
=TRIM(text)
26. COUNTA Function in Excel
The counta function in Excel is used to count the number of cells in the selected range, which is not empty.
Here is the syntax:
=COUNTA(value1, [value2], ...)
For example, assume you have a dataset containing the first names and last names of students in a class along with their marks in English. You are required to count the number of students in the class.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function COUNTA.
- Provide the range where you want to count the number of students.
- Press Enter to execute.
The count function in Excel is used to count the number of cells in the selected range, which is not empty and also contains a numeric value.
Here is the syntax:
=COUNT(value1, [value2], ...)
For example, assume you have a dataset containing the first names and last names of students in a class along with their marks in English. You are required to count the number of students who attended the exam.
Here are the steps.
- Start with an “=” sign.
- Give the name of the function COUNT.
- Provide the range of cells.
- Press Enter to execute.
28. MIN and MAX in Excel
The min function is used to find the minimum number (smallest) from the selected range of cells.
Here is the syntax:
=MIN(number1, [number2], ...)
The min function is used to find the maximum number (largest) from the selected range of cells.
Here is the syntax:
=MAX(number1, [number2], ...)
For example, assume you have a dataset containing the first names and last names of students in a class along with their marks in English. You are required to find the topper (student with maximum marks) and lowest scorer (student with minimum marks) in the class.
Here are the steps.
To find the student with maximum marks
- Start with an “=” sign.
- Give the name of the function MAX.
- Provide the range of cells.
- Press Enter to execute.
To find the student with minimum marks
- Start with an “=” sign.
- Give the name of the function MIN.
- Provide the range of cells.
- Press Enter to execute.
Formulas and functions are two important terminologies that are often used interchangeably. Though they sound the same, they are different in many ways. Here are the differences listed:
|
Excel Formulas |
Excel Functions |
| Excel Formulas are customised mathematical expressions that are used to perform a specific operation | Excel Functions are set of predefined mathematical expressions that are used to perform a specific operation |
| SYNTAX: =<Mathematical Expression> | SYNTAX: = <Function_Name>(<Parameters by commas>) |
| Example: #Expression to calculate percentage = (( C1 + C2) / 200 ) * 100 | Example: #Expression to calculate the sum of numbers =SUM(C1, C2, C3) |
To learn more on Microsoft Excel, check out this video by Intellipaat:
Conclusion
I hope this blog has helped you get familiar with the top Excel formulas and functions that you will use in your day-to-day life. Excel is a very important tool that is used worldwide in all the industries. It is one of the mandatory tools to know if you are aiming towards becoming a manager and further.
If you want to have your hands on Excel and master it, consider enrolling in our Advanced Certification in Microsoft Excel training program to get exposure to certification-oriented courses that can endorse your value.
Our Data Science Courses Duration and Fees
Cohort starts on 9th Mar 2025
₹69,027
Cohort starts on 2nd Mar 2025
₹69,027
Cohort starts on 16th Feb 2025
₹69,027