📊 Excel/Google Sheets Formulas
Essential formulas for data analysis and automation
📈 Lookup & Reference Functions
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Searches for a value in the first column of a range and returns a value in the same row from a specified column.
Example:
=VLOOKUP(A2, Products!A:D, 3, FALSE)Finds product in A2 and returns price from column 3
XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Modern replacement for VLOOKUP. Searches any column and returns corresponding values from any column (Excel 365/Sheets).
Example:
=XLOOKUP(B2, Names, Salaries, "Not Found")Finds name in B2 and returns salary
INDEX + MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Powerful combination for flexible lookups. More versatile than VLOOKUP - can look left or right.
Example:
=INDEX(C:C, MATCH(A2, A:A, 0))Returns value from column C where A2 matches column A
OFFSET
=OFFSET(reference, rows, cols, [height], [width])
Returns a reference to a range that is offset from a starting cell by a specified number of rows and columns.
Example:
=OFFSET(A1, 5, 2, 3, 1)Returns 3 cells starting 5 rows down and 2 columns right from A1
🔢 Mathematical & Statistical Functions
SUMIF / SUMIFS
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Sums values based on one (SUMIF) or multiple (SUMIFS) conditions.
Example:
=SUMIFS(D:D, A:A, "North", B:B, ">1000")Sums sales for North region where value > 1000
COUNTIF / COUNTIFS
=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)
Counts cells that meet one or multiple criteria.
Example:
=COUNTIFS(A:A, "Complete", B:B, "High Priority")Counts rows with "Complete" status and "High Priority"
AVERAGEIF / AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Calculates average based on one or multiple conditions.
Example:
=AVERAGEIFS(D:D, A:A, "Product A", C:C, ">=2024")Average sales for Product A from 2024 onwards
ROUND / ROUNDUP / ROUNDDOWN
=ROUND(number, num_digits)
Rounds numbers to specified decimal places. ROUNDUP always rounds up, ROUNDDOWN always rounds down.
Example:
=ROUND(A1*1.15, 2)Multiplies by 1.15 and rounds to 2 decimals
📝 Text Functions
CONCATENATE / TEXTJOIN
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Joins multiple text strings with a specified delimiter. More powerful than CONCATENATE.
Example:
=TEXTJOIN(", ", TRUE, A2:A10)Joins cells A2 to A10 with commas, ignoring blanks
LEFT / RIGHT / MID
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
Extracts characters from text strings from the beginning, end, or middle.
Example:
=LEFT(A2, 5)Returns first 5 characters from A2
TRIM / CLEAN
=TRIM(text)
=CLEAN(text)
=CLEAN(text)
TRIM removes extra spaces. CLEAN removes non-printable characters.
Example:
=TRIM(A2)Removes leading, trailing, and extra spaces
UPPER / LOWER / PROPER
=UPPER(text) / =LOWER(text) / =PROPER(text)
Converts text to uppercase, lowercase, or proper case (first letter capitalized).
Example:
=PROPER(A2)Converts "john doe" to "John Doe"
📅 Date & Time Functions
TODAY / NOW
=TODAY()
=NOW()
=NOW()
TODAY returns current date. NOW returns current date and time. Both update automatically.
Example:
=TODAY()-A2Calculates days since date in A2
DATE / DATEDIF
=DATE(year, month, day)
=DATEDIF(start_date, end_date, unit)
=DATEDIF(start_date, end_date, unit)
DATE creates a date. DATEDIF calculates difference between dates in days, months, or years.
Example:
=DATEDIF(A2, TODAY(), "Y")Calculates age in years from birthdate
EOMONTH / EDATE
=EOMONTH(start_date, months)
=EDATE(start_date, months)
=EDATE(start_date, months)
EOMONTH returns last day of month. EDATE adds/subtracts months from a date.
Example:
=EOMONTH(TODAY(), 0)Returns last day of current month
WEEKDAY / WORKDAY
=WEEKDAY(serial_number, [return_type])
=WORKDAY(start_date, days, [holidays])
=WORKDAY(start_date, days, [holidays])
WEEKDAY returns day of week (1-7). WORKDAY calculates working days excluding weekends and holidays.
Example:
=WORKDAY(TODAY(), 10)Returns date 10 working days from today
🎯 Logical Functions
IF / IFS
=IF(logical_test, value_if_true, value_if_false)
=IFS(condition1, value1, condition2, value2, ...)
=IFS(condition1, value1, condition2, value2, ...)
IF tests a condition. IFS tests multiple conditions (no nesting needed).
Example:
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")Assigns letter grade based on score
AND / OR / NOT
=AND(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
AND requires all conditions to be TRUE. OR requires at least one TRUE. NOT reverses TRUE/FALSE.
Example:
=IF(AND(A2>100, B2="Active"), "Qualified", "Not Qualified")Checks multiple conditions
IFERROR / IFNA
=IFERROR(value, value_if_error)
=IFNA(value, value_if_na)
=IFNA(value, value_if_na)
Returns alternative value if formula results in error (IFERROR) or #N/A (IFNA).
Example:
=IFERROR(VLOOKUP(A2, Data!A:B, 2, 0), "Not Found")Shows "Not Found" if VLOOKUP fails
SWITCH
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Evaluates expression against multiple values and returns corresponding result. Cleaner than nested IFs.
Example:
=SWITCH(A2, "S", "Small", "M", "Medium", "L", "Large", "Unknown")Converts size codes to full names
📊 Advanced Functions
FILTER (Google Sheets)
=FILTER(range, condition1, [condition2], ...)
Returns filtered array based on specified conditions. Dynamic and powerful.
Example:
=FILTER(A2:D100, C2:C100>1000, B2:B100="Active")Returns rows where value>1000 and status is Active
UNIQUE / SORT
=UNIQUE(range, [by_col], [exactly_once])
=SORT(range, [sort_column], [is_ascending])
=SORT(range, [sort_column], [is_ascending])
UNIQUE extracts unique values. SORT sorts data dynamically.
Example:
=SORT(UNIQUE(A2:A100), 1, TRUE)Returns unique values sorted alphabetically
QUERY (Google Sheets)
=QUERY(data, query, [headers])
Uses SQL-like syntax to query data. Extremely powerful for data manipulation.
Example:
=QUERY(A1:D100, "SELECT A, SUM(D) WHERE B='North' GROUP BY A")SQL-style query for aggregation
ARRAYFORMULA (Google Sheets)
=ARRAYFORMULA(array_formula)
Applies formula to entire range automatically. No need to drag formulas down.
Example:
=ARRAYFORMULA(IF(A2:A<>"", A2:A*B2:B, ""))Multiplies two columns for all non-empty rows
🔄 Excel vs Google Sheets Key Differences
Excel Exclusive
=XLOOKUP() - Modern lookup
=FILTER() - Requires 365
=XMATCH() - Advanced matching
=LET() - Variable assignment
=LAMBDA() - Custom functions
Google Sheets Exclusive
=QUERY() - SQL-like queries
=ARRAYFORMULA() - Auto array
=IMPORTRANGE() - Cross-sheet
=GOOGLEFINANCE() - Stock data
=IMPORTHTML() - Web scraping
💡 Pro Tips for Formula Mastery
- Use Named Ranges: Instead of A1:A100, use "Sales_Data" for better readability and maintenance
- Absolute References: Use $ (e.g., $A$1) when you don't want references to change when copying
- F4 Shortcut: Press F4 to cycle through reference types (A1 → $A$1 → A$1 → $A1)
- Array Formulas: Ctrl+Shift+Enter in Excel to create array formulas for complex calculations
- Error Handling: Always wrap risky formulas (VLOOKUP, division) in IFERROR for cleaner sheets
- Combine Functions: Nest functions for powerful results (e.g., IFERROR + VLOOKUP + TRIM)
- Test Incrementally: Build complex formulas step by step, testing each component
- Document Formulas: Add comments or nearby notes explaining complex formula logic
- Use Helper Columns: Break complex formulas into multiple columns for easier debugging
- Keyboard Shortcut: Press F2 to edit formulas quickly and see cell references highlighted
