Purpose
Return value
Syntax
=ARRAYTOTEXT(array,[format])
- array - The array or range to convert to text.
- format - [optional] Output format. 0 = concise (default), and 1 = strict.
Using the ARRAYTOTEXT function
The ARRAYTOTEXT function converts an array or range into a text string in a specific format that contains all values. Values are separated by commas (,) or semicolons (;), depending on the format requested and the structure of the array . ARRAYTOTEXT takes two arguments: array and format . Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array generated by another function. The optional format argument controls the structure of the output. There are two formats available, concise and strict. The concise format is a simple, human-readable format. For example, values separated by commas. The strict format outputs a machine-parseable structure that describes an array in Excel.
ARRAYTOTEXT is related to the VALUETOTEXT function , which performs the same kind of text conversion on single values.
Array syntax
All arrays in Excel are wrapped in curly brackets {}, and the delimiters between array elements indicate rows and/or columns. In the US version of Excel, a comma (,) separates columns and a semicolon (;) separates rows. For example, both arrays below contain numbers 1 through 3, but one is horizontal, and one is vertical:
{1,2,3} // columns (horizontal)
{1;2;3} // rows (vertical)
The array below shows the numbers 1-6 in three rows and two columns:
{1,2;3,4;5,6} // 3 rows x 2 columns
The commas (,) indicate a new column, and the semicolons (;) indicate a new row.
The delimiters used by the ARRAYTOTEXT function depend on the Regional settings for your computer. In the United States, the concise (default) format will always output a comma and a space (", “) between values. When the format is set to strict , the delimiter between values in columns is a comma (”,"), and the delimiter between values in different rows is a semicolon (;). However, in other locales, the delimiter may vary.
Concise format
When format is zero (0), ARRAYTOTEXT will return a concise format . Essentially, the concise format is a plain, human-readable text string separated by a comma and a space, regardless of the array’s orientation. For example, if we have the values “Red”, “Blue”, and “Green” in the range A1:A3, the output from ARRAYTOTEXT looks like this:
=ARRAYTOTEXT(A1:A3) // returns "Red, Blue, Green"
If the values “Red”, “Blue”, and “Green” are in the range A1:C1 (i.e., columns instead of rows), the output is exactly the same:
=ARRAYTOTEXT(A1:C1) // returns "Red, Blue, Green"
In both cases, the delimiter is a comma followed by a space (", “), and the final text string contains no curly braces. ARRAYTOTEXT will output the concise format by default.
Because the concise format is a simple human-readable text string, you can sometimes use it as a shortcut in formulas that output delimited text strings ( example here ), as an alternative to the TEXTJOIN function . However, remember that ARRAYTOTEXT delimiters are based on regional settings, which vary by user. For example, if settings are for French/France, you will see semicolons (;) instead of commas. The other potential issue is that ARRAYTOTEXT has no control over empty values. TEXTJOIN is a better option if you need to control delimiters or ignore empty values.
Strict format
When format is set to 1, ARRAYTOTEXT will return a text string that is a fully qualified array constant like {“red”,“blue”,“green”}. The output will appear wrapped in curly braces {}, and delimiters will follow the structure of the array provided — semicolons separating rows and commas separating columns in the US version of Excel. The strict format mirrors what you see in the formula bar if you enter a formula like this and use the keyboard shortcut F9 :
=A1:C3
In fact, you can paste the strict output format directly into the formula bar, and Excel will return values to the worksheet in a range that follows the original structure of the array. For example, this formula will return a range with 6 cells (3 rows, and 2 columns):
={"red",1;"blue",2;"green",3}
You can use ARRAYTOTEXT with the strict format to get a fully qualified array constant that can be used inside another formula, just like a normal range or array. One potential use of ARRAYTOTEXT in strict mode is to store named values in a worksheet instead of named ranges. For example, suppose you have a small table, 5 rows x 3 columns, where the first column is alphabetic, and the other two are numeric.
Note that ARRAYTOTEXT will always ignore number formatting , regardless of format .
Example - Basic usage
With the values 1, 2, and 3 in cells A1:A3, ARRAYTOTEXT returns the following in the default concise mode and in strict mode:
=ARRAYTOTEXT(A1:A3) // returns "1, 2, 3"
=ARRAYTOTEXT(A1:A3,1) // returns "{1;2;3}"
Notice that both results are text values, but in the second example, values are separated by semicolons, and the output is enclosed in curly braces. The strict format option will also wrap text values in double quotes (”"). For example, with “red”, “blue”, and “green” in A1:A3:
=ARRAYTOTEXT(A1:A3) // returns "red, blue, green"
=ARRAYTOTEXT(A1:A3,1) // returns "{"red";"blue";"green"}"
In the example shown above, the formula in E11 refers to a range that contains both text values and numbers:
=ARRAYTOTEXT(B14:C16) // returns "Red, 3, Green, 2, Purple, 1"
=ARRAYTOTEXT(B14:C16,1) // returns "{"Red",3;"Green",2;"Purple",1}"
Notice that only the second formula’s text values are enclosed in double quotes. In addition, rows are separated by semicolons and columns are separated by commas, following the structure of arrays in the US version of Excel.
Example - Strict format to create a named value
Because ARRAYTOTEXT’s strict format can create array constants , you can use it to create a named table. To give a table a name that you can refer to in a formula, without storing it as a range in the worksheet, you can follow a process like this:
- Lay out the table in a worksheet
- Invoke ARRAYTOTEXT on the table with strict format.
- Copy the resulting value to the clipboard.
- Open the name manager ( Control + Shift + F3 ) and enter a name.
- In “Refers to”, type an equal sign (=) and paste the copied text.
You can now delete the original table in the workbook and use the name like an ordinary named range.
Let me know if you discover another good use case for ARRAYTOTEXT.
Purpose
Return value
Syntax
=BYCOL(array,lambda)
- array - The array or array to process.
- lambda - The lambda function to apply to each column.
Using the BYCOL function
The Excel BYCOL function applies a function to each column in array and returns one result per column in a single array . The purpose of BYCOL is to process data in an array or range in a “by column” fashion. For example, if BYCOL is given an array with 10 columns, BYCOL will return an array with 10 results. The calculation performed on each column can be a stock function like SUM, COUNT, etc., or a custom LAMBDA function designed to operate on column values. BYCOL can accept an abbreviated “eta lamba” syntax for simple operations like SUM, as explained below.
The concept and syntax
Essentially, BYCOL allows you to loop over all columns in a range or array and perform a custom calculation on each column in a single formula. The second argument, function, determines the calculation performed :
=BYCOL(array,function)
The function can be provided in two ways: a short-form “eta lambda” syntax or a long-form custom LAMBDA syntax. For example, to sum each column in an array, the short-form syntax looks like this:
=BYCOL(array,SUM)
The long-form custom syntax looks like this:
=BYCOL(array,LAMBDA(column,SUM(column))
Why two syntax options? The short form is for convenience. It is concise and easy to read. However, the behavior cannot be customized. The long-form syntax uses the LAMBDA function and can be customized as desired. See below for an example of BYCOL formulas that use both options.
Note: in the examples above, we use the variable name “column” in the LAMBDA function for clarity. However, you are free to use whatever name you like.
Example - sum all columns
In the worksheet below, the BYCOL function is used to sum the values in each column of the range C5:H14. The formula in cell C16 looks like this:
=BYCOL(C5:H14,LAMBDA(column,SUM(column)))
The BYCOL function returns all sums at once in a single array. Because there are 6 columns in the range, the result is an array that contains 6 sums like this:
{8859,8690,9020,9112,8876,9153}
The values in this array spill into the range C16:H16. The formulas below are other examples of how BYCOL can be used on the same data with formulas that follow the same pattern:
=BYCOL(C5:H14,LAMBDA(column,MAX(column))) // max
=BYCOL(C5:H14,LAMBDA(column,MIN(column))) // min
=BYCOL(C5:H14,LAMBDA(column,AVERAGE(column))) // average
Example - Short form “eta lambda” syntax
While BYCOL is designed to accept a function defined by LAMBDA, it will also accept a short-form “eta lambda” syntax for simple operations. Using the eta lambda syntax, you can pass a function by name only into BYCOL like this:
=BYCOL(array,SUM)
This formula is equivalent to the long-form version:
=BYCOL(array,LAMBDA(x,SUM(x)))
For example, in the worksheet below, we have adapted the formula in J5 to use the short-form syntax. The formula now looks like this:
=BYCOL(C5:H14,SUM)
The Excel formula engine knows how to pass each column into the SUM function, and the results are exactly the same as the long-form version. You can use the same syntax to call other functions like this:
=BYCOL(array,SUM) // sum each column
=BYCOL(array,MAX) // max of each column
=BYCOL(array,MIN) // min of each column
=BYCOL(array,COUNT) // count of each column
The short-form eta syntax works for functions that accept a single argument, such as SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, etc. It will not work in all scenarios because the logic can’t be modified. For example, the formula in the next section below can’t use the eta lambda syntax.
Example - Count values over 900
In this example, the goal is to count the values in each column greater than 900. The formula is a bit more complex because we need more logic. This is a situation where we can’t use the short-form syntax above. The formula in cell C16 looks like this:
=BYCOL(C5:H14,LAMBDA(column,SUM(--(column>900))))
Working from the inside out, a custom lambda counts all values in a column greater than 900:
LAMBDA(column,SUM(--(column>900))) // custom lambda
The logical expression column>900 returns an array of TRUE and FALSE values, and we use a double negative (–) to coerce these values to 1s and 0s. The SUM function then sums the 1s and 0s, and the result is the count of values greater than 900:
SUM(--(column>900))
The LAMBDA runs on each column in the data. Since there are 6 columns in the range C5:H14, BYCOL returns 6 results that spill into the range C16:H16. See Boolean operations in array formulas for more information about the logic inside of SUM.
You could use COUNTIFS instead of SUM to solve this problem. However, COUNTIFS requires a range , which means you can’t give BYCOL an array if you use COUNTIFS in the calculation. For this reason, I generally avoid the *IFS functions when working with the new dynamic array functions where arrays are more common.
Example - BYCOL with multiple functions
It is also possible to configure BYCOL to run more than one function at the same time, although it is not obvious how. The trick is to wrap the functions in the HSTACK or VSTACK functions (depending on the layout needed). For example, in the worksheet below, BYCOL is configured to return the sum, the maximum value, the minimum value, and the average value for each column at the same time. The formula in C12, copied across, is:
=BYCOL(C5:H10,VSTACK(SUM,MAX,MIN,AVERAGE))
Although this formula uses the short-form syntax, you can take the same approach with custom LAMBDA calculations.
Because of the current array of arrays limitation in Excel, this formula will not spill down across all columns. It will initially only calculate results for the first column. You will then need to copy the formula manually to all columns in a second step.
