SpreadsheetGear Logo SpreadsheetGear Features

The SpreadsheetGear family of products is made up of powerful and flexible Microsoft Excel compatible spreadsheet components and includes SpreadsheetGear 2023 for .NET Framework, SpreadsheetGear Engine for .NET and SpreadsheetGear for Windows.

SpreadsheetGear enables ASP.NET, ASP.NET Core, Windows Forms, WPF, iOS, Android, Linux, macOS and UWP developers to easily take advantage of scalable Excel Reporting, dynamic dashboards from Excel charts and ranges, powerful spreadsheet controls, comprehensive Excel compatible charting, the fastest and most complete Excel compatible calculations and more.

Which SpreadsheetGear product is right for you?

SpreadsheetGear 2023 for .NET Framework comes with assemblies built for .NET Framework 3.5 and .NET 4.6.2. SpreadsheetGear Engine for .NET requires a platform which supports .NET 6 or .NET Standard 2.0 or later. SpreadsheetGear for Windows requires .NET 6 for Windows or later. Although the feature set across products are very similar, there are some differences...

Feature Finder

Enter one or more keywords to filter down the list of features.
 

General Features

Calculation Engine

  • Fastest and most complete Excel compatible calculation engine available (significantly faster than Excel in many cases).
    Samples
    Related Samples:
  • Multi-threaded calculations efficiently utilize today's multi-core CPUs.
    multithreading multithreaded
  • Interruptible background calculation enables responsive applications.
  • 449 Excel compatible financial, date, time, text, lookup, math, trigonometry, statistical, engineering, database, information, logical and web functions (see list here).
    Samples
    Related Samples:
    DAVERAGE DCOUNT DCOUNTA DGET DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP DATE DATEDIF DATEVALUE DAY DAYS360 EDATE EOMONTH HOUR MINUTE MONTH NETWORKDAYS NETWORKDAYS.INTL NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM WORKDAY WORKDAY.INTL YEAR YEARFRAC BESSELI BESSELJ BESSELK BESSELY BIN2DEC BIN2HEX BIN2OCT COMPLEX CONVERT DEC2BIN DEC2HEX DEC2OCT DELTA ERF ERF.PRECISE ERFC ERFC.PRECISE GESTEP HEX2BIN HEX2DEC HEX2OCT IMABS IMAGINARY IMARGUMENT IMCONJUGATE IMCOS IMDIV IMEXP IMLN IMLOG10 IMLOG2 IMPOWER IMPRODUCT IMREAL IMSIN IMSQRT IMSUB IMSUM OCT2BIN OCT2DEC OCT2HEX ACCRINT ACCRINTM AMORDEGRC AMORLINC COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD CUMIPMT CUMPRINC DB DDB DISC DOLLARDE DOLLARFR DURATION EFFECT FV FVSCHEDULE INTRATE IPMT IRR ISPMT MDURATION MIRR NOMINAL NPER NPV ODDFPRICE ODDFYIELD ODDLPRICE ODDLYIELD PMT PPMT PRICE PRICEDISC PRICEMAT PV RATE RECEIVED SLN SYD TBILLEQ TBILLPRICE TBILLYIELD VDB XIRR XNPV YIELD YIELDDISC YIELDMAT CELL ERROR.TYPE INFO ISBLANK ISERR ISERROR ISEVEN ISLOGICAL ISNA ISNONTEXT ISNUMBER ISODD ISREF ISTEXT N NA TYPE AND FALSE IF IFERROR NOT OR TRUE ADDRESS AREAS CHOOSE COLUMN COLUMNS HLOOKUP HYPERLINK INDEX INDIRECT LOOKUP MATCH OFFSET ROW ROWS TRANSPOSE VLOOKUP ABS ACOS ACOSH ASIN ASINH ATAN ATAN2 ATANH CEILING CEILING.PRECISE COMBIN COS COSH DEGREES EVEN EXP FACT FACTDOUBLE FLOOR FLOOR.PRECISE GCD INT LCM LN LOG LOG10 MDETERM MINVERSE MMULT MOD MROUND MULTINOMIAL ODD PI POWER PRODUCT QUOTIENT RADIANS RAND RANDBETWEEN ROMAN ROUND ROUNDDOWN ROUNDUP SERIESSUM SIGN SIN SINH SQRT SQRTPI SUBTOTAL SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 TAN TANH TRUNC BETADIST BETAINV BINOMDIST CHIDIST CHIINV CHITEST CONFIDENCE COVAR EXPONDIST FDIST FINV FTEST GAMMADIST GAMMAINV LOGINV LOGNORMDIST MODE NEGBINOMDIST NORMDIST NORMINV NORMSDIST PERCENTILE PERCENTRANK POISSON QUARTILE RANK STDEV STDEVP TDIST TINV TTEST VAR VARP WEIBULL ZTEST AVEDEV AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS BETA.DIST BETA.INV BINOM.DIST BINOM.INV CHISQ.DIST CHISQ.DIST.RT CHISQ.INV CHISQ.INV.RT CHISQ.TEST CONFIDENCE.NORM CONFIDENCE.T CORREL COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS COVARIANCE.P COVARIANCE.S CRITBINOM DEVSQ EXPON.DIST F.DIST F.DIST.RT F.INV F.INV.RT F.TEST FISHER FISHERINV FORECAST FREQUENCY GAMMA.DIST GAMMA.INV GAMMALN GAMMALN.PRECISE GEOMEAN GROWTH HARMEAN HYPGEOM.DIST HYPGEOMDIST INTERCEPT KURT LARGE LINEST LOGEST LOGNORM.DIST LOGNORM.INV MAX MAXA MEDIAN MIN MINA MODE.MULT MODE.SNGL NEGBINOM.DIST NORM.DIST NORM.INV NORM.S.DIST NORM.S.INV NORMSINV PEARSON PERCENTILE.EXC PERCENTILE.INC PERCENTRANK.EXC PERCENTRANK.INC PERMUT POISSON.DIST PROB QUARTILE.EXC QUARTILE.INC RANK.AVG RANK.EQ RSQ SKEW SLOPE SMALL STANDARDIZE STDEV.P STDEV.S STDEVA STDEVPA STEYX T.DIST T.DIST.2T T.DIST.RT T.INV T.INV.2T T.TEST TREND TRIMMEAN VAR.P VAR.S VARA VARPA WIEBULL.DIST Z.TEST CHAR CLEAN CODE CONCATENATE DOLLAR EXACT FIND FINDB FIXED LEFT LEFTB LEN LENB LOWER MID MIDB PROPER REPLACE REPLACEB REPT RIGHT RIGHTB SEARCH SEARCHB SUBSTITUTE T TEXT TRIM UPPER USDOLLAR VALUE ACOT ACOTH AGGREGATE ARABIC BASE BINOM.DIST.RANGE BITAND BITLSHIFT BITOR BITRSHIFT BITXOR CEILING.MATH COMBINA COT COTH CSC CSCH DAYS DECIMAL ENCODEURL FILTERXML FLOOR.MATH GAMMA GAUSS IFNA IMCOSH IMCOT IMCSC IMCSCH IMSEC IMSECH IMSINH IMTAN ISFORMULA ISO.CEILING ISOWEEKNUM MUNIT NUMBERVALUE PDURATION PERMUTATIONA PHI RRI SEC SECH SHEET SHEETS SKEW.P UNICHAR UNICODE WEBSERVICE XOR
  • All types, operators, defined names, data tables and arrays.
    Samples
    Related Samples:
  • Support for 64K worksheets and unlimited workbooks in formulas.
  • Worksheet specific defined names and global defined names.
    Samples
    Related Samples:
  • Built-In Document Properties and Custom Document Properties.
    Samples
    Related Samples:
  • Custom functions, minimal recalc, iteration, precision as displayed and more.
    Samples
    Related Samples:
  • Copy formulas between cells, worksheets, workbooks and workbook sets with full support for reference fixups.
  • Insert and delete worksheets, rows, columns and cells with full support for reference fixups.
  • Cut, copy and paste with full support for smart reference fixups across multiple worksheets and workbooks.
  • Special care was taken with numerical stability.
    rounding precision accuracy floating point arithmetic

