Previously we finished with Name Range, if you have any questions about that, do not hesitate to ask in the comments. In this tutorial, we will start with Excel functions starting from the very simple and moving towards a complicated one. Learning even the simple functions makes your work with Excel so easy.
To practice Excel, you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
What is an Excel Function?
A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells.
Functions are used in a formula to do the job asked within using cell references.
Format of writing a Formula in Excel
For writing a formula a general format is used for all the functions. The result of the formula will be displayed within the cell containing the formula.
A formula will always start with an “=” sign, followed by the “Function”, then the start parenthesis “(“. There should be no space within the format of the formula. When inputting a function, Excel will not realize it until you put an open parenthesis. After the parenthesis, the Excel will be ready to accept the conditions of that particular function. A function's conditions will always end with a closing parenthesis “)”.
Please remember the following when writing a formula:
There should be no space within a formula.
Parenthesis is used to tell Excel when the condition of a particular function begins and ends.
Commas are used to separate one condition from the other within the parenthesis.
Any text, character, or space must always be put within quotes “” called strings.
Numbers can be written without quotes and will be taken as numbers.
A function can be written within the other function, but the format must be taken care of.
The Help provided by Excel to write a formula
Formula writing is easy if you follow the rules of the format. Each function has its format to follow. Excel will immediately provide help for writing a formula.
Try to put an equal sign in any cell and when you start writing the first letter of a particular function Excel will provide help for that function.
Shortcuts for most common functions
Under the Formula Tab, you can find the most useful and common functions, which you can use. With practice, you will remember many of the functions and will be just typing them directly within a cell.
Starting with our first function ever – “IF”
As the name is suggesting, “IF” is used to check whether the condition is met within a particular referenced cell, and what to do when the condition is “True”, and if “False” what to do.
The format of IF can be written as below:
=IF(Reference_Cell_Condition, Do this if True, Do this if False)
For example, we want to see if in cell C2 the values are over 100. So, we will write the IF formula for this condition as follows:
=IF(C2>100,”Over 100”,”100 or less”)
The first thing you must have noticed is that there is no space anywhere within the formula, except for text within the quotes, i.e., string. You can put anything with the quotes as per your liking to tell you whether the condition is True or False.
Secondly, Excel will check cell C2, if the value within the cell is more than 100, it means the condition is True. So, it will take the first option, i.e., Over 100, and will show the result in the cell where you have put the formula. If the number in C2 is 100 or less, it means the condition is False, so, it will display the second option, i.e., “100 or less”.
When we will press enter, we will see the result. As in C2, the number is 30, i.e., not more than 100, so the condition is false and hence option 2 was picked by the formula.
If we change the condition, for example, to more than 100, we will see that now the condition in C2 is true for the formula and it will display option 1.
I hope you are getting the idea with the IF function. A tip for writing this and any other function is that when you put an equal sign and input “i", Excel will give you all the functions starting with this letter. Just click on IF and press TAB. Excel will automatically put the complete function with an open parenthesis. This will increase the speed of how you input the formula.
How to Edit a formula
If you click on the cell containing the formula, you will see that the formula bar is now displaying the formula.
You can directly click in the formula bar and edit it there, or there is another way. When you are on the cell containing the formula, press the F2 key and the formula will appear. Edit it, when done, just press Enter key or Tab key.
If you input the wrong format for the function, Excel will show an error. Help will be provided as to what mistake are you making. Just click on it and get help.
Want to see which cells have got the Formulas in your data
If you want to see which of the cells contain formulas and are involved in calculating rather than showing an input value, it is simple.
You can easily view all the formulas and edit them if you want. When done, do not forget to click on “Show Formulas” again to disengage it to revert to your calculations.
Caught up in an error and don’t know what to do
Sometimes, it happens that the error you will be getting will not be in the format of the formula, but the results. Your formula is not picking the conditions which you want it to be and clearly, some other calculation or condition should be displayed and you are not satisfied with the results.
There is an option called “Evaluate Formula”, which can be used to check each condition of the formula. It will show step by step as the formula will check for a condition, do what when the condition is True, do what when the condition is False. By checking step by step in a formula, you will be able to catch the error. As you will be learning more about formulas and functions, more and more complex formulas you will use and invent on your own, catching an error in such instances will be very difficult, so Evaluate Formula will help.
Click on the cell whose formula you want to evaluate, then click on Evaluate Formula:
You will see that as you start evaluating, Excel will underline the portion of the formula to show you what it is saying, then give you what it has seen, then if you go on pressing Evaluate, it will show whether the condition is met, and what Excel will do with it. Look at the following successive diagrams:
As this is a very simple formula, so within 3 steps the whole evaluation ended, but for more complex formulas you go on pressing Evaluate, till you get where the mistake is and how to correct it.
Let’s see IF in a more complicated scenario
Look at this data of students:
Under the Remarks column, we want to put the IF function, so as to check that if some students have more than 80% marks, we want to show “Excellent” remarks in front of the student.
The formula will be written as below:
As the percentage is in column K, and we have chosen from the first student, therefore, used K2.
The above formula can be read in simple English as:
“Check Cell K2, if the percentage is more or equal (see both signs are used) to 80%, put “Excellent”, otherwise, show Below 80%”.
After entering this formula, we will drag this formula downwards using our mouse for all the student and see the result:
You can see the arrows pointing towards the result of the formula according to the values of column K. Dragging them down will automatically change the reference from K2, to K3, K4, K5…..and so on, as we are using relative reference without a “$” sign.
Going for more than one IF function – The Nested IF
Now, suppose, we also want that the students who have got more than 70, but less than 80 should get a remark “Very good”. We will use another IF function within our previous IF function. This type of IF function is called nested IF functions, meaning one IF within the other.
Let’s first see how to write such a nested IF formula:
=IF(K2>=80%,”Excellent”,IF(>=70%,”Very Good”,”Below 70%”))
For a nested IF function or for any other nested functions, you don’t end the function, rather put other IF where the first condition is getting false. You will end both of the functions together at the end by putting 2 close parentheses. Another thing to note is that Excel will always check the first condition foremost, only when this condition is false that it will go to the second IF. If the first condition will become True, it will never even read the second IF. You must keep this in mind to place your conditions strategically.
The above formula will be read like this:
“Check K2, if the percentage is equal or more than 80%, show “Excellent”, if the condition is not true, go and check K2 again. If it is equal or more than 70%, show “Very good”, if False, show “Below 70%”.”
As you can see, there are no percentages greater or equal 70%, therefore, we cannot see Very Good.
Let’s go on further by nesting more IF in the formula:
=IF(K2>=80%,”Excellent”,IF(>=70%,”Very Good”,IF(K2>=60%,”Good”,IF(K2>-50%,”Fair”,”Below 50%”))))
You can see that there are 4 nested IF functions use. I hope you are not overlooking the 4 close parentheses at the end for each IF function. They are required. If even one is missing, Excel will show an error.
Summary of IF Function
According to Microsoft:
The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.
So, an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR to extend the logical test.
In the next tutorial, we will combine AND & OR Functions with IF.
Any Questions will be welcomed.