Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog

Navigation overview

Navigation is how I refer to the act of “moving around” in Excel.  It includes moving from cell to cell, from worksheet to worksheet, and workbook to workbook. It also includes shortcuts, and the various tools we use to implement shortcuts, like macros, the Ribbon, and the Quick Access toolbar. This page will deal with the first part - going from one place to another in Excel.

Cell to Cell

Most of your time in Excel is spent either entering formulas, formatting, or moving.  This is all about moving, both the cursor and one or more cells. First off, some nomenclature:
This outlined area is one cell.  It’s name is “B4” because it exists at the junction of column B and row 4 This is the formula bar.  When you enter a formula in a cell, it also shows up here.  Clicking on the “fx” brings up function help.
This is the range area, and it shows the current range.  A range is a series of contiguous (touching) cells.
Excel’s cursor takes on a range of shapes, depending on where it is relative to the active cell (or range).  Here, where it is on the border of the active cell, the cursor adds a “+” sign.  That is only possible if the “ctrl” key is also being pressed, and it means that the cell being pointed at is going to be “added” to a selected area. This little square is the cell “handle”.  Clicking and dragging this will copy the cell’s contents into the other cells that it is dragged to.  The context menu (right-click menu) provides more options for what is to be copied.
Let’s look at moving from one cell to another cell in a workbook. MOUSE:  you can use your mouse to move the cursor to another cell and left-click on it.  You probably know that already. TAB:  you can use the keyboard’s TAB key to move to the next cell to the right. ENTER:  you can use any ENTER key to move the mouse down one cell.       Except, if you have been using TAB to move to the right, when you next use ENTER, you will go down one row AND LEFT to the beginning of the range, basically right below the first cell you entered data into. To see this graphically:
To leave each cell (A2 -> B2, B2 -> C2, and so on to H2) I pressed the TAB key.  To leave H2, I pressed the ENTER key.  This brought the cursor back to the “beginning of the line”, just like the Carriage Return on a typewriter. Note that this only works if you type or tab; if you use the mouse or an arrow key, the Carriage Return is turned off, and Enter just puts you in the cell immediately below the active cell. ARROW KEYS:  The arrow keys work exactly as you would expect - Up, Down, Left and Right, one cell for each click. HOME:  The Home key moves the cursor to column A of the current row. END:  This is non-intuitive.  The END key puts the keyboard into “End Mode” (you can see that in the STATUS bar on the bottom left of the Excel window).  You then click an arrow and the cursor will move as follows: If your current cell has a non-blank cell to the right, and you click the right arrow, the cursor will move to the last non-blank cell in the row (to the right). If your current cell has a blank cell to the right, and you click the right arrow, the cursor will move to the next non-blank cell in the row (to the right). So far, we’re just navigating an empty spreadsheet.  New options arise when that spreadsheet has stuff in the cells...
Here’s a data set we’ve used elsewhere; sales data from a variety of stores over 6 years.  Lots of data. Below, the cursor is in cell E23.  We can do all the same things, like ENTER and TAB, that we did earlier, but in addition:
Using arrow keys, with the CTRL key, to move around the active range.
Note that, if you hold down the SHIFT key with the CTRL key, you will select the range between your starting cell and your ending cell.  This is the same as if you had entered “END” mode with the End key. To select the whole range (the active or current range) (cells A19:H28) use CTRL + SHIFT + 8.  This is something you’ll want to remember. This section would be remiss if it didn’t mention the CTRL + G, then “Special”: CTRL + G brings up the “Go To” menu.  You can see I have some existing Named Ranges, like “City” and “Day”.  However, the real value here is the “Special” button at the bottom. When you click the special button, you get some very interesting options: Whichever item you select will result in the selection of all  cells which satisfy the criteria chosen.  This can be useful in checking formulas on your sheet, but the primary use I have is on the right-side column, toward the bottom:  “Visible Cells only”. This option selects ONLY the visible cells, so that you can then copy just the visible cells.  This is really handy if you have filtered data using an autofilter.  If you just select the range of all the cells after you filter data, you will pick up also those cells which have been filtered out.  To copy (and then paste, of course) only the visible, unfiltered cells, use this feature to select “visible cells only”.  We’ll go over a macro to do this at the beginning of the Macros section.