Essential Excel functions
Microsoft Excel can be touted as your best friend in data gathering. While Excel is undoubtedly very useful in its own right, it’s the user who needs to familiarize themselves with the features to maximize the program, as well as its capabilities. Boasting a diverse array of features and quirks that can be used in countless situations, here are just some Excel functions you might find handy:
1. VLOOKUP
Have you ever encountered an immense, seemingly complicated table on Excel with multiple rows and columns? What if you need to find one specific thing in that table? You wouldn’t have to go through the entire table to find what you’re looking for. Simply use the “VLOOKUP” function.
To use “VLOOKUP” simply type: “=VLOOKUP, lookup value, table array, column index number, FALSE”.
Lookup value is the cell number where you type the “search value”. For example, if you want to find the price of a product, lookup value is a value outside the table where you type the name of the product. Table array is simply the range of the entire table. Column index number is the column in which the value is located. So if price is under column 4, you should type in “4”. Lastly, type in “FALSE” so Excel will return an exact value to you.
2. SUM
Befittingly dubbed as one of the simplest yet most useful Excel formulas, the “SUM” function essentially gets the sum of numbers in a range of cells. You could compute for the sum by simply highlighting the range of cells with your mouse, and it will display the sum — but what if it’s a rather long table that will take time to highlight everything? If this is the case, use “SUM”. To enable the “SUM” function, simply type in “=SUM” for an entire range of cells.
3. IF
Let’s say you’re a teacher recording scores in Excel, and you want to see if your students passed or failed. You then make another column beside their scores to show the results. You could do this one by one by looking at their score, and manually typing “Passed or Failed”. If there are hundreds of students, this will take quite some time. In this case, simply use the “IF” function.
In order to activate this function, type: “=IF, logical statement, value if true, value if false”.
In this scenario of recording scores to check if they passed or failed, the logical statement should look somewhat like this: “cell number>=passing score”.
The logical statement should always be testable using true or false statements. An example for a logical statement in this scenario would be “C6>=70”. Next, type in the value if the logical statement is true or false. Using the same scenario, the value for a “true statement” should be “Passed” while the value for a false statement should be “Failed”. Once you finish this, simply use auto fill by dragging the “result” down to fill up the rest of the cells with either “Passed” or “Failed” depending on the score. The “IF” function is very flexible in the sense that it is used in countless situations and can also be used to create “Nesting IF Statements”.
4. CONCATENATE
“CONCATENATE”’s quirk is that it basically combines various texts in different cells. This may be particularly useful when generating usernames for different purposes, such as ID numbers. When using “CONCATENATE”, Excel will simply compile all the texts together. If you want to add a space, you’ll have to type in a “ “ in between the two cells when writing the formula.
To operate the “CONCATENATE” function, simply type in: “=CONCATENATE”, then type in the cells in which you want to merge texts. Don’t forget to type in “ “ if you want to add another space!
5. COUNTIF
The “COUNTIF” function, true to its name, counts the number of cells in a selected range which contains a specific text or number. Going back to the earlier example of a teacher tabulating scores, the teacher can use the “COUNTIF” function to see how many people passed or failed.
To use the “COUNTIF” function, type “=COUNTIF, range,”*specific text*”.
If the teacher wants to check how many passed or failed the test, he/she simply has to type “=COUNTIF, range of all the cells with either “PASSED” or “FAILED” and “*PASSED*” or “*FAILED*” depending what he/she is looking for.
The COUNTIF function can be of use when faced with a long column of data, and you have to find how many cells contain a specific text.
Whether you’re a computer whiz or an Excel rookie; you’ll find there are an infinite number of shortcuts, functions, and codes designed to make your life a little easier — it all just takes a bit of patience, luck, and perseverance. Happy coding!
Written by Luis Co and Josh Santos.
