Understanding Cells and Ranges –
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, which consists of its column letter and row number. For example, cell D9 is the cell in the fourth column and the ninth row.
A group of one or more cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon.
Here are some examples of range addresses:
| C24 | A range that consists of a single cell. |
| A1:B1 | Two cells that occupy one row and two columns |
| A1:A100 | 100 cells in column A. |
| A1:D4 | 16 cells (four rows by four columns). |
| C1:C1048576 | An entire column of cells; this range also can be expressed as C:C |
| A6:XFD6 | An entire row of cells; this range also can be expressed as 6:6 |
| A1:XFD1048576 | All cells in a worksheet. This range also can be expressed as either A:XFD or 1:1048576. |
Selecting ranges –
To perform an operation on a range of cells in a worksheet, you must first select the range.
For example, if you want to make the text bold for a range of cells, you must select the
range and then choose Home ➪ Font ➪ Bold (or press Ctrl+B).
When you select a range, the cells appear highlighted. The exception is the active cell,
which remains its normal color. Figure below shows an example of a selected range (A4:D8) in
a worksheet. Cell A4, the active cell, is in the selected range but not highlighted.
If you want to follow along then download the workbook – click here to download
You can select a range in several ways:
- Left-click and drag over the range. If you drag to the end of the window, the work-
sheet will scroll. - Press the Shift key while you use the navigation keys to select a range.
- Press F8 to enter Extend Selection mode (Extend Selection appears in the status
bar). In this mode, click the lower-right cell of the range or use the navigation keys
to extend the range. Press F8 again to exit Extend Selection mode. - Type the cell or range address into the Name box (located to the left of the Formula
bar) and press Enter. Excel selects the cell or range that you specified. - Choose Home ➪ Editing ➪ Find & Select ➪ Go To (or press F5 or Ctrl+G) and enter a
range’s address manually in the Go To dialog box. When you click OK, Excel selects
the cells in the range that you specified.
Selecting complete rows and columns –
Often, you’ll need to select an entire row or column. For example, you may want to apply
the same numeric format or the same alignment options to an entire row or column. You
can select entire rows and columns in much the same manner as you select ranges:
- Click the row or column header to select a single row or column or click and drag
for multiple rows or columns. - To select multiple (nonadjacent) rows or columns, click the first row or column
header and then hold down the Ctrl key while you click the additional row or col-
umn header that you want. - Press Ctrl+spacebar to select the column(s) of the currently selected cells. Press
Shift+spacebar to select the row(s) of the currently selected cells.
Selecting noncontiguous ranges –
Most of the time, the ranges that you select are contiguous—a single rectangle of cells.
Excel also enables you to work with noncontiguous ranges, which consist of two or more
ranges (or single cells) that aren’t necessarily adjacent to each other. Selecting noncontigu-
ous ranges is also known as a multiple selection. If you want to apply the same formatting
to cells in different areas of your worksheet, one approach is to make a multiple selection.
When the appropriate cells or ranges are selected, the formatting that you select is applied
to all of them. Figure below shows a noncontiguous range selected in a worksheet. Three
ranges are selected: B3:E3, B6:C8, and cell F15.
You can select a noncontiguous range in the same ways that you select a contiguous
range with a few minor differences. Instead of simply clicking and dragging for contigu-
ous ranges, hold down the Ctrl key while you click and drag. If you’re selecting a range
using the arrow keys, press Shift+F8 to enter Add or Remove Selection mode (that term
will appear in the status bar). Press Shift+F8 again to exit Add or Remove Selection mode.
Anywhere you type the range manually, such as in the Name box or the Go To dialog box,
simply separate the noncontiguous ranges with a comma. For example, typing A1:A10,
C5:C6 will select those two noncontiguous ranges.
Selecting multi sheet ranges –
In addition to two-dimensional ranges on a single worksheet, ranges can extend across mul-
tiple worksheets to be three-dimensional ranges.
Suppose you have a workbook set up to track budgets. One approach is to use a separate
worksheet for each department, making it easy to organize the data. You can click a sheet
tab to view the information for a particular department.
Figure below shows a simplified example. The workbook has four sheets: Totals, Operations,
Marketing, and Manufacturing. The sheets are laid out identically. The only difference is
the values. The Totals sheet contains formulas that compute the sum of the corresponding
items in the three departmental worksheets.
Assume that you want to apply formatting to the sheets—for example, to make the column
headings bold with background shading. One (albeit not-so-efficient) approach is to format
the cells in each worksheet separately. A better technique is to select a multisheet range
and format the cells in all the sheets simultaneously. The following is a step-by-step exam-
ple of multisheet formatting using the workbook shown in Figure above.
- Activate the Totals worksheet by clicking its tab.
- Select the range B3:F3
- Press Shift and click the Manufacturing sheet tab. This step selects all worksheets
between the active worksheet (Totals) and the sheet tab that you click—in essence,
a three-dimensional range of cells (see Figure below). When multiple sheets are
selected, the workbook window’s title bar displays Group to remind you that you’ve
selected a group of sheets and that you’re in Group mode.
4. Choose Home ➪ Font ➪ Bold and then choose Home ➪ Font ➪ Fill Color to apply a
colored background. Excel applies the formatting to the selected range across the
selected sheets.
5. Click one of the other sheet tabs. This step selects the sheet and cancels Group
mode; Group is no longer displayed in the title bar.
When a workbook is in Group mode, any changes that you make to cells in one worksheet
also apply to the corresponding cells in all of the other grouped worksheets. You can use
this to your advantage when you want to set up a group of identical worksheets because
any labels, data, formatting, or formulas you enter are automatically added to the same
cells in all of the grouped worksheets.
In general, selecting a multisheet range is a simple two-step process: select the range in
one sheet and then select the worksheets to include in the range. To select a group of con-
tiguous worksheets, select the first worksheet in the group and then press Shift and click
the sheet tab of the last worksheet that you want to include in the selection. To select
individual worksheets, select one of the worksheets in the group and then press Ctrl and click the sheet tab of each additional worksheet that you want to select. If all of the work-
sheets in a workbook aren’t laid out the same, you can skip the sheets that you don’t want
to format. When you make the selection, the sheet tabs of the selected sheets display in
bold with underlined text, and Excel displays Group in the title bar.
Selecting special types of cells –
As you use Excel, you may need to locate specific types of cells in your worksheets. For
example, wouldn’t it be handy to be able to locate every cell that contains a formula—or
perhaps all of the formula cells that depend on the active cell? Excel provides an easy way
to locate these and many other special types of cells: select a range and choose Home ➪
Editing ➪ Find & Select ➪ Go To Special to display the Go To Special dialog box, as shown
in Figure below.
After you make your choice in the dialog box, Excel selects the qualifying subset of cells in
the current selection. Often, this subset of cells is a multiple selection. If no cells qualify,
Excel lets you know with the message No cells were found.
Table Below offers a description of the options available in the Go To Special dialog box.
| Option | What it does |
|---|---|
| Comments | Selects the cells that contain a cell comment |
| Constants | Selects all nonempty cells that don’t contain formulas. Use the check boxes under the Formulas option to choose which types of nonformula cells to include |
| Formulas | Selects cells that contain formulas. Qualify this by selecting the type of result: numbers, text, logical values (TRUE or FALSE), or errors. |
| Blanks | Selects all empty cells. If a single cell is selected when the dialog box dis- plays, this option selects the empty cells in the used area of the worksheet. |
| Current Region | Selects a rectangular range of cells around the active cell. This range is determined by surrounding blank rows and columns. You can also press Ctrl+Shift+*. |
| Current Array | Selects the entire array. |
| Objects | Selects all embedded objects on the worksheet, including charts and graphics. |
| Row Differences | Analyzes the selection and selects cells that are different from other cells in each row. |
| Column Differences | Analyzes the selection and selects the cells that are different from other cells in each column. |
| Precedents | Selects cells that are referred to in the formulas in the active cell or selec-tion (limited to the active sheet). You can select either direct precedents or precedents at all levels. |
| Dependents | Selects cells with formulas that refer to the active cell or selection (limited to the active sheet). You can select either direct dependents or depen- dents at all levels |
| Last Cell | Selects the bottom-right cell in the worksheet that contains data or for- matting. For this option, the entire worksheet is examined, even if a range is selected when the dialog box displays. |
| Visible Cells Only | Selects only visible cells in the selection. This option is useful when deal- ing with a filtered list or a table. |
| Conditional Formats | Selects cells that have a conditional format applied (by choosing Home ➪ Styles ➪ Conditional Formatting). The All option selects all such cells. The Same option selects only the cells that have the same conditional format- ting as the active cell. |
| Data Validation | Selects cells that are set up for data entry validation (by choosing Data ➪ Data Tools ➪ Data Validation). The All option selects all such cells. The Same option selects only the cells that have the same validation rules as the active cell |
Selecting cells by searching –
Another way to select cells is to choose Home ➪ Editing ➪ Find & Select ➪ Find (or press
Ctrl+F), which allows you to select cells by their contents. The Find and Replace dialog box
is shown in Figure below. This figure illustrates additional options that are available when you
click the Options button.
Enter the text you’re looking for; then click Find All. The dialog box expands to display all
of the cells that match your search criteria. For example, Figure below shows the dialog box
after Excel has located all cells that contain the text supplies. You can click an item in the
list, and the screen will scroll so that you can view the cell in context. To select all of the
cells in the list, first select any single item in the list. Then press Ctrl+A to select them all.
The Find and Replace dialog box supports two wildcard characters:
| ? | Matches any single character |
| * | Matches any number of characters |
Wildcard characters also work with values when the Match Entire Cell Contents option is
selected. For example, searching for 3* locates all cells that contain a value that begins
with 3. Searching for 1?9 locates all three-digit entries that begin with 1 and end with 9.
Searching for *00 locates values that end with two zeros.
If your searches don’t seem to be working correctly, double-check these three options:
Match Case – If this check box is selected, the case of the text must match exactly. For
example, searching for smith does not locate Smith.
Match Entire Cell Contents – If this check box is selected, a match occurs if the cell con-
tains only the search string (and nothing else). For example, searching for Excel doesn’t
locate a cell that contains Microsoft Excel. When using wildcard characters, an exact
match is not required.
Look In – This drop-down list has three options: Values, Formulas, and Comments. The
Formulas option looks only at the text that makes up the formula or the contents of the
cell if there is no formula. The Values option looks at the cell value and the results, not the
text, of the formula. If, for example, Formulas is selected, searching for 900 doesn’t find a
cell that contains the formula =899+1 but will find a cell with a value of 900. The Values
option will find both of those cells.