Excel tips and keyboard shortcuts

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

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)