Tidbit:CSE functions in Excel

CSE functions (aka array formulas) in Excel are functions that apply across a range of cells to aggregate the result in once cell.

CSE functions are applied when a function is entered into the cell, and instead of leaving the cell pressing the enter key, it is left by pressing ctrl-shift-enter (hence the CSE name).

The functions are useful for generating lists of values in a single cell.

For example, a CSE function could be used to generate a semicolon separated list of email addresses based on a column of email addresses and a second column that is used as the filter.

This table contains the 4 bottom rows of data. Entering the formula CONCAT(IF(B$2:B$5>"",A$1:A$5 & "; ","")) into the top right cell and then clicking ctrl-shift-enter will populate the cell as seen here.

	A	B
1		a@b.ca; d@b.ca;
2	a@b.ca	x
3	b@b.ca
4	c@b.ca
5	d@b.ca	y

I tried these out in LibreOffice Calc rather than Excel. They work there as well. One strange thing I found is that CSE doesn't always seem to "catch" when leaving a cell. I found that removing the "=" you usually need to add to designate a function often fixes it.