Excel Basics Array Learning (Part-1)

Excel Array Basics

An array is simply a collection of items .

- The items which is listed between curly brackets termed as array constants.Lets say array constants items are 1,2,3,4,5 , so it must be surrounded by curly brackets i.e {1,2,3,4,5}.

Now, In Excel array can be one-dimensional or two-dimensional.


One-dimensional Array :-

• Horizontal Array ( That can be stored in a range that consists of one row)
• Vertical Array (That can be stored in a range that consists of one column)

Two-dimensional Array:-

• That can be stored in a rectangular range.

* Excel doesn't support three-dimensional arrays.

Following are the basic understanding the Dimensions of an Array with example :-

One Dimensional Array, Horizontal Array:-

Here the array constants are separated by commas (,).
For Example:- Select a range lets say, A1:E1 ,which means one row and five columns. Enter array constant as ={1,2,3,4,5} in formula bar and press ctrl+shift+enter for curly brackets. It would appear as {={1,2,3,4,5}}in formula bar. You will notice that the A1,B1,C1,D1 and E5 filled with elements 1,2,3,4 and 5 respectively. Note while entering the array constant in formula bar, first I used manually curly brackets and then pressed ctrl+shift+enter.

Similarly, Array constants can also be text .
For Example:  {={"jan","feb","mar","april","may","june","july","aug","sept","oct","nov","dec"}

Point to remember :- If you need to edit something onto the formula, you have to press again ctrl+shift+enter instead of just pressing Enter.

One Dimensional Array, Vertical Array:-

Here the array constants are separated by semicolon (;).
For Example:- Select a range lets say, A1:A5 ,which means one column and five rows. Enter array constant as ={1;2;3;4;5} in formula bar and press ctrl+shift+enter for curly brackets. It would appear as {={1;2;3;4;5}}in formula bar. You will notice that the A1,A2,A3,A4 and A5 filled with elements 1,2,3,4 and 5 respectively. Note while entering the array constant in formula bar, first I used manually curly brackets and then pressed ctrl+shift+enter.

Similarly, Array constants can also be text .
For Example:  {={"jan";"feb";"mar";"april";"may";"june";"july";"aug";"sept";"oct";"nov";"dec"}

Point to remember :- If you need to edit something onto the formula, you have to press again ctrl+shift+enter instead of just pressing Enter.

Two Dimensional Array :-

A two dimensional array uses the combination of both commas and semicolons for the separation of horizontal elements and vertical elements respectively. The following example shows a 3*2 array constant:
Select a range let say A1:B3,which implies 3 rows and 2 columns . Enter 1,2;3,4;5,6 in formula bar and press ctrl+shift+enter, which puts curly brackets i.e it would appear as {1,2;3,4;5,6}. You will notice that A1,B1,A2,B2,A3,B3,A4 and B4 occupies elements 1,2,3,4,5 and 6 respectively.