4 Most Useful Excel Functions
Microsoft excel is a powerful tool used for data manipulation and organization. The mastery of its functions and formulas can help you exceed expectations and make you a Rockstar among your cubicle mates. However, new excel users may find the multitude of options overwhelming. If you are unfamiliar with excel, here are five of the most important functions a beginner can pick up and produce results quickly.
IF Statements
The If Statement formula is the ultimate validation tool within excel. The aim is to test a logical statement and return a corresponding statement if that value is true or different value if the statement false.
=IF(logicalTest,valueIfTrue,valueIfFalse)
To take this function to the next level try Nesting If statements. With the same beginning structure of an IF function the formula evaluates the Logical Test and provides a value if that statement is True. However, if that statement is false a new statement is evaluated. You can keep nesting IF Statements as long as it takes you to find a truthful result or otherwise. In some programming languages this is similar to an IF ELSE statement.
Here’s an example of an IF statement evaluating three consecutive conditions. Notice the last value returns the default value if all of the conditions are FALSE.
=IF(logicalTest,valueIfTrue, IF(logicalTest2,valueIfTrue, IF(logicalTest3,valueifTrue, valueIfFalse)
VLOOKUP
My nomination for Microsoft Excel’s Most Valuable Function would have to go to VLOOKUP. The VLOOKUP looks at a cell value and tries to find a match in another specified range. Then it returns a corresponding value from a column specified by the user.I know that may sound confusing so let’s dive into a sample VLOOKUP and see how it operates.
=VLOOKUP(valueToLookup, rangeToSearch, ColumnToReturn,TrueOrFalse) .
ValueToLookup- It’s the value you are trying to find a match for. You can input a value, a reference or a string of text.
RangeToSearch- In excel known as the Table array, it’s a range of cells being referenced to search for that sneaky value above.
ColumnToReturn -In the column furthest left of the table that you specified in “RangeToSearch” Excel is going to track down the value and then look to this field to churn out a corresponding value.
TrueOrFalse- Pretty straightforward field this section of the formula accepts two values; one being True, the other, wait for it…False! True will return the closest matches and False will only return a value for an exact match(False is most accurate unless you are okay with fuzzy answers).
While VLOOKUP is a great formula and I still use it all the time, it isn’t without limitations. One is that It can only be used when the value being searched for is to the left the values being returned.
The combination formula IndexMatch provides a workaround in some cases.
LEFT, RIGHT, MID
The LEFT, RIGHT and MID functions are three ways to extract characters from a string in a cell. Each one allows you to extract from a different portion of the cell as broken down below:
LEFT(Cell Value, Number of Characters)- Beginning with leftmost character, returns number of characters specified.
RIGHT(Cell Value, Number of Characters)- Beginning with rightmost character, returns number of characters specified.
MID(Cell Value, Starting Point in String, Number of characters to return)- MID uses the same fundamental concept as LEFT and RIGHT but with a slightly different execution.MID Begins at point specified by user and returns the amount of characters determined in the formula.
CONCATENATE
The definition of concatenate is to chain or link together in a series and excel’s reluctance to abbreviate this command leaves little mystery as to the utility of this function. Actually excel does allow you to type CONCAT for short.
Use this function to join strings from two or more cells into one word.
CONCATENATE(Cell1, Cell2, Cell3 And so on); This function can also be used with a range of cells. If you want CONCATENATE to generate spaces or punctuation in your formatting, enter the space or punctuation in between as one of the items in the formula.
CONCATENATE(Cell1, “ “ , Cell2);
