Functions in Excel can work with multiple cells by providing ranges as input parameters. The Excel documentation will refer to these ranges as either “vector” or “array” ranges. A range is specified by a beginning cell address, a colon, followed by the final cell address. A valid range in Excel looks like: D4:D10, which includes all 7 cells starting with D4 and ending with D10. The cells in a range are generally ajacent, i.e. touching each other, but it is possible to select a set of non-adjacent cells as well, referred to as an irregular cell range. We will only be considering ranges with adjacent cells inthis post.
A vector range contains a range of cells from either a single row, or a single column, as shown in Fig. 1.
The range A1:E1 is in the first row, containing index numbers for some insects. The range B4:B8 is part of the second column and contains insect names. Both of these ranges are vectors and can be used in this form in functions that accept vector ranges.
Alternatively, we can select multiple rows and columns together as shown in Fig. 2.
The upper range in Fig. 2 is A1:E2, which spans two rows and five columns, while the lower range is A4:B8, which covers five rows and two columns. Some Excel functions require vector ranges, and some require array ranges. There are Excel functions that can use both types.
A straightforward example is the Sum() function which adds up all numerical values in the range. Fig. 3 shows 3 examples of using the Sum() function with horizontal and vertical vector ranges, and with an array range. The action of the Sum() function is to simply add together the contents of all the numerical values in the range.
Other Excel functions will accept more than a single range to work with and the Excel documentation will help to clarify what the exact requirements for the function are.