Workbook Features

Worksheet Features

Printing Features

Charting Features

Formatting Features

Spreadsheet Control Features

  • SpreadsheetGear Explorer Sample Solutions for Windows provide source code which demonstrates commonly used features and APIs.
    Samples
    Related Samples:
  • Windows Forms and WPF WorkbookView spreadsheet controls provide viewing, navigation and editing support which is familiar to Excel users.
  • In-cell editing and optional FormulaBar control provide the ability to enter and edit cell values and formulas, including support for pointing at cell references in formulas across worksheets and workbooks.
  • Smart data entry with optional data validation as well as automatic recognition and formatting of dates, times, percentages, currency, scientific notation and fractions will be familiar to Excel users.
  • Conditional formatting enables font colors, font styles, background colors, borders as well as other graphical features such icon sets, data bars, color scales and more, which are based on simple comparison operators, complex formulas or more advanced rules.
  • Grouping and outlining which allows users to easily expand and collapse the displayed level of detail.
  • WorkbookDesigner provides an MDI Windows application which may be used from within Visual Studio 2013, Visual Studio 2015, Visual Studio 2017, Visual Studio 2019 and Visual Studio 2022 at Design Time and by your applications at run time.
    VS2013 VS2015 VS2017 VS2019 VS2022
  • WorkbookExplorer provides the ability to manipulate workbooks, worksheets and defined names as well as their properties.
  • RangeExplorer provides extensive modeless formatting of the currently selected range with support for number formats, alignment, fonts, borders, colors, patterns, gradients, protection options, hyperlinks, conditional formats and data validation.
  • ChartExplorer provides basic modeless modification and formatting of the selected chart with support for chart types and data sources, legend options, primary and secondary axes options and various series options.
  • Modeless find and replace dialog with support for find, find all, replace and replace all.
  • Support for an unlimited number of workbooks associated with each WorkbookView.
  • DisplayReference and DisplayReferenceName properties allow complete control over what appears for each sheet tab, including the ability to make multiple workbooks appear as one workbook and the ability to specify dynamically changing ranges using worksheet functions such as OFFSET().
  • Cut, copy, paste and paste special support including rich data and formatting interchange with Excel and full formula fixups.
  • Frozen rows and columns, split panes, sheet tab colors, zoom, multi-level undo/redo, autofit rows and columns, goto, clear, printing, print preview, interruptible background calculation, real time workbook updates and more.
  • CommandManager provides the ability to add custom undoable commands as well as the ability to override the behavior of common commands.
  • Full integration with Visual Studio 2013, Visual Studio 2015, Visual Studio 2017, Visual Studio 2019 and Visual Studio 2022 including the ability to use the WorkbookDesigner, WorkbookExplorer, RangeExplorer, ChartExplorer and ShapeExplorer at design time and at runtime.
    VS2013 VS2015 VS2017 VS2019 VS2022

Supported File Formats

  • XLSX - Open XML Workbook
  • XLSM - Open XML Macro Enabled Workbook
  • XLS - Excel 97-2003 Workbook
  • CSV - Comma Delimited Text
  • TXT - Tab Delimited Text

Microsoft .NET Features

  • Easy to use API which will be familiar to Excel VBA and COM developers while taking advantage of .NET's expressiveness.
  • Supports 32- and 64- bit versions of the Microsoft .NET Framework NET 3.5 .NET 4.0, .NET 4.5.x, .NET 4.6.x, .NET 4.7.x, .NET 4.8, as well as .NET 6 and any platform that supports .NET Standard 2.0.
  • Supports 32- and 64- bit versions of Windows 8.1, Windows 10, Windows 11, Windows Server 2012 (including R2), Windows Server 2016, Windows Server 2019 and Windows Server 2022.
  • Visual Studio 2013, Visual Studio 2015, Visual Studio 2017, Visual Studio 2019 and Visual Studio 2022 integration, including IntelliSense and Dynamic Help.
    VS2013 VS2015 VS2017 VS2019 VS2022
  • The security of 100 percent safe managed code which does not require FullTrust.
  • Hassle-free deployment with strong-named assemblies as well as no license keys, registry entries or configuration issues.
  • No dependency on Microsoft Excel, which is not supported in a server environment.
    Automation COM Interop

Supported Excel-Compatible Functions

449 of Excel's financial, date, time, text, lookup, math, trigonometry, statistical, engineering, database, information, logical and web functions. Click here for a list of unsupported functions.

Database and List Management Functions

Function NameDescription
DAVERAGE
Indicates the average of the values that meet the specified criteria.
DCOUNT
Counts the number of cells containing numbers that meet the specified criteria.
DCOUNTA
Counts nonblank cells containing numbers or text that meet the specified criteria.
DGET
Returns a single value that meets the specified criteria.
DMAX
Extracts the highest value that meets the specified criteria.
DMIN
Extracts the lowest value that meets the specified criteria.
DPRODUCT
Returns the product of the values that meet the specified criteria.
DSTDEV
Estimates the standard deviation of a population, based on a sample of selected entries from the database.
DSTDEVP
Returns the calculation of the standard deviation of a population, based on the sum of the whole population.
DSUM
Returns the total of the values that meet the specified criteria.
DVAR
Estimates the variance of a sample population based on the values that meet the specified criteria.
DVARP
Returns the calculation of the true variance of an entire population based on the values that meet the specified criteria.

Date and Time Functions

Function NameDescription
DATE
Returns the serial number that represents a date.
DATEDIF
2009 (V5)
Returns the difference of two dates in years, months or days.
DATEVALUE
Converts date text to a DATEVALUE serial number.
DAY
Returns the corresponding day of the month serial number or date text from 1 to 31.
DAYS
2017 (V8)
Returns the number of days between the two specified dates.
DAYS360
Returns the number of days between two set dates based on a 360-day year.
EDATE
2009 (V5)
Returns the value or serial number of the date which is a certain number of months before or after a user-specified date.
EOMONTH
2009 (V5)
Returns the date at the end of the month a specified number of months before or after a specified date.
HOUR
Returns the hour as a serial number integer between 0 and 23.
ISOWEEKNUM
2017 (V8)
Returns the ISO week number for a specified date.
MINUTE
Returns the serial number that corresponds to the minute.
MONTH
Returns the corresponding serial number of the month of a date between 1 and 12.
NETWORKDAYS
2009 (V5)
Returns the number of working days between two dates. Excludes weekends and specified holidays.
NETWORKDAYS.INTL
2012 (V7)
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days.
NOW
Returns the current date and time in the form of a serial number.
SECOND
Returns the seconds portion of a serial time value.
TIME
Returns the decimal value of a given time.
TIMEVALUE
Returns the decimal number for a given time.
TODAY
Returns the current date as a serial number.
WEEKDAY
Returns the corresponding day of the week as a serial number.
WEEKNUM
2009 (V5)
Returns the number where a week falls numerically within a year.
WORKDAY
2009 (V5)
Returns a date that is a specified number of working days before or after a given date.
WORKDAY.INTL
2012 (V7)
Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days.
YEAR
Returns the corresponding year as a serial number in the form of an integer.
YEARFRAC
2009 (V5)
Calculates the fraction of the year between two dates.

Engineering Functions

Function NameDescription
BESSELI
2009 (V5)
Returns the BESSEL function in modified form for imaginary arguments.
BESSELJ
2009 (V5)
Returns the actual BESSEL function.
BESSELK
2009 (V5)
Returns the BESSEL function in modified form for imaginary arguments.
BESSELY
2009 (V5)
Returns the BESSEL function, also known as the Weber or Neumann function.
BIN2DEC
2009 (V5)
Converts a binary number to decimal form.
BIN2HEX
2009 (V5)
Converts a binary number to a hexadecimal.
BIN2OCT
2009 (V5)
Converts a binary number to octal form.
BITAND
2017 (V8)
Returns the bitwise AND of the two specified numbers.
BITLSHIFT
2017 (V8)
Returns the specified number shifted left by the specified amount.
BITOR
2017 (V8)
Returns the bitwise OR of the two specified numbers.
BITRSHIFT
2017 (V8)
Returns the specified number shifted right by the specified amount.
BITXOR
2017 (V8)
Returns the bitwise XOR of the two specified numbers.
COMPLEX
2009 (V5)
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
CONVERT
2009 (V5)
Interprets data from one measurement system to another.
DEC2BIN
2009 (V5)
Converts decimal numbers to binary form.
DEC2HEX
2009 (V5)
Converts decimal numbers to hexadecimal.
DEC2OCT
2009 (V5)
Converts decimal numbers to octal.
DELTA
2009 (V5)
Tests whether numbers or values are equal with a number result. Returns "0" for unequal, "1" for equal.
ERF
2009 (V5)
Returns the integrated error function between a lower and upper limit.
ERF.PRECISE
2012 (V7)
Returns the error function
ERFC
2009 (V5)
Returns a complementary ERF function integrated between 'x' and infinity.
ERFC.PRECISE
2012 (V7)
Returns the complementary ERF function integrated between x and infinity
GESTEP
2009 (V5)
Returns the value 1 if the number is greater than or equal to a specified step value, otherwise it returns 0.
HEX2BIN
2009 (V5)
Converts hexadecimal numbers to binary form.
HEX2DEC
2009 (V5)
Converts hexadecimal numbers to decimal form.
HEX2OCT
2009 (V5)
Converts hexadecimal numbers to octal form.
IMABS
2009 (V5)
Returns the absolute value (modulus) of a complex number in x+yi or x+yj text format.
IMAGINARY
2009 (V5)
Returns the coefficient of a complex number in x+yi or x+yj text format.
IMARGUMENT
2009 (V5)
Returns the theta argument - an angle expressed in radians.
IMCONJUGATE
2009 (V5)
Returns the complex conjugate of a complex number in x+yi or x+yj text format.
IMCOS
2009 (V5)
Returns the cosine of a complex number in x+yi or x+yj text format.
IMCOSH
2017 (V8)
Returns the hyperbolic cosine of the specified complex number.
IMCOT
2017 (V8)
Returns the cotangent of the specified complex number.
IMCSC
2017 (V8)
Returns the cosecant of the specified complex number.
IMCSCH
2017 (V8)
Returns the hyperbolic cosecant of the specified complex number.
IMDIV
2009 (V5)
Returns the quotient of complex numbers in x+yi or x+yj text format.
IMEXP
2009 (V5)
Returns the exponential of a complex number in x+yi or x+yj text format.
IMLN
2009 (V5)
Returns the natural logarithm of a complex number in x+yi or x+yj text format.
IMLOG10
2009 (V5)
Returns the common logarithm (Base 10) of a complex number in x+yi or x+yj text format.
IMLOG2
2009 (V5)
Returns the common logarithm (Base 2) of a complex number in x+yi or x+yj text format.
IMPOWER
2009 (V5)
Returns a complex number raised to a power in x+yi or x+yj text format.
IMPRODUCT
2009 (V5)
Returns the product from 2 to 29 complex numbers in x+yi or x+yj text format.
IMREAL
2009 (V5)
Returns the real coefficient of a complex number in x+yi or x+yj text format.
IMSEC
2017 (V8)
Returns the secant of the specified complex number.
IMSECH
2017 (V8)
Returns the hyperbolic secant of the specified complex number.
IMSIN
2009 (V5)
Returns the sine of a complex number in x+yi or x+yj text format.
IMSINH
2017 (V8)
Returns the hyperbolic sine of the specified complex number.
IMSQRT
2009 (V5)
Returns the square root of a complex number in x+yi or x+yj text format.
IMSUB
2009 (V5)
Returns the difference of two complex numbers in x+yi or x+yj text format.
IMSUM
2009 (V5)
Returns the sum of 2 to 29 complex numbers in x+yi or x+yj text format.
IMTAN
2017 (V8)
Returns the tangent of the specified complex number.
OCT2BIN
2009 (V5)
Converts an octal number to binary form.
OCT2DEC
2009 (V5)
Converts an octal number to decimal form.
OCT2HEX
2009 (V5)
Converts an octal number to hexadecimal form.

Financial Functions

Function NameDescription
ACCRINT
2009 (V5)
Returns accrued interest for securities that pay periodic interest.
ACCRINTM
2009 (V5)
Returns the accrued interest for securities that pay interest at the maturity date.
AMORDEGRC
2009 (V5)
Returns the depreciation for each accounting period within the formula.
AMORLINC
2009 (V5)
Returns the depreciation for each accounting period.
COUPDAYBS
2009 (V5)
Returns the number of days from the beginning of the period to the coupon-period settlement date.
COUPDAYS
2009 (V5)
Returns the number of days in the period that contains the coupon period settlement date.
COUPDAYSNC
2009 (V5)
Returns the number of days between the settlement date to the next coupon date.
COUPNCD
2009 (V5)
Returns the next coupon date after the settlement date.
COUPNUM
2009 (V5)
Returns the total number of coupons to be paid between the settlement and maturity dates, rounded up to the nearest whole coupon.
COUPPCD
2009 (V5)
Returns the coupon date previous to the settlement date.
CUMIPMT
2009 (V5)
Returns the cumulative interest on a loan between start and stop dates.
CUMPRINC
2009 (V5)
Returns the cumulative principal amount between start and stop dates on a loan or mortgage.
DB
Returns the asset depreciation for a period using the fixed declining balance method.
DDB
Returns the asset depreciation for a period using the double-declining balance method or another specified method.
DISC
2009 (V5)
Returns the security discount rate.
DOLLARDE
2009 (V5)
Converts a fraction dollar price into a decimal dollar price.
DOLLARFR
2009 (V5)
Converts a decimal dollar price into a fraction dollar price.
DURATION
2009 (V5)
Returns the Macauley duration for an assumed par value.
EFFECT
2009 (V5)
Returns the effective interest rate annually. This is based on the nominal annual interest rate and the number of compounding periods per year.
FV
Returns the future value of an investment that makes payments as a lump sum or as a series of equal periodic payments.
FVSCHEDULE
2009 (V5)
Returns the future value of a principal amount after applying several, or a series of compound interest rates.
INTRATE
2009 (V5)
Returns the interest rate of a security that is fully invested.
IPMT
Returns the interest for a period of time based on an investment with periodic constant payments and a constant interest rate.
IRR
Returns the internal rate of return for a series of cash flows represented by numbers in the form of values.
ISPMT
Calculates the interest paid during a defined period of an investment.
MDURATION
2009 (V5)
Returns the modified duration of a security with a par value assumed to be $100.
MIRR
Returns a modified internal rate of return for several periodic cash flows.
NOMINAL
2009 (V5)
Returns the nominal annual interest rate given an effective rate and the total number of compounding periods for the year.
NPER
Returns the total number of periods for an investment. This is based on a periodic constant payment and a constant interest rate.
NPV
Calculates the net present value of an investment from the discount rate and several future payments and income.
ODDFPRICE
2009 (V5)
Returns the value of a security based on a per $100 face value and an odd (short or long) first period.
ODDFYIELD
2009 (V5)
Returns the security yield with an odd first period.
ODDLPRICE
2009 (V5)
Returns the per $100 face value of a security having an odd last coupon period.
ODDLYIELD
2009 (V5)
Returns the security yield that has an odd last period.
PDURATION
2017 (V8)
Returns the number of periods for the specified present value to reach the specified future value given the specified interest rate.
PMT
Calculates the loan payment for a loan based on constant payments and constant interest rates.
PPMT
Returns the principal payment for a period of an investment based on periodic constant payments and a constant interest rate.
PRICE
2009 (V5)
Returns the value of a security based on price per $100 face value and periodic interest payments.
PRICEDISC
2009 (V5)
Returns the value of a discounted security based on a price per $100 face value.
PRICEMAT
2009 (V5)
Returns the value of a security that pays interest at maturity and price per $100 face value.
PV
Returns the present value based on an investment.
RATE
Returns per period the interest of an annuity.
RECEIVED
2009 (V5)
Based on a fully invested security, returns the amount received at maturity.
RRI
2017 (V8)
Returns the effective interest rate required for the specified present value to reach the specified future value in the specified number of periods.
SLN
Returns the straight-line depreciation on an asset.
SYD
Based on a specified period, SYD returns the sum-of-years' digits depreciation of an asset.
TBILLEQ
2009 (V5)
Returns the bond equivalent yield for a treasury bill.
TBILLPRICE
2009 (V5)
Returns the price per $100 face value for a treasury bill.
TBILLYIELD
2009 (V5)
Returns the yield of a treasury bill.
VDB
For a period you specify, returns the depreciation of an asset.
XIRR
2009 (V5)
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
XNPV
2009 (V5)
Returns the net present value for a schedule of cash flows that is not necessarily periodic.
YIELD
2009 (V5)
Based on a yield that pays periodic interest, returns the yield of the security.
YIELDDISC
2009 (V5)
Returns the annual yield for a discounted security.
YIELDMAT
2009 (V5)
Returns the annual yield based on a security that pays interest at a maturity.

Information Functions

Function NameDescription
CELL
2012 (V7)
Returns information about a cell's location, formatting, or contents in the upper-left cell in a reference.
ERROR.TYPE
Returns the corresponding number value associated with an error type in Microsoft Excel.
INFO
Returns operating environment information.
ISBLANK
Returns TRUE if the cell is empty, FALSE if it contains data.
ISERR
Returns TRUE if value contains any error value except #N/A, FALSE if it does not.
ISERROR
Returns TRUE if value contains any error value (including #N/A), FALSE if it does not.
ISEVEN
2009 (V5)
Returns TRUE if value is an even number, FALSE if it is not.
ISFORMULA
2017 (V8)
Returns TRUE if the specified cell contains a formula.
ISLOGICAL
Returns TRUE if value is a logical value, FALSE if it is not.
ISNA
Returns TRUE if value is #N/A, FALSE if it is not.
ISNONTEXT
Returns TRUE if value is not text, FALSE if it is.
ISNUMBER
Returns TRUE if value is a number, FALSE if it is not.
ISODD
2009 (V5)
Returns TRUE if value is an odd number, FALSE if it is not.
ISREF
Returns TRUE if value is a reference, FALSE if it is not.
ISTEXT
Returns TRUE if value is text, FALSE if it is not.
N
Returns a value converted to a number.
NA
An alternative representation of the error value #N/A.
SHEET
2017 (V8)
Returns the one based index of the specified sheet, or the index of the sheet containing the formula if no sheet is specified.
SHEETS
2017 (V8)
Returns the number of sheets in a 3d cell reference, or the number of sheets in the workbook containing the formula if no reference is specified.
TYPE
Determines the type of value in a cell.

Logical Functions

Function NameDescription
AND
Returns TRUE if all the arguments are TRUE in the formula, and FALSE if any one argument is FALSE.
FALSE
Returns the value FALSE. May be typed directly into the cell as "FALSE".
IF
Returns a value if one condition is TRUE and returns another value if the condition is FALSE.
IFERROR
2012 (V7)
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFNA
2017 (V8)
Returns the specified first argument unless it is #N/A, in which case it returns the specified second argument.
NOT
Returns the reverse value of its arguments; TRUE becomes FALSE and FALSE becomes TRUE.
OR
Returns FALSE if all arguments are FALSE, and TRUE if at least one argument is TRUE.
TRUE
Returns the value TRUE. May be typed directly into the cell as "TRUE".
XOR
2017 (V8)
Returns TRUE if the specified arguments contain an odd number of TRUE values, or FALSE if the values contain an even number of TRUE values.

Lookup and Reference Functions

Function NameDescription
ADDRESS
Given specified row and column numbers, creates a cell address as text.
AREAS
Returns the number of areas based on a reference.
CHOOSE
Returns an item from a list of values..
COLUMN
Returns the column number(s) based on a given reference.
COLUMNS
Returns the number of columns based on an array or reference.
HLOOKUP
Searches for a specified value in an array or a table's top row.
HYPERLINK
Creates a shortcut to jump to a document stored on a network server.
INDEX
Returns the value of an element selected by the row number and column letter indexes.
INDIRECT
Returns the contents of a cell using its reference.
LOOKUP
Looks in the first row or column of a range or array, and returns the specified value from the same position in the last row or column of the range or array.
MATCH
Returns the relative position of an item in an array that matches a specified value in a specified order, or the position of an item.
OFFSET
Returns a reference to a range that is a specific number of rows and columns from a cell or range of cells.
ROW
Returns the row number based on a reference.
ROWS
Returns the number of rows in a reference or array.
TRANSPOSE
Returns a horizontal range of cells as vertical or vice versa.
VLOOKUP
Searches for a value in the leftmost column of a table and returns a value from the same row in a column number that you specify.

Math and Trigonometry Functions

Function NameDescription
ABS
Returns the absolute value of a number.
ACOS
Returns the arccosine of a number in radians in the range 0 to pi.
ACOSH
Returns the inverse hyperbolic cosine of a number.
ACOT
2017 (V8)
Returns the inverse cotangent of the specified number.
ACOTH
2017 (V8)
Returns the inverse hyperbolic cotangent of the specified number.
AGGREGATE
2017 (V8)
Returns an aggregate in a list or database
ARABIC
2017 (V8)
Converts the specified Roman numeral to a number.
ASIN
Returns the arcsine of a number in radians in the range -pi/2 to pi/2.
ASINH
Returns the inverse hyperbolic sine of a number.
ATAN
Returns the arctangent of a number in radians in the range -pi/2 to pi/2
ATAN2
Returns the four-quadrant arctangent of the specified x- and y- coordinates in radians between -pi and pi excluding -pi. A positive result represents a counterclockwise angle from the x-axis, a negative result represents a clockwise angle.
ATANH
Returns the inverse hyperbolic tangent of a number.
BASE
2017 (V8)
Converts the specified number to text with the specified radix and minimum length.
CEILING
Returns a number rounded up, away from zero, to the nearest multiple of significance.
CEILING.MATH
2017 (V8)
Returns the specified number rounded up using the specified significance and mode.
CEILING.PRECISE
2012 (V7)
Rounds a number to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number if rounded up.
COMBIN
Returns the number of combinations for a given number of items.
COMBINA
2017 (V8)
Returns the number of combinations with the specified number of items.
COS
Returns the cosine of the given angle.
COSH
Returns the hyperbolic cosine of a number.
COT
2017 (V8)
Returns the cotangent of the specified angle.
COTH
2017 (V8)
Returns the hyperbolic cotangent of the specified angle.
CSC
2017 (V8)
Returns the cosecant of the specified angle.
CSCH
2017 (V8)
Returns the hyperbolic cosecant of the specified angle.
DECIMAL
2017 (V8)
Converts the specified text to a number using the specified radix.
DEGREES
Converts radians into degrees.
EVEN
Returns a number rounded up to the next even integer for positive integers and rounded down to the next even integer for negative numbers.
EXP
Returns e (2.71828182845804) raised to the power of a specified number.
FACT
Returns the factorial of a number.
FACTDOUBLE
2009 (V5)
Returns the double factorial of a number.
FLOOR
Returns a number rounded down, toward zero, to the nearest multiple of significance.
FLOOR.MATH
2017 (V8)
Returns the specified number rounded down using the specified significance and mode.
FLOOR.PRECISE
2012 (V7)
Rounds a number to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
GCD
2009 (V5)
Returns the greatest common divisor of two or more integers.
INT
Rounds a number down to the nearest integer.
ISO.CEILING
2017 (V8)
Returns the specified number rounded up using the specified significance.
LCM
2009 (V5)
Returns the least common multiple of integers.
LN
Returns the natural (base e) logarithm of a number.
LOG
Returns the logarithm of a number of the base you specify.
LOG10
Returns the base-10 logarithm of a number.
MDETERM
Returns the matrix determinant of an array.
MINVERSE
Returns the inverse matrix for the matrix stored in an array.
MMULT
Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.
MOD
Returns the remainder of a division operation (modulus).
MROUND
2009 (V5)
Returns a number rounded to the desired multiple. Rounds up if the remainder after dividing the number by the multiple is at least half the value of the multiple.
MULTINOMIAL
Returns the ratio of the factorial of the sum of the values to the product of the factorials.
MUNIT
2017 (V8)
Returns an identity matrix with the specified n by n dimension.
ODD
Returns a number rounded up away from zero to the nearest odd integer.
PI
Returns the approximate number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
POWER
Returns the result of a specified number raised to a specified power.
PRODUCT
Multiplies all the numbers given as arguments and returns the product.
QUOTIENT
2009 (V5)
Returns the integer portion of a division.
RADIANS
Converts degrees to radians.
RAND
Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.
RANDBETWEEN
2009 (V5)
Returns a random integer between the integers you specify. A new random number is returned every time the worksheet is calculated.
ROMAN
Converts an Arabic numeral to Roman, as text.
ROUND
Round a number to a specified number of digits.
ROUNDDOWN
Rounds a number down, towards zero.
ROUNDUP
Rounds a number up, away from zero.
SEC
2017 (V8)
Returns the secant of the specified angle.
SECH
2017 (V8)
Returns the hyperbolic secant of the specified angle.
SERIESSUM
2009 (V5)
Returns the sum of a power series.
SIGN
Determines the sign of a number. Returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.
SIN
Returns the sine of a given angle.
SINH
Returns the hyperbolic sine of a number.
SQRT
Returns a positive square root.
SQRTPI
2009 (V5)
Returns the square root of (NUMBER * Pi)
SUBTOTAL
Returns a subtotal in a list or database.
SUM
Adds all the numbers in a range of cells.
SUMIF
Adds the cells specified by a certain criteria.
SUMIFS
2012 (V7)
Adds the cells in a range that meet multiple criteria
SUMPRODUCT
Multiplies corresponding components in the given arrays, and returns the sum of those products.
SUMSQ
Returns the sum of the squares of the arguments.
SUMX2MY2
Returns the sum of the difference of squares of corresponding values in two arrays.
SUMX2PY2
Returns the sum of the sum of squares of corresponding values in two arrays.
SUMXMY2
Returns the sum of squares of differences of corresponding values in two arrays.
TAN
Returns the tangent of the given angle.
TANH
Returns the hyperbolic tangent of a number.
TRUNC
Truncates a number to an integer by removing the fractional part of a number.

Pre-Excel 2010 Statistical Functions

Function NameDescription
BETADIST
Returns the cumulative beta probability density function.
BETAINV
Returns the inverse of the cumulative beta probability density function.
BINOMDIST
Returns the individual term binomial distribution probability.
CHIDIST
Returns the one-tailed probability of the chi-squared (X^2) distribution; the area in the right tail under the chi-squared distribution curve.
CHIINV
Returns the inverse of the one-tailed probability of the chi-squared (X^2) distribution.
CHITEST
Returns the test for independence of the characteristics in a table.
CONFIDENCE
Returns the confidence interval for a population mean.
COVAR
Returns the covariance, the average of products of deviations, for each data point pair.
EXPONDIST
Returns the exponential distribution.
FDIST
Returns the F probability distribution.
FINV
Returns the inverse of the F probability distribution.
FTEST
Returns the result of an F-test.
GAMMADIST
Returns the gamma distribution.
GAMMAINV
Returns the inverse of the gamma cumulative distribution.
LOGINV
Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard deviation.
LOGNORMDIST
Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard deviation.
MODE
Returns the most frequently occurring, or repetitive, number in an array or range of data.
NEGBINOMDIST
Returns the negative binomial distribution.
NORMDIST
Returns the normal cumulative distribution for the specified mean and standard deviation.
NORMINV
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORMSDIST
Returns the standard normal cumulative distribution function.
PERCENTILE
Returns the k-th percentile of values in a range.
PERCENTRANK
Returns the rank of a value in a data set set as a percentage of the data set.
POISSON
Returns the Poisson distribution.
QUARTILE
Returns the quartile of a data set.
RANK
Returns the rank of a number in a list of numbers.
STDEV
Estimates standard deviation based on a sample.
STDEVP
Estimates standard deviation based on a sample assuming that the arguments represent the total population.
TDIST
Returns the percentage points (probability) for the student t-distribution, where a numeric value (x) is a calculated value of t for which the percentage points are to be computed.
TINV
Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.
TTEST
The probability associated with t-test.
VAR
Returns an estimate for the variance of a population based on a sample data set.
VARP
Calculates variance based on the entire population.
WEIBULL
Returns the Weibull distribution.
ZTEST
Returns the two-tailed P-value of a z-test.

Statistical Functions

Function NameDescription
AVEDEV
Returns the average of the absolute deviations of data points from their mean.
AVERAGE
Returns the average of its arguments.
AVERAGEA
Returns the average of the values in its list of arguments including text and logical values.
AVERAGEIF
2012 (V7)
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFS
2012 (V7)
Returns the average (arithmetic mean) of all cells that meet multiple criteria
BETA.DIST
2012 (V7)
Returns the beta cumulative distribution function
BETA.INV
2012 (V7)
Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOM.DIST
2012 (V7)
Returns the individual term binomial distribution probability
BINOM.DIST.RANGE
2017 (V8)
Returns the probability of the specified trial using a binomial distribution.
BINOM.INV
2012 (V7)
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
CHISQ.DIST
2012 (V7)
Returns the chi-squared distribution
CHISQ.DIST.RT
2012 (V7)
Returns the one-tailed probability of the chi-squared distribution
CHISQ.INV
2012 (V7)
Returns the inverse of the left-tailed probability of the chi-squared distribution
CHISQ.INV.RT
2012 (V7)
Returns the inverse of the right-tailed probability of the chi-squared distribution
CHISQ.TEST
2012 (V7)
Returns the test for independence.
CONFIDENCE.NORM
2012 (V7)
Returns the confidence interval for a population mean.
CONFIDENCE.T
2012 (V7)
Returns the confidence interval for a population mean, using a Student's t distribution
CORREL
Returns the correlation coefficient between two data sets.
COUNT
Counts the number of cells that contain numbers (including dates and formulas that evaluate to numbers) within the list of arguments.
COUNTA
Counts the number of cells that are not empty.
COUNTBLANK
Counts the empty cells in a specified range.
COUNTIF
Counts the number of cells in a range that meet a given criteria.
COUNTIFS
2012 (V7)
Counts the number of cells within a range that meet multiple criteria
COVARIANCE.P
2012 (V7)
Returns covariance, the average of the products of paired deviations
COVARIANCE.S
2012 (V7)
Returns the sample covariance, the average of the products deviations for each data point pair in two data sets
CRITBINOM
Returns the minimum number yields a binomial distribution less than or equal to the specified criteria
DEVSQ
Returns the sum of the squares of deviations of a data set from their sample mean.
EXPON.DIST
2012 (V7)
Returns the exponential distribution.
F.DIST
2012 (V7)
Returns the F probability distribution.
F.DIST.RT
2012 (V7)
Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets
F.INV
2012 (V7)
Returns the inverse of the F probability distribution
F.INV.RT
2012 (V7)
Returns the inverse of the (right-tailed) F probability distribution
F.TEST
2012 (V7)
Returns the result of an F-test.
FISHER
Returns the Fisher transformation at x.
FISHERINV
Returns the inverse of the Fisher transformation at y.
FORECAST
Calculates or predicts a future value by using existing values.
FREQUENCY
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
GAMMA
2017 (V8)
Returns the gamma function result for the specified number.
GAMMA.DIST
2012 (V7)
Returns the gamma distribution.
GAMMA.INV
2012 (V7)
Returns the inverse of the gamma cumulative distribution.
GAMMALN
Returns the natural logarithm of the gamma function.
GAMMALN.PRECISE
2012 (V7)
Returns the natural logarithm of the gamma function.
GAUSS
2017 (V8)
Returns the probability that a number will fall between the mean and the specified standard deviation in a normal distribution.
GEOMEAN
Returns the geometric mean of an array or range of positive data.
GROWTH
Calculates predicted exponential growth by using existing data.
HARMEAN
Returns the harmonic mean of a data set.
HYPGEOM.DIST
2012 (V7)
Returns the hypergeometric distribution.
HYPGEOMDIST
Returns the hypergeometric distribution.
INTERCEPT
Calculates the point at which a line will intersect the y-axis by using existing x and y values.
KURT
Returns the Kurtosis of a data set.
LARGE
Returns the k-th largest value in a data set.
LINEST
Calculates a straight line that best fits your data using the least squares method.
LOGEST
Calculates an exponential curve that fits your data and returns an array of values that describes the curve.
LOGNORM.DIST
2012 (V7)
Returns the lognormal distribution, of x, where ln(x) is normally distributed with mean and standard deviation.
LOGNORM.INV
2012 (V7)
Returns the inverse of the lognormal cumulative distribution.
MAX
Returns the largest value in a set of values.
MAXA
Returns the largest value in a set of values including text and logical values.
MEDIAN
Returns the median of the given numbers.
MIN
Returns the smallest value in a set of values.
MINA
Returns the smallest value in a set of values including text and logical values.
MODE.MULT
2012 (V7)
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.
MODE.SNGL
2012 (V7)
Returns the most common value in a data set.
NEGBINOM.DIST
2012 (V7)
Returns the negative binomial distribution.
NORM.DIST
2012 (V7)
Returns the normal cumulative distribution.
NORM.INV
2012 (V7)
Returns the inverse of the normal cumulative distribution.
NORM.S.DIST
2012 (V7)
Return the standard normal cumulative distribution.
NORM.S.INV
2012 (V7)
Returns the inverse of the standard normal cumulative distribution.
NORMSINV
Returns the inverse of the standard normal cumulative distribution function.
PEARSON
Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.
PERCENTILE.EXC
2012 (V7)
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INC
2012 (V7)
Returns the k-th percentile of values in a range.
PERCENTRANK.EXC
2012 (V7)
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERCENTRANK.INC
2012 (V7)
Returns the percentage rank of a value in a data set
PERMUT
Returns the number of permutations for a given number of objects that can be selected from a range of numbers.
PERMUTATIONA
2017 (V8)
Returns the number of permutations given the specified total number of items and the specified number of items chosen for each permutation.
PHI
2017 (V8)
Returns the value of the probability density function of the specified number for the standard normal distribution.
POISSON.DIST
2012 (V7)
Returns the Poisson distribution.
PROB
Returns the probability that values in a range are between two specified limits.
QUARTILE.EXC
2012 (V7)
Returns the quartile of the data set, based on percentile values from 0..1, exclusive.
QUARTILE.INC
2012 (V7)
Returns the quartile of a data set.
RANK.AVG
2012 (V7)
Returns the rank of a number in a list of numbers.
RANK.EQ
2012 (V7)
Returns the rank of a number in a list of numbers.
RSQ
Returns the r^2 value of a linear regression line.
SKEW
Returns the skew of a distribution.
SKEW.P
2017 (V8)
Returns the population skewness of the specified distribution.
SLOPE
Returns the slope of a regression line through data points in KNOWN_Y'S and KNOWN_X'S.
SMALL
Returns the k-th smallest value in a data set.
STANDARDIZE
Returns a normalized value from a distribution characterized by MEAN and STANDARD_DEV.
STDEV.P
2012 (V7)
Calculates standard deviation based on the entire population
STDEV.S
2012 (V7)
Estimates standard deviation based on a sample.
STDEVA
Estimates standard deviation based on a sample. Includes text and logical values.
STDEVPA
Estimates standard deviation based on a sample assuming that the arguments represent the total population. Includes text and logical values.
STEYX
Returns the standard error of the predicted y value for each x in the regression.
T.DIST
2012 (V7)
Returns the percentage points (probability) for the student t-distribution.
T.DIST.2T
2012 (V7)
Returns the percentage points (probability) for the student t-distribution.
T.DIST.RT
2012 (V7)
Returns the Student's t-distribution.
T.INV
2012 (V7)
Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.
T.INV.2T
2012 (V7)
Returns the inverse of the Student's t-distribution.
T.TEST
2012 (V7)
Returns the probability associated with a Student's t-test.
TREND
Returns the y-values along a linear trendline that best fits the values in a data set.
TRIMMEAN
Returns the mean of the interior of a data set.
VAR.P
2012 (V7)
Calculates variance based on the entire population
VAR.S
2012 (V7)
Estimates variance based on a sample.
VARA
Returns an estimate for the variance of a population based on a sample data set and may include text or logical values.
VARPA
Calculates variance based on the entire population and may include text or logical values.
WIEBULL.DIST
Returns the Weibull distribution.
Z.TEST
2012 (V7)
Returns the one-tailed probability-value of a z-test.

Text Functions

Function NameDescription
CHAR
Returns the character specified by a number.
CLEAN
Removes all nonprintable characters from text.
CODE
Returns a numeric code from the first character in a text string. The opposite of the CHAR function.
CONCATENATE
Joins several text strings into one text string.
DOLLAR
Converts a number to text using Currency format, with the decimals rounded to the specified place.
EXACT
Compares two text strings and returns TRUE if they are exactly the same, and FALSE otherwise.
FIND
Locates one text string within another text string, and returns the number of the starting position of FIND_TEXT from the leftmost character of WITHIN_TEXT.
FINDB
Returns the position of specified text within another specified text string based on the number of bytes each character uses from the first character of WITHIN_TEXT.
FIXED
Rounds a number to a specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
LEFT
Returns the first character(s) in a text string.
LEFTB
Returns the first character(s) in a text string based on a specified number of bytes
LEN
Returns the number of characters in a text string.
LENB
Returns the number of characters in a text string expressed in bytes.
LOWER
Converts all letters in a text string to lowercase.
MID
Returns a specific number of characters from a text string starting at the position you specify.
MIDB
Returns a group of characters based on a specified number of bytes from a text string starting at the position you specify.
NUMBERVALUE
2017 (V8)
Converts the specified text to a number using the specified decimal separator and thousands separator.
PROPER
Capitalizes the first letter of each word in a text string or sentence.
REPLACE
Replaces part of a text string with a different text string based on the number of characters you specify.
REPLACEB
Replaces part of a text string with a different text string based on the number of characters you specify in terms of bytes.
REPT
Repeats specified text a given number of times.
RIGHT
Returns the last character(s) in a text string.
RIGHTB
Returns the last character(s) in a text string based on a specified number of bytes.
SEARCH
Returns the number of the character at which a specific character or text string is first found, reading from left to right.
SEARCHB
Returns the number of the character at which a specific character or text string is first found in bytes, reading from left to right.
SUBSTITUTE
Substitutes NEW_TEXT for OLD_TEXT in a string.
T
Returns the text referred to by a value.
TEXT
Converts a value to text in a specific number format.
TRIM
Removes all spaces from text except single spaces between words.
UNICHAR
2017 (V8)
Convert the specified UTF-32 code point to text.
UNICODE
2017 (V8)
Convert the first character in the specified text to a UTF-32 code point.
UPPER
Converts text to uppercase.
USDOLLAR
Converts a number to text using US Dollar format, with the decimals rounded to the specified place.
VALUE
Converts a text string that represents a number to a number.

Web Functions

Function NameDescription
ENCODEURL
2017 (V8)
Returns the specified string as an encoded URL.
FILTERXML
2017 (V8)
Returns the selected node(s) from the specified xml and xpath expression.
WEBSERVICE
2017 (V8)
Returns the text result of an HTTP request from the specified URL. Note that SpreadsheetGear V9 and later disables this function by default. To enable it for a given IWorkbookSet, set IWorkbookSet.EnableWebService to true.