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 youve 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, youll 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 cant just format-% it (itll 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