Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel navigation tips from AccountingWEB members

by
25th Dec 2005
Save content
Have you found this content useful? Use the button above to save it to your profile.

In early 2004, a member going under the name of Chancer asked an Any Answers question about What's the most useful shortcut in Excel?

The item drew 2,000 reader and 32 comments. This article pulls together tips from that thread and previous postings and IT Zone Expert Guides to help you manage your spreadsheets more efficiently.

Our thanks go to all those members who took part in the debate and who have shared their expertise via Any Answers - it's an area where you all help AccountingWEB to make a difference. The contributors for this community Expert Guide include: Neil Eglintine, Chris Bales, James Thorne, Trevor Reade, Nadine Davey, Simon Hurst, Jay Tanna, Julian Wattam, Alan Webb, Bob Foley, Damian Flynn, Ray Chidell, Peter Malins, Katharine Elliott, Arthur Ely, Duncan Williamson, Nigel Hughes, John Mackay, Mike Rees, Patrick Light, Clint Westwood, John Watson, Celia Eastman, Phil Scherer, David Ganado, Peter Hool, David Carter, Richard Carter and Simon Maclaren.

Contents
1. General Windows tips
2. Excel navigation
3. Worksheet controls
4. Pivot Tables
5. Other ExcelZone Expert Guides

Windows basics

  • Start with the all-time classic: UNDO for when you've made a hash of things:
    Ctrl-Z - Undo Last Action
    (can also be accessed in Excel using the bent arrow icon pointing to the left - but the whole point of keyboard shortcuts is that they are quicker and easier on your wrists than using the mouse to point at icons).
  • Basic file management commands:
    Ctrl+N - Create a New File
    Ctrl+O - Open a File
    Ctrl+P - Print File
    Ctrl+S - Save File
  • Keyboard shortcuts for faster cutting, copying and pasting:
    Ctrl+C - Copy Selection
    Ctrl+X - Cut Selection
    Ctrl+V - Paste Selection
  • Find/replace text commands
    Ctrl+F - Find Text
    Ctrl+H - Replace Text
  • Navigate through dialogue boxes
    Tab - jump to next available option (or Shift+Tab to go backwards)
  • Jump between Windows applications
    Alt+Esc - Cycles through open applications.
    Alt+Tab - Cycles through a menu of icons showing all open applications (or Alt+Shift+Tab to go backwards through the list)
  • To minimise all visible Windows, hold down the Windows/Start key and press M. This is very useful when you've got a lot of windows running and just want to get to something on your desktop.
  • Windows key+E -Open Windows Explorer.
  • Right click - Will bring up a selection of commonly used application commands; they may require one or two more key selections or mouse clicks to execute.
  • Who needs a mouse, anyway?
    It is possible to use most Windows applications without ever having to resort to a mouse. Pressing the Alt key followed by any of the underlined letters in the File menu bar will activate that option. You can then select any of the sub-options by pressing the underlined letter for whichever command you want.

    Many more Windows tips are available in Simon Hurst's Windows ProductivITy Kit module, which includes three tutorials: Windows for the Far too Busy; Copying and Moving; and Managing the Desktop and Taskbar. The Windows module on its own costs £30, or it can be bought as part of the entire ProductivITy Kit (see details below).

    Excel Navigation

  • Move around a spreadsheet more quickly with these commands:
    Ctrl+ arrow keys - Jump to beginning/end of row/column
    Ctrl+[ - Takes you to the precedents (the formulae)
    Ctrl+] - Takes you to the cells dependent on the active cell.
  • Go to a range quickly by clicking on the down arrow in the Name Box at the upper left corner of your Excel screen (which displays a list of all range names in the worksheet). Clicking the name of the range to which you want to go will highlight the entire range, with the upper-left cell active.
  • Copying tricks
    Shift+Space - Select the current row.
    Ctrl+Space - Select the current column.
    Ctrl+Shift+[arrow] - Select data in the direction of arrow (goes to the last entry in that direction)
    Ctrl+Shift+8 (ie asterisk)- Select block of all adjacent data
    Ctrl+D - Copy into the active cell from the cell directly above (formulae are updated as with normal copy, paste)
    Ctrl+R - Copy into the active cell from the cell on its left.
    Copying vertically - double click the bottom right of the cell (there must be information in each row of an adjacent column).
    Ctrl+; (semicolon) - Insert today's date
    Ctrl+Shift+; (ie colon) - Insert current time
    Format Painter icon (the paintbrush symbol found among the toolbar options) - Copies formatting to another cell
  • Select multiple cells (not necessarily adjacent ones: Ctrl+Click them if non-adjacent), type the text you want to enter and press Ctrl+Enter. The same text will be entered in all the selected cells.
  • Double click the bottom right hand of the top cell of a column to automatically fill down - quicker and more accurate than dragging, especially if you have hundreds of rows.
  • Adding & deleting cells, rows and columns
    Ctrl+[Plus] - Add a cell, column or row at the insertion point (depending on what is selected).
    Control+[Minus] - Delete cell, row or column at the insertion point.
  • Excel function keys (some of these are common to all MS Office applications)
    F1 - Help
    F2 - Edit A Cell
    F3 - Displays the paste name box
    F4 - Changes cell reference from relative to Absolute. Click in the cell then press the key to toggle from relative to Absolute references
    F5 - Go To a cell or range name
    F6 - Next Pane
    F7 - Spell Check
    F8 - Activate range extension feature
    F9 - Recalculate Spreadsheet
    F10 - Activate the Menu Bar
    F11 - Insert New Chart
    F12 - Save As
    Shift+F2 - Edit/Create Cell Note
    Shift+F4 - Repeat Find
    Shift+F10 - Activate the Cell Popup Menu
    Shift + F11 - Create new worksheet
    Ctrl+F3 - Display Create a Name Box
    Ctrl+F9 - Minimise Active Worksheet
    Ctrl+F10 - Restore Active Worksheet
  • In the same way that Windows commands can all be accessed via the Alt key, David Ganado explains that using the same technique can save you a lot of time in Excel. Use Alt to get into the normal toolbars but then learn the sequence to carry out specific tasks, for example, pressing Alt, I, R in order, (without having to hold them down simultaneouslywill insert a row, without having to find your cursor, go to the toolbar, and return to the active cell. Other sequences include:
    Alt, W, F - Freeze panes
    Alt, D, F, F - Insert/remove autofilter
    Alt, E, S, V - Paste special (values)
    Alt, E, S, T - Paste special (format)
    Alt, I, R (or C) - Insert row (or column)
    Alt, E, D, R (or C) - Delete entire row (or column)
    Alt, E, D, L (or U) - Delete active cell and shift left (or up)
    Alt, O, C, A - Autofit column width
    Alt, D, G, G (or U) - Group (or ungroup) data
    Alt, T, P, P - Protect/unprotect worksheet
  • Faster close-downs
    Use Alt+F to open the File command menu, then press Ctrl+Shift to select the Exit option.

    Worksheet controls

  • Shift+F11 - Create new worksheet.
  • Moving between worksheets
    Ctrl+PageUp/PageDown - Moves you to the next/previous worksheet.
  • Worksheet Group mode - Ctrl and Click on sheets to Group and then it is only necessary to enter changes on one sheet, and this affects all grouped sheets simultaneously.
  • Use Format Painter (see 'copying tricks' above) to copy all the formattting from one worksheet to another:
    1. Click the Select All button (in the top left corner) to highlight the contents of the sheet you want to copy
    2. Click the Format Painter, then select the sheet tab at the bottom of the screen for the sheet that you want to receive the formatting, or open another Excel file that you want to format.
    3. Click on the Select All button in the new sheet. All of your formatting will transfer.

    Read the Manual
    Simon Maclaren, a contributor to a June 2002 thread Excel tips thread started by Jay Tanna, suggested: "There are always new tricks to be learnt. One of the best ways is to print out the shortcut Help screens. (go to Help, Index and type in shortcut). Read it from time to time - there is too much in it to take in at one reading. This is about the best method I know of increasing my Excel knowledge."

    Pivot Tables
    Hidden away among Excel's command structure is one of its most powerful features - the pivot table function, which gives you the ability to "slice and dice" data and analyse it form different persepctives. IT Zone consultant David Carter has written a series of tutorials on the topic:

  • Interested in Pivot Tables? Start here
  • How to make formatting 'stick' in Pivot Tables

    ExcelZone Expert Guides

  • ExcelZone - Tools and tips for spreadsheet users
  • Gail Perry's Excel Almanac - Top Productivity Tips
  • Top 20 Excel productivity points by Chris Bales
  • Office ProductivITy Kit workshop: Advanced Excel
  • Spreadsheet errors and how to avoid them

Replies (10)

Please login or register to join the discussion.

avatar
By User deleted
12th Aug 2004 15:17

Yet more ...
Alt+PageDown moves one screen to the right
Alt+PageUp moves one screen to the left
(on the same worksheet)

The most useful additional 'custom' icons on my toolbar are Freeze Panes, Set Print Area, Paste Values, Clear Border and Light Shading.

Thanks (0)
avatar
By nigelreese
18th Feb 2004 23:29

Ctrl + D - Copy cell above
A useful one I recently came accross is Ctrl D
This copies the contents of the cell immediately above.

Thanks (0)
avatar
By lornajane
18th Feb 2004 18:16

Yet another shortcut
I don't think this one is in the above list either.
To show formulae set in your sheet, type Ctrl + ` (key to the left of the 1 on the top row). This toggles to show formulae and values.
This is a great item - there are already several tips new to me just in a cursory look through - I especially love filling in a formula down a column just by clicking on the black corner of the highlighted cell.

Thanks (0)
avatar
By carnmores
18th Feb 2004 12:11

Toolbars
the best bit of advice i can give is to learn to customise the toolbars.

the example below re copying and pasting can easily achieved by having the appropriate icon on your toolbar. i have one to paste values, one to paste formula etc. I also use icon for addding/deleting rows/columns.

you can also customise your toolbars and the resultant dropdown menus.


have a go!

Thanks (0)
avatar
By paulwakefield1
18th Feb 2004 15:10

Another shortcut
Don't think this one is in the list above.

Ctrl + Tab to cycle through open Excel files

Thanks (0)
avatar
By AnonymousUser
17th Feb 2004 12:43

Three quickies
To highlight the whole spreadsheet from top left to bottom right, press Home then hold shift and press End then Home again.

Paste special is a fantastic function for getting rid of formulas, copying formulas or formats, transposing, etc. Highlight what you want to "paste special" then right click your mouse and select paste special to see what you can do.

The most useful function I have ever come across is the "visible cells only" function - this is well hidden and you wouldn't know about it unless someone has told you about it!! I use it when I am subtotalling and I want to copy or format just the subtotals without touching the data. To do this you run your subtotal function, then click the "2" on the bar that comes up to the left to collapse the sheet to display just the subtotals. Then using the menus select Edit - Go To - Special - Visible Cells Only - OK. Then you will see only the visible cells are highlighted (you can un-collapse the subtotal to see this). From here you can copy and paste, or another useful tip is if you want to format just the subtotals, say, by making the subtotals bold or double underlined, etc.

Thanks (0)
avatar
By User deleted
17th Feb 2004 12:59

Re: Visible Cells Only
The shortcut for visible cells only is Alt-;(ie semi-colon) - far quicker than looking for it through the menus

Thanks (0)
avatar
By carnmores
17th Feb 2004 11:37

Pivot v autosort/subtotals
back in the dim distant past i asked a question about subtotals after sorting and was advised to use a pivot table which was in retrospect blindingly obvious. there is however an equally efficacious method using sort & subtotals, maybe D Carter can show us the best way to use them

Thanks (0)
avatar
By AnonymousUser
17th Feb 2004 16:49

Re: Visible cells only
I must admit, I am a mouse/menu man but I will use Alt-; in the future. Thanks for that!!

Thanks (0)
avatar
By [email protected]
30th Apr 2013 09:55

Best Excel keyboard shortcuts

Surely the best of all keyboard shortcuts, must be the "Alt" button in Excel 2007 and 2010? That one gives you access to all Excel menus, so that makes it pretty powerful.
My second favourite must be "Shift" "F10" because that's equivalent to Excel's right mouse click. With that one you get access to cut ("Ctrl" "X"), copy ("Ctrl" "C"), paste ("Ctrl" "V"), paste special ("Ctrl" "Alt" "V"), insert ("Ctrl" "Shift" "+"), delete ("Ctrl" minus "-"), insert comment ("Shift" "F2") and define name ("Ctrl" "F3").
There's got to be some winners in amongst that lot!

Thanks (0)