Spreadsheets (like Microsoft Excel, Google Sheets, and LibreOffice Calc) use
formulas to perform calculations, manipulate data, and automate tasks.
While the specific names and nuances of some functions might vary slightly
between different spreadsheet software, the core principles and most
common formulas are universal.
Fundamental Principles of Spreadsheet Formulas:
* Start with an equals sign (=): Every formula in a spreadsheet must begin
with an equals sign. This tells the spreadsheet that you are entering a
calculation, not just text.
* References: Formulas often refer to the values in other cells.
* Single cell: A1 refers to the value in cell A1.
* Range: A1:A10 refers to all cells from A1 to A10.
* Multiple cells/ranges: A1,B5,C1:C3 refers to cells A1, B5, and the range
C1 to C3.
* Named ranges: You can define a name for a range of cells (e.g.,
SalesData) and use that name in formulas, making them more readable.
* Other sheets: Sheet2!A1 refers to cell A1 on "Sheet2".
* Operators: Standard mathematical operators are used:
* + (Addition)
* - (Subtraction)
* * (Multiplication)
* / (Division)
* ^ (Exponentiation)
* & (Concatenation - joins text strings)
* = (Equality)
* > (Greater than)
* < (Less than)
* >= (Greater than or equal to)
* <= (Less than or equal to)
* <> (Not equal to)
* Functions: Pre-built formulas that perform specific calculations. They have
a name and often require "arguments" (inputs) within parentheses.
* Syntax: =FUNCTION_NAME(argument1, argument2, ...)
* Arguments can be numbers, cell references, ranges, text strings, logical
values (TRUE/FALSE), or even other functions (nested functions).
Common Categories of Spreadsheet Formulas/Functions:
Here's a comprehensive list of common and important formulas, categorized
for easier understanding:
1. Basic Arithmetic & Aggregation:
* =SUM(range): Adds all numbers in a specified range.
* Example: =SUM(A1:A10)
* =AVERAGE(range): Calculates the average (mean) of numbers in a range.
* Example: =AVERAGE(B2:B10)
* =COUNT(range): Counts the number of cells in a range that contain
numbers.
* Example: =COUNT(C:C) (counts numbers in column C)
* =COUNTA(range): Counts the number of non-empty cells in a range
(numbers, text, errors, etc.).
* Example: =COUNTA(A1:A50)
* =COUNTBLANK(range): Counts the number of empty cells in a range.
* Example: =COUNTBLANK(D1:D100)
* =MAX(range): Returns the largest value in a range.
* Example: =MAX(Sales)
* =MIN(range): Returns the smallest value in a range.
* Example: =MIN(Prices)
* =PRODUCT(range): Multiplies all the numbers in a range.
* Example: =PRODUCT(E1:E5)
2. Conditional & Logical Functions:
* =IF(logical_test, value_if_true, value_if_false): Performs a logical test and
returns one value if TRUE, another if FALSE.
* Example: =IF(A1>100, "High", "Low")
* =AND(logical1, logical2, ...): Returns TRUE if all arguments are TRUE.
* Example: =IF(AND(A1>10, B1<20), "Valid", "Invalid")
* =OR(logical1, logical2, ...): Returns TRUE if any argument is TRUE.
* Example: =IF(OR(C1="Yes", D1="Approved"), "Action Required", "No
Action")
* =NOT(logical): Reverses the logical value of its argument.
* Example: =IF(NOT(ISBLANK(E1)), "Data Exists", "Blank")
* =IFERROR(value, value_if_error): Returns value if it doesn't result in an
error; otherwise, returns value_if_error.
* Example: =IFERROR(A1/B1, "Error in division")
* =COUNTIF(range, criteria): Counts cells within a range that meet a single
specified criterion.
* Example: =COUNTIF(F:F, "Complete")
* =COUNTIFS(range1, criteria1, range2, criteria2, ...): Counts cells within
multiple ranges that meet multiple criteria.
* Example: =COUNTIFS(G:G, ">100", H:H, "East")
* =SUMIF(range, criteria, [sum_range]): Sums values in a range that meet a
single criterion. sum_range is optional; if omitted, range is summed.
* Example: =SUMIF(Category, "Food", Amount)
* =SUMIFS(sum_range, criteria_range1, criteria1, ...): Sums values in a
range that meet multiple criteria.
* Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
* =AVERAGEIF(range, criteria, [average_range]): Calculates the average of
values in a range that meet a single criterion.
* =AVERAGEIFS(average_range, criteria_range1, criteria1, ...): Calculates the
average of values in a range that meet multiple criteria.
3. Lookup & Reference Functions:
* =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]):
Looks for a value in the first column of a table and returns a value from the
same row in a specified column.
* range_lookup: TRUE for approximate match (default), FALSE for exact
match. Use FALSE for most data lookups.
* Example: =VLOOKUP(B2, ProductTable, 2, FALSE)
* =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]):
Similar to VLOOKUP, but looks for a value in the first row of a table.
* =INDEX(array, row_num, [column_num]): Returns the value of a cell at the
intersection of a specified row and column in a given range. Often used with
MATCH.
* Example: =INDEX(A1:C10, 5, 2) (returns value in B5)
* =MATCH(lookup_value, lookup_array, [match_type]): Returns the relative
position of an item in a range that matches a specified value.
* match_type: 0 for exact match, 1 for less than, -1 for greater than. Use 0
for most lookups.
* Example: =MATCH("Banana", A1:A10, 0) (returns the row number where
"Banana" is found)
* =OFFSET(reference, rows, cols, [height], [width]): Returns a reference to a
range that is a specified number of rows and columns from a starting
reference. Very powerful for dynamic ranges.
* =INDIRECT(ref_text, [a1]): Returns the reference specified by a text string.
* Example: =SUM(INDIRECT("A"&ROW())) (sums values in column A
starting from the current row)
* =CHOOSE(index_num, value1, [value2], ...): Chooses a value from a list of
values based on an index number.
* =HYPERLINK(link_location, [friendly_name]): Creates a shortcut or jump
that opens a document stored on a network server, an intranet, or the
Internet.
4. Text Functions:
* =CONCATENATE(text1, [text2], ...) or =TEXT1 & TEXT2 & ...: Joins multiple
text strings into one.
* Example: =CONCATENATE("Hello ", A1) or ="Hello " & A1
* =LEFT(text, [num_chars]): Returns the first num_chars from a text string.
* Example: =LEFT(B2, 3)
* =RIGHT(text, [num_chars]): Returns the last num_chars from a text string.
* =MID(text, start_num, num_chars): Returns a specific number of
characters from a text string, starting at a specified position.
* =LEN(text): Returns the number of characters in a text string.
* =UPPER(text): Converts text to uppercase.
* =LOWER(text): Converts text to lowercase.
* =PROPER(text): Capitalizes the first letter of each word in a text string.
* =TRIM(text): Removes extra spaces from text (removes leading/trailing
spaces and leaves only single spaces between words).
* =FIND(find_text, within_text, [start_num]): Returns the starting position of
one text string within another (case-sensitive).
* =SEARCH(find_text, within_text, [start_num]): Returns the starting position
of one text string within another (not case-sensitive).
* =SUBSTITUTE(text, old_text, new_text, [instance_num]): Replaces existing
text with new text in a string.
* =REPLACE(old_text, start_num, num_chars, new_text): Replaces part of a
text string with another text string.
* =TEXT(value, format_text): Converts a value to text with a specific number
format.
* Example: =TEXT(A1, "MM/DD/YYYY")
5. Date & Time Functions:
* =TODAY(): Returns the current date.
* =NOW(): Returns the current date and time.
* =DATE(year, month, day): Returns a date value.
* Example: =DATE(2025, 5, 20)
* =YEAR(date): Returns the year from a date.
* =MONTH(date): Returns the month from a date.
* =DAY(date): Returns the day from a date.
* =TIME(hour, minute, second): Returns a time value.
* =HOUR(time): Returns the hour from a time.
* =MINUTE(time): Returns the minute from a time.
* =SECOND(time): Returns the second from a time.
* =DATEDIF(start_date, end_date, unit): Calculates the difference between
two dates.
* unit: "Y" for years, "M" for months, "D" for days.
* Example: =DATEDIF(A1, B1, "Y")
* =WEEKDAY(date, [return_type]): Returns the day of the week for a date.
* =WORKDAY(start_date, days, [holidays]): Returns a date that is a specified
number of working days before or after the start date.
* =NETWORKDAYS(start_date, end_date, [holidays]): Returns the number of
whole working days between two dates.
6. Financial Functions (Common Examples):
* =PMT(rate, nper, pv, [fv], [type]): Calculates the payment for a loan based
on constant payments and a constant interest rate.
* =FV(rate, nper, pmt, [pv], [type]): Calculates the future value of an
investment.
* =PV(rate, nper, pmt, [fv], [type]): Calculates the present value of an
investment.
* =NPV(rate, value1, [value2], ...): Calculates the net present value of an
investment.
* =IRR(values, [guess]): Calculates the internal rate of return for a series of
cash flows.
7. Statistical Functions (Beyond Basic Aggregation):
* =STDEV.S(number1, [number2], ...): Estimates standard deviation based
on a sample.
* =STDEV.P(number1, [number2], ...): Calculates standard deviation based
on the entire population.
* =VAR.S(number1, [number2], ...): Estimates variance based on a sample.
* =VAR.P(number1, [number2], ...): Calculates variance based on the entire
population.
* =RANK(number, ref, [order]): Returns the rank of a number in a list of
numbers.
* =PERCENTILE.INC(array, k): Returns the k-th percentile of values in a
range, inclusive.
* =MODE.SNGL(number1, [number2], ...): Returns the most frequently
occurring value in a dataset.
* =MEDIAN(number1, [number2], ...): Returns the median (middle value) of
a set of numbers.
8. Mathematical & Trigonometric Functions:
* =ABS(number): Returns the absolute value of a number.
* =ROUND(number, num_digits): Rounds a number to a specified number of
digits.
* =ROUNDUP(number, num_digits): Rounds a number up, away from zero.
* =ROUNDDOWN(number, num_digits): Rounds a number down, toward
zero.
* =INT(number): Rounds a number down to the nearest integer.
* =TRUNC(number, [num_digits]): Truncates a number to an integer or to a
specified number of decimal places.
* =SQRT(number): Returns the positive square root of a number.
* =POWER(number, power) or number^power: Returns the result of a
number raised to a power.
* =MOD(number, divisor): Returns the remainder after a number is divided
by a divisor.
* =RAND(): Returns a random number between 0 and 1.
* =RANDBETWEEN(bottom, top): Returns a random integer between the
numbers you specify.
* =PI(): Returns the value of pi (3.14159265358979).
* =SIN(angle): Returns the sine of the given angle. (Angles are in radians)
* =COS(angle): Returns the cosine of the given angle.
* =TAN(angle): Returns the tangent of the given angle.
* =DEGREES(angle): Converts radians to degrees.
* =RADIANS(angle): Converts degrees to radians.
Advanced Concepts:
* Array Formulas (CSE Formulas in older Excel): Formulas that perform
multiple calculations on one or more sets of values and return either a single
result or multiple results. Often involve pressing Ctrl+Shift+Enter (in older
Excel) or implicitly handled in newer versions.
* Dynamic Array Functions (Microsoft 365/Google Sheets): Functions that
"spill" results into adjacent cells, making complex calculations easier.
Examples include FILTER, SORT, UNIQUE, XLOOKUP, SORTBY, SEQUENCE,
RANDARRAY.
* Lambda Functions (Microsoft 365/Google Sheets): Allows you to create
reusable custom functions directly within your spreadsheet, without needing
VBA.
* Error Handling: Using functions like IFERROR or ISERROR to manage
formula errors (#DIV/0!, #N/A, etc.).
This is not an exhaustive list of every single formula, as Excel alone has
hundreds of functions. However, it covers the vast majority of formulas you'll
encounter and use in everyday spreadsheet tasks and data analysis. The
best way to learn them is to practice!
Good luck to you All
