Wednesday, September 26, 2012

Understand Ranges in Microsoft Excel


Two Types of Range
There are two types of ranges in Excel -- contiguous and noncontiguous. These are described below:
Contiguous Range -- A contiguous range is a simple connected rectangular group of one or more cells.
Noncontiguous Range -- A noncontiguous range consists of two or more non-connected contiguous ranges.
Range Addresses
Single Cell Range Address-- A single cell range address consists of a column address followed by a row address. For example, the range address D16 means the cell at column D and row 16.
Multi-Cell Range Address-- A multi cell range consists of the starting corner of a range, followed by a colon, and then the ending corner of a range. It typically defines a rectangle in multiple rows and multiple columns, but it could be just a portion of a row or a portion of a column. See the examples below:
A1:C3 is an example of a typical multi-row multi-column range; it defines a 3 x 3 connected group of cells that extends from cell A1 to cell C3 inclusive.
A1:J1 is an example of a single-row multi-column range; it defines a 10 x 1 connected group of cells that extends from cell A1 to cell J1 inclusive.
A1:A10 is an example of a typical multi-row single-column range; it defines a 1 x 10 connected group of cells that extends from cell A1 to cell A10 inclusive.
Range Address Including Sheet -- Both single cell and multi-cell range addresses can contain a sheet name. If the sheet name is not included, the range is assumed to refer to the current sheet. Examples follow:
Sheet3!D16is an example of a single cell range that is located on worksheet 3.
Sheet3!A1:C3 is an example of a multi-row multi-column range that is located on worksheet 3.
Noncontiguous Range Addresses -- Noncontiguous range addresses consist of a comma separated group of range addresses. An example follows:
Sheet1!A1:C3, Sheet2!A1:C3, Sheet3!A1:C3 is an example of a noncontiguous range address that consists of the cells in the range A1:C3 on each of Sheet1, Sheet2, and Sheet3.
Selecting a Range
There are several ways to select a range. Descriptions follow:
-- Click on a cell, hold the left mouse button down, and drag to select a range.
-- Select a cell, press [F8], and use the arrow keys to select the range.
Selecting a Noncontiguous Range
To select a noncontiguous range, select the first part of the range with the mouse, then hold down the[Ctrl] key and select the second group of cells in the range. Repeat until you have selected all portions of the range.
Summary
This article defined the different types of ranges in Excel, gave some examples, and described basic methods for selecting ranges.

No comments:

Post a Comment