Excel functions reference

 

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

 

LEN(text)

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

 

TRIM(text)

Removes spaces from text except single spaces between words

 

LTRIM(text)

Removes leading spaces from a string

 

RTRIM(text)

Removes trailing spaces from a string

 

CONCATENATE(text1, [text2], …)

Links or joins text together

 

LEFT(text,[num_chars])

Returns characters from beginning of a string.

 

RIGHT(text,[num_chars])

Returns characters from end of a string.

 

MID(text, start_num, num_chars)

Returns specified no of characters from provided starting point

 

LOWER(text)

converts text to lowercase

 

UPPER(text)

converts text to lowercase

 

PROPER(text)

Capitalize the first letter in each word

 

VALUE(text)

converts text to numeric value

 

EXACT(text1, text2)

Compares two text strings

 

REPT(text, number_of_times)

Repeat text number of times specified.

 

MATH functions

 

SUM(number1, number2, …)

Add numbers

 

SUMIF(range, condition)

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

 

CEILING(number, multiple)

Round a number up to the nearest specified multiple

 

FLOOR(number, multiple)

Round a number up to the nearest specified multiple

 

ROUND(number, num_digits)

Round a number to a given number of digits

 

MROUND(number, multiple)
Round a number to the nearest specified multiple

 

ROUNDUP(number, num_digits)

Round a number up to a given number of digits

 

ROUNDDOWN(number, num_digits)

Round a number down to a given number of digits

 

TRUNC(number, [num_digits])

Truncate a number to a give precision

 

LCM(number1, number2, ..)

Finds Least common multiple of numbers.

 

GCD(number1, number2, ..)

Finds greatest common divisor of numbers.

 

FACT(number)

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(range1,range2, …)

Count numbers in a cell range.

 

COUNTA(range1,range2, …)
Count the number of non-blank cells

 

COUNTIF(range,criteria)

Count cells that match criteria

 

COUNTIFS(range1, criteria1, range2, criteria2, …)

Count cells matching multiple criteria

 

AVERAGE(number1, number2, …)

Average of numbers

 

AVERAGEA(value1, value2, …)

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

 

AND(logical1, [logical2], …)

Test multiple conditions with AND

 

OR(logical1, [logical2], …)

Test multiple conditions with OR

 

NOT(logical)

Reverse arguments or results

 

IFERROR(value, value_if_error)

Trap and handle errors

 

 

You may also like following articles on similar concepts :

 

Top 10 Excel functions

 

 

© 2015 – 2023, https:. All rights reserved. On republishing this post, you must provide link to original post

Leave a Reply.. code can be added in <code> </code> tags