Excel: Shortcuts for Selecting a Cell, Highlighting a Range, Going to the End of your Data

 
 
 
I have found that I understand Excel because I seem to think in Excel terms.  Even before computers (yes, I’m as old as dirt), I would create very rough, rough-drafts, then clean them up.  This is how to do Excel.  Put your data in first, then move it around until it is presentable to you, then format your cells to make presentable to others. 
 
That said, first select the cells or range you want to change, then apply the formatting.  There are several ways to select your cells, often with varying methods of completing the same task.  Remember, Excel is the third generation of popular spreadsheets:  Visicalc, then Lotus 123, now Excel.  PCs that ran Visicalc didn’t have a mouse, so there were lots of cursor shortcuts.  Here’s a few of the handy ones:
 
Function Keys– that row of keys at the top of your keyboard:  F1, F2, etc.  There is one that is very handy for moving around in Excel:
 
Excel Selecting F5  F5 – This is the GoTo button.  Sometimes you don’t want to take your hands off the keyboard and use your mouse.  You can press F5 and type in cell coordinates, as A1, to move right to a cell.  This is especially handy if you have named your cells or ranges.
 
Excel Selecting Go To
 
Directional Keys– These are those odd keys on the right that include cursors (, , , and ), Page Up, Page Down, Home, End.  Here is a good layout diagram from a website called “All-About-Computer-Parts.” 
 
Excel Keyboard
 
All these keys are self-explanatory except for the End key.  Think of End as the “End-of-the-Line” key.  If you start with your cursor at the top cell in a column in a table, when you press End, then the Down cursor, the cursor will jump to the last cell in that column that has something in it.   If you start with an empty cell, End/Down will jump down to the last empty cell in that column.  Think of it this way:  when you press End in conjunction with the cursor keys (↑, ↓, ←, and →), your cursor will jump to the last cell in that row or column that is similar (empty if you start on empty, full if you start on full).
Excel Cursor Keys
 
 Paste this short spreadsheet into Excel, then follow its instructions.  
 
Start here.
 Press End and ↓
(the down cursor)
to jump to the bottom
of this column
of text quickly.
Now press End and ↑
(the up cursor)
to jump to the top
of this column
of text quickly.
.
Command Keys – Those unique computer keys:  Shift, Ctrl, Alt, Windows, Esc
 
Excel Selecting ShiftShift is powerful when used in combination with the End/Down keys.  Click on one cell, hold down shift and click on another cell.  (Go ahead, do this and see what happens.)  You will select an entire range, like dragging.  Now combine it with End/Down.  You can select a whole column of data without having to drag and scroll down (and down and down and down).
 
 
 
Now start here.
Press Shift, End and ↓
(the down cursor)
to jump to the bottom
of this column
of text quickly
and select
all the cells
between the top
and the bottom
cells.
 
From the bottom, see what happens when you combine End/Up (or Right or Left).
 
By adding Shift, you now get this:

Excel Selecting Shift Down Range

It’s all selected and ready for formatting or copying.

 
 
 
 
 
 
 
 
Ctrl (or Control) is handy, too.  Holding down the Ctrl button while you select cells (or rows or columns) selects parts of your spreadsheet that aren’t next to each other.  Let’s say you want to turn rows 1, 3 and 5 grey.  Click the row numbers while you are holding down Ctrl.  They will all be highlighted or selected, then change the fill color to grey (or some more interesting color).
 
Excel Selecting Ctrl
 
So remember, use all the shortcuts at your fingertips to select your cells or range of cells first, then do your formatting.  Please format to make your spreadsheet easier to read, do not format to make it a work of modern art which obscures your data.
 

4 Comments

  1. What if I want to set up conditional formatting that I want to change the font color of everything = 0 red from cell $A$5 to the end of the contents of column A? Would it be $A$5:$A*?

    Like

  2. I need a bit more information. From what you're asking, I think it would be better to set up your data like a table first, so the highlighting is only in the portion of column A that is in the table. Or perhaps format the entire column A and change the formatting for the first five rows.

    Like

Leave a Reply to LORD Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s