Friday, March 16, 2012

Excel 2010: 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:

  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.


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.” 


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). 

 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

Shift 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:

 

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).

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*?

    ReplyDelete
  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.

    ReplyDelete
  3. This is perfect. On the internet, there are all these complicated explanations. Love this Old-School!

    ReplyDelete