Excel tips and keyboard shortcuts

Useful tips that take a moment to learn, and save a lifetime of frustration.

Using accelarator keys (underscored letters) to access commands quicklyUsing a mouse wastes time.  But almost all menu commands have a letter that is underlined called an accelerator key. 

To open the toolbar menu, press Alt and then the underlined letter (e.g. F for File, E for Edit, O for Formats…).  Then release the Alt key and just type the letter in the desired command that is underlined (for instance S for A for Save As).

·                    Alt- E                                                   => Pulls up Edit menu

·                    then ‘S’ (not holding Alt anymore)         => Pulls up PasteSpecial window

·                    then ‘T’                                                => PasteSpecial Formats

·                    Alt- ‘I’ , ‘N’, ‘D’                                  => Define named range

·                    Alt- ‘I’, ‘F’                                           => Insert a formula into a cell

Keyboard navigation
·                    Ctrl- Arrow Key                                  => jumps to the last non-blank cell if you are currently in a block of text (or next non-blank cell if you are in a blank cell)

·                    Ctrl- Home                                            => goes to cell A1 (upper left corner of sheet)

·                    Ctrl- End                                               => goes to end of active spreadsheet (i.e. bottom right of the area you’ve used)

·                    Ctrl- Pgdw                                            => go to next sheet in a workbook

·                    Ctrl- Pgup                                            => go to previous sheet

·                    Alt- Pgdw                                            => right one complete screen

·                    Shift-Alt- Pgdw                                    => left one complete screen

·                    F5 / Ctrl-G                                           => Goto a cell, range, etc.

Within (or not within) a selection, you can move around using these keys:
·                    Enter                                                    => one cell down (you can change that setting in Tools- Options-Edit: “On Enter Move Down”)

·                    Shift- Enter                                           => one cell up

·                    Tab                                                      => one cell right

·                    Shift- Tab                                             => one cell left

·                    To find the bottom right hand corner of a selection: If you are in the upper left corner of a selection and hit Shift- Tab, you’ll move to the bottom right, which is really useful when you want to see the range of what you selected

Selecting cells without using the mouse
·                    Shift =>  To select a cell without using the mouse, move around the spreadsheet holding down the Shift key. 
Other special functions are:

·                    Ctrl- *  (i.e. Ctrl- Shift-“8”)       => select the block of data (e.g. table) that you are currently in..

·                    Ctrl-D => to fill in a series Down:  highlight one or more cells that you want to use as a pattern for a whole series, plus blank cells below it that you would like to put that series in, and hit Ctrl-D — it will fill in the blank cells selected Downwards (e.g. if A1 and A2 have “1? and “2? in them respectively, and you highlight A3:A10, by pressing Ctrl-D, cell A3 will have “3?, A4 “4?, etc.)

·                    Ctrl-R => to fill in a series to the Right

Formatting with keys

·                    F2                                            => Displays the formula used in a cell.

·                    Ctrl- 1                                      => pulls up cell properties window. Also works for chart items!

Charts

·                    Format all the fonts for a chart at once: Select Format Chart Area, then the Font tab

·                    To select hard to access parts of a chart (the miniscule data series, the elusive data labels, whatever): click on anything in the chart, then use up and down arrows til you get it.

Inputting text in a cell
·                    Alt- Enter                                 => If you are typing in a cell and hit Enter, it jumps to the next cell.  But if you hold down Alt-Enter, it will create a new paragraph in the cell itself so you can put multiple lines in a cell… useful for including addresses!

·                    &                                             => Concatenates two strings (i.e. joins words, lines of text) (e.g. if A1: , A2: and A3: <=A1&A2> then A3: “LizAab”)

·                    &” “&                                       => To insert a space character in a formula.  For instance, if you wanted to write Liz Aab instead of LizAab in the above example, you would write <=A1&" "&A2>

PasteSpecial- Operators
To get from a number like 100 into 100%, you can’t just format-% it (it’ll read 10000%). So you want to divide all of a selection by 100. Do this:

(1) Type 100 in an available cell

(2) Select that cell

(3) Copy (Ctrl-C)

(4) Highlight the text you want to convert

(5) PasteSpecial- Operation-Divide

File system navigation when saving/opening a file

·                    Getting into the Folders field: If you are in the “Save File As:” field, press Shift- Tab to back into the Folder selection area.

·                     Moving up in File Tree: From the Folders area, hit Backspace to move up one level in the file system (to its parent folder)

3 thoughts on “Excel tips and keyboard shortcuts

  1. Pingback: Business Hacks » Excel-lent Shortcuts on BNET

  2. Liz, could you please share some observations about whats going on with the stock market in China. Is “playing the market” fairly widespread or hobby of the select few? How do the Chinese feel about the market? Do they view it as investing or pure speculation.

    Would you venture to voice an opinion on how the middle class would react if the market went down by 50%?

    yevgeny

Leave a Reply