Excel Function Library, lists and explains all Microsoft Excel functions categorically with easy examples for free.
AMORDEGRC Function returns the depreciation of an asset for each accounting period.
AMORLINC function calculates depreciation of an asset, on a prorated basis, for a specified accounting period.
ACCRINTM function returns the accrued interest of a security (usually a bond) that pays interest at maturity.
ACCRINT function calculates the accrued interest for a security that pays interest on a periodic basis.
COUPDAYBS function calculates the number of days from the start of a coupon period to the settlement date of the security
COUPDAYS function returns the number of days in the coupon period that contains settlement date.
COUPDAYSNC function calculates the number of days from the settlement date to the next coupon date.
COUPNCD function is used to find out the first coupon payment date after the settlement date of a security/bond.
COUPNUM function calculates the total number of coupon payments to be made from the settlement date to the maturity date of the security.
COUPPCD function provides the most recent coupon payment date before the settlement date of the security.
CUMIPMT function calculates the cumulative interest paid between the start date and the end date.
CUMPRINC function calculates the cumulative principal paid on a loan between the specified period.
DB function calculates depreciation of an asset by using fixed declining balance method over the useful life of the asset.
DDB function calculates depreciation of an asset by using the Double Declining (DDB) Method or another method specified by the user.
DISC function calculates the discount rate for a security.
DOLLARDE function converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.
DURATION function calculates the Macauley duration of a security that has a par value of $100 and pays interest on a periodic basis.
EFFECT function calculates an effective annual interest rate for a loan or an investment.
FVSCHEDULE function returns the future value of an investment (an initial principal) after applying series of compound interest rates.
INTRATE function calculates the interest rate for a fully invested security.
ISPMT function calculates the amount of interest paid on a loan/ investment for a specific period.
IPMT function calculates the interest payment portion from a given periodic payment against an investment, based on periodic constant payments.
IRR (Internal Rate of Return) function calculates the internal rate of return for an investment having subsequent cash inflows occurring at regular intervals, such as monthly or annually.
NPV function calculates the net present value of an investment by using a discount rate and a series of future payments.
N function converts a value to a number.
PMT function is used to calculate the periodic payment for a loan.
PPMT function calculates the principal value of an investment for a given period.
RECEIVED function calculates the amount of return at maturity of fully invested security.
SMALL function (To find out k-th smallest values e.g.2nd smallest value)
SYD function returns the sum-of-years’ digits depreciation of an asset for a specific period.
AND function tests a number of logical conditions set by a user.
IF function checks whether a specified condition is met or not.
OR function tests multiple logical conditions at the same time.
For a brief description of the most common text functions, read the TEXT functions section.
BAHTTEXT function converts a number to Thai text.
CLEAN function is used to remove non-printable characters.
CONCATENATE function is used to join different text strings to a single string.
DOLLAR function converts a number value to text.
EXACT function is used to compare different text strings.
LARGE function returns with the largest numerical value in a list.
LOWER function returns with the lowerest numerical value from a list.
SEARCH function returns the location of a character within a string.
SUBSTITUTE function is used to replace specific text in a text string.
T function checks whether the supplied value is a text or not.
TEXT function converts a numeric value to a specified text format
TRIM function is used to remove irregular spaces between words which may appear while importing data from another application.
UNICHAR function returns a Unicode character against a specified number.
UNICODE function returns the Unicode number for the first character of a supplied text string.
PROPER function is used to capitalize each starting letter of a text string while ignoring numbers and punctuation.
REPT function repeats a text string, a given number of times.
UPPER case function converts text to uppercase.
Date & Time
HOUR function is used to extract an hour component between 0 (12:00 A.M.) to 23 (11:00 P.M.)
DAY function is used to extract a day number (1 to 31) from a date given.
DATE function is used to combine three different values (of date, month and year) to form a date.
MONTH function returns with a month number from 1 to 12.
TODAY function is useful when you need a current date in a worksheet and it remains updated whenever you open the worksheet.
Year function is used to extract 4-digit year number from a given Excel’s format date.
YEARFRAC function to get a fractional value representing fractional years between two dates.
TIMEVALUE function returns with an accurate text value in the decimal number of the time.
NOW function returns with the current date and time which is keep updated whenever you open the worksheet.
NETWORKDAYS function returns with a number of working days between two dates after excluding weekends and holidays.
Lookup & Reference
Math & Trig
ABS function is used to turn negative values into positive one.
ROUND function used to round a number to the decimal places.
SUMIFS function performs the SUM function with multiple criteria.
SUMIF function performs the SUM function with single criteria.
SUMPRODUCT function first multiplies corresponding arrays, then returns with the sum.
COUNT function calculates how many cells/numbers are present in a selected range of cells.
COUNTA function counts cells that are not empty.
COUNTBLANK function used to count the number of blank cells in a range of cells.
COUNTIF function is used to count cells that meet a criterion.
COUNTIFS function counts the number of cells based on single or multiple criteria.
MAX function returns with the larger of two values.
MIN function returns with the minimum value from a set of values.
ISNUMBER function checks whether a cell has a number or text value.
ISODD function checks whether a cell’s data has odd or even numbers.
ISERROR function checks whether a value is an error or not
ISLOGICAL function tests whether a value is a logical or not.
ISTEXT function tests whether a value is a text or not.
ISBLANK function tests whether a value is blank or not.
INFO function returns with information about the current operating environment.
CELL function returns with cell’s information regarding formatting, location, and contents etc.
ENCODEURL function converts URL to coded form.