Formula, DAX, VBA & SQL Library
Search, preview visual examples, custom-generate cell ranges, and copy ready-to-use
100+ formulas (Basic to Advance).
Basic Sum (SUM)
Add all numeric values in a specified cell range instantly.
Conditional Sum (SUMIF)
Adds all numbers in a range that meet a specified single criterion.
Multi-Condition Sum (SUMIFS)
Adds all numbers in a range that meet multiple specified criteria.
Basic Count (COUNT)
Counts the number of cells in a range that contain numeric values.
Count Non-Blank Cells (COUNTA)
Counts the number of cells in a range that are not empty, including text, numbers, and errors.
Count Empty Cells (COUNTBLANK)
Counts the total number of blank or empty cells within a specified range.
Conditional Count (COUNTIF)
Counts the number of cells in a range that meet a specified single criterion.
Multi-Condition Count (COUNTIFS)
Counts the number of times specified criteria are met across multiple ranges.
Vertical Lookup (VLOOKUP)
Searches vertically down the first column of a table and retrieves data from a specified column in the same row.
Horizontal Lookup (HLOOKUP)
Searches horizontally across the top row of a table and retrieves data from a specified row in the same column.
Modern Lookup (XLOOKUP)
Searches a range or array for a match and returns a corresponding item from a second range in any direction.
Minimum Value (MIN)
Returns the smallest numerical value from a specified range of cells.
Maximum Value (MAX)
Returns the largest numerical value from a specified range of cells.
K-th Largest Value (LARGE)
Returns the k-th largest value in a data set (e.g., 1st largest, 2nd largest, etc.).
Basic Average (AVERAGE)
Calculates the arithmetic mean of all numeric values in a specified cell range.
Conditional Average (AVERAGEIF)
Calculates the average of cells in a range that meet a single specified criterion.
Multi-Condition Average (AVERAGEIFS)
Calculates the average of cells in a range that meet multiple specified criteria.
Extract Left Characters (LEFT)
Extracts a specified number of characters starting from the beginning (left) of a text string.
Extract Right Characters (RIGHT)
Extracts a specified number of characters starting from the end (right) of a text string.
Extract Middle Characters (MID)
Extracts a specific number of characters from a text string, starting at the position you specify.
Combine Text (CONCAT)
Combines text from multiple ranges or strings into a single text value.
Logical Condition (IF)
Checks whether a condition is met, returning one value if TRUE and another value if FALSE.
All Conditions True (AND)
Checks whether all arguments are TRUE, returning TRUE only if every condition is met.
Any Condition True (OR)
Checks whether any arguments are TRUE, returning TRUE if at least one condition is met.
Handle Errors (IFERROR)
Evaluates a formula and returns a custom fallback value if it results in an error.
Index Lookup (INDEX)
Returns the value of a cell at the intersection of a specific row and column within a given range.
Match Position (MATCH)
Searches for a specified item in a range of cells and returns the relative position of that item.
Two-Way Lookup (INDEX & MATCH)
Combines INDEX and MATCH to perform flexible lookups in any direction across rows and columns.
Current Date (TODAY)
Returns the current system date, updating automatically when the worksheet recalculates.
Current Date & Time (NOW)
Returns the current date and time formatted to your system settings, updating on calculation.
Construct Date (DATE)
Creates a valid Excel date serial number from individual year, month, and day components.
Date Difference (DATEDIF)
Calculates the number of days, months, or years between two dates.
Extract Unique Values (UNIQUE)
Returns a list of unique values from a list or range, automatically spilling into adjacent cells.
Filter Data Array (FILTER)
Filters a range of data based on criteria you define, returning matching records as a dynamic array.
Sort Data Array (SORT)
Sorts the contents of a range or array by a specified column index in ascending or descending order.
Search Text Position (SEARCH)
Finds the starting position of a text string within another text string (case-insensitive).
Join Text with Delimiter (TEXTJOIN)
Combines text from multiple ranges or strings with a specified delimiter, ignoring empty cells if desired.
Remove Extra Spaces (TRIM)
Removes all leading, trailing, and repeated spaces from text, leaving only single spaces between words.
Convert to Uppercase (UPPER)
Converts all letters in a specified text string to uppercase.
Convert to Lowercase (LOWER)
Converts all letters in a specified text string to lowercase.
Remove Non-Printable Characters (CLEAN)
Removes all non-printable characters (ASCII values 0 through 31) from a text string.
Capitalize Words (PROPER)
Capitalizes the first letter of each word in a text string and converts all other letters to lowercase.
Text Length (LEN)
Returns the total number of characters in a text string, including spaces and punctuation.
Replace Characters (REPLACE)
Replaces part of a text string with a different text string based on a starting position and character count.
Convert Text to Number (VALUE)
Converts a text string that represents a number into a true numerical value.
Format Number as Text (TEXT)
Converts a numeric value into text using a specified custom format pattern.
Substitute Text (SUBSTITUTE)
Replaces existing text with new text in a text string, with optional instance matching.
Find Exact Position (FIND)
Finds the starting position of one text string within another (case-sensitive).
Standard Rounding (ROUND)
Rounds a number to a specified number of decimal places or digits.
Round Upward (ROUNDUP)
Rounds a number upward, away from zero, to a specified number of decimal places.
Round Downward (ROUNDDOWN)
Rounds a number downward, toward zero, to a specified number of decimal places.
Multiple Conditions (IFS)
Evaluates multiple conditions in order and returns the value corresponding to the first TRUE condition.
Evaluate Expression (SWITCH)
Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
Calculate Future Workday (WORKDAY)
Returns the date before or after a specified number of workdays, automatically excluding weekends and optional holidays.
Count Working Days (NETWORKDAYS)
Returns the total number of whole working days between two dates, automatically excluding weekends and optional holidays.
End of Month Date (EOMONTH)
Returns the serial number of the last day of the month before or after a specified number of months.
Shift Date by Months (EDATE)
Returns the date that is the indicated number of months before or after a specified start date.
Date Difference (DATEDIF)
Calculates the number of days, months, or years between two specified dates.
Extract Year (YEAR)
Returns the four-digit year corresponding to a specified date value.
Extract Month (MONTH)
Returns the month of a date, represented as an integer ranging from 1 to 12.
Extract Day (DAY)
Returns the day of the month for a given date, represented as a number from 1 to 31.
Day of Week (WEEKDAY)
Returns an integer representing the day of the week for a given date (e.g., 1 for Sunday, 7 for Saturday).
Week Number of Year (WEEKNUM)
Returns the week number of a specific date in the year (1 through 54).
Check Numeric Value (ISNUMBER)
Checks whether a value is a number, returning TRUE if it is numeric and FALSE if not.
Check Text Value (ISTEXT)
Checks whether a value is text, returning TRUE if it contains text characters and FALSE otherwise.
Check Blank Cell (ISBLANK)
Checks whether a cell is completely empty, returning TRUE if blank and FALSE if it contains data.
Extract Text Only (T)
Returns the text referred to by a value, or an empty string if the value is not text.
Convert to Number (N)
Converts a value to a number (dates to serials, TRUE to 1, non-numeric to 0).
Round Down to Integer (INT)
Rounds a number down to the nearest whole integer.
Round to Next Even Integer (EVEN)
Rounds a positive number up and a negative number down to the nearest even integer.
Round to Next Odd Integer (ODD)
Rounds a positive number up and a negative number down to the nearest odd integer.
Logical TRUE Value (TRUE)
Returns the logical value TRUE directly in a formula.
Logical FALSE Value (FALSE)
Returns the logical value FALSE directly in a formula.
Format Number as Text (TEXT)
Converts a numeric value into text using a specified custom format pattern.
Check for Any Error (ISERROR)
Checks whether a value or formula returns any Excel error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
Check for Formula Presence (ISFORMULA)
Checks whether a cell reference contains a formula, returning TRUE if it does and FALSE if it contains a static value.
Calculate Loan Payment (PMT)
Calculates the periodic payment for a loan based on constant payments and a constant interest rate.
Present Value (PV)
Calculates the total current value of a series of future cash flows or annuity payments.
Future Value (FV)
Calculates the future value of an investment based on periodic, constant payments and interest rate.
Formula Details
Leave a reply