If you need to save or process data in your daily work, chances are you use excel for that. After all, this software has many features and tools that can help you do that kind of work.
One excel feature that is most often used by its users is formulas. There are a lot of formulas you can utilize in excel with each of their unique functions. The high number, however, can make it troublesome to master them all.
We’ve compiled the most essential Excel formulas to understand with their brief descriptions. Prioritize mastering these and you’ll be an Excel master in no time!
IF
Need the comparison results of some numbers to their targets or want to label your spending posts based on their certain spending limits? The IF formula can be your best friend in doing those kinds of works.
When using IF, you need to input a logic condition, the result if it is true, and the result if it is false. You need to compare both numbers in your logic condition. When your comparison is correct, you can produce a particular text, for example, to label the result. When it isn’t correct, you label it with a different kind of text.
As you often need to do comparison processes when processing data, this IF formula is something you should master. Doing so should make your job much easier to complete when you work with your data in excel.
SUM
One of the most basic calculations we often do with our numbers is the sum process. An example in work can be when we need to sum all the revenues we get from all products. Or maybe when we want to know the total inventories we have currently in our warehouse.
If you have all those numbers you want to add in excel, then SUM can make quick work for you. Just input all your numbers in the formula and enter. You will get your sum result immediately!
VLOOKUP/INDEX MATCH
When we have multiple worksheets or data tables in excel, at some point you’ll need to consolidate that data.
Thankfully, there are formulas that can help us to lookup data, which are VLOOKUP and the INDEX MATCH combination.
To use this powerful combo, we just need to supply our lookup reference and where they should find the data. They will automatically pull the data based on those inputs.
Between the two, most people probably use VLOOKUP for their data lookup process. However, if you can, you should learn how to use the INDEX MATCH formulas combination too. It is much more flexible in its process and result compared to VLOOKUP.
AVERAGE
When processing numbers, calculating their average is also sometimes essential to get the results we need.
We can use this calculation type for many use cases. Things such as knowing how much profit we usually make daily or our estimated production capacity are two examples from many. If we process our numbers in excel, then we can use the AVERAGE formula to average them fast. Like SUM, just input all the numbers you want to average and enter. You will get the average of those numbers immediately as a result.
COUNTA
COUNTA is a formula you can use to count the amount of data you have in a cell range in Excel. When you have a lot of data, this formula is a handy one to use.
In processing data, you may want to know its amount when you check for its completeness or subcategories count. With COUNTA, you can do those kinds of things much quicker compared to just counting them manually.
SUMIFS
Having some criteria for the numbers you want to sum in Excel? If you do, then you can use the formula that Excel provides to do that kind of calculation, SUMIFS.
In SUMIFS, you need to input your numbers cell range, the cell ranges which your criteria will evaluate, and your criteria. SUMIFS will only sum the numbers from the data entries which meet all your criteria.
When you have many data entries you need to sum your numbers selectively, this formula will surely become useful.
AVERAGEIFS
Besides summing based on criteria, you probably need to average numbers using criteria too. The AVERAGEIFS formula in excel can do that kind of calculation for you.
Like SUMIFS, you need to input the number and data cell ranges plus the criteria. AVERAGEIFS will do the rest by selecting your data entries and averaging the numbers that pass its evaluation process.
COUNTIFS
Like SUMIFS and AVERAGEIFS, COUNTIFS is something you use if you have criteria for your data processing needs. As the name suggests, you can use this formula if you want to count the data entries that pass your criteria.
COUNTIFS will only count the data entries that pass all your criteria in their rows/columns. When you have much data and you want to know the amount you have for certain specifications, this is your formula.
Of course, there are other formulas you might need to use depending on your job. But, the formulas listed above should be the essential ones you need to start optimizing your work in Excel.
So, have you mastered these formulas?
About the Author:
Daniel Ya is an Excel enthusiast and the founder of Compute Expert, an Excel educational website that provides free Excel tutorials that you can use to master Excel for your daily work.
