Articles with the tag:
Close
Changelog
Close
Articles with the tag:
Close

Insert function

The ability to perform basic calculations is the principal reason for using a spreadsheet. Some of them are performed automatically when you select a range of cells in your spreadsheet:

  • AVERAGE is used to analyze the selected range of cells and find the average value.
  • COUNT is used to count the number of the selected cells containing values ignoring empty cells.
  • MIN is used to analyze the range of data and find the smallest number.
  • MAX is used to analyze the range of data and find the largest number.
  • SUM is used to add all the numbers in the selected range ignoring empty cells or those contaning text.

The results of these calculations are displayed in the right lower corner at the status bar.

Basic Calculations

To perform any other calculations you can insert a needed formula manually using the common mathematical operators or insert a predefined formula - Function.

The possibilities to work with Functions are accessible from both Home and Formula tab. At the Home tab, you can use the Insert function Insert function icon button to add one of commonly used functions (SUM, MIN, MAX, COUNT) or open the Insert Function window that contains all available functions classified by category.

Formula tab

At the Formula tab you can use the following buttons:

  • Function - to open the Insert Function window that contains all available functions classified by category.
  • Autosum - to quickly access the SUM, MIN, MAX, COUNT functions. When you select a functions from this group, it automatically performs calculations for all cells in the column above the selected cell so that you don't need to enter arguments.
  • Recently used - to quickly access 10 recently used functions.
  • Financial, Logical, Text and data, Date and time, Lookup and references, Math and trigonometry - to quickly access functions that belongs to the corresponding categories.
  • More functions - to access the functions from the following groups: Database, Engineering, Information and Statistical.

To insert a function,

  1. select a cell you wish to insert a function into,
  2. proceed in one of the following ways:
    • switch to the Formula tab and use the buttons available at the top toolbar to access a function from a specific group, or use the Additional option from the menu to open the Insert Function window;
    • switch to the Home tab, click the Insert function Insert function icon icon, select one of the commonly used functions (SUM, MIN, MAX, COUNT) or click the Additional option,
    • right-click within a selected cell and select the Insert Function option from the contextual menu,
    • click the Function icon icon before the formula bar,
  3. in the Insert Function window that opens, select the necessary function group, then choose the function you need from the list and click OK.
  4. enter the function arguments either manually or dragging to select a range of cells to be included as an argument. If the function requires several arguments, they must be separated by commas.

    Note: generally, numeric values, logical values (TRUE, FALSE), text values (must be quoted), cell references, cell range references, names assigned to ranges and other functions can be used as function arguments.

  5. Press the Enter key.

To enter a function manually using the keyboard,

  1. select a cell,
  2. enter the equal sign (=)

    Each formula must begin with the equal sign (=).

  3. enter the function name

    Once you type the initial letters, the Formula Autocomplete list will be displayed. As you type, the items (formulas and names) that match the entered characters are displayed in it. If you hover the mouse pointer over a formula, a tooltip with the formula description will be displayed. You can select the necessary formula from the list and insert it by clicking it or pressing the Tab key.

  4. enter the function arguments

    Arguments must be enclosed into parentheses. The opening parenthesis '(' is added automatically if you select a function from the list. When you enter arguments, a tooltip that contains the formula syntax is also displayed.

    Function tooltip

  5. when all the agruments are specified, enter the closing parenthesis ')' and press Enter.

Here is the list of the available functions grouped by categories:

