To create a row array constant, type the values separated by commas and enclose then in braces, for example 1,2,3,4.

A single array formula can perform multiple calculations and replace thousands of usual formulas.

And still, 90 of users have never used array functions in their worksheets simply because they are scared to start learning them.

The aim of this tutorial is to make the learning curve as easy and smooth as possible.

Essentially, an array is a collection of items.

The items can be text or numbers and they can reside in a single row or column, or in multiple rows and columns.

In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula.

So, the results returned by an array formula is also an array.

So, all it takes is a single array formula and 2 quick steps.

When working with hundreds and thousands of rows of data, just think how much time you can save by entering one array formula in a single cell.

A single array formula can replace literally hundreds of usual formulas.

Here is an array formula that counts all chars, and another one that counts any given characters.

You must press the CtrlShiftEnter shortcut to complete an array formula.

To do this, select one or several arguments within a functions parentheses, and then press the F9 key.

An array formula entered in a range of cells is called a multi-cell formula.

An array formula residing in a single cell is called a single-cell formula.

So, you just enter the following formula and press Ctrl Shift Enter.

TRANSPOSE is one of such functions and we are going to utilize it to transpose the above table, i.e.

Since we are converting rows to columns, be sure to select the same number of rows and columns as your source table has columns and rows, respectively.

To learn the nuts and bolts of TRANSPOSE, please check out this tutorial: How to transpose in Excel - convert rows to columns.

Whenever you try doing this, Microsoft Excel will throw the warning You cannot change part of an array.

If your Excel array formula produces an array larger than the selected range, the excess values wont appear on the worksheet.

If an array returned by the formula is smaller than the selected range, NA errors will appear in extra cells.

These values never change when you copy a formula to other cells or values.

Now, lets see what other array types exist and how you create them.

Excel For Mac Array Calculation