If you are one of those who use Microsoft Excel at workplace, you must be using some of the common functions like SUM, COUNT, AVERAGE, etc. Excel Formulas make our work easier and save a lot of time. But many times, you do things manually because you don’t know what formula to use and which excel function can help. Actually there are hundreds of functions in Excel and each one of these is meant to do some specific task. Knowing about all these will be quite difficult for an average excel user and no one actually recommend you to learn everything. But there are handful of excel formulas which are extremely useful and can make you really smart at work!
In this article, I am going to list down the top 10 excel function sets which you must know if your work demands working with Microsoft excel.
COUNT & COUNTA
As the names suggest, COUNT and COUNTA functions are used to count the number of cells which are non-empty. While COUNT function will only count the cells with numbers (and dates), COUNTA will count all cells which are non-blank (including text).
=COUNT(value1, value2, value3,…..)
=COUNTA(value1, value2, value3,…..)
For more information, have a look at COUNT & COUNTA functions article.
IF & IFERROR
IF function is the logical function in excel which returns a value based on some condition. This is one of the most important functions to learn because you will use it quite extensively.
Let’s understand IF function with an example.
You are required to pay 20% income tax if your total income is equal to or more than $50000. However, for senior citizen (with age more than 60 years), it is only 15%. If your income is less than $50000, you don’t require paying any tax.
In above formula, we are using nested IF function. The first IF statement checks if the income is less than $50000. If true, it returns zero. Otherwise, it uses second IF statement to check the age. If it is more than 60, it returns 15% of the total income otherwise 20% of total income.
You can learn more about Excel IF function here.
IFERROR has been added with Excel 2007. You can use to it to display a value when an error is found in a formula. Handy when you don’t want to scare your visitor with an error message like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
COUNTIF, SUMIF and AVERAGEIF
Sometimes you need to count the number of cells based on some condition. For example from a sales data table, you just want to count the number of sales with a value more than $10; COUNTIF function can be quite handy. Similar to COUNTIF, there are two more functions SUMIF and AVERAGEIF which do the SUM and AVERAGE based on criteria.
You can go through my tutorial on SUMIF function for more details.
While COUNTIF, SUMIF and AVERAGEIF allow you to specify one condition, there is another set of functions which will let you use multiple criteria at the same time. These are SUMIFS, COUNTIFS and AVERAGEIFS and have been introduced with Excel 2007.
Here, you first need to define the sum range followed by criteria range and criteria.
Suppose, from a sales table, you wish to know the SUM of sales value of any specific brand sold by some specific sales person with a specified value, SUMIFS can be quite handy. See the example below:
There are three criteria in this example. And the formula is:
=SUMIFS(Table1[Sales Value],Table1[Model],”Samsung”,Table1[Sales Person],”Jullie”,Table1[Sales Value],”>250″)
One of the most powerful function in excel is VLOOKUP. Learn it today and you will be much ahead of your colleagues which don’t know how to use it.
VLOOKUP formula searches a value from any column of a data table and returns any value from the searched row. For example if you have list of few people and you wish to get phone numbers and address from a huge database, VLOOKUP will be quite handy. You can also use VLOOKUP to compare two data tables and find out the common and odd values.
VLOOKUP is a big chapter and I won’t explain everything here. For understanding it once for all, go through my complete guide on Excel VLOOKUP.
These two functions are string functions of excel. CONCATENATE will combine the values in two (or more) cells into one. For example, if you wish to get the full name of a person from a data table with first name, middle name and last name; you can easily get it with CONCATENATE function.
=CONCATENATE(text1, text2, text3,….)
Though, if you don’t want to remember it, you can simply use “&” to do the same task like this:
= text1 & text2 & text3 & ……
Note that to insert space between first, middle and last name we have also combined spaces.
TRIM & LEN
TRIM function can save you from headache by removing the extra spaces in a cell. It will leave only a single space between two words and remove any other space.
Sometimes, when there are extra spaces in a cell before or after text (it happens especially when you pull the data from database), many of important functions like VLOOKUP and IF will not work properly. In that situation, you can first use TRIM function.
In this example, I have added a space before the text and extra space in between two words. See, what trim does.
In the above example, we have used LEN function to count the number of characters. No need to mention here that it also includes the spaces.
UPPER, LOWER & PROPER
These three functions are string functions and are used to transform the text to upper, lower and proper cases. Don’t worry if your colleague has typed everything in Upper case and you want to make it lower or create proper cases for the texts filled in hundreds of cells. Proper cell will capitalize the first letter in the text string and also any other letter which is not after a letter.
=UPPER(text), =LOWER(text), = PROPER(text)
LEFT & RIGHT
LEFT and RIGHT are the string manipulation function and are quite handy in a lot of situations. Suppose from chassis numbers, we wish to know the year of manufacturing which is coded at the fourth and fifth letters. We can easily get that using LEFT and RIGHT functions together.
LEFT is used to get the number of characters from the left. Similarly, RIGHT gives the number of characters from right.
=LEFT(text, num_char), =RIGHT(text, num_char)
Here the num_char is the number of characters from LEFT and RIGHT you wish to return.
In the above example, we are first extracting five characters from left and then out of this, two characters from right. This gives us the month of manufacturing of the chassis.
The last one in this list is the DATE function. You provide the year, month and day and it returns the date. This is quite simple but very powerful when you wish to do some date manipulation like addition or subtraction of day, month or year.
Similarly, if you wish to extract the year, month or day from any date, there are three functions YEAR, MONTH and DAY.
=YEAR(date), MONTH(date), DAY(date)
Suppose we are interested to get a date exactly after 100 days from now, we can use the following formula.
In the above example, we are first using TODAY() function which returns the current date. After that, we are first extracting year, month and day and adding 100 in day. Then we are using DATE function to get the date after 100 days.
The above 10 sets of Excel Formulas are extremely useful at simplifying your work. You can combine two or more functions together to make a more powerful formula. I frequently use these functions for my work and am sure if you know about these, you can work really smarter!