Function Category Description Functions
Text and Data Functions Are used to correctly display the text data in your spreadsheet. ASC; CHAR; CLEAN; CODE; CONCATENATE; CONCAT; DOLLAR; EXACT; FIND; FINDB; FIXED; LEFT; LEFTB; LEN; LENB; LOWER; MID; MIDB; NUMBERVALUE; PROPER; REPLACE; REPLACEB; REPT; RIGHT; RIGHTB; SEARCH; SEARCHB; SUBSTITUTE; T; TEXT; TEXTJOIN; TRIM; UNICHAR; UNICODE; UPPER; VALUE
Statistical Functions Are used to analyze data: finding the average value, the largest or smallest values in a range of cells. AVEDEV; AVERAGE; AVERAGEA; AVERAGEIF; AVERAGEIFS; BETADIST; BETA.DIST; BETA.INV; BETAINV; BINOMDIST; BINOM.DIST; BINOM.DIST.RANGE; BINOM.INV; CHIDIST; CHIINV; CHISQ.DIST; CHISQ.DIST.RT; CHISQ.INV; CHISQ.INV.RT; CHITEST; CHISQ.TEST; CONFIDENCE; CONFIDENCE.NORM; CONFIDENCE.T; CORREL; COUNT; COUNTA; COUNBLANK; COUNTIF; COUNTIFS; COVAR; COVARIANCE.P; COVARIANCE.S; CRITBINOM; DEVSQ; EXPON.DIST; EXPONDIST; F.DIST; FDIST; F.DIST.RT; F.INV; FINV; F.INV.RT; FISHER; FISHERINV; FORECAST; FORECAST.ETS; FORECAST.ETS.CONFINT; FORECAST.ETS.SEASONALITY; FORECAST.ETS.STAT; FORECAST.LINEAR; FREQUENCY; FTEST; F.TEST; GAMMA; GAMMA.DIST; GAMMADIST; GAMMA.INV; GAMMAINV; GAMMALN; GAMMALN.PRECISE; GAUSS; GEOMEAN; HARMEAN; HYPGEOMDIST; HYPGEOM.DIST; INTERCEPT; KURT; LARGE; LOGINV; LOGNORM.DIST; LOGNORM.INV; LOGNORMDIST; MAX; MAXA; MAXIFS; MEDIAN; MIN; MINA; MINIFS; MODE; MODE.MULT; MODE.SNGL; NEGBINOMDIST; NEGBINOM.DIST; NORMDIST; NORM.DIST; NORMINV; NORM.INV; NORMSDIST; NORM.S.DIST; NORMSINV; NORM.S.INV; PEARSON; PERCENTILE; PERCENTILE.EXC; PERCENTILE.INC; PERCENTRANK; PERCENTRANK.EXC; PERCENTRANK.INC; PERMUT; PERMUTATIONA; PHI; POISSON; POISSON.DIST; PROB; QUARTILE; QUARTILE.EXC; QUARTILE.INC; RANK; RANK.AVG; RANK.EQ; RSQ; SKEW; SKEW.P; SLOPE; SMALL; STANDARDIZE; STDEV; STDEV.S; STDEVA; STDEVP; STDEV.P; STDEVPA; STEYX; TDIST; T.DIST; T.DIST.2T; T.DIST.RT; T.INV; T.INV.2T; TINV; TRIMMEAN; TTEST; T.TEST; VAR; VARA; VARP; VAR.P; VAR.S; VARPA; WEIBULL; WEIBULL.DIST; ZTEST; Z.TEST
Math and Trigonometry Functions Are used to perform basic math and trigonometry operations such as adding, multiplying, dividing, rounding, etc. ABS; ACOS; ACOSH; ACOT; ACOTH; AGGREGATE; ARABIC; ASIN; ASINH; ATAN; ATAN2; ATANH; BASE; CEILING; CEILING.MATH; CEILING.PRECISE; COMBIN; COMBINA; COS; COSH; COT; COTH; CSC; CSCH; DECIMAL; DEGREES; ECMA.CEILING; EVEN; EXP; FACT; FACTDOUBLE; FLOOR; FLOOR.PRECISE; FLOOR.MATH; GCD; INT; ISO.CEILING; LCM; LN; LOG; LOG10; MDETERM; MINVERSE; MMULT; MOD; MROUND; MULTINOMIAL; ODD; PI; POWER; PRODUCT; QUOTIENT; RADIANS; RAND; RANDBETWEEN; ROMAN; ROUND; ROUNDDOWN; ROUNDUP; SEC; SECH; SERIESSUM; SIGN; SIN; SINH; SQRT; SQRTPI; SUBTOTAL; SUM; SUMIF; SUMIFS; SUMPRODUCT; SUMSQ; SUMX2MY2; SUMX2PY2; SUMXMY2; TAN; TANH; TRUNC
Date and Time Functions Are used to correctly display date and time in your spreadsheet. DATE; DATEDIF; DATEVALUE; DAY; DAYS; DAYS360; EDATE; EOMONTH; HOUR; ISOWEEKNUM; MINUTE; MONTH; NETWORKDAYS; NETWORKDAYS.INTL; NOW; SECOND; TIME; TIMEVALUE; TODAY; WEEKDAY; WEEKNUM; WORKDAY; WORKDAY.INTL; YEAR; YEARFRAC
Engineering Functions Are used to perform some engineering calculations: converting between different bases, finding complex numbers etc. BESSELI; BESSELJ; BESSELK; BESSELY; BIN2DEC; BIN2HEX; BIN2OCT; BITAND; BITLSHIFT; BITOR; BITRSHIFT; BITXOR; COMPLEX; CONVERT; DEC2BIN; DEC2HEX; DEC2OCT; DELTA; ERF; ERF.PRECISE; ERFC; ERFC.PRECISE; GESTEP; HEX2BIN; HEX2DEC; HEX2OCT; IMABS; IMAGINARY; IMARGUMENT; IMCONJUGATE; IMCOS; IMCOSH; IMCOT; IMCSC; IMCSCH; IMDIV; IMEXP; IMLN; IMLOG10; IMLOG2; IMPOWER; IMPRODUCT; IMREAL; IMSEC; IMSECH; IMSIN; IMSINH; IMSQRT; IMSUB; IMSUM; IMTAN; OCT2BIN; OCT2DEC; OCT2HEX
Database Functions Are used to perform calculations for the values in a certain field of the database that correspond to the specified criteria. DAVERAGE; DCOUNT; DCOUNTA; DGET; DMAX; DMIN; DPRODUCT; DSTDEV; DSTDEVP; DSUM; DVAR; DVARP
Financial Functions Are used to perform some financial calculations calculating the net present value, payments etc. 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; PDURATION; PMT; PPMT; PRICE; PRICEDISC; PRICEMAT; PV; RATE; RECEIVED; RRI; SLN; SYD; TBILLEQ; TBILLPRICE; TBILLYIELD; VDB; XIRR; XNPV; YIELD; YIELDDISC; YIELDMAT
Lookup and Reference Functions Are used to easily find the information from the data list. ADDRESS; CHOOSE; COLUMN; COLUMNS; FORMULATEXT; HLOOKUP; HYPERLINLK; INDEX; INDIRECT; LOOKUP; MATCH; OFFSET; ROW; ROWS; TRANSPOSE; VLOOKUP
Information Functions Are used to give you the information about the data in the selected cell or a range of cells. ERROR.TYPE; ISBLANK; ISERR; ISERROR; ISEVEN; ISFORMULA; ISLOGICAL; ISNA; ISNONTEXT; ISNUMBER; ISODD; ISREF; ISTEXT; N; NA; SHEET; SHEETS; TYPE
Logical Functions Are used to check if a condition is true or false. AND; FALSE; IF; IFERROR; IFNA; IFS; NOT; OR; SWITCH; TRUE; XOR
Return to previous page
Try now for free Try and make your decision No need to install anything
to see all the features in action