Excel functions can greatly enhance your ability to perform tasks in day to day activities in Excel. While the are hundreds of functions out there, I feel there are 15 functions that all Excel users should know. Whether you are an office worker, or a small business owner using Excel to keep track of your finances or just the casual user, these 15 functions can be very important time savers and enhance your ability to create better performing spreadsheets!
The Vlookup function is one of the most important functions out there yet many people do not know anything about it. This function will look for a piece of information in a large table of data and pull in any field from that table into your new table. The best way to see this is through an example.
Let us say we have 2 tables. The first one is just a list of 3 names. The second is a list of 25 names along with 3 test scores they took. What we want to do is pull the second test score from the large table and put it into the small list of names.
So first, we have the list of three students below.
We want to use the VLOOKUP function to pull in the test score from Subject 2 from the larger table below:
So instead of manually looking for the name yourself in this large table, you can use the vlookup function to do it.
Go to the cell next to Morty Seinfeld’s name in the small table and Insert a function or click the Fx button.
Once you press that, you will need to find the vlookup function. Either type it in the search at the top and press Go or find it in the list at the bottom.
Once you click it, you will see the Vlookup wizard pop up. This is where we can start to enter in our variables.
Lookup_value – This is the cell we are trying to find in the large table. In this example, we are looking up Morty Seinfeld. We could physically type in Morty Seinfeld however, we want to be able to copy and paste this to the rest of the table, so we need to make it a reference.
Table array – This is the range of the large table Tip 1 – The leftmost column needs to contain the field you are looking for. Tip 2 – Hilight the entire column. If you just hilight a range, like A1:D50, then when you copy the formula, this range is going to change too.
Col_index_num – This is the column we want to pull. In our large table, name is column 1. Subject is column 2. Subject 2 is column 3 and Subject 3 is column 4.
Range_lookup – This is either true or false. If you choose False, then Excel is going to look for an exact match. In this case, looks for “Morty Seinfeld”. If it doesn’t find it, it returns an #N/A. If you enter True, then it will look for the match but if it doesn’t find it, it will return what Excel considers the closest. I rarely ever use True.
So the wizard is the easiest way to enter in the function. However, you can also just type it in if you want.
This will produce the same results.
Then once we press enter and finish the formula, Excel will pull in the results. you can then copy and paste it down and get the test scores for all 3 people in this example.
The Sum function is probably the most widely used function out there so this may be one you already know. This function will just Sum up a group of numbers. Very simple.
Let’s use our example above. Let’s say we want to total up the 3 test scores below.
We would put our cursor in the cell we want the total to appear. In this example, put the cursor right below the list of numbers. Then Insert the Function. One nice thing about the sum function is that there is a shortcut button on the Home tab. It looks like:
Once you press it, Excel will look for numbers near there and put a dotted box around the numbers that it thinks that you want summed up.
If the dotted box is around the correct numbers, then just press enter and you will get your total. But if it is not, then just hilight the cells that you DO want summed. Then press enter around the new numbers and get your result.
Sometimes Excel will pick the wrong range of numbers or even include the title in the list to add up. So be sure to double check all the ranges before pressing enter!
Then once you press enter, you will get this result:
MAX and MIN functions will simply return the largest and smallest result from a range of numbers. Let’s use our Test score example from above one more time.
We have this list of test scores and we need to find the highest and lowest score for each Subject.
So first go to the cell next to highest score in insert a function. Go to the function MAX. This function will return the highest value.
All you need to do is drag your cursor over the cells that you want to find the max value for. In this example, its cells B4 to B24. That is Subject 1. What Excel will do is look through that range and pull in the highest value. In this example, it is 95. You can also hilight multiple ranges.
Now go to cell B26 and insert a function. Insert the function MIN. This will work the exact opposite of MAX. You still will hilight the full range of cells that you want to check, but this function will pull in the lowest value.
So now you have added in a MAX and MIN value for this Subject. Now if you want to find that for Subject 2 and 3, just copy and paste the formula over and you will get the new results.
The IF function is used to determine whether a statement is True or False and then performs an action based on the result. The IF statement is broken out as
IF(Criteria,True value,False value)
You can see this best by following an example
In this example we have a table of salesmen and their Forecasted sales. We have a column stating “Is Forecast over 150k?”
So we want to make an if statement to ask “Are sales over 150k?”. If so, then do an action. If not, then do something else.
So go to cell G4 and insert the IF function.
Start to fill in the Wizard fields.
Logical_test – D4 > 150000 – here we put a reference to the Forecast amount and check if it is over 150000. In cell D4, the value is exactly 150000, but since it is not over 150000, then the result is false. As you can see above.
Value if true – Here you enter in what you want for the result if it is true. You can put any word or combination of words. You can put an amount. Or you can even put another formula.
Value if false – Same as above, just for the false result.
Once you copy this formula down, then it will test all the fields in column D Forecast and give you the results.
The SUMIF function will look for a certain criteria and if it finds it, then it will Sum up related cells.
Here we have a table of salesmen with forecasts and month of their forecast close. On the right we listed the months and we want to sum up the Forecasts for each month. So go to the first cell and insert the SUMIF function
Range – This is group of cells that we want to look in. In this example, we want to find the months in this column
Criteria – This is the cell we are looking for. This example we use cell I4, which is the month in the right hand table. In this example, it’s January.
Sum range – This is the column we need to look in to pull in the dollar amount. In this example its the Forecast column, which is column D.
Then after you copy it down, you will get the results like above. Then this will change as the values in the original table change.
The COUNTIF function works the same way as the SUMIF, however it just counts the fields that match a certain criteria, instead of summing them. See the following example.
We will use our same table as last time. We just added a column to the table on the right saying “Count by Month”
We will go to cell K4 and insert the COUNTIF function.
Range – this is the range of data that we want to count. We will check for a certain criteria here.
Criteria – This is what we are looking for in the Range. You can enter something like “June” in there. However we want to make it a reference so we can copy it down, so we will enter I4, which is “January”
We don;’t need anything else since we are only counting the cells and not adding up anything.
So once we do this, it will find 4 instances of January. You can double check by counting them yourself. Then just copy this formula down and you will get the following table.
The AND function is a logical function that checks multiple criteria and will return a TRUE value if ALL of the criteria are TRUE. Otherwise it returns a false.
This type of statement usually gets linked inside an IF statement. Where the IF statement will perform certain actions depending on if the AND statement is True or False.
Let’s look at this following table.
We have this table which lists hours slept, Man/Woman and last years income.
Let’s say we want to find out in each person is a Man AND if their income is over 100k. Since we are requiring two fields to be True, then we need to use AND.
So let’s fill in this wizard. you can fill out many criteria fields. The first one is to ask if this is a man or woman.
So we want c3=”man”. We are checking the man/woman column and returning a True if they are a man. In this instance, C3 says woman. So you can see in the wizard that it says FALSE.
Next, we want to see if income is over 100k. So we enter D3>100000. Column D is the annual income field so we check if it is over 100k. In this instance, it is not, so it returns a False.
Then overall, we need all criteria to be True in order for the AND statement to be true. Since it is not, it is FALSE.
Once we do this, copy it to all the rows.
Above are the final results. I hilighted the True results. you can see that column C has Man and column D has income over 100k.
The OR function works similar to the AND statement. It checks multiple criteria however it only requires ONE statement to be true to make the whole statement TRUE.
So let’s look at the same example.
We are using the same data, but now we are looking for EITHER 1. They sleep over 8 hours OR Their income is under 50k
Since we only are look for one of them to be true, we can use the OR function.
Criteria 1 is B3>8 – here we are checking if the hours of sleep is over 8. In this case it is 8, so the result is False. It isn’t OVER 8.
Criteria 2 D3 < 50000 – Since income is 10000, it’s result is TRUE. And since only one statement needs to be TRUE, then the full statement is TRUE.
See the final results now and check for yourself.
The Left, right and concatenate functions are great Text functions for manipulating data in cells. You can take any numbers or words in a cell and pull in the left or right characters from them and put them in a new cell.
Conversely, you can combine cells into a new cell.
Let’s start by looking at the Left function. Say we have the following:
We have people’s phone numbers in a list. Now say we want to have a column with just the area code. We can use the left function on the phone number and pull in the 3 leftmost characters.
Insert the function in cell C3.
Text – This is the cell we are looking at and pulling the info from. In this case it is the first phone number, cell B3
Num_chars – how many characters from the text do we want to pull from the LEFT side.
Then copy the cell down for it to get the area code on all the phone numbers:
The RIGHT function works the same as the left, but just pulls from the right side. Say we want to pull in the last 4 digits on the phone number. We can use the Right function.
The Text is the cell we are pulling the information from. And the number of characters is the characters from the right side. Once you copy it down, you will see the following:
CONCATENATE – This function will combine multiple cells or words into one cell. Let’s say we now want to combine the area code with the last 4 digits into one cell and put the word “phone” in front of it. This can be done with this function.
You just put either the word or cell reference in each text. For example, the start of the phrase is the word “phone” . Since we want the same word, we can just type in that exact word.
Then for the next two, we want to just put a reference in there so we can copy it down. So just put C3 into Text2 and D3 into Text3.
Then this will give you the following after copying and pasting it down.
The round function is used to take an amount that has many decimals and round it to the number of decimals that you want. It is better than formatting the cell because if you formal the cell 10.3678 to decimals, it will show 10.37 however the actual number in the formula will still be 10.3678. However if you use the Round function, it will turn the number into 10.37.
For example, we have the following spreadsheet:
We need to divide labor into Total expenses in column D. If we just use the formula B2/C2, we will get a long number after the decimal.
Now we can format the cell to 2 decimals and it will display it correctly. However if you are doing further calculations using that amount, it could throw off those numbers.
So instead, we want to use the Round function instead. It looks like this:
Here we used the Round function to put the original formula in the Number field and the the Num_digits is the number of digits after the decimal place you want to show.
So we changed the number in D2 to .21.
The proper function is a text function to capitalize each word. This will also make the rest of the letters small.
For example, if we have the following:
We want to make each of these phases into a proper looking style, which would be to have the first letter of each word capitalized and all the rest of the word small.
So we insert the Proper function:
Then when we copy it down, we will get:
The now function is very easy. It is a simple function that will just tell you exactly what time and day it is. The you can format it as a date to show the date and time or just the date.
You can simple type Now() into a cell or insert the Now function:
It will give the following result:
Then you can format it as just a date if you like to get this:
So that is our list of the top 15 functions that every Excel user should know. They can save you a lot of time in performing tasks and make you more efficient. There are many many more functions besides these that can also be helpful ,so I wouldn’t stop here. However knowing these 15 can be enough for the basic Excel user to start with and to be effective in Excel!