Range Definition and Use in Excel Spreadsheets

Range Definition and Use in Excel Worksheets
Range Definition and Use in Excel Worksheets. © Ted French

A range is a group or block of cells in a worksheet that has been selected or highlighted. When cells have been selected they are surrounded by an outline or border as shown in the image to the left.

A range can also be a group or block of cell references that can be, for example,

  • entered as an argument for a function;
  • used to create a graph;
  • used to create bookmarks to specific data in a workbook.

By default, this outline or border surrounds only one cell in a worksheet at a time, which is known as the active cell.

Changes to a worksheet - such as data editing or formatting, by default, affect the active cell.

When a range of more than one cell is selected, changes to the worksheet - with certain exceptions such as data entry and editing - affect all cells in the selected range.

Contiguous and Non-contiguous Ranges

A contiguous range of cells is a group of highlighted cells that are adjacent to each other - such as the range C1 to C5 shown in the image above.

A non-contiguous range consist of two or more separate blocks of cells. These blocks can be separated by rows or columns as shown by the ranges A1 to A5 and C1 to C5.

Both contiguous and non-contiguous ranges can include hundreds or even thousands of cells and span worksheets and workbooks.

Naming a Range

Ranges are so important in Excel and Google Spreadsheets that names can be given to specific ranges to make them easier to work with and reuse when referencing them in such things as charts and formulas.

Selecting a Range in a Worksheet

There a number of ways to select a range in a worksheet. These include using:

  • the mouse;
  • the keyboard;
  • the Name Box in Excel;
  • or a combination of the three.

A range consisting of adjacent cells can be created by dragging with the mouse or by using a combination of the Shift and four Arrow keys on the keyboard.

Ranges consisting of non-adjacent cells can be created by using the mouse and keyboard or just the keyboard.

Selecting a Range for Use in a Formula or Chart

When entering a range of cell references as an argument for a function or when creating a chart, in addition to typing in the range manually, the range can also be selected using pointing.

Ranges are identified by the cell references or addresses of the cells in the upper left and lower right corners of the range. These two references are separated by a colon ( : ) which tells Excel to include all the cells between these start and end points.

Range vs. Array

At times the terms range and array seem to be used interchangeably for Excel and Google Spreadsheets, since both terms are related to the use of multiple cells in a workbook or file.

To be precise, the difference lies in the fact that  a range refers to the selection or identification of multiple cells such as A1: A5, whereas an array would refer to the values located in those cells such as {1;2;5;4;3}.

Some functions - such as SUMPRODUCT and INDEX take arrays as arguments, whereas others - such as SUMIF and COUNTIF accept only ranges for arguments.

That is not to say that a range of cell references cannot be entered as arguments for SUMPRODUCT and INDEX as these function can extract the values from the range and translate them into an array.

For example, the formulas

=SUMPRODUCT(A1:A5,C1:C5)

=SUMPRODUCT({1;2;5;4;3},{1;4;8;2;4})

both return a result of 69 as shown in cells E1 and E2 in the image.

On the other hand, SUMIF and COUNTIF do not accept arrays as arguments. So, while the formula

=COUNTIF(A1:A5,"<4") returns an answer of 3 (cell E3 in the image);

the formula

=COUNTIF({1;2;5;4;3},"<4")

is not accepted by Excel because it uses an array for an argument. As a result, the program displays a message box listing possible problems and corrections.