This post provides a reference for the Excel functions and their formulas for an easy reference.
Click on the function name to explore details about the function and examples.
Note: Optional parameters are specified within square brackets.
TEXT functions
Counts character in a cell
REPLACE(old_text,start_num,num_chars_new_text)
Replace part of text based on position of characters
SUBSTITUTE(text, old_text,new_text, [instance_num])
Replace text in a string with another string
FIND(find_text, within_text, [start_num])
Case sensitive search to find starting position of a substring in a string
SEARCH(find_text, within_text,start_num)
Case insensitive search to find starting position of a substring in a string
Removes spaces from text except single spaces between words
Removes leading spaces from a string
Removes trailing spaces from a string
CONCATENATE(text1, [text2], …)
Links or joins text together
Returns characters from beginning of a string.
Returns characters from end of a string.
MID(text, start_num, num_chars)
Returns specified no of characters from provided starting point
converts text to lowercase
converts text to lowercase
Capitalize the first letter in each word
converts text to numeric value
Compares two text strings
Repeat text number of times specified.
MATH functions
Add numbers
Add numbers in range that are matching provided condition.
SUMIFS(sum_range, range1, condition1, [range2], [condition2], …)
Sum cells that match multiple criteria
SUMPRODUCT(array1,[array2], …)
Multiply and then Add arrays
Round a number up to the nearest specified multiple
Round a number up to the nearest specified multiple
Round a number to a given number of digits
MROUND(number, multiple)
Round a number to the nearest specified multiple
Round a number up to a given number of digits
Round a number down to a given number of digits
Truncate a number to a give precision
Finds Least common multiple of numbers.
Finds greatest common divisor of numbers.
Finds factorial of a number
LOOKUP functions
VLOOKUP(value,table_array,index_num,[range_lookup])
Lookup a value in a table by matching on the first column
MATCH(lookup_value, lookup_array, [match_type])
Finds match for a value in a range of cells
HLOOKUP(value, table_array, row_index, [range_lookup])
Look up a value in a table by matching on the first row
LOOKUP(lookup_value, lookup_vector, [result_vector])
Look up a value in a one-column range
Statistical functions
Count numbers in a cell range.
COUNTA(range1,range2, …)
Count the number of non-blank cells
Count cells that match criteria
COUNTIFS(range1, criteria1, range2, criteria2, …)
Count cells matching multiple criteria
Average of numbers
Average of numbers or text
AVERAGEIF(range, criteria, [average_range])
Average of numbers matching criteria
AVERAGEIFS(average_range, range1, criteria1, range2, criteria2, …)
Average cells that match multiple criteria
Logical functions
IF(condition, [value_if_true], [value_if_false])
Tests for a specific condition
Test multiple conditions with AND
Test multiple conditions with OR
Reverse arguments or results
IFERROR(value, value_if_error)
Trap and handle errors
You may also like following articles on similar concepts :
© 2015 – 2023, https:. All rights reserved. On republishing this post, you must provide link to original post