SELECT

Description

Selects data from a workbook range, a defined name, or a Workday report. The SELECT function is similar to an SQL SELECT statement. The primary rules for writing a SELECT statement are: Data that you reference in a select statement must have a header name. Surround the select statement with double quotes. Surround strings with single quotes. If you reference a column heading or table name that contains spaces or special characters, enclose it in backticks. Use question marks for parameters. If the SELECT formula doesn't finish calculating within 30 minutes, it returns an #ERROR. SELECT can process up to 5 million rows, but the number of rows processed might not exactly match the number of returnable rows due to the aggregation that SELECT performs. To see working examples of the SELECT function, find the "Worksheets Function Example Workbooks" page on Community or paste this link into your browser: https://community.workday.com/node/408872.

Keep in mind that the SELECT function is not intended as a replacement for the Data Wizard.

Syntax

SELECT(

select_statement

, [

parameter

], ...)

select_statement

: The select statement. The statement is similar to an SQL select statement. Enclose the select statement in double quotes. The basic format is:

"SELECT column1, [column2], ... FROM table"

where

column

is the data to return and

table

is the data source to select from.

You can embed functions in the SELECT statement such as COUNT, AVG, SUM, LIMIT, and more. The complete list of functions that SELECT supports is below.

When doing an operation on date values in your SELECT formulas, keep in mind that Worksheets works differently from SQL syntax. Worksheets calculations treat dates as serial numbers. Example: The date March 20, 2023 is represented as the serial number 45005. Also, SQL automatically converts string dates for calculations, but Worksheets doesn't.

If the string from
or where
exists in a column name, you must surround it with quotes because from and where are reserved words in SQL.

In the FROM clause of the statement, you can use values such as a:

Workday report.

Parameter that you specify as an argument.

parameter

: A statement parameter.

Examples

We'll use this table to show some simple examples:

header1	header2	header3
1	a	red
2	b	orange
3	c	orange
4	d	red
5	e	red

The formula:

=SELECT("SELECT header1,header3 FROM ? WHERE header3 = ?",A1:C6,"red")

has this result:

header1	header3
1	red
4	red
5	red

Automatic recalculation and the parameter argument.
We recommend using the parameter argument of the function to reference data; Worksheets considers these references to be data dependencies, so the SELECT formula runs automatically when you change the data in the referenced cells. If you use the SELECT statement to refer to data instead, the formula doesn't re-run automatically if the data changes, and even selecting Data > Recalculate doesn't cause the formula to run again.

In the following formula, we use the parameter argument to reference the data. If the data changes in the range A1:C6, Worksheets automatically recalculates and updates the result:

=SELECT("SELECT header1,header3 FROM ?",A1:C6)

Defined name
. In this example, an area was given the defined name all_employees:

=SELECT("SELECT employeeName FROM ?", all_employees)

Column, row, or range
. You can refer to a column, row, or area in the same sheet, a different sheet, or in another workbook. If you don't specify a sheet, the range must be on the current sheet.

=SELECT("SELECT employeeName FROM ?",$A:$G)

=SELECT("SELECT employeeName FROM ?",'All Employees Sheet'!$A:$G)

=SELECT("SELECT employeeName FROM ?",All_Employees_Sheet!$A:$G)

=SELECT("SELECT employeeName FROM ?", !'Other Workbook Name'!'All Employees Sheet'!A:G)

More Examples and Results

Formula	Result
SELECT("SELECT employeeID, employeeName FROM ?”, AllEmployeesSheet!$A:$G)	Returns the employeeID and employeeName from columns A through G of the sheet named AllEmployeesSheet.
SELECT("SELECT COUNT(employeeLastName) FROM ?”, AllEmployeesSheet)	Returns the number of rows in the employeeLastName column in the sheet named AllEmployeesSheet.
SELECT("SELECT employeeFirstName, employeeLastName, employeeRating FROM ? ORDER BY employeeRating DESC LIMIT 3", AllEmployeesSheet!$A:$G)	Return the first name, last name, and rating of the 3 highest rated employees.

Workday report
. Query data from a Workday report.

=SELECT("SELECT employeeName FROM WorkdayReports.AllEmployees") where the report name is AllEmployees.

=SELECT("SELECT employeeName FROM WorkdayReports.`All Employees`") where the report name is All Employees.

=SELECT("SELECT * from WorkdayReports.`All Employees`") selects all rows from the report.

Notes

We recommend using the parameter section of the function to reference data; Worksheets considers these references to be data dependencies, so the SELECT formula runs whenever you select

Data

Recalculate

. The data you refer to in the SELECT statement portion of the formula isn't considered dependent data, so selecting

Data

Recalculate

doesn't cause the formula to run again even if the underlying data changed.

This function is intended for use in array formulas.

SELECT Functions: Constants

TRUE	CURRENT_DATE
FALSE	CURRENT_TIME
NULL	CURRENT_TIMESTAMP

SELECT Functions: Operators and Logical

UNION	JOIN	INTERSECT	INNER JOIN
UNION ALL	LEFT JOIN	EXCEPT	BETWEEN
NOT BETWEEN	IN	*	<=
\|\| (string concatenation)	NOT IN	/	<>
% (modulo)	() (parentheses)	+	!=
<< (bitshift left)	IF	- (minus)	>=
>> (bitshift right)	+ (unary positive)	- (unary negative)	~ (bit inversion)
EXISTS	! (logical not)	\|	LIKE
NOT EXISTS	NOT	<	NOT LIKE
IS NULL	AND	>	CASE ¹
IS NOT NULL	OR	=	&
IFNULL	ISNULL	POW	"

¹ Including WHEN THEN ELSE END keywords

SELECT Functions: Scalar, Substring

DAYOFMONTH	YEAR	LOWER	UPPER
HOUROFDAY	COALESCE	LTRIM	DATE Y M D
MINUTE	NULLIF	RTRIM	DATETIME Y M D H M S
MONTH	ROUND	SUBSTRING	INSTANCE (ID and description)
SECOND	LENGTH	TRIM	DATEDIF
REPLICATE	STUFF	LOCATE	INSTR

SELECT Functions: Math, Financial, Statistical

ABS	COUNT	LOG10	Q (Quarter)
AVG	FLOOR	MAX	SQRT
CEIL	LOG	MIN	SUM
LIMIT	ASC	DESC