Array Constants in Microsoft Excel

Array Constant comprises static numeric values that mean these values cannot be modified once the user copies the formula to another location in the worksheet. The array elements would contain numbers, text, and decimal values. Users must use either the F9 key or ctrl+shift+Enter keys to compute the array formula. A diversity of inbuilt array functions like the Transpose function, LINEST, and AGGREGATE functions are available in MS Excel.

Various types of Array constants are

• Horizontal Array Constants

• Vertical Array Constants

• Two-Way Array Constants

Utilizing Horizontal Array Constants

It is also known as Row Array constants. The array constants values are to be selected row-wise.

• Step 1 − Consider the table as shown in the below image

• Step 2 − Suppose we ought to have information about the Rehan employee. Enter the range =A5:D5 in the A11 cell.

• Step 3 − Then press the f9 function key to fetch the specified employee comprehensive information.

Note − The array constant values are separated by commas in the case of the Horizontal array constant. Only string value is to be enclosed with double quotes.

Vertical Array Constants

The alternative name of the Vertical Array constants is Column Array Constants. The entries need to be done column-wise.

By using Column Array Constants

• Step 1 − Consider the Employee table created in MS Excel

• Step 2 − Enter the formula =A2:A9 in the A11 cell and press the F9 key to obtain the Vertical array constants.

• Step 3 − The array values are depicted in the A11 cell. The user will see that the array values are enclosed in the curly brackets. The individual employee's name is covered with double quotes. Furthermore, the semicolon is used among the texts to indicate the separation of the different items.

Example 3: By using Two-Way Array Constants

The alternate name of this type is Table Array Constants. In this case, the complete range of the table is to be chosen to fetch all employee information in array form.

• Step 1 − Consider the table as depicted in below image −

• Step 2 − Enter the range =a2:d9 in the A12 cell as illustrated in the below image −

• Step 3 − After that, press the f9 function key to fetch the Table array constant values. The result is shown in the G11 cell.

Conclusion

Managing large numbers of values becomes more manageable when using efficient array constants like those mentioned in the above examples. Whether leveraging Column Array constants or harnessing Row Array Constants within Microsoft Excel, capturing relative employee information into spreadsheet cells is no longer an arduous task.