Page 256 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Array Formulas in Excel

Excel is among the most powerful and widely used spreadsheet tools which eases organizing numbers and data
within the sheets with pre-built formulas and functions. In this article, we are going to study Array Formulas. Arrays
are one of them. Using Excel Array Formulas, make it super easy to handle a lot of data. Before understanding
and using the Array formulas, let’s first understand what is Array in Excel?

Array in Excel
An Array in Excel is the structure that holds a collection of data. These data can be in the form of numbers or texts.
There could be an array of 1-D or 2-D as well in Excel. For example, the array of fruits in excel would be written
something like this:-
{“Apple”, “Banana”, “Grapes”, “Guava”}
We can directly enter it into the sheet by selecting the cells where you want to enter the data and then writing the
array preceded by an “=” sign. After these press Ctrl+Shift+Enter. For example, let’s enter these values in cells
A1 to D1.
Array-in-excel

The array created here is a horizontal array. You can also create a vertical array by changing the selected cells.
You can create an array by specifying the starting and the ending positions, for example, if you wish to make an
array of elements between A1 to F1 then you can write A1:F1. Now. let’s move to array formulas.
Array formulas
Array formulas enable to process of several values and give several outputs. In a simple world, it can do multiple
calculations and reduce a lot of human efforts. In other words, we can say that it solves array calculations and
give an array as output too. Let’s understand more of them with an example, of students with their test marks,

Here, we have used a formula to evaluate the result in the ‘Total’ field. For inserting the formula, click on the cell
where you want to store the answer and write the formula. Now, let’s try to calculate the same for all students

243

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77