Useful Spreadsheet Functions (Excel & Google Sheets)
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
Still loading...
ABCDEFG
1
CategoryE- Excel / G-GoogleFunctionUsageExcel DescriptionGoogle Descriptionhttp://tiny.cc/sseg
2
ArrayEGTRANSPOSETRANSPOSE(array_or_range)Returns the transpose of an arrayTransposes the rows and columns of an array or range of cells.
3
Date &
Time
GDATEDIFDATEDIF(start_date, end_date, unit)Calculates the number of days, months, or years between two dates.
4
Date &
Time
EGDATEVALUEDATEVALUE(date_string)Converts a date in the form of text to a serial numberConverts a provided date string in a known format to a date value.
5
Date &
Time
EGDAYDAY(date)Converts a serial number to a day of the monthReturns the day of the month that a specific date falls on, in numeric format.
6
Date &
Time
EGDAYS360DAYS360(start_date, end_date, method)Calculates the number of days between two dates based on a 360-day yearReturns the difference between two days based on the 360 day year used in some financial interest calculations.
7
Date &
Time
EGEDATEEDATE(start_date)Returns the serial number of the date that is the indicated number of months before or after the start dateReturns a date a specified number of months before or after another date.
8
Date &
Time
EGNETWORKDAYSNETWORKDAYS(start_date, end_date, holidays)Returns the number of whole workdays between two datesReturns the number of net working days between two provided days.
9
Date &
Time
EGNOWNOW()Returns the serial number of the current date and timeReturns the current date and time as a date value.
10
Date &
Time
EGTIMETIME(hour, minute, second)Returns the serial number of a particular timeConverts a provided hour, minute, and second into a time.
11
Date &
Time
EGTIMEVALUETIMEVALUE(time_string)Converts a time in the form of text to a serial numberReturns the fraction of a 24-hour day the time represents. Only available in the new Google Sheets.
12
Date &
Time
EGTODAYTODAY()Returns the serial number of today's dateReturns the current date as a date value. 7/25/2017
13
Date &
Time
EGWEEKDAYWEEKDAY(date, type)Converts a serial number to a day of the weekReturns a number representing the day of the week of the date provided.
14
Date &
Time
EGWEEKNUMWEEKNUM(date, [type])Converts a serial number to a number representing where the week falls numerically with a yearReturns a number representing the week of the year where the provided date falls. Only available in the new Google Sheets.
15
Date &
Time
EGWORKDAYWORKDAY(start_date, num_days, holidays)Returns the serial number of the date before or after a specified number of workdaysCalculates the number of working days from a specified start date.
16
Date &
Time
EGYEARYEAR(date)Converts a serial number to a yearReturns the year specified by a given date.
17
Date &
Time
EGYEARFRACYEARFRAC(start_date, end_date, day_count_convention)Returns the year fraction representing the number of whole days between start_date and end_dateReturns the number of years, including fractional years, between two dates using a specified day count convention.
18
FilterGFILTERFILTER(range, condition1, condition2)Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions.
19
FilterGSORTSORT(range, sort_column, is_ascending, sort_column2, is_ascending2)Sorts the rows of a given array or range by the values in one or more columns.
20
FilterGUNIQUEUNIQUE(range)Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.
21
GoogleGARRAYFORMULAARRAYFORMULA(array_formula)Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
22
GoogleGIMAGEIMAGE(url, mode)Inserts an image into a cell.
23
GoogleGIMPORTHTMLIMPORTHTML(url, query, index)Imports data from a table or list within an HTML page.
24
GoogleGIMPORTRANGEIMPORTRANGE(spreadsheet_key, range_string)Imports a range of cells from a specified spreadsheet.
25
GoogleGIMPORTXMLIMPORTXML(url, xpath_query)Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
26
GoogleGQUERYQUERY(data, query, headers)Runs a Google Visualization API Query Language query across data.
27
GoogleGSPARKLINESPARKLINE(data, options)Creates a miniature chart contained within a single cell.
28
InformationEGCELLCELL(info_type, reference)Returns information about the formatting, location, or contents of a cellReturns the requested information about the specified cell. Only available in the new Google Sheets.
29
InformationEGERROR.TYPEERROR.TYPE(reference)Returns a number corresponding to an error typeReturns a number corresponding to the error value in a different cell.
30
InformationEGISBLANKISBLANK(value)Returns TRUE if the value is blankChecks whether the referenced cell is empty.
31
InformationGISEMAILISEMAIL(value)Checks whether a value is a valid email address. Only available in the new Google Sheets.
32
InformationEGISERRISERR(value)Returns TRUE if the value is any error value except #N/AChecks whether a value is an error other than `#N/A`.
33
InformationEGISERRORISERROR(value)Returns TRUE if the value is any error valueChecks whether a value is an error.
34
InformationEGISLOGICALISLOGICAL(value)Returns TRUE if the value is a logical valueChecks whether a value is `TRUE` or `FALSE`.
35
InformationEGISNAISNA(value)Returns TRUE if the value is the #N/A error valueChecks whether a value is the error `#N/A`.
36
InformationEGISNONTEXTISNONTEXT(value)Returns TRUE if the value is not textChecks whether a value is non-textual.
37
InformationEGISNUMBERISNUMBER(value)Returns TRUE if the value is a numberChecks whether a value is a number.
38
InformationEGISREFISREF(value)Returns TRUE if the value is a referenceChecks whether a value is a valid cell reference.
39
InformationEGISTEXTISTEXT(value)Returns TRUE if the value is textChecks whether a value is text.
40
InformationGISURLISURL(value)Checks whether a value is a valid URL. Only available in the new Google Sheets.
41
InformationEGTYPETYPE(value)Returns a number indicating the data type of a valueReturns a number associated with the type of data passed into the function. Only available in the new Google Sheets.
42
LogicalEGADDRESSADDRESS(row, column, absolute_relative_mode, use_a1_notation, sheet)Returns a reference as text to a single cell in a worksheetReturns a cell reference as a string.
43
LogicalEGANDAND(logical_expression1, logical_expression2)Returns TRUE if all of its arguments are TRUEReturns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
44
LogicalEGIFIF(logical_expression, value_if_true, value_if_false)Specifies a logical test to performReturns one value if a logical expression is `TRUE` and another if it is `FALSE`.
45
LogicalEGIFERRORIFERROR(value, value_if_error)Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formulaReturns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
46
LogicalEGNOTNOT(logical_expression)Reverses the logic of its argumentReturns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`.
47
LogicalEGOROR(logical_expression1, logical_expression2)Returns TRUE if any argument is TRUEReturns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.
48
Lookup & ReferenceEGCHOOSECHOOSE(index, choice1, choice2)Chooses a value from a list of valuesReturns an element from a list of choices based on index.
49
Lookup & ReferenceEGCOLUMNCOLUMN(cell_reference)Returns the column number of a referenceReturns the column number of a specified cell, with `A=1`.
50
Lookup & ReferenceEGCOLUMNSCOLUMNS(range)Returns the number of columns in a referenceReturns the number of columns in a specified array or range.
51
Lookup & ReferenceEGHLOOKUPHLOOKUP(search_key, range, index, is_sorted)Looks in the top row of an array and returns the value of the indicated cellHorizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
52
Lookup & ReferenceEGHYPERLINKHYPERLINK(url, link_label)Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the InternetCreates a hyperlink inside a cell.
53
Lookup & ReferenceEGINDEXINDEX(reference, row, column)Uses an index to choose a value from a reference or arrayReturns the content of a cell, specified by row and column offset.
54
Lookup & ReferenceEGLOOKUPLOOKUP(search_key, search_range|search_result_array, [result_range])Looks up values in a vector or arrayLooks through a row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column. Only available in the new Google Sheets.
55
Lookup & ReferenceEGMATCHMATCH(search_key, range, search_type)Looks up values in a reference or arrayReturns the relative position of an item in a range that matches a specified value.
56
Lookup & ReferenceEGOFFSETOFFSET(cell_reference, offset_rows, offset_columns, height, width)Returns a reference offset from a given referenceReturns a range reference shifted a specified number of rows and columns from a starting cell reference.
57
Lookup & ReferenceEGROWROW(cell_reference)Returns the row number of a referenceReturns the row number of a specified cell.
58
Lookup & ReferenceEGROWSROWS(range)Returns the number of rows in a referenceReturns the number of rows in a specified array or range.
59
Lookup & ReferenceEGVLOOKUPVLOOKUP(search_key, range, index, is_sorted)Looks in the first column of an array and moves across the row to return the value of a cellVertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
60
Math/ StatisticalEGCOUNTBLANKCOUNTBLANK(range)Counts the number of blank cells within a rangeReturns the number of empty cells in a given range.
61
Math/ StatisticalEGCOUNTIFCOUNTIF(range, criterion)Counts the number of cells within a range that meet the given criteriaReturns a conditional count across a range.
62
Math/ StatisticalEGCOUNTIFSCOUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])Counts the number of cells within a range that meet multiple criteriaReturns the count of a range depending on multiple criteria. Only available in thenew Google Sheets.
63
Math/ StatisticalGCOUNTUNIQUECOUNTUNIQUE(value1, value2)Counts the number of unique values in a list of specified values and ranges.
64
Math/ StatisticalEGEVENEVEN(value)Rounds a number up to the nearest even integerRounds a number up to the nearest even integer.
65
Math/ StatisticalEGFLOORFLOOR(value, factor)Rounds a number down, toward zeroRounds a number down to the nearest integer multiple of specified significance.
66
Math/ StatisticalEGISEVENISEVEN(value)Returns TRUE if the number is evenChecks whether the provided value is even.
67
Math/ StatisticalEGISODDISODD(value)Returns TRUE if the number is oddChecks whether the provided value is odd.
68
Math/ StatisticalEGODDODD(value)Rounds a number up to the nearest odd integerRounds a number up to the nearest odd integer.
69
Math/ StatisticalEGPRODUCTPRODUCT(factor1, factor2)Multiplies its argumentsReturns the result of multiplying a series of numbers together.
70
Math/ StatisticalEGRANDRAND()Returns a random number between 0 and 1Returns a random number between 0 inclusive and 1 exclusive.
71
Math/ StatisticalEGRANDBETWEENRANDBETWEEN(low, high)Returns a random number between the numbers you specifyReturns a uniformly random integer between two values, inclusive.
72
Math/ StatisticalEGROUNDROUND(value, places)Rounds a number to a specified number of digitsRounds a number to a certain number of decimal places according to standard rules.
73
Math/ StatisticalEGROUNDDOWNROUNDDOWN(value, places)Rounds a number down, toward zeroRounds a number to a certain number of decimal places, always rounding down to the next valid increment.
74
Math/ StatisticalEGROUNDUPROUNDUP(value, places)Rounds a number up, away from zeroRounds a number to a certain number of decimal places, always rounding up to the next valid increment.
75
Math/ StatisticalEGSUBTOTALSUBTOTAL(function_code, range1, range2)Returns a subtotal in a list or databaseReturns a subtotal for a vertical range of cells using a specified aggregation function.
76
Math/ StatisticalEGSUMSUM(value1, value2)Adds its argumentsReturns the sum of a series of numbers and/or cells.
77
Math/ StatisticalEGSUMIFSUMIF(range, criterion, sum_range)Adds the cells specified by a given criteriaReturns a conditional sum across a range.
78
Math/ StatisticalEGSUMIFSSUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])Adds the cells in a range that meet multiple criteriaReturns the sum of a range depending on multiple criteria. Only available in thenew Google Sheets.
79
ParserGSPLITSPLIT(text, delimiter, split_by_each)TEXT TO COLUMNSDivides text around a specified character or string, and puts each fragment into a separate cell in the row. (TEXT TO COLUMNS, PARSE)
80
ParserGCONVERTCONVERT(value, start_unit, end_unit)Converts a numeric value to a different unit of measure.
81
ParserGTO_DATETO_DATE(value)Converts a provided number to a date.
82
ParserGTO_DOLLARSTO_DOLLARS(value)Converts a provided number to a dollar value.
83
ParserGTO_PERCENTTO_PERCENT(value)Converts a provided number to a percentage.
84
ParserGTO_PURE_NUMBERTO_PURE_NUMBER(value)Converts a provided date/time, percentage, currency or other formatted numeric value to a pure number without formatting.
85
Paste SpecialEGPS- Values OnlyIf imported or copied data ends up as text in an Excel worksheet it can create problems if that data is used in calculations involving functions. Paste special offers a quick and easy way to correct the problem by converting the text back to number data. Pastes only the text contained in the original range of cells.
86
Paste SpecialGCtrl+Shift+VMatch destination formatting: Formats the pasted data to match the formatting already existing in the new location.Paste Text Without Formatting in Google Chrome. (Will mimic all ready in place text style.)
87
Paste SpecialEPS- Values and Number FormatsValues and Number Formatting: Formatting is preserved only for numeric values, including dates. In addition, only formula results (see "Values Only") are pasted. Text formatting is removed.
88
Paste SpecialEGPS- Formats OnlyFormatting only: Pastes only the formatting, leaving all values and formulas out of the pasted range.
This option is identical to using the paint format tool -- it only copies cell formatting, and won't disrupt any existing text or formulas.
89
Paste SpecialGPS- Conditional Formatting OnlyThis option only applies conditional formatting rules to a range of cells.
90
Paste SpecialEGPS- Formulas OnlyMatch destination formatting: Formats the pasted data to match the formatting already existing in the new location.Pastes the formulas contained in a copied range of cells, not the resulting calculations of the formulas.
91
Paste SpecialEPS- Formulas and number formatsKeep source formatting: Preserves all original formatting of the pasted selection. Preserves all formulas as well.
92
Paste SpecialEPS- Column widthsKeep source column widths: Preserves the column widths as well as all formulas and formatting.
93
Paste SpecialEGPS- Validation OnlyPastes data validation rules for the copied cells to the paste area.Pastes a data validation rule over a range of cells without disrupting any existing formatting, formulas or text.
94
Paste SpecialEGPS- All Except BordersPastes all of these options without any cell borders that have been added.
95
Paste SpecialEGPS- TansposeTo transpose data, in spreadsheet talk, means to switch the layout of data located in rows to columns and data in columns to rows.This option allows you to paste a rotated version of the copied cells. For example, if you copy a column of cells and use paste transpose, it will paste them into a row, and vice versa. 
96
Paste SpecialEPS- Skip BlanksAvoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.
97
Paste
Link
EPaste Link Lets you create a link between areas of data that will update when the source data changes. This linking can also be done for multiple copies of the same Excel chart located in different files. Link data located on separate worksheets or in separate Excel files using Paste Link. When the source file changes, the destination file will update as well.
98
StatisticalEGAVERAGEAVERAGE(value1, value2)Returns the average of its argumentsReturns the numerical average value in a dataset, ignoring text.
99
StatisticalEGAVERAGEIFAVERAGEIF(criteria_range, criterion, [average_range])Returns the average (arithmetic mean) of all the cells in a range that meet a given criteriaReturns the average of a range depending on criteria. Only available in the new Google Sheets
100
StatisticalEGAVERAGEIFSAVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])Returns the average (arithmetic mean) of all cells that meet multiple criteria.Returns the average of a range depending on multiple criteria. Only available in thenew Google Sheets
Loading...
 
 
 
Functions