Launch your Graphy
There are no items in your cart
Add More
Add More
| Item Details | Price | ||
|---|---|---|---|
List Of Top 19 Useful Microsoft Excel Formulas
Thu Sep 2, 2021
Excel is one of the most widely used tools in everyday businesses. It is very helpful in managing tons of data in one spreadsheet.
However, when you know certain formulas and functions it becomes much easier. To help you out we have listed down the top 19 formulas that you must know to make your work excel more efficiently.
So, let’s get started.
Table Of Contents:
- List Of 19 Useful MS Excel Formulas
- Frequently Asked Questions
- Conclusion
List Of 19 Useful Excel Formulas
1.SUMIF
This is one of the most important formulas you must know. However, if you need to sum values based on many different criteria, then this is the formula you need.
Formula: =SUMIF(RANGE,CRITERIA [sum_range] )[sum_range] is optional.
2. FIND/SEARCH
In excel you have many rows, columns, and cells. With so much data present it is difficult to find a specific cell that you are looking for. That is where this formula becomes helpful.
=FIND is used when you want to find a specific set of data. For example, if you want to find the word Small, you will get just that.
=SEARCH is used when you want a broader set of results. For example, if you search for the word Small, you will get results for Small as well as small.
Formula:
=FIND(FIND_TEXT,WITHIN_TEXT[START_NUMBER])
=SEARCH(FIND_TEXT,WITHIN_TEXT,[START_NUMBER])[START_NUMBER] is used to indicate the starting cell in the text to search.
3. RANK
The rank formula is used mainly for data analysis.
=RANK allows you to rank your values in a dataset in either ascending or descending order.
Formula: =RANK(SELECT CELL,RANGE_TO_RANK_AGAINST[ORDER])
4. CONCATENATE
This formula is mostly used when you have a lot of data to input daily. The majority of the time you will miss a call or leave a row empty by accident.
This formula will help you identify the empty cells without you having to go through all of the data.
Formula: =COUNTA(SELECT CELL)
5. COUNTA
This formula is mostly used when you have a lot of data to input daily. The majority of the time you will miss a call or leave a row empty by accident.
This formula will help you identify the empty cells without you having to go through all of the data.
Formula: =COUNTA(SELECT CELL)
You may also like:
6. LEN
If you want to find out how many characters exist in one cell then this is the formula you are going to use.
=LEN helps to identify this with ease. It is especially useful when there are too many characters in a cell to count.
Formula: =LEN(SELECT CELL)
7. MINIFS
This formula allows you to take a minimum set of values and match on criteria. You can use it to find the minimum number in a row or column.
You can use this instead of sifting through tons of data.
Formula: MINIFS(RANGE1,CRITERIA1,RANGE2)
8. MAXIFS
Since we spoke about =MINIFS, we have to speak about =MAXIFS.
This formula is just like =MINIFS only this is it allows you to take a maximum set of values and match on criteria.
You can use this formula to find the maximum number in a row or column.
Formula: =MAXIFS(RANGE1,CRITERIA1,RANGE2)
9. IFERROR
=IFERROR is a formula that every analyst uses. If you don't, you should definitely start. Like in the second formula, looking for a specific text may either give you too little or too many results. This causes a #VALUE error.
=IFERROR is used to replace the errors with a value or text.
Formula: IFERROR(FIND”VALUE”,SELECT CELL, VALUE_IF_ERROR)
10. AVERAGEIFS
Just like SUMIFS this formula allows you to take an average based on one or more criteria.
Formula: =AVERAGEIF(SELECT CELL,CRITERIA,[AVERAGE_RANGE])
11. DAYS/NETWORKDAYS
These two formulas are similar to each other but they do have one difference. =DAYS can determine the number of calendar days between two specific dates.
Whereas, =NETWORKDAYS can determine the number of working days between two specific dates. These formulas can help you manage time and finish a specific project in time.
Formula:
=DAYS(SELECT CELL,SELECT CELL) =NETWORKDAYS(SELECT CELL,SELECT CELL,[number of holidays])[number of holidays] is optional and can be used only if you want to calculate them.
12. SUMPRODUCT
=SUMPRODUCT is a formula that multiples one range of values by its corresponding row counterparts.
Formula: =SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL)
13. VLOOKUP
This formula allows you to search for a specific value in a column, in order to return a value from a different column in the same row.
Formula: =VLOOKUP(LOOKUP VALUE,RANGE, COLUMN NUMBER, FALSE OR TRUE)
14. HLOOKUP
Just like VLOOKUP, HLOOKUP is similar but has a difference.
This formula allows you to search for a specific value in a row, in order to return a value from a different row in the same column.
Formula: =HLOOKUP(LOOKUP VALUE,RANGE, COLUMN NUMBER, FALSE OR TRUE)
15. PROPER
=PROPER is a very interesting and useful formula. Usually, when you are typing on Excel, you have so much data and so little time.
You cannot manually check to see if every word is written correctly. That’s where this formula comes in. It helps you sort out the uppercase and lowercase letters in a cell.
Formula: =PROPER(SELECT CELL)
16. TRIM
Many times while filling in data in excel we leave unnecessary gaps in the cell. Because of that, our results show incorrect or an error. This formula will help avoid that.
Formula: =TRIM(SELECT CELL)
17. AVERAGE
This function gives us the sum total value of the data in the selected cell range. It is very helpful for those who do accounting.
Formula: =AVERAGE(SELECT CELL)
18. SUM
This function can give you the average value of the data in the selected cell range. It is very helpful for big data sets.
Formula: =SUM(SELECT CELL)
19. COUNT
IF This function gives us the count of rows or columns in a data set when a particular condition has to be satisfied.
Formula: =COUNTIF(RANGE, CRITERIA)
Frequently Asked Questions
1. What is the most useful tool in Excel?
VLOOKUP is the most useful tool in Excel because it will give us the exact row that will satisfy the necessary condition.
2. What is the difference between Excel & advanced Excel?
Advanced Excel uses more pivot tables, pivot charts, formulas, functions, and macros whereas Excel does most of the work manually.
3. What types of jobs are available after learning excel?
Learning Excel is useful for any job and it also is a plus point on your resume while applying for jobs.
4. What are formulas & functions in Excel?
Formulas and functions are shortcuts to help save your time and get the results you desire quickly.
Conclusion
So, there you have it, friends.
The top 19 Excel formulas and functions will make your life so much easier.
Try them out and see how your time gets saved.
You will also be able to work more efficiently.
Letstute
Letstute (Universal Learning Aid Pvt. Ltd.) is an E-learning company based in Mumbai, India.