Categories
Excel

Understanding Cells and Ranges

A cell is a single unit of information in a worksheet, identified by its column letter and row number. For example, A1 is the cell in the first column and first row. A range is a group of cells that you can select and refer to as a whole. For example, A1:C5 is a range that includes 15 cells in three columns and five rows.

  1. Cell
  2. Range
  3. Reference Operators
  4. Selecting Ranges
  5. Selecting Non-contiguous Ranges

CELL

A cell is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address (also called cell reference), which consists of its column letter and row number (columns are represented by letters, and rows are represented by numbers).

A combination of a column letter and a row number gives each cell a unique address. The first cell in a worksheet would have an address of A1. A cell that is at the intersection of column D and row 9 would have a cell address of D9 ( fourth column and the ninth row).

Range

A range is a collection of (adjacent or non-adjacent) cells that you work with as a group rather than separately. This enables you to fill the range with values, move or copy the range, sort the range data, and insert and delete ranges.

To select a cell, simply click on it. To select a range, the range reference operator : (colon) is used. The reference operator creates a reference to all the cells between two cell references, including the cells themselves. For example, A1:A10 refers to the range of cells from A1 to A10 in a worksheet.

Range (or Reference) Operators

There are three range (or reference) operators:

  1. Range Operator (colon :)

Specifies a range. Produces one reference to all the cells between two references. For example, A1:B3 includes cells A1, A2, A3, B1, B2, and B3.

  1. Union Operator (comma ,)

Specifies the union of multiple cells or ranges. This operator combines multiple ranges or cell references into one reference. For example, A1:B3,C3 includes cells A1, A2, A3, B1, B2, B3 and C3.

  1. Intersection Operator (single space)

Specifies the intersection of multiple cells or ranges. This operator produces one reference to cells that are common to two (or more) references. For example, the reference A1:B3 B1:C3 references cells B1, B2, and B3. You can use more than one reference operator in a single formula.

Note: When you select a range, it appears highlighted, but the active cell within the range is not highlighted.

Selecting a Range

To select a cell, simply click on it. To select a range, click and drag your mouse over the cells you want to include.

You can select a range in several ways:

  • Using Mouse: Click the left mouse button and drag, highlighting the range. Then release the mouse button.
  • Keyboard Shift Key: Press and hold the shift key while using the arrow keys to select a range.
  • Keyboard F8 Key: Press F8 key and then use the arrow keys to highlight the range. Press the Escape key (or F8 key again) to return to normal movement.
  • Name Box: Write the cell or range address into the Name box and press Enter. Excel selects the cell or range that you specified.
  • Go To Dialog Box: Enter a cell address or range in the Go To dialog box and click OK, Excel will select the cells in the range that you specified.
    To open the Go To dialog box press F5 or click Find & Select from the Home tab and click Go To (Home > Find & Select > Go To).

Selecting Multiple Non-contiguous Ranges

Selecting Multiple Ranges
  • Ctrl Key and Mouse: Select a range then press and hold Ctrl to select another range, click and drag the mouse (or arrow keys) to highlight additional cells or ranges.
  • Shift+F8 Keys: Select a range then press Shift+F8 to select another range.
  • Name Box: Enter the range address in the Name box and press Enter. Separate each range address with a comma (see Range/Reference Operators).
  • Go To Dialog Box: Enter the range address in the Reference box, and separate each range address with a comma. Click OK, and Excel selects the ranges.

Understanding Workbooks and Worksheets: