Ranges: A Select Tool in Excel 2010

And now for something if not completely, then at least slightly, different. To date, we’ve explored a variety of ways for meandering across the worksheet, all of which bring us to one particular cell at journey’s end.

ut Excel also provides us with the  means for occupying more than one cell at the same time.

What does that mean? Well, it means that it’s time to dust off another  one of those have-to-know concepts. This matter doesn’t originate with Excel, of course, but history aside, spreadsheet users very often need to visit, work with, or be able to identify a group of cells at the same time. Why? The reasons are several.

For  example, a user may want to change the font that currently appears in a large cluster of cells. Having the ability to bring about  that change simultaneously in all those cells is obviously a great deal more efficient than having to revise each cell individually.

After all, what if you wanted to change the font for, say, 50 million cells? In addition, Excel users very often need to perform some sort of mathematical operation on a gaggle of cells at the same time. Indeed, isn’t adding rows or columns of  numbers—the classic Excel task— exactly what we’re talking about here?

So in order to do this kind of work we need to be able to define ranges on the worksheet.

Strictly speaking, a range is any collection of cells that has been selected or identified at the same time. But more conventionally put, a range is a set of adjacent cells that exhibit a rectangular shape—and the concept is far simpler than it  sounds.

Figure 2–10, for example, is a range:

Figure 2–10. A typical range
As is Figure 2–11:

Figure 2–11. Another range example, this one narrower and shorter
And so is Figure 2–12:

Figure 2–12. One column, eleven rows
You’re doubtless getting the idea. Our cell pointer—and that’s what it is—stretches when it selects a range, serving as its  perimeter; and with one clear exception, a bluish fill color identifies exactly those cells that populate the range (and why the very first cell in a range remains white is a matter to be revealed later).

And so here’s the point behind all this: if I want to change the font in a range of cells, I can select those cells I want as  illustrated above, and then go ahead and issue a font-change command. And as a result, only the cells in the range will be  affected.

And how do you go about selecting cells in a range? It’s rather easy—and again, both mouse and keyboard approaches stand at the ready. If you’re mouse-inclined, click the first cell of the desired range—which is, typically, the upper-left cell in the block  of cells you want to select. Keep the mouse button down, and pull—or drag—across and/or down the cells you want to  incorporate into the range.

When you’re done, release the mouse button, and the blue-blanketed range remains selected.

You can also select an entire column by simply clicking a column header—that is, the alphabetized area in which the columns  are named. Doing so highlights that column, as in Figure 2–13:

Figure 2–13. An entire column selected
Yes—all one-million-plus cells in the K column are now selected (hope you weren’t expecting a foldout showing them all).

And you can select a row by clicking one of the numbered row headers on the side of the screen. And by clicking the  column/row header area and dragging across or down that area, you can select multiple columns or rows.

And if you opt for keyboard cell-selection approaches, first select that upper-left cell, using any navigational means you wish.

Then hold down the Shift key, keep it down, and press any of the keyboard arrow keys in the direction of the cells you wish to  select. For example, you can first press the Right and then the Down arrows, thus enabling you to describe a range of as many  columns and rows as you wish.

Just remember to keep the Shift key down throughout the process. When you’re done, release the Shift key and observe your range, decked out in blue. (Just keep in mind for the record that you could start your range selection by clicking what is the  upper-right cell of the desired range, and dragging left and down and/or up. It’s just that most people—at least those who speak and write English—tend to think left to right.) But I’ve been holding out on you.

There’s yet another way to designate a range,  and that alternative takes us back once again to the name box, along with an  important data entry principle. If, for example I  type this: D13:H23  in the name box and then press Enter, cells D13 through  H23 will be selected, turning that tell-tale blue (with the exception of D13, which serves as the “first”, upper-left cell in the  range and so remains white).

Note the expression—D13:H23. It means that all the cells from D13—the upper-left cell in the range— through H23—the   lower-right cell in the range—have been designated for the range selection; and this upper-left/lower-right-cell nomenclature   for range boundaries is indispensable to Excel formulas. Thus, by way of preview, if you see an expression that looks   something like this: =SUM(A34:C57) You’ll know it means that all the numbers in cells A34 through C57 are to be added  (And by the way – this formula: =SUM(A:A) – would add all the cells in the A column).

One more point (for now) about ranges. Consider this possibility, shown in Figure 2–14:

Figure 2–14. Two ranges selected at the same time
So what’s going on here? In this case, two ranges seem to have been selected at the same time.

How’s that done? Truth to be told, rather easily. First, select one range as per the usual techniques. Then, keep the Ctrl key  down, and with your mouse, drag across a second set of cells. You can even select three or more sets of cells with this  approach—and if you’re wondering why you would want to do such a thing, the answer is that you may wish to subject all these  cells to the same change—you may want to alter the font size in just these selected cells, for example. Or you may want to  delete the contents of a range or two of cells. If you do, select the range(s), and just press the Delete key. (And let’s pass on the question about whether the screen shot above depicts two different ranges, or merely one range consisting of two  non-adjacent sets of cells. In reality either answer could apply depending on your purposes, but in the great majority of cases  you’d be regarding these as two distinct ranges.) And if you want to try something a bit more exotic, you can also type  something like this in the good old name box, followed by pressing Enter: A3:D34,E6:H23 Note the comma. The above  instruction will select cells A3 through D34, as well as E6 through H23.

And if you mess up—that is, if you select the wrong set of cells—the easiest thing to do is simply click anywhere on the   worksheet. Doing so “turns off” the blue color scheme for the selected range, and you can start range-selecting again. And in  any case, you’re going to need to turn off the range sooner or later if you plan on doing work anywhere else on the worksheet.  Now if you really need to change the font for 50 million or so cells—or something even more global—try this. It’s easy to    overlook, but observe the button wedged between the A column and row 1 headings…Click it and all 17 billion cells turn blue   (excepting A1 above. You can also press Ctrl-A to select all the worksheet cells). You’ve thereby selected the entire  worksheet,  and you might opt for this  mass procedure if, for example, you wanted to change the color of all the text in your worksheet to say, green. Once you’re finished, just click anywhere on the sheet, and the blue selection color disappears, as in  Figure 2–15:

Figure 2–15. Click here to select all the worksheet cells A last introductory point about ranges. Like amoeba, ranges can be single-celled, and if you’re at a loss to understand why—after all, how in the world is selecting a one-celled range different from  simply referring to a single cell?—stay tuned. There are sometimes very good and productive reasons for working this  way.

(Note: See the appendix on range names for a discussion of this and other rangerelated tips.) .