List of Essential Excel Formulas
There are hundreds of functions in Excel that can be very helpful in crunching numbers and transforming data into information. Here are three categories of critical functions that every savvy Excel user should know how to read and use.
See these formulas and functions in action by downloading: List of Excel Formulas.xlsx
It’s Only Logical
IF
Syntax: =IF(logical test,value if true,value if false)
How to read: If this is true, then this, otherwise this.
Uses: To create data where there isn’t any.
Example: if this year’s purchases are greater than last year’s, then we want to designate the status as “growth.” If this year’s purchase is less than last year, it should be designated as, “decline.” Our formula could look like this:
AND
Syntax: =AND(logical1,logical2,logicaln)
How to read: This is true AND this is true AND this is true
Uses: To test whether all conditions expressed in each logical are accurate.
Say we continue our example above. If we need for the number of purchases to exceed 20 and the total purchases to equal greater than $50,000, we could create a formula in column C, if we recorded the number of purchases in column B.
The result will show either TRUE or FALSE. We could then use this new value to create an IF statement showing a Y or blank for the Premium? Column.
OR
Syntax: =OR(logical1,logical2,logicaln)
How to read: This is true OR this is true OR this is true
Uses: To test whether any conditions expressed in each logical are accurate. If we reinterpret what “premium” means, for example either over 20 purchases or over $50,000 in purchases, it would look like this:
If any of the conditions are true, the cell value will be TRUE. With the AND function above, all of the conditions had to be true.
That Depends! Conditional Functions
SUMIF
Syntax: =SUMIF(range,criteria,sum range)
How to read: What range do you want to examine and for what criteria? When found, what do you want to sum?
Uses: To add up values in a particular range based on certain criteria.
Example: we have values from various locations. We only want to create a sum with those from Detroit. So our formula would look like this:
A2 through A14 is where we will find the value Detroit. The values we want to add are in B2 through B14. If we wanted to add up all values over $500,000, we could do that with a simpler SUMIF formula, because what we want to examine is the same thing we want to add. When that’s the case, you do not have to specify the sum range if it’s going to be the same as the range. That formula would look like this:
Do you notice the double quotes around the criteria? Unless your criteria is a single value or cell reference, it usually has to be enclosed in double quotes.
SUMIFS
Syntax: =SUMIFS(sum range,criteria range1,criteria1,criteria range2,critiera2,criteria range n,critieran)
How to read: Add up this range if each of these criteria ranges satisfy their corresponding criteria.
Uses: To perform a SUMIF with more than one set of criteria. For example, if we extend our example above to include both sets of criteria, our SUMIFS formula would look like this:
In this case, you do have to specify B2 through B14 to evaluate whether the values are greater than $500,000, even though it is also the sum range.
Cleaning up with Text Functions
TEXT
Syntax: =TEXT(value,format text)
How to read: Put this numeric value in this text format.
Uses: To address the problem with zero front-filled values, like some zip codes.
Example: the original zip code column is formatted with a Special Format called Zip Code. However, when you try to use this column in a mail merge, you’re likely to get the values in the Actual Value column, which would be missing the zeroes on the front of the last zip code. By applying the text function and specifying the format as “00000”, the mail merge will actually use the zero front-filled number.
Notice the double quotes. These are required in order for Excel to recognize the format.
TRIM
Syntax: =TRIM(text)
How to read: Remove all leading and trailing spaces from this value, leaving only single spaces between words.
Uses: When values in one list are supposed to match another, but don’t, it can be due to leading and/or trailing spaces. Using the TRIM function will help.
Note: To finish up, you may want to perform a Copy/Paste Values to permanently change the values in the cell.
Example: we use a formula that declares G2=F2. Some work and some don’t in column H. Though it might not seem obvious, stray spaces are causing FALSE declarations. However, when we attempt =I2=F2 the first column of names is shown at equal to the second (column J).
Think of this list as a starter kit for a strong foundation in Excel and continue to practice different scenarios and building logicals and conditionals in your Excel sheets.
