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
· 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!
· 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>
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)