Visit Our Facebook Page
Visit Our Google Plus Page
Visit Our Twitter Page
What's New
ASP.NET, Windows Forms and Silverlight SpreadsheetGear Samples
Purchase SpreadsheetGear
Download SpreadsheetGear
Which SpreadsheetGear Product
is Right for You?

SpreadsheetGear 2012 for .NET comes with assemblies built for .NET 2.0 and .NET 4.0 while SpreadsheetGear 2012 for Silverlight requires Silverlight 5. Although the vast majority of features are identical, there are some differences...

SpreadsheetGear 2012

The SpreadsheetGear 2012 family of products is made up of powerful and flexible Microsoft Excel compatible spreadsheet components and includes SpreadsheetGear 2012 for .NET and SpreadsheetGear 2012 for Silverlight.

SpreadsheetGear 2012 enables ASP.NET, Windows Forms, WPF and Silverlight 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.

Excel Automation Samples  Download SpreadsheetGear 2012

SpreadsheetGear Feature Finder

   
 

Section: General FeaturesGeneral Features

  • Royalty free deployment to 32 bit and 64 bit Windows 2000, Windows XP, Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows Server 2003 (including R2), Windows Server 2008 (including R2) and Windows Server 2012 (including R2).
  • Create, read, modify, view, edit, format, calculate, print and write Microsoft Excel 97-2003 (xls) and Excel 2007-2010 Open XML (xlsx and xlsm) workbooks without Excel.
  • Scalable and reliable Excel Reporting.
    Related Samples
    Close
    Related Samples:
  • Powerful Windows Forms, WPF and Silverlight spreadsheet controls.
  • Fastest and most complete Excel compatible calculation engine available (significantly faster than Excel in many cases).
    Related Samples
    Close
    Related Samples:
  • Easily create images from Excel charts and ranges.
    Related Samples
    Close
    Related Samples:
  • Comprehensive Excel compatible charting.
    Related Samples
    Close
    Related Samples:
  • Versatile autofilters with top or bottom n items or percent, above or below average, custom criteria, sorting, SUBTOTAL and more.
    Related Samples
    Close
    Related Samples:
  • Read and write password protected xls, xlsx and xlsm workbooks.
  • Read and write CSV and tab separated text files.
  • Support for cell comments, pictures, text boxes, check boxes, drop-downs, list boxes, spinners, scrollbars, buttons, lines and many autoshapes.
  • Drag Fill / Auto Fill including support for numbers, dates, times, month names, day names and certain text patterns.
  • Fill Data Series support including automatic, chronological, linear, growth, day, weekday, month, year, start value, step value, stop value and the ability to use regression for linear or exponential trends.
    Related Samples
    Close
    Related Samples:
  • Fast single variable and single target goal seeking.
    Related Samples
    Close
    Related Samples:
  • Data validation including operators, alert messages, alert styles, custom formulas and drop-down lists.
    Related Samples
    Close
    Related Samples:
  • Conditional formats with support for simple comparison operators and custom formulas.
    Related Samples
    Close
    Related Samples:
  • Copy and insert DataTables into pre-formatted ranges with complete formula, border and format fixups.
  • Easily convert entire workbooks or specific ranges to a DataSet or DataTable.
    Related Samples
    Close
    Related Samples:
  • Copy, transpose and consolidate across cells, worksheets, workbooks and workbook sets.
  • Insert, delete and clear rows, columns and cells.
  • Stable sort with an unlimited number of keys and the ability to sort text as numbers.
    Related Samples
    Close
    Related Samples:
  • Grouping and outlining of rows and columns.
    Related Samples
    Close
    Related Samples:
  • Supports 1,048,576 rows, 16,384 columns, 64K worksheets, unlimited workbooks and unlimited workbook sets.
  • Copy, insert and move entire worksheets.
    Related Samples
    Close
    Related Samples:
  • Reads and writes VBA Macros.
  • A wide variety of C# and Visual Basic samples including live ASP.NET and Silverlight samples.

Section: Calculation EngineCalculation Engine

  • Fastest and most complete Excel compatible calculation engine available (significantly faster than Excel in many cases).
    Related Samples
    Close
    Related Samples:
  • Multithreaded calculations efficiently utilize todays multi-core CPUs.
  • Interruptible background calculation enables responsive applications.
  • 398 Excel compatible financial, date, time, text, lookup, math, trigonometry, statistical, database and Analysis Toolpak functions.
    Related Samples
    Close
    Related Samples:
  • All types, operators, defined names, data tables and arrays.
  • Support for 64K worksheets and unlimited workbooks in formulas.
  • Worksheet specific defined names and global defined names.
  • Custom functions, minimal recalc, iteration, precision as displayed and more.
    Related Samples
    Close
    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.

Section: Workbook FeaturesWorkbook Features

  • Read and write workbooks to disk, memory streams and byte arrays.
  • Read and write password protected xls, xlsx and xlsm workbooks.
  • Workbook and worksheet scoped named ranges.
  • Options to show and hide worksheet tabs and scrollbars.

Section: Worksheet FeaturesWorksheet Features

  • AutoFilters
    Related Samples
    Close
    Related Samples:
    • Top or bottom n items.
    • Top or bottom n percent.
    • Above or below average.
    • Last day, month, quarter, week or year.
    • This day, month, quarter, week or year.
    • Next day, month, quarter, week or year.
    • Specific day, month, year, hour, minute or second.
    • Year to date.
    • Filter by font color or interior color.
    • Custom criteria such as ">50".
    • Filter by specific list of values.
    • Support for the SUBTOTAL function which works in conjunction with autofilters to return various statistics for filtered items including average, count, max, min, product, standard deviation, sum and variance.
    • Autofilter support in many commands including cell formatting, copy   paste, fill, delete and more.
  • Drag Fill / AutoFill, including suppor for numbers, dates, times, month names, day names and certain text patterns.
    Related Samples
    Close
    Related Samples:
  • Fill Data Series support including automatic, chronological, linear, growth, day, weekday, month, year, start value, step value, stop value and the ability to use regression for linear or exponential trends.
  • GoalSeek - Fast single variable and single target goal seeking.
    Related Samples
    Close
    Related Samples:
  • Cell Data Validation - including operators, alert messages, alert styles, custom formulas and drop-down lists.
    Related Samples
    Close
    Related Samples:
  • Conditional Formatting - support for simple comparison operators and custom formulas.
    Related Samples
    Close
    Related Samples:
  • ADO.NET DataSet and DataTable Conversion
  • Supports 1,048,576 rows, 16,384 columns, 64K worksheets, unlimited workbooks and unlimited workbook sets.
  • Insert, delete and clear rows, columns and cells.
  • Enable or disable worksheet protection, worksheet passwords and cell locking.
    Related Samples
    Close
    Related Samples:
  • Autoshapes, text boxes and pictures including JPEG, PNG, GIF, BMP, WMF and EMF.
    Related Samples
    Close
    Related Samples:

Section: Printing FeaturesPrinting Features

  • Print workbooks, worksheets, ranges and charts in any combination.
  • Horizontal and vertical page breaks.
    Related Samples
    Close
    Related Samples:
  • Page Setup Options
    Related Samples
    Close
    Related Samples:
    • Print area, title rows and title columns.
    • Margins, page order, page orientation, zoom and fit to pages.
    • Custom headers and footers.
  • Print preview.
  • Print to XPS files.

Section: Charting FeaturesCharting Features

  • The most comprehensive Excel compatible charting support available in a Microsoft .NET Framework component.
    Related Samples
    Close
    Related Samples:
  • GDI+ and XAML imaging classes provide the ability to generate an image (such as GIF or PNG) with one line of code.
    Related Samples
    Close
    Related Samples:
  • Chart rendering supports the most common charting features:
    • Chart types include area, bar, column, line, pie, stock, XY scatter, radar and bubble charts.
      Column Chart Line Chart Pie Chart Bar Chart
      Area Chart XY Scatter Chart Bubble Chart Radar Chart
    • Chart type options include multiple chart groups (combination charts), stacked and 100% stacked values, bar gap and overlap, line markers, drop lines, high-low lines, trendlines, series line smoothing, open-close bars, error bars, exploded pies, and data point labels.
    • Axes options include axes titles, primary and secondary axes sets, auto and manual scaling, logarithmic and date scales, major and minor gridlines and tick marks, reversed axes, tick label positions and rotated text.
    • General chart options include chart titles, legends, theme colors, auto colors, gradients including Excel compatible preset gradients, solid and dashed line styles, number formats and fonts.

Section: Formatting FeaturesFormatting Features

  • Number formats, including full support for getting and setting Excel compatible custom number formats as well as getting the formatted text of a cell as Excel would display it.
    Related Samples
    Close
    Related Samples:
  • Fonts, borders, Office theme colors with tinting and shading, RGB colors, Excel palette indexed colors and patterns.
    Related Samples
    Close
    Related Samples:
  • Horizontal alignment, vertical alignment, word wrap and rotated text.
    Related Samples
    Close
    Related Samples:
  • Merged cells, hyperlinks and conditional formats.
    Related Samples
    Close
    Related Samples:
  • Grouping and outlining of rows and columns.
    Related Samples
    Close
    Related Samples:
  • Automatic row heights and column widths.
  • Copy formats without affecting formulas or values.
  • Named cell style support.

Section: Spreadsheet Control FeaturesSpreadsheet Control Features

  • Windows Forms, WPF and Silverlight 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 and borders which are based on simple comparison operators or complex formulas.
  • 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 2005, Visual Studio 2008, Visual Studio 2010, Visual Studio 2012 and Visual Studio 2013 at design time and by your applications at run time.
  • 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, 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 2005, Visual Studio 2008, Visual Studio 2010 and Visual Studio 2012 including the ability to use the WorkbookDesigner, WorkbookExplorer, RangeExplorer, ChartExplorer and ShapeExplorer at design time and at runtime.
  • SpreadsheetGear Explorer Sample Solutions for Windows and Silverlight provide source code which demonstrates commonly used features and APIs.

Section: Supported File FormatsSupported 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

Section: Microsoft .NET FeaturesMicrosoft .NET Framework 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 2.0, .NET 3.0, .NET 3.5 .NET 4.0 .NET 4.5 and Silverlight 5.
  • Supports 32 and 64 bit versions of Windows 2000, Windows XP, Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows Server 2003 (including R2), Windows Server 2008 (including R2) and Windows Server 2012 (including R2).
  • Microsoft Visual Studio 2005, Visual Studio 2008, Visual Studio 2010, Visual Studio 2012 and Visual Studio 2013 integration, including IntelliSense and Dynamic Help.
  • 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.
 

 
'f(x)' Watermark Image

Supported Excel Compatible Functions

398 of Excel 2010's financial, date, time, text, lookup, math, trigonometry, statistical, database, engineering, information and logical functions.

Database and List Management Functions
DAVERAGEIndicates the average of the values that meet the specified criteria.
DCOUNTCounts the number of cells containing numbers that meet the specified criteria.
DCOUNTACounts nonblank cells containing numbers or text that meet the specified criteria.
DGETReturns a single value that meets the specified criteria.
DMAXExtracts the highest value that meets the specified criteria.
DMINExtracts the lowest value that meets the specified criteria.
DPRODUCTReturns the product of the values that meet the specified criteria.
DSTDEVEstimates the standard deviation of a population, based on a sample of selected entries from the database.
DSTDEVPReturns the calculation of the standard deviation of a population, based on the sum of the whole population.
DSUMReturns the total of the values that meet the specified criteria.
DVAREstimates the variance of a sample population based on the values that meet the specified criteria.
DVARPReturns the calculation of the true variance of an entire population based on the values that meet the specified criteria.
Date and Time Functions
DATEReturns the serial number that represents a date.
DATEDIFReturns the difference of two dates in years, months or days.
DATEVALUEConverts date text to a DATEVALUE serial number.
DAYReturns the corresponding day of the month serial number or date text from 1 to 31.
DAYS360Returns the number of days between two set dates based on a 360-day year.
EDATEReturns the value or serial number of the date which is a certain number of months before or after a user-specified date.
EOMONTHReturns the date at the end of the month a specified number of months before or after a specified date.
HOURReturns the hour as a serial number integer between 0 and 23.
MINUTEReturns the serial number that corresponds to the minute.
MONTHReturns the corresponding serial number of the month of a date between 1 and 12.
NETWORKDAYSReturns the number of working days between two dates. Excludes weekends and specified holidays.
NETWORKDAYS.INTLReturns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days.
NOWReturns the current date and time in the form of a serial number.
SECONDReturns the seconds portion of a serial time value.
TIMEReturns the decimal value of a given time.
TIMEVALUEReturns the decimal number for a given time.
TODAYReturns the current date as a serial number.
WEEKDAYReturns the corresponding day of the week as a serial number.
WEEKNUMReturns the number where a week falls numerically within a year.
WORKDAYReturns a date that is a specified number of working days before or after a given date.
WORKDAY.INTLReturns 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.
YEARReturns the corresponding year as a serial number in the form of an integer.
YEARFRACCalculates the fraction of the year between two dates.
Engineering Functions
BESSELIReturns the BESSEL function in modified form for imaginary arguments.
BESSELJReturns the actual BESSEL function.
BESSELKReturns the BESSEL function in modified form for imaginary arguments.
BESSELYReturns the BESSEL function, also known as the Weber or Neumann function.
BIN2DECConverts a binary number to decimal form.
BIN2HEXConverts a binary number to a hexadecimal.
BIN2OCTConverts a binary number to octal form.
COMPLEXConverts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
CONVERTInterprets data from one measurement system to another.
DEC2BINConverts decimal numbers to binary form.
DEC2HEXConverts decimal numbers to hexadecimal.
DEC2OCTConverts decimal numbers to octal.
DELTATests whether numbers or values are equal with a number result. Returns "0" for unequal, "1" for equal.
ERFReturns the integrated error function between a lower and upper limit.
ERF.PRECISEReturns the error function
ERFCReturns a complementary ERF function integrated between 'x' and infinity.
ERFC.PRECISEReturns the complementary ERF function integrated between x and infinity
GESTEPReturns the value 1 if the number is greater than or equal to a specified step value, otherwise it returns 0.
HEX2BINConverts hexadecimal numbers to binary form.
HEX2DECConverts hexadecimal numbers to decimal form.
HEX2OCTConverts hexadecimal numbers to octal form.
IMABSReturns the absolute value (modulus) of a complex number in x+yi or x+yj text format.
IMAGINARYReturns the coefficient of a complex number in x+yi or x+yj text format.
IMARGUMENTReturns the theta argument - an angle expressed in radians.
IMCONJUGATEReturns the complex conjugate of a complex number in x+yi or x+yj text format.
IMCOSReturns the cosine of a complex number in x+yi or x+yj text format.
IMDIVReturns the quotient of complex numbers in x+yi or x+yj text format.
IMEXPReturns the exponential of a complex number in x+yi or x+yj text format.
IMLNReturns the natural logarithm of a complex number in x+yi or x+yj text format.
IMLOG10Returns the common logarithm (Base 10) of a complex number in x+yi or x+yj text format.
IMLOG2Returns the common logarithm (Base 2) of a complex number in x+yi or x+yj text format.
IMPOWERReturns a complex number raised to a power in x+yi or x+yj text format.
IMPRODUCTReturns the product from 2 to 29 complex numbers in x+yi or x+yj text format.
IMREALReturns the real coefficient of a complex number in x+yi or x+yj text format.
IMSINReturns the sine of a complex number in x+yi or x+yj text format.
IMSQRTReturns the square root of a complex number in x+yi or x+yj text format.
IMSUBReturns the difference of two complex numbers in x+yi or x+yj text format.
IMSUMReturns the sum of 2 to 29 complex numbers in x+yi or x+yj text format.
OCT2BINConverts an octal number to binary form.
OCT2DECConverts an octal number to decimal form.
OCT2HEXConverts an octal number to hexadecimal form.
Financial Functions
ACCRINTReturns accrued interest for securities that pay periodic interest.
ACCRINTMReturns the accrued interest for securities that pay interest at the maturity date.
AMORDEGRCReturns the depreciation for each accounting period within the formula.
AMORLINCReturns the depreciation for each accounting period.
COUPDAYBSReturns the number of days from the beginning of the period to the coupon-period settlement date.
COUPDAYSReturns the number of days in the period that contains the coupon period settlement date.
COUPDAYSNCReturns the number of days between the settlement date to the next coupon date.
COUPNCDReturns the next coupon date after the settlement date.
COUPNUMReturns the total number of coupons to be paid between the settlement and maturity dates, rounded up to the nearest whole coupon.
COUPPCDReturns the coupon date previous to the settlement date.
CUMIPMTReturns the cumulative interest on a loan between start and stop dates.
CUMPRINCReturns the cumulative principal amount between start and stop dates on a loan or mortgage.
DBReturns the asset depreciation for a period using the fixed declining balance method.
DDBReturns the asset depreciation for a period using the double-declining balance method or another specified method.
DISCReturns the security discount rate.
DOLLARDEConverts a fraction dollar price into a decimal dollar price.
DOLLARFRConverts a decimal dollar price into a fraction dollar price.
DURATIONReturns the Macauley duration for an assumed par value.
EFFECTReturns the effective interest rate annually. This is based on the nominal annual interest rate and the number of compounding periods per year.
FVReturns the future value of an investment that makes payments as a lump sum or as a series of equal periodic payments.
FVSCHEDULEReturns the future value of a principal amount after applying several, or a series of compound interest rates.
INTRATEReturns the interest rate of a security that is fully invested.
IPMTReturns the interest for a period of time based on an investment with periodic constant payments and a constant interest rate.
IRRReturns the internal rate of return for a series of cash flows represented by numbers in the form of values.
ISPMTCalculates the interest paid during a defined period of an investment.
MDURATIONReturns the modified duration of a security with a par value assumed to be $100.
MIRRReturns a modified internal rate of return for several periodic cash flows.
NOMINALReturns the nominal annual interest rate given an effective rate and the total number of compounding periods for the year.
NPERReturns the total number of periods for an investment. This is based on a periodic constant payment and a constant interest rate.
NPVCalculates the net present value of an investment from the discount rate and several future payments and income.
ODDFPRICEReturns the value of a security based on a per $100 face value and an odd (short or long) first period.
ODDFYIELDReturns the security yield with an odd first period.
ODDLPRICEReturns the per $100 face value of a security having an odd last coupon period.
ODDLYIELDReturns the security yield that has an odd last period.
PMTCalculates the loan payment for a loan based on constant payments and constant interest rates.
PPMTReturns the principal payment for a period of an investment based on periodic constant payments and a constant interest rate.
PRICEReturns the value of a security based on price per $100 face value and periodic interest payments.
PRICEDISCReturns the value of a discounted security based on a price per $100 face value.
PRICEMATReturns the value of a security that pays interest at maturity and price per $100 face value.
PVReturns the present value based on an investment.
RATEReturns per period the interest of an annuity.
RECEIVEDBased on a fully invested security, returns the amount received at maturity.
SLNReturns the straight-line depreciation on an asset.
SYDBased on a specified period, SYD returns the sum-of-years' digits depreciation of an asset.
TBILLEQReturns the bond equivalent yield for a treasury bill.
TBILLPRICEReturns the price per $100 face value for a treasury bill.
TBILLYIELDReturns the yield of a treasury bill.
VDBFor a period you specify, returns the depreciation of an asset.
XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
XNPVReturns the net present value for a schedule of cash flows that is not necessarily periodic.
YIELDBased on a yield that pays periodic interest, returns the yeild of the security.
YIELDDISCReturns the annual yield for a discounted security.
YIELDMATReturns the annual yield based on a security that pays interest at a maturity.
Information Functions
CELLReturns information about a cell's location, formatting, or contents in the upper-left cell in a reference.
ERROR.TYPEReturns the corresponding number value associated with an error type in Microsoft Excel.
INFOReturns operating environment information.
ISBLANKReturns TRUE if the cell is empty, FALSE if it contains data.
ISERRReturns TRUE if value contains any error value except #N/A, FALSE if it does not.
ISERRORReturns TRUE if value contains any error value (including #N/A), FALSE if it does not.
ISEVENReturns TRUE if value is an even number, FALSE if it is not.
ISLOGICALReturns TRUE if value is a logical value, FALSE if it is not.
ISNAReturns TRUE if value is #N/A, FALSE if it is not.
ISNONTEXTReturns TRUE if value is not text, FALSE if it is.
ISNUMBERReturns TRUE if value is a number, FALSE if it is not.
ISODDReturns TRUE if value is an odd number, FALSE if it is not.
ISREFReturns TRUE if value is a reference, FALSE if it is not.
ISTEXTReturns TRUE if value is text, FALSE if it is not.
NReturns a value converted to a number.
NAAn alternative representation of the error value #N/A.
TYPEDetermines the type of value in a cell.
Logical Functions
ANDReturns TRUE if all the arguments are TRUE in the formula, and FALSE if any one argument is FALSE.
FALSEReturns the value FALSE. May be typed directly into the cell as "FALSE".
IFReturns a value if one condition is TRUE and returns another value if the condition is FALSE.
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOTReturns the reverse value of its arguments; TRUE becomes FALSE and FALSE becomes TRUE.
ORReturns FALSE if all arguments are FALSE, and TRUE if at least one argument is TRUE.
TRUEReturns the value TRUE. May be typed directly into the cell as "TRUE".
Lookup and Reference Functions
ADDRESSGiven specified row and column numbers, creates a cell address as text.
AREASReturns the number of areas based on a reference.
CHOOSEReturns an item from a list of values..
COLUMNReturns the column number(s) based on a given reference.
COLUMNSReturns the number of columns based on an array or reference.
HLOOKUPSearches for a specified value in an array or a table's top row.
HYPERLINKCreates a shortcut to jump to a document stored on a network server.
INDEXReturns the value of an element selected by the row number and column letter indexes.
INDIRECTReturns the contents of a cell using its reference.
LOOKUPLooks 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.
MATCHReturns the relative position of an item in an array that matches a specified value in a specified order, or the position of an item.
OFFSETReturns a reference to a range that is a specific number of rows and columns from a cell or range of cells.
ROWReturns the row number based on a reference.
ROWSReturns the number of rows in a reference or array.
TRANSPOSEReturns a horizontal range of cells as vertical or vice versa.
VLOOKUPSearches 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
ABSReturns the absolute value of a number.
ACOSReturns the arccosine of a number in radians in the range 0 to pi.
ACOSHReturns the inverse hyperbolic cosine of a number.
ASINReturns the arcsine of a number in radians in the range -pi/2 to pi/2.
ASINHReturns the inverse hyperbolic sine of a number.
ATANReturns the arctangent of a number in radians in the range -pi/2 to pi/2
ATAN2Returns 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.
ATANHReturns the inverse hyperbolic tangent of a number.
CEILINGReturns a number rounded up, away from zero, to the nearest multiple of significance.
CEILING.PRECISERounds 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.
COMBINReturns the number of combinations for a given number of items.
COSReturns the cosine of the given angle.
COSHReturns the hyperbolic cosine of a number.
DEGREESConverts radians into degrees.
EVENReturns a number rounded up to the next even integer for positive integers and rounded down to the next even integer for negative numbers.
EXPReturns e (2.71828182845804) raised to the power of a specified number.
FACTReturns the factorial of a number.
FACTDOUBLEReturns the double factorial of a number.
FLOORReturns a number rounded down, toward zero, to the nearest multiple of significance.
FLOOR.PRECISERounds a number to the nearest integeror to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
GCDReturns the greatest common divisor of two or more integers.
INTRounds a number down to the nearest integer.
LCMReturns the least common multiple of integers.
LNReturns the natural (base e) logarithm of a number.
LOGReturns the logarithm of a number of the base you specify.
LOG10Returns the base-10 logarithm of a number.
MDETERMReturns the matrix determinant of an array.
MINVERSEReturns the inverse matrix for the matrix stored in an array.
MMULTReturns 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.
MODReturns the remainder of a division operation (modulus).
MROUNDReturns 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.
MULTINOMIALReturns the ratio of the factorial of the sum of the values to the product of the factorials.
ODDReturns a number rounded up away from zero to the nearest odd integer.
PIReturns the approximate number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
POWERReturns the result of a specified number raised to a specified power.
PRODUCTMultiplies all the numbers given as arguments and returns the product.
QUOTIENTReturns the integer portion of a division.
RADIANSConverts degrees to radians.
RANDReturns 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.
RANDBETWEENReturns a random integer between the integers you specify. A new random number is returned every time the worksheet is calculated.
ROMANConverts an Arabic numeral to Roman, as text.
ROUNDRound a number to a specified number of digits.
ROUNDDOWNRounds a number down, towards zero.
ROUNDUPRounds a number up, away from zero.
SERIESSUMReturns the sum of a power series.
SIGNDetermines 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.
SINReturns the sine of a given angle.
SINHReturns the hyperbolic sine of a number.
SQRTReturns a positive square root.
SQRTPIReturns the square root of (NUMBER * Pi)
SUBTOTALReturns a subtotal in a list or database.
SUMAdds all the numbers in a range of cells.
SUMIFAdds the cells specified by a certain criteria.
SUMIFSAdds the cells in a range that meet multiple criteria
SUMPRODUCTMultiplies corresponding components in the given arrays, and returns the sum of those products.
SUMSQReturns the sum of the squares of the arguments.
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays.
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays.
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays.
TANReturns the tangent of the given angle.
TANHReturns the hyperbolic tangent of a number.
TRUNCTruncates a number to an integer by removing the fractional part of a number.
Pre-Excel 2010 Statistical Functions
BETADISTReturns the cumulative beta probability density function.
BETAINVReturns the inverse of the cumulative beta probability density function.
BINOMDISTReturns the individual term binomial distribution probability.
CHIDISTReturns the one-tailed probability of the chi-squared (X^2) distribution; the area in the right tail under the chi-squared distribution curve.
CHIINVReturns the inverse of the one-tailed probability of the chi-squared (X^2) distribution.
CHITESTReturns the test for independence of the characteristics in a table.
CONFIDENCEReturns the confidence interval for a population mean.
COVARReturns the covariance, the average of products of deviations, for each data point pair.
EXPONDISTReturns the exponential distribution.
FDISTReturns the F probability distribution.
FINVReturns the inverse of the F probability distribution.
FTESTReturns the result of an F-test.
GAMMADISTReturns the gamma distribution.
GAMMAINVReturns the inverse of the gamma cumulative distribution.
LOGINVReturns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard deviation.
LOGNORMDISTReturns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard deviation.
MODEReturns the most frequently occuring, or repetitive, number in an array or range of data.
NEGBINOMDISTReturns the negative binomial distribution.
NORMDISTReturns the normal cumulative distribution for the specified mean and standard deviation.
NORMINVReturns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORMSDISTReturns the standard normal cumulative distribution function.
PERCENTILEReturns the k-th percentile of values in a range.
PERCENTRANKReturns the rank of a value in a data set set as a percentage of the data set.
POISSONReturns the Poisson distribution.
QUARTILEReturns the quartile of a data set.
RANKReturns the rank of a number in a list of numbers.
STDEVEstimates standard deviation based on a sample.
STDEVPEstimates standard deviation based on a sample assuming that the arguments represent the total population.
TDISTReturns 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.
TINVReturns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.
TTESTThe probability associated with t-test.
VARReturns an estimate for the variance of a population based on a sample data set.
VARPCalculates variance based on the entire population.
WEIBULLReturns the Weibull distribution.
ZTESTReturns the two-tailed P-value of a z-test.
Statistical Functions
AVEDEVRetuns the average of the absolute deviations of data points from their mean.
AVERAGEReturns the average of its arguments.
AVERAGEAReturns the average of the values in its list of arguments including text and logical values.
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFSReturns the average (arithmetic mean) of all cells that meet multiple criteria
BETA.DISTReturns the beta cumulative distribution function
BETA.INVReturns the inverse of the cumulative distribution function for a specified beta distribution
BINOM.DISTReturns the individual term binomial distribution probability
BINOM.INVReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
CHISQ.DISTReturns the chi-squared distribution
CHISQ.DIST.RTReturns the one-tailed probability of the chi-squared distribution
CHISQ.INVReturns the inverse of the left-tailed probability of the chi-squared distribution
CHISQ.INV.RTReturns the inverse of the right-tailed probability of the chi-squared distribution
CHISQ.TESTReturns the test for independence.
CONFIDENCE.NORMReturns the confidence interval for a population mean.
CONFIDENCE.TReturns the confidence interval for a population mean, using a Student's t distribution
CORRELReturns the correlation coefficient between two data sets.
COUNTCounts the number of cells that contain numbers (including dates and formulas that evaluate to numbers) within the list of arguments.
COUNTACounts the number of cells that are not empty.
COUNTBLANKCounts the empty cells in a specified range.
COUNTIFCounts the number of cells in a range that meet a given criteria.
COUNTIFSCounts the number of cells within a range that meet multiple criteria
COVARIANCE.PReturns covariance, the average of the products of paired deviations
COVARIANCE.SReturns the sample covariance, the average of the products deviations for each data point pair intwo data sets
CRITBINOMReturns the minimum number yields a binomial distribution less than or equal to the specified criteria
DEVSQReturns the sum of the squares of deviations of a data set from their sample mean.
EXPON.DISTReturns the exponential distribution.
F.DISTReturns the F probability distribution.
F.DIST.RTReturns the (right-tailed) F probability distribution (degree of diversity) for two data sets
F.INVReturns the inverse of the F probability distribution
F.INV.RTReturnd the inverse of the (right-tailed) F probability distribution
F.TESTReturns the result of an F-test.
FISHERReturns the Fisher transformation at x.
FISHERINVReturns the inverse of the Fisher transformation at y.
FORECASTCalculates or predicts a future value by using existing values.
FREQUENCYCalculates how often values occur within a range of values and then returns a vertical array of numbers.
GAMMA.DISTReturns the gamma distribution.
GAMMA.INVReturns the inverse of the gamma cumulative distribution.
GAMMALNReturns the natural logarithm of the gamma function.
GAMMALN.PRECISEReturns the natural logarithm of the gamma function.
GEOMEANReturns the geometric mean of an array or range of positive data.
GROWTHCalculates predicted exponential growth by using existing data.
HARMEANReturns the harmonic mean of a data set.
HYPGEOM.DISTReturns the hypergeometric distribution.
HYPGEOMDISTReturns the hypergeometric distribution.
INTERCEPTCalculates the point at which a line will intersect the y-axis by using existing x and y values.
KURTReturns the Kurtosis of a data set.
LARGEReturns the k-th largest value in a data set.
LINESTCalculates a straight line that best fits your data using the least squares method.
LOGESTCalculates an exponential curve that fits your data and returns an array of values that describes the curve.
LOGNORM.DISTReturns the lognormal distribution, of x, where ln(x) is normally distributed with mean and standard deviation.
LOGNORM.INVReturns the inverse of the lognormal cumulative distribution.
MAXReturns the largest value in a set of values.
MAXAReturns the largest value in a set of values including text and logical values.
MEDIANReturns the median of the given numbers.
MINReturns the smallest value in a set of values.
MINAReturns the smallest value in a set of values including text and logical values.
MODE.MULTReturns a vertical array of the most frequestly occurring, or repetitive values in an array or range of data.
MODE.SNGLReturns the most common value in a data set.
NEGBINOM.DISTReturns the negative binomial distribution.
NORM.DISTReturns the normal cumulative distribution.
NORM.INVReturns the inverse of the normal cumulative distribution.
NORM.S.DISTReturn the standard normal cumulative distribution.
NORM.S.INVReturns the inverse of the standard normal cumulative distribution.
NORMSINVReturns the inverse of the standard normal cumulative distribution function.
PEARSONReturns 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.EXCReturns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INCReturns the k-th percentile of values in a range.
PERCENTRANK.EXCReturns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERCENTRANK.INCReturns the percentage rank of a value in a data set
PERMUTReturns the number of permutations for a given number of objects that can be selected from a range of numbers.
POISSON.DISTReturns the Poisson distribution.
PROBReturns the probability that values in a range are between two specified limits.
QUARTILE.EXCReturns the quartile of the data set, based on percentile values fro 0..1, exclusive.
QUARTILE.INCReturns the quartile of a data set.
RANK.AVGReturns the rank of a number in a list of numbers.
RANK.EQReturns the rank of a number in a list of numbers.
RSQReturns the r^2 value of a linear regression line.
SKEWReturns the skew of a distribution.
SLOPEReturns the slope of a regression line through data points in KNOWN_Y'S and KNOWN_X'S.
SMALLReturns the k-th smallest value in a data set.
STANDARDIZEReturns a normalized value from a distribution characterized by MEAN and STANDARD_DEV.
STDEV.PCalculates standard deviation based on the entire population
STDEV.SEstimates standard deviation based on a sample.
STDEVAEstimates standard deviation based on a sample. Includes text and logical values.
STDEVPAEstimates standard deviation based on a sample assuming that the arguments represent the total population. Includes text and logical values.
STEYXReturns the standard error of the predicted y value for each x in the regression.
T.DISTReturns the percentage points (probability) for the student t-distribution.
T.DIST.2TReturns the percentage points (probability) for the student t-distribution.
T.DIST.RTReturns the Student's t-distribution.
T.INVReturns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.
T.INV.2TReturns the inverse of the Student's t-distribution.
T.TESTReturns the probability associated with a Student's t-test.
TRENDReturns the y-values along a linear trendline that best fits the values in a data set.
TRIMMEANReturns the mean of the interior of a data set.
VAR.PCalculates variance based on the entire population
VAR.SEstimates variance based on a sample.
VARAReturns an estimate for the variance of a population based on a sample data set and may include text or logical values.
VARPACalculates variance based on the entire population and may include text or logical values.
WIEBULL.DISTReturns the Weibull distribution.
Z.TESTReturns the one-tailed probability-value of a z-test.
Text Functions
CHARReturns the character specified by a number.
CLEANRemoves all nonprintable characters from text.
CODEReturns a numeric code from the first character in a text string. The opposite of the CHAR function.
CONCATENATEJoins several text strings into one text string.
DOLLARConverts a number to text using Currency format, with the decimals rounded to the specified place.
EXACTCompares two text strings and returns TRUE if they are exactly the same, and FALSE otherwise.
FINDLocates one text string within another text string, and returns the number of the starting position of of FIND_TEXT from the leftmost character of WITHIN_TEXT.
FINDBReturns 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.
FIXEDRounds 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.
LEFTReturns the first character(s) in a text string.
LEFTBReturns the first character(s) in a text string based on a specified number of bytes
LENReturns the number of characters in a text string.
LENBReturns the number of characters in a text string expressed in bytes.
LOWERConverts all letters in a text string to lowercase.
MIDReturns a specific number of characters from a text string starting at the position you specify.
MIDBReturns a group of characters based on a specified number of bytes from a text string starting at the position you specify.
PROPERCapitalizes the first letter of each word in a text string or sentence.
REPLACEReplaces part of a text string with a different text string based on the number of characters you specify.
REPLACEBReplaces part of a text string with a different text string based on the number of characters you specify in terms of bytes.
REPTRepeats specified text a given number of times.
RIGHTReturns the last character(s) in a text string.
RIGHTBReturns the last character(s) in a text string based on a specified number of bytes.
SEARCHReturns the number of the character at which a specific character or text string is first found, reading from left to right.
SEARCHBReturns the number of the character at which a specific character or text string is first found in bytes, reading from left to right.
SUBSTITUTESubstitutes NEW_TEXT for OLD_TEXT in a string.
TReturns the text referred to by a value.
TEXTConverts a value to text in a specific number format.
TRIMRemoves all spaces from text except single spaces between words.
UPPERConverts text to uppercase.
USDOLLARConverts a number to text using US Dollar format, with the decimals rounded to the specified place.
VALUEConverts a text string that represents a number to a number.