MIN
Finds the lowest value in a range.
Syntax:
=MIN(number, number)
Example:
If you want to determine who has the lowest salary in a column:
=MIN(H2:H10)
MAX
Finds the highest value in a range.
Syntax:
=MAXnumber, number)
Example:
If you want to determine who has the lowest salary in a column:
=MIN(H2:H10)
IF
If the value is true, enter this output.
If the value is false, enter this output.
Syntax:
=IF(if_value_true, output, output_if_value_false)
Example:
Column D is the age of all employees.
If they’re older than 30 (if the value is true), then they’re Old.
If they’re not older than 30 (if the value is false), then they’re Young.
=IF(D2:D10 > 30, “Old”, “Young”)
IFS
You can have multiple conditions.
If no output, it means that nothing met the specified condition(s).
Syntax:
=IFS(Logical_test1, value_if_true, …)
Example:
Column F is job title.
If the value in column F is “Salesman”, then the output should be “Sales”.
If the value in column F is “HR”, then the output should be “Fire Immediately”
If the value is column F is “Regional Manager” then the output should be “Give Christmas Bonus”
=IFS(F2:F10 = “Salesman”, “Sales”, F2:F10 = “HR”, “Fire Immediately”, F2:F10 = “Regional Manager”, “Give Christmas Bonus”)
LEN
Use it to determine the length of cells.
Syntax:
=LEN(range/cell)
Example:
=LEN(C2:C10)
Use Cases:
Use it to identify bad SSN (e.g., too long or too short).
Use it to check phone numbers; not valid if too long or short.
LEFT
Like a substring (i.e., you can choose a certain part from a string) from the left.
Syntax:
=LEFT(range, how_many_characters_you_want)
Example:
Column C contains first names.
C2: Pamela
C3: Jimmy
C4: Angela
=LEFT(C2:C4, 3)
Output:
Pam
Jim
Ang
*Note:
You can’t use LEFT function on a cell formatted to date. You will get random numbers as it will convert the dates to “General” (i.e., numbers) before extraction. And general will change the date to random numbers.
Example:
=LEFT(H2:H10, 4)
11/2/2011 (date)>37197 (General)>7197 NOT 2011
Solution:
convert all date cells to text.
Syntax:
=TEXT(Value you want to format, “Format code you want to apply”)
=TEXT(H2:H10, “dd/mm/yy”)
Then after you get the correct date format, copy and paste those cells with the formula in a different column AND then you can extract the portion of the substring wanted in a different column.
Why do you need to copy and paste? Because it’s a formula.
RIGHT
Like a substring (i.e., you can choose a certain part from a string) from the right.
Syntax:
=RIGHT(range, how_many_characters_you_want)
Example 1:
Column A contains employee IDs.
C2: 1001
C3: 1002
C4: 1003
=LEFT(C2:C4, 1)
Output:
1
2
3
Example 2:
Extract year of each employee’s start date.
Column H, Start Date:
H2: 11/2/2011
H3: 10/3/1999
H4: 7/4/2000
=RIGHT(H2:H10, 4)
2011
1999
2000
Use cases:
Use it to extract the year, month, or day.
Use it to extract pertinent parts of employee IDs.
Etc.–any case where need a substring from the right.
*Note:
You can’t use RIGHT function on a cell formatted to date. You will get random numbers as it will convert the dates to “General” (i.e., numbers) before extraction. And general will change the date to random numbers.
Example:
=RIGHT(H2:H10, 2)
11/2/2011 (date)>37197 (General)>37 NOT 11
Solution:
convert all date cells to text.
Syntax:
=TEXT(Value you want to format, “Format code you want to apply”)
=TEXT(H2:H10, “dd/mm/yy”)
Then after you get the correct date format, copy and paste those cells with the formula in a different column AND then you can extract the portion of the substring wanted in a different column.
Why do you need to copy and paste? Because it’s a formula.
TEXT
Syntax:
=TEXT(Value you want to format, “Format code you want to apply”)
Use Case:
You can’t use LEFT or RIGHT function on a cell formatted to dates. You will get random numbers as it will convert the dates to “General” (i.e., numbers) before extraction. And general will change the date to random numbers.
Example:
=LEFT(H2:H10, 4)
11/2/2011 (date)>37197 (General)>7197 NOT 2011
Solution:
convert all date cells to text.
=TEXT(H2:H10, “dd/mm/yy”)
Then after you get the correct date format, copy and paste those cells with the formula in a different column AND then you can extract the portion of the substring wanted in a different column.
Why do you need to copy and paste? Because it’s a formula.
TRIM
Removes all spaces from a text string (including leading and trailing spaces) except for a single space between words (e.g., the space between “Jim” and “Halpert” would remain = “Jim Halpert”).
Syntax:
=TRIM(cell or range)
Example:
Column C has last names.
C2: Halpert
C3: Beasley
C4: Schrute
=TRIM(C2:C4)
C2: Halpert
C3: Beasley
C4: Schrute
CONCATENATE
Joins two or more text strings into one string.
Syntax:
=CONCATENATE(value1, value2)
Example:
Join first and last name columns (with a space between).
=CONCATENATE(B2, “ “, C2)
OR
=CONCATENATE(B2:B10, “ “, C2:C10)
Use Cases:
-Generate an email: =CONCATENATE(B11, “.”C11, “@gmail.com”)
in this example, column b contains first names and column c contains last names
SUBSTITUTE
Replaces existing text with new text in a text string.
Syntax:
SUBSTITUTE(text, old_text, new_text, [instance_num])
*instance is optional; that’s what [] means.
If you don’t include an instance, every value in the selected range will be changed.
Example 1, with no instance:
Convert all dates to mm/d/yyyy format. Currently some use hyphens.
Column H contains employee start dates.
=SUBSTITUTE(H2:H10, “-“, “/”)
Output: All dates between H2:H10 are converted to / format (e.g., 9/6/2015).
Example 2, with 1 instance:
Column H contains employee start dates.
Replace the “/” with “-“ in the first instance of the range H2:H10.
=SUBSTITUTE(H2:H10, “/”, “-“, 1)
Output:
9-6/12
10-3/1999
7-4/2000
etc.
Example 2, with 2 instances:
Column H contains employee start dates.
Replace the “/” with “-“ in the first instance of the range H2:H10.
=SUBSTITUTE(H2:H10, “/”, “-“, 2)
Output:
9/6-12
10/3-1999
7/4-2000
etc.
SUM
Adds cells or cell ranges.
Syntax:
SUM(number1, number2, …)
Example:
Column G is all salaries in the marketing dept.
=SUM(G2:G10)
output: $437,000
SUMIF
sum if a certain criteria is met.
Syntax:
SUMIF(range, criteria, [sum_range])
[sum_range] is optional.
If you use sum_range, the cells specified there will be added.
If sum_range is omitted, Excel adds the cells that are specified in the range argument.
Example, without sum_range:
Only add up the salaries at the company that are greater than $50,000.
Column G is salary
=SUMIF(G2:G10, “>50000”)
output: $128,000 (vs $437,000, which is the total for all salaries in the company).
Example with sum_range:
Sum of the commissions for property values over $160,000.
Column A is for property value.
Column B is for commission.
=SUMIF(A2:A5, “>160000”, B2:B5)
Output: $63,000
SUMIFS
Adds all of the arguments that meet multiple criteria.
Syntax:
=SUMIFS(sum_range, critera_range1, criteria1, …)
-sum_range: the area that will be added after the specified criteria has been met
-criteria_range1: what range is being reviewed?
-critera1: what criteria is being reviewed in the specified range
Example:
Sum the salaries of employees who are female and older than 30.
Col G: Salary of employees
Col E: Gender of employees
Col D: Age of employees
=SUMIFS(G2:G10, E2:E10, “Female”, D2:D10, “>30”)
Output: $88,000
COUNT
The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments.
Notes:
-Arguments that are numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as “1”) are counted.
-Logical values and text representations of numbers that you type directly into the list of arguments are counted.
-Arguments that are error values or text that cannot be translated into numbers are not counted.
-If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.
Syntax:
=COUNT(value1, [value2], …)
Example:
A1:A20: =COUNT(A1:A20).
In this example, if five of the cells in the range contain numbers, the result is 5.
COUNTIF
Counts the number of cells that meet a specific criteria.
Syntax:
=COUNTIF(range, criteria)
-range: where do you want to look?
-criteria: what do you want to look for?
Example:
=COUNTIF(A2:A5, “London”)
If 2 of the cells have “London” in the specified range, the output is 2.
COUNTIFS
Counts the number of times all criteria are met. Multiple criteria.
Syntax:
=COUNTIFS(criteria_range1, criteria1, …)
Example 1:
Count how many times Davidoski exceeded a sales quote for Q1, Q2, and Q3.
Col B: Exceeded Q1 quota (Yes/No)
Col C: Exceeded Q2 quota (Yes/No)
Col D: Exceeded Q3 quota (Yes/No)
=COUNTIFS(B2:D2, “=Yes”)
Result: 1
Example 2:
Count how many salespeople exceeded both their Q1 and Q2 quotas.
Col B: Exceeded Q1 quota (Yes/No)
Col C: Exceeded Q2 quota (Yes/No)
=COUNTIFS(B2:B5, “=Yes”, C2:C5, “=Yes”)
Result: 2
COUNTA
Counts the number of cells that are not empty in a range.
Syntax:
COUNTA(value1, [value2], …)
Example:
Count the number of non-blank cells in cells A2 through A6.
=COUNTA(A2:A6)
result: 5
DAYS
Returns the number of days between two dates.
Syntax:
DAYS(end_date, start_date)
Example:
Col C: Start Date
Col D: End Date
C2: 15-MAR-2021
D2: 1-FEB-2021
=DAYS(C2:D2)
Result: 42
NETWORKDAYS
returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified as holidays.
Use Case:
Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Syntax:
NETWORKDAYS(start_date, end_date, [holidays])
*Holidays: Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.
Dataset for Examples:
Date Description
10/1/2012 Start Date of the Project
3/1/2012 End Date of the Project
11/22/2012 Holiday
12/4/2012 Holiday
1/21/2013 Holiday
Example 1:
Number of work days between the start (10/1/2012) and end date (3/1/2013)
=NETWORKDAYS(A2,A3)
Example 2:
Number of workdays between the start (10/1/2012) and end date (3/1/2013) with the three holidays as non-working days.
=NETWORKDAYS(A2, A3, A4:A6)
XLOOKUP
Finds things in a table or range by row.
Look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.
Use cases:
-Look up the price of an automotive part by the part number
-Find an employee name based on their employee ID
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
-look-up value: the value to search for
-lookup_array: the array or range to search
-return_array: the array or range to return
-[if_not_found]: Where a valid match is not found, return the [if_not_found] text you supply
-[match_mode]: specify the match type.
0 Exact match. If none found, return #N/A. This is the default.
-1 Exact match. If none found, return the next smaller item.
1 Exact match. If none found, return the next larger item.
2 A wildcard match where *, ?, and ~ have special meaning.
-[search_mode]: specify the search mode to use:
1 Perform a search starting at the first item. This is the default.
-1 Perform a reverse search starting at the last item.
2 Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Example 1:
Look up a country name in a range (B2:B11) and then return its telephone country code (D2:D11).
F2: Brazil
Col B: Country
Col D: Prefix/Telephone Country Code
=XLOOKUP(F2, B2:B11, D2:D11)
Result: +55
Example 2: Find the end_date and email for each person listed.
Col A: List of people (full names)
Col I: List of full names
Col O: End Dates
Col P: Emails
Enter the following formula in Col B (End_Date). (Col C is Email)
=XLOOKUP(A3,I2:I10, O2:P10)
Result:
B C D
1 Full_Name End_Date Email
2 Toby Flenderson 8/30/2017 toby.flenderson@dudnermifflin.com
Note: It will put the results in 2 different columns (i.e., the end_date and email columns).
Example 3:
Look up employee information based on an employee ID number.
B2: 8389
Col B: Employee ID
Col C: Employee Name
Col D: Department
=XLOOKUP(B2, B5:B15, C5:D14)
Example 4a: Find email addresses for listed individuals.
=XLOOKUP(A5,H2:H11,O2:O11, “Not found”)
result:
A5: Not Found
vs.
Example 4b: Find email addresses for listed individuals.
“*“&: anything that appears before A5 value (Beasley) is fine
A5: Beasley
Col H: Full Name
Col O: Email
2: Wildcard character match (i.e., Excel should use special characters like * or ? to find or match text that contains a specified pattern, rather than an exact text string.
=XLOOKUP(“*“&A5,H2:H11,O2:O11, “Not found”, 2)
result:
A5: pam.beasley@dundermifflin.com
Example 4c:
A6: Meredith
Col H: Full Name
Col O: Email
=XLOOKUP(A6&”*“,H3:H12,O3:O12, “Not found”,2 )
Result:
meredith.palmer@yahoo.com
Note:
Moved &”*” to after A6 because A6 is just the first name.
Result:
C2 (employee name): Dianne Pugh
D2 (department): Finance
Example 5:
Look up employee information based on an employee ID number. If ID is not found, enter “ID not found”.
B2: 1234
Col B: Employee ID
Col C: Employee Name
Col D: Department
=XLOOKUP(B2, B5:B15, C5:D14, “ID not found”)
Result:
C2 (employee name): ID not found
D2 (department):
XLOOKUP vs VLOOKUP
