Contents
Array Formula is one of the most powerful tools in Excel, but also one of the most complex. An array in Excel is a set of elements that can be processed as a whole or as a set, i.e. each element separately. In this tutorial, we will learn the basics about arrays.
One-dimensional and two-dimensional arrays
Excel distinguishes between one-dimensional and two-dimensional arrays. A one-dimensional array, also called a vector, can be stored in one row (horizontal array)…
…or in one column (vertical array).
A two-dimensional array is stored in a range of cells, also known as a matrix.
Unlike the VBA programming language, Excel does not support XNUMXD arrays.
Array Dimension
Earlier we noted that arrays in Excel can be one-dimensional and two-dimensional. In turn, one-dimensional can be horizontal and vertical. Now let’s look at how elements in arrays are separated.
Examples are given for the version of Excel.
- Elements in one-dimensional horizontal arrays are separated by semicolons (;). For example, the figure below shows a one-dimensional horizontal array, which consists of 5 elements:
If we enter it into an Excel worksheet, we get the following result:
If we enter it into an Excel worksheet, we get the following result:
To insert such an array into Excel, you must first select a row consisting of 5 cells, then enter the array formula and press the key combination Ctrl + Shift + Enter.
- Elements in a one-dimensional vertical array are separated by a colon (:). For example, the figure below shows a one-dimensional vertical array, which consists of 4 elements.If you enter it on the worksheet, we get the following result:
- In two-dimensional arrays, a semicolon is used to separate elements in a row, and a colon is used to separate rows. The figure below shows an array that consists of 3 rows and 5 columns.If you enter this array into Excel, the result will be:
If you enter it on the worksheet, we get the following result:
If you enter this array into Excel, the result will be:
When creating a two-dimensional array, it is necessary to take into account that all its rows have the same number of elements. For example, the following array cannot exist because its second row contains only 4 elements.
If you try to enter such an array on the worksheet, then nothing will work. Excel won’t let you do that.
You can use the value as a placeholder for a missing element in an array #N/A (no data). For example, the following entry would be syntactically correct:
Entering this array into an Excel worksheet, we get the following:
Single-cell and multi-cell array formulas
Excel supports two types of array formulas: multi-cell and single-cell. In the first case, the formula spans several cells, i.e. returns an array as result:
In the second case, the formula is entered into one cell and returns only one value:
So, in this lesson, you got acquainted with the basic terms and definitions regarding array formulas in Excel. For more information about arrays, read the following articles:
- Multicell array formulas in Excel
- Single cell array formulas in Excel
- Arrays of constants in Excel
- Editing array formulas in Excel
- Applying array formulas in Excel
- Approaches to editing array formulas in Excel