Basic Excel
Save Time with Excel Range Selections
Here are some key ways to think about and use selected areas of
your worksheets.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

One of the big differences between earlystage Excel users
and gurus is that gurus can do simple things in Excel much
more quickly than other users can.
They can do this because they understand
how Excel works in situations that most Excel users experience
frequently. For example...
Change the Boundaries of a Selection
If you want to
use your keyboard to extend or reduce the range shown here, you
hold down your Shift key and then press an arrow key.
Suppose you press Shift+DownArrow, what
would your new range be? And why?
I'll answer the "why" question first, because it's the most
important...
When you use Shift plus some arrow key, Excel always
moves the corner of your selected range that's opposite your
active cell.
Therefore, in this case, pressing Shift+DownArrow
one time will move the opposite cell from C4 to C5...extending
your selection to range B2:C5.
Now, returning to the original range shown above, suppose you
want to extend your selection to the left. That is, suppose you
want to select the range A2:C4. How would you do it with your
keyboard?
You can do it with two keystrokes...
Keystroke
1: Press Ctrl+. (period) to move your
active cell clockwise to the next nearest corner. Doing so, of
course, also moves the opposite cell, as well...to cell B4.
Keystroke 2: Press Shift+LeftArrow
to move the newly positioned opposite cell to the left, extending your selection
to the range A2:C4.
Copy and Paste Long Columns of Formulas
This
image shows a situation that I face frequently...
Here, column B contains many values. There could be hundreds
of values, or even thousands of them in the column.
And cell C3 contains a formula that I want to copy and paste
parallel to the data in column B. How could you do it quickly?
Here's the slow way to do it, a way that
I've seen many Excel users use...
 Press Ctrl+C to copy cell C3.
 Select cell C4.
 Hold down your Shift key.
 Tap on the Page Down key many times
until you can see the cell in column C that's adjacent to
the last cell of data in column B.
 Move your selection up or down with your arrow keys
until the opposite cell of your selection is in the adjacent
cell where your column of formulas should end.
 Press Ctrl+V to paste your data.
Here's a fast way...
 Press Ctrl+C to copy cell C3.
 Tap your LeftArrow key once to select
cell B3.
 Press Ctrl+DownArrow, which causes your
active cell to slide down your column of data to the last
cell that contains contiguous data in column B.
 Tap your RightArrow key once to select
the adjacent cell in column C.
 Press Ctrl+Shift+UpArrow to extend the
selection up column C until the opposite cell bumps into
cell C3.
 Press Ctrl+V to paste your data to the
selected area of column C.
And here's the fastest way, which works only
on vertical lists...
 Select cell C3.
 Doubleclick on the tiny black square in the
bottomright corner of the cell.
 After you do so, Excel will copy and paste your formula
parallel to the Value column.
Yes, each of the first two methods uses six steps. But step 4 of the slow way
describes what could be many, many keystrokes. And the fast way
describes just six keystrokes.
Of course, the fastest way is best.
Select
Cells Based on Their Content
You easily can select all cells in a range—or in an entire
worksheet—that contain empty cells, numbers, text, error values,
formulas, and more.
To do so, you use the Go To Special dialog, shown here.
To launch the dialog, press either the F5
key or choose Ctrl+G. to launch the Go
To dialog. Then, in the bottomleft corner of the
dialog, choose Special...
If you've not used this dialog very often, here are some
ideas to get you started...
Select Constants in a Report
Suppose someone gives you a large workbook and you want to make
sure that its report sheet contains only formulas linked to
data, not hardcoded values.
To do so, you'd first make sure that only one cell in the
report worksheet is selected. (If more than once cell is
selected, the dialog checks only the selected range.)
Then you'd launch the Go To Special dialog, and choose
Constants. When you do so, you could choose any combination of
Numbers, Text, Logicals, and Errors, as shown in the grayedout
section above. But in this example, you'd choose only Numbers.
Then, when you press OK, Excel would select every cell in
your worksheet that contains a number.
Select Column Differences
Suppose
you get another worksheet that looks like this. Here, the Values
column appears to contain numbers, and the two Formulas columns
appear to contain
formulas.
You can check both those assumptions by using the Go To
Special dialog twice. First, you can use it to select cells with constants
that are numbers. Second, you can use it to select cells with formulas.
However, you also can use it to check that all the formulas
are consistent. To illustrate, here are the formulas in the
cells shown:
B2: =A2*2
C2: =A2+B2
But how are you to ensure that all formulas in each column
contain equivalent formulas?
To do so, first select the range B2:C6, as shown in the left
figure here:
Launch the Go
To Special dialog. Then select the Column
differences option, which selects every cell in the two
columns that don't have a formula equivalent to the formulas in
the row of the active cell...with each column treated
independently.
The right figure shows that Excel selected three cells, which
have these formulas:
B4: =1+5
C5: =3+3
C6: =303
Each formula, as you can see, is quite different from the
formulas in cells B2 and C2.
Select Empty Cells
Finally,
suppose you get a table of data that looks like this, with sales
for each product listed as shown. This layout needs to be
changed, because you can't sort the data, and you can't
summarize it with functions like SUMIFS.
What you need to do, of course, is to fill in the empty cells
in column A with the product names above each cell.
You can do this easily...
1. Select the range A2:A9, which is the range of cells you
need to modify.
2.
Launch the Go To Special dialog; choose the
Blanks option; then press OK.
After you do so, Excel selects the empty cells, as shown here.
3. In your formula bar, type the equal sign followed by a
reference to the cell immediately above the active cell. So, in
this example, you would type =A2.
4. Hold down your Ctrl key and then press
Enter. After you do
so, Excel enters the same formula in every selected cell. That
is, it fills the empty cells with formulas
that return a reference to the cell immediately above each cell. By
doing so, it fills the empty cells with the correct
product labels.
5. To turn all the formulas into values, select the range
A2:A9 again, copy the range, and the choose Home,
Clipboard, Paste, Paste Special, Values, OK.
