Cells and Ranges

Source: J-Walk Bench

• A1: The steak sauce cell
• IM21: The legal drinking age cell
• K9: The dog cell
• AK47: The assault weapon cell
• IV2: The second intravenous solution cell
• B9: The malignant cell
• HI5: The alternate handshake cell
• AH:HA: The discovery range
• F16: The fighter jet cell
• AM:FM: The radio range
• ET2: The Brute' cell
• AW42: The root beer for two cell
• BU:BU: The erroneous range
• BY:BY The farewell range
• IC2: The double-vision cell
• IQ100: The average intelligence cell
• HO:HO: The Santa Claus range
• GO2: The destination cell
• FU2: The same to you cell
• EX2: The second former spouse cell
• CU8: The oil-rich country cell
• BU10: The shirt fastener cell
• BC49:BD1: The '96 Presidential Election results range
• AG1:GB1: The '00 disputed Florida vote range
• T42: The old soft-shoe cell
• U2: The Irish rock group cell
• BI123: The Lotus marketing slogan cell
• C4: The explosive cell
• V8: The vegetable juice cell
• R2:D2: The android range
• I1:U1: The tied game cell
• AP:ES: The Simian range
• H8:U2: The ex-wife range
• IN2:CA9: The dog-lover range
• AC:DC: The electric range
• D84:U2: The double date range
• I812: The Monica Lewinsky meets Linda Lovelace cell
• I12:CU2: The "when can we meet" cell

Creating Simple Macros

This is the extracts of an presentation that I had prepared for an Training on Creation Simple MS Excel Macros.

Link to the file: Excel Macro Training.ppt

Introduction

• We do a lot of repetitive tasks like copy pasting, formatting etc.
• A macros help us to automate tedious or repetitive tasks with ease of a click of a button or shortcut key.
• A macro is a set of instructions that tells Excel to perform one or more actions for you. Macros are like computer programs, but they run completely within Excel.
• Macros can carry out sequence of actions much more quickly than you could do yourself.

Agenda

There are 2 ways to create a a macro: You can record it, or can build it by entering instructions in a module. Either way your instructions are encoded in the programming language MS VBA.

• You’ll learn how to rerecord and execute a simple macro.
• Then you will learn how to view the recorded macro and make it more useful by doing some simple editing.

Overview

The overall process for recording a macro consists of three steps.

• Start the macro recorder and supply a name for the macro.
• Perform the actions you want to record
• Stop the macro recorder.

Using the Macro Recorder

Rather than type macros character by character, you can have Excel create a macro by recording the menu commands, keystrokes, and other action needed to accomplish a task.

Lets see how to create a macro to draw a boarder around a selected area.

1. Select the small area in a blank work sheet
2. Choose Tools>Macro>Record New Macro. Excel displays the Record Macro dialogue box.
3. Assign Name to the macro.
4. Assign a key combination to the macro by entering a letter.
5. Store the macro in the currently active work book.
6. Enter a description for the macro in the description box.
7. To begin recording click OK. Excel displays the message Recording in the status bar and Stop Recording tool bar.
8. Press Ctrl+1 and draw borders in the borders tab.
9. Click the Stop Recording Macro button on the Stop Recording toolbar.

Running a Macro without Using a Keyboard Shortcut

• You don’t have to know a macro’s key combination to run the macro. Instead, you can use the Macro dialog box:

• Choose Tools>Macro, Macros to display the dialog box.

• Select the name of the macro, and click Run.

• You also can use the Macro dialog box to view and edit macros, as you’ll see in the next section.

Behind the Scenes: The VBA Environment

• Now that you’ve recorded your macro, let’s find out what Excel did. When you clicked OK in the Record Macro dialog box, Excel created something called a module in the active workbook. As you drawn a table in the worksheet, Excel recorded your actions and inserted the corresponding VBA code in the module.
• The new module doesn’t appear with the other sheets in the workbook; to view the module, choose Tools, Macro, Macros. Next, select the “Border” macro, and click the Edit button.
• The Visual Basic Editor (VBE) starts up, and the module that contains the “Border” macro appears.
• The first and last lines of the code act as the beginning and endpoints for the macro you’ve recorded; a Sub statement starts the macro and names it, and an End Sub statement ends the macro. You’ll notice that special VBA terms, called keywords, are displayed in dark blue.

Adding Code to an Existing Macro

• Suppose you’ve recorded a macro that enters a series of labels, sets their font, and then draws a border around them. Then you discover that you forgot a step or that you recorded a step incorrectly—you chose the wrong border format, for example. What do you do?
• To add code to an existing macro, you can record actions in a temporary macro and then transfer the code into the macro you want to change. For example, to the “Border” macro a step that sets font and border options for the table, follow these steps:

1. Choose Tools, Macro, Record New Macro. Excel presents the Record Macro dialog box. In the Macro Name box, enter MacroTemp and click OK. Excel displays the Stop Recording toolbar.

2. Choose Format, Cells, and click the Font tab. Select Trebuchet MS, 10-point, and Bold Italic. Then click OK to apply the formats.

3. Click the Stop Recording button on the Stop Recording toolbar.

4. Choose Tools, Macro, Macros. In the Macro dialog box, select MacroTemp and click Edit.

5. A window appears that contains the original macro you recorded plus the MacroTemp macro.

6. Select all the code inside the macro—from the line beginning With through the line beginning End With—and then choose Edit, Copy.

7. Scroll up to display the “Border” macro

8. Click at the penultimate that contains this statement:

End Sub

9. Press Enter to create a blank line. Then position the insertion point at the beginning of the blank line.

10. Choose Edit, Paste.

11. Scroll back down and delete the entire MacroTemp macro, from the Sub statement to the End Sub statement.

To test the macro, return to Excel (press Alt+F11 or select the Excel button on the taskbar). Clear the company name and address that you entered earlier when you recorded the macro. Then press Ctrl+Shift+A.

Using the Personal Macro Workbook

• When you recorded the “Border” macro earlier, you placed the macro in a module that belongs to the active workbook. A macro that has been placed in a module is available only when the workbook containing the module is open.
• To make a macro available at all times, store it in the Personal Macro Workbook. This workbook is normally hidden. you can unhide it by choosing Window, Unhide and selecting Personal in the Unhide dialog box.
• If you don’t see the Personal file in the Unhide dialog box, or if the Unhide command is unavailable, you have not yet created a Personal Macro Workbook.
• To create one, begin recording a macro, as described earlier in this chapter, andselect the Personal Macro Workbook option in the Record New Macro dialog box. Excel creates the Personal Macro Workbook and places its file (Personal.xls) in the XLStart folder.
• Excel opens Personal.xls, as it does any other file in the XLStart folder, each time you start Excel.
• Because the Personal Macro Workbook is always available when you work in Excel, it’s a good place to record macros that you want to be able to use in any workbook.

Going On from Here

• You’ve learned how to create macros with the help of the macro recorder. As you learn more about the VBA programming language (a subject beyond the scope of this session), you’ll notice that the macro recorder often creates more code for a task than you really need. The recorder adds these lines because it didn’t (and couldn’t) know they weren’t necessary. You can edit them out without changing the functionality of the macro in any way.
• As you move toward expertise in VBA, you will probably find yourself creating most of your code directly in the VBE, bypassing the recorder altogether

Conclusion

• The best way to use the lessons learned today is to practice it. Use the macros in day to day office work.
• Try to innovate and experiment on the things you have learned.
• Share your acquired knowledge with others.

Spreadsheet Glossary

Source: J Walk.com

Audit: Annual event where CAs come in and officially point out mathematical errors in your spreadsheets.

Auto-Save: What your spreadsheet does immediately after you make a huge mistake and just before you press Undo. If you have a sound card, you may be able to hear your program laughing at you.

Backup: Many spreadsheet programs will automatically create a backup of your current file. This gives you two copies of the same non-balancing file.

Cell: This is where you spend the rest of your life when your boss has made you spend several nights at the office until a large project is finished. The cell will have bars if you are a type A personality, and will be padded if you are type B.

CIRC: See Circular Reference.

Circular Reference: See CIRC.

Cursor: What one becomes who sits in front of the same spreadsheet that won't balance for more than 2 hours.

Cut, Copy and Paste: What you do with your printouts to get something intelligible. See Table.

Format: Visual enhancements to spreadsheets to draw attention away from inaccuracies.

Formula: Mathematical equations that display as ERR, DIV/0 or N/A.

Frame: What you do with your first error free spreadsheet. (Contact MENSA if you did this in less than 1 month.)

Goal Seek: When you know the answer (because you did it on your calculator in 10 seconds) but can't get the spreadsheet to work right. Also known as Backsolve.

GPF: What happens when you finally get your spreadsheet working correctly. But before you've had a chance to save it.

Group Mode: The ability to populate a group of worksheets with the same error, saving yourself lots of time.

Import: Ability to get huge amounts of data from an outside source allowing you to create hundreds and thousands of rows of seemingly meaningful data.

Invalid File: Error you get once you have finally perfected your spreadsheet and attempt to load it the next day to print. Only occurs if you don't do regular backups.

Link: Ability to get erroneous information from a file without actually loading it into memory.

Macro: Automated mistakes, made at the speed of light.
Print Preview: Lets you know if your printout will be Portrait or Landscape. Information on the screen bears no resemblance to what actually prints out.

Solver: Spreadsheet tool usually used by geeks who want to bring their computer's processor to its knees. No valid data ever obtained, but it makes you feel good that you forced your computer to think for more than half a second.

Sort: Fast method to randomize a column of formula references.
Table: Furniture used to spread out all of your papers and arrange them so they look like what is on the screen. See Cut, Copy and Paste.

Titles, Horizontal: Headings printing on the left margin you wanted at the top of your document.

Titles, Vertical: Headings printing on the top margin you wanted down the left side of your document.

Undo: Feature that enables you to revert from the current spreadsheet with 47 error values to where you were before you made your mistake when you only had 13 errors.

What-If Analysis: The process of automatically generating detailed projections for hundreds of different scenarios - not one of which has a snowball's chance in hell of being even remotely possible.

Window: Ability to look at multiple pieces of multiple files simultaneously. While this may sound attractive, you must have at least three hands to navigate these windows to successfully build formulas - unless you have a mouse, then you need four hands.

Wizard: Interface enhancements that enable you to create complex and sophisticated errors at unprecedented speeds.

WYSIWYG: What You So Intensely Wish You'd Got

Color Codes in MS Excel

If you want to apply some color in your Excel Sheet through the UDF, then use the following Color codes:

SL No.	Color	Color Code
1	White	2
2	Lavender	39
3	Pale Blue	37
4	Light Turquoise	34
5	Light Green	35
6	Light Yellow	36
7	Tan	40
8	Rose	38
9	Grey 25%	15
10	Plum	54
11	Sky Blue	33
12	Turquoise	8
13	Bright Green	4
14	Yellow	6
15	Gold	44
16	Pink	7
17	Grey 40%	48
18	Voilet	13
19	Light Blue	41
20	Aqua	42
21	Sea Green	50
22	Lime	43
23	Light Orange	45
24	Red	3
25	Grey 50%	16
26	Blue-Grey	47
27	Blue	5
28	Teal	14
29	Green	10
30	Dark Yellow	12
31	Orange	46
32	Dark Red	9
33	Grey 80%	56
34	Indigo	55
35	Dark Blue	11
36	Dark Teal	49
37	Dark Green	51
38	Olive Green	52
39	Brown	53
40	Black	1

Ctrl+Shift+Numericals Excel Keyboard Shortcuts

Short Cut	Name
Ctrl+Shift+`	Apply General format
Ctrl+Shift+1	Apply Number format
Ctrl+Shift+2	Apply Time format
Ctrl+Shift+3	Apply Date format
Ctrl+Shift+4	Apply Currency format
Ctrl+Shift+5	Apply percentage format
Ctrl+Shift+6	Apply Exponential number format
Ctrl+Shift+7	Apply an outline border to selection
Ctrl+Shift+8	Select Box
Ctrl+Shift+9	Display hidden rows in selection
Ctrl+Shift+0	Display hidden columns in selection
Ctrl+Shift+[	Go to All the Precedents
Ctrl+Shift+]	Go to All the Dependents
Ctrl+Shift+\	In a selected column, select the cells that don't match the value in the active cell.
Ctrl+Shift+.	Copies the cell to theleft (similar to Ctrl+D)
Ctrl+Shift+,	Fill as Above
Ctrl+Shift+'	Fill as Above and let it edit
Ctrl+Shift+/
Ctrl+Shift+`
Ctrl+Shift+-	Remove outline border from selection
Ctrl+Shift+:	Insert the current time
Ctrl+Shift+=	Insert Cee/Row Funcion- Similar to Insert>Cell Menu

Ctrl+Shift+Alphabet Excel Keyboard Shortcuts

Short Cut	Name
Ctrl+Shift+A
Ctrl+Shift+B
Ctrl+Shift+C
Ctrl+Shift+D
Ctrl+Shift+E
Ctrl+Shift+F	Short Cut to Fonts
Ctrl+Shift+G
Ctrl+Shift+H
Ctrl+Shift+I
Ctrl+Shift+J
Ctrl+Shift+K
Ctrl+Shift+L
Ctrl+Shift+M
Ctrl+Shift+N
Ctrl+Shift+O	Select all cells that contain a comment
Ctrl+Shift+P	Shortcut to Font Size
Ctrl+Shift+Q
Ctrl+Shift+R
Ctrl+Shift+S
Ctrl+Shift+T
Ctrl+Shift+U
Ctrl+Shift+V
Ctrl+Shift+W
Ctrl+Shift+X
Ctrl+Shift+Y
Ctrl+Shift+Z

Ctrl+Numerical Excel Keyboard Shortcuts

Short Cut	Name
Ctrl+1	Open the Format Cells dialog box
Ctrl+2	Bold
Ctrl+3	Italics
Ctrl+4	Underline
Ctrl+5	Strike through the selection
Ctrl+6	Display Place Holders for Objects
Ctrl+7	Show /Hide Standard Tool Bar
Ctrl+8	Show Outline Symbols
Ctrl+9	Hide selected rows
Ctrl+0	Hide selected columns
Ctrl+.	Blank
Ctrl+,	Blank
Ctrl+'	Deletes the contents of the cell and prepares the same for Editing
Ctrl+/	Select the array containing the active cell.
Ctrl+`	Show Formula
Ctrl+-	Delete Cell/Row Similar to Edit>Delete
Ctrl+;	Insert the current date
Ctrl+=	Blank
Ctrl+[	Select cells that a selected formula directly references
Ctrl+]	Select formulas that directly reference the active cell
Ctrl+\	In a selected row, select the cells that don't match the value in the active cell.

Ctrl+Alphabet Excel Keyboard Shortcuts

Short Cut	Name
Ctrl+A	Select All
Ctrl+B	Bold Selection
Ctrl+C	Copy the selected text or objects to the Clipboard
Ctrl+D	Fill data down through selected cells
Ctrl+E	Blank
Ctrl+F	Open the Find tab of the Find And Replace dialog box
Ctrl+G	Open GoTo
Ctrl+H	Open the Replace tab of the Find And Replace dialog box
Ctrl+I	Italicize the selection
Ctrl+J	Blank
Ctrl+K	Insert a hyperlink
Ctrl+L	Create List
Ctrl+M	Blank
Ctrl+N	Create a new workbook
Ctrl+O	Open a workbook
Ctrl+P	Print a workbook
Ctrl+Q	Blank
Ctrl+R	Fill data through selected cells to the right
Ctrl+S	Save a workbook
Ctrl+T	Blank
Ctrl+U	Underline the selection
Ctrl+V	Paste the contents of the Clipboard
Ctrl+W	Close a workbook
Ctrl+X	Cut the selected text or objects to the Clipboard
Ctrl+Y	Repeat last action
Ctrl+Z	Undo last edit

Excel Macros

Over the years I have been developing Macros to be used by me in my day to day work. Here are the links to the files that I have prepared for the same.

Copy the PERSONAL.XLS to Documents and Settings>Your User Name>Application Data>Microsoft>Excel>XLSTART.

Please note that these folders have to be unhidden. You can do that from the folder options.
The PERSONAL. XLS will automatically open at when Excel is started.

the links are:

Macro file - PERSONAL.xls
Macro List

Copying Tables from the Internet:

It is observed that many times while copying tables with numbers from Internet or some Databases, the numbers are not useable, in the sense that if you add them no result is found. This happens due to the fact that the data copied also includes some hidden text potion preceding the numbers in each cell that is not visible. What to do? Simple copy hidden text from one cell and do a find the hidden text (Ctrl+H) and replace that text with blank data (Ctrl+V; Ctrl+A)

Excel Keyboard Shortcuts

Source: E-mail forward

Since it is the first posting to this site it is befitting that I start with the most common Shortcut Keys.

I have noticed that many people are more comfortable with using mouse, rather than using the keyboard to work in Excel. However I strongly feel that it significantly slows down the pace of work. Common difficulty in using the keyboard is that people can’t simply remember the shortcut keys. Here I would advise to take the same approach, when you first learnt to ride a bicycle. Practice.

Rather than trying to read and remember the shortcut keys so that you can apply, you try to learn the shortcut keys by applying them while you work. First step in doing that would be to access the most commonly used menu function using keyboard. The second step would be to use the other keyboard shortcuts that are inbuilt in the Excel.

Hope the following shortcut keys are useful to you.

Happy Excelling!

Keys for moving and scrolling in a worksheet or workbook
Arrow keys: Move one cell up, down, left, or right
CTRL+arrow: key Move to the edge of the current data region
HOME: Move to the beginning of the row
CTRL+HOME: Move to the beginning of the worksheet
CTRL+END: Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottom-most used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1
PAGE DOWN: Move down one screen
PAGE UP: Move up one screen
ALT+PAGE DOWN: Move one screen to the right
ALT+PAGE UP: Move one screen to the left
CTRL+PAGE DOWN: Move to the next sheet in the workbook
CTRL+PAGE UP: Move to the previous sheet in the workbook
CTRL+F6 or CTRL+TAB: Move to the next workbook or window
CTRL+SHIFT+F6 or CTRL+SHIFT+TAB: Move to the previous workbook or window
F6: Move to the next pane in a workbook that has been split
SHIFT+F6: Move to the previous pane in a workbook that has been split
CTRL+BACKSPACE: Scroll to display the active cell
F5: Display the Go To dialog box
SHIFT+F5: Display the Find dialog box
SHIFT+F4: Repeat the last Find action (same as Find Next)
TAB: Move between unlocked cells on a protected worksheet

Keys for moving in a worksheet with End mode on
END: Turn End mode on or off
END, arrow key: Move by one block of data within a row or column END, HOME: Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottom-most used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1
END, ENTER: Move to the last cell to the right in the current row that is not blank; unavailable if you have selected the Transition navigation keys check box on the Transition tab (Tools menu, Options command)

Keys for moving in a worksheet with SCROLL LOCK on
SCROLL LOCK: Turn SCROLL LOCK on or off
HOME: Move to the cell in the upper-left corner of the window
END: Move to the cell in the lower-right corner of the window
UP ARROW or DOWN ARROW: Scroll one row up or down
LEFT ARROW or RIGHT ARROW: Scroll one column left or right
Tip When you use scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to preserve your selection while you scroll through the worksheet, turn on SCROLL LOCK first.

Keys for previewing and printing a document
CTRL+P or CTRL+SHIFT+F12: Display the Print dialog box Work in print preview
Arrow keys: Move around the page when zoomed in
PAGE UP or PAGE DOWN: Move by one page when zoomed out
CTRL+UP ARROW or CTRL+LEFT ARROW: Move to the first page when zoomed out
CTRL+DOWN ARROW or CTRL+RIGHT ARROW: Move to the last page when zoomed
out

Keys for working with worksheets, charts, and macros
SHIFT+F11 or ALT+SHIFT+F1: Insert a new worksheet
F11 or ALT+F1: Create a chart that uses the current range
ALT+F8: Display the Macro dialog box
ALT+F11: Display the Visual Basic Editor
CTRL+F11: Insert a Microsoft Excel 4.0 macro sheet
CTRL+PAGE DOWN: Move to the next sheet in the workbook
CTRL+PAGE UP: Move to the previous sheet in the workbook
SHIFT+CTRL+PAGE DOWN: Select the current and next sheet in the workbook
SHIFT+CTRL+PAGE UP: Select the current and previous sheet in the workbook

Keys for entering data on a worksheet
ENTER: Complete a cell entry and move down in the selection
ALT+ENTER: Start a new line in the same cell
CTRL+ENTER: Fill the selected cell range with the current entry
SHIFT+ENTER: Complete a cell entry and move up in the selection
TAB: Complete a cell entry and move to the right in the selection
SHIFT+TAB: Complete a cell entry and move to the left in the selection
ESC: Cancel a cell entry
BACKSPACE: Delete the character to the left of the insertion point, or delete the
selection
DELETE: Delete the character to the right of the insertion point, or delete the selection
CTRL+DELETE: Delete text to the end of the line
Arrow keys: Move one character up, down, left, or right
HOME: Move to the beginning of the line
F4 or CTRL+Y: Repeat the last action
SHIFT+F2: Edit a cell comment
CTRL+SHIFT+F3: Create names from row and column labels
CTRL+D: Fill down
CTRL+R: Fill to the right
CTRL+F3: Define a name

Keys for working in cells or the formula bar
BACKSPACE: Edit the active cell and then clear it, or delete the preceding character in
the active cell as you edit cell contents
ENTER: Complete a cell entry
CTRL+SHIFT+ENTER: Enter a formula as an array formula
ESC: Cancel an entry in the cell or formula bar
CTRL+A: Display the Formula Palette after you type a function name in a formula
CTRL+SHIFT+A: Insert the argument names and parentheses for a function after you type a function name in a formula
CTRL+K: Insert a hyperlink
ENTER (in a cell with a hyperlink): Activate a hyperlink
F2: Edit the active cell and position the insertion point at the end of the line
F3: Paste a defined name into a formula
SHIFT+F3: Paste a function into a formula
F9: Calculate all sheets in all open workbooks
CTRL+ALT+F9: Calculate all sheets in the active workbook
SHIFT+F9: Calculate the active worksheet
= (equal sign): Start a formula
ALT+= (equal sign): Insert the AutoSum formula
CTRL+; (semicolon): Enter the date
CTRL+SHIFT+: (colon): Enter the time
CTRL+SHIFT+" (quotation mark): Copy the value from the cell above the active cell into the cell or the formula bar
CTRL+` (single left quotation mark): Alternate between displaying cell values and displaying cell formulas
CTRL+' (apostrophe): Copy a formula from the cell above the active cell into the cell or the formula bar
ALT+DOWN ARROW: Display the AutoComplete list Keys for formatting data
ALT+' (apostrophe): Display the Style dialog box
CTRL+1: Display the Format Cells dialog box
CTRL+SHIFT+~: Apply the General number format
CTRL+SHIFT+$: Apply the Currency format with two decimal places (negative numbers appear in parentheses)
CTRL+SHIFT+%: Apply the Percentage format with no decimal places
CTRL+SHIFT+^: Apply the Exponential number format with two decimal places
CTRL+SHIFT+#: Apply the Date format with the day, month, and year
CTRL+SHIFT+@: Apply the Time format with the hour and minute, and indicate A.M. or P.M.
CTRL+SHIFT+!: Apply the Number format with two decimal places, thousands separator, and minus sign (–) for negative values
CTRL+SHIFT+&: Apply the outline border
CTRL+SHIFT+_: Remove outline borders
CTRL+B: Apply or remove bold formatting
CTRL+I: Apply or remove italic formatting
CTRL+U: Apply or remove an underline
CTRL+5: Apply or remove strikethrough formatting
CTRL+9: Hide rows
CTRL+SHIFT+( (opening parenthesis): Unhide rows
CTRL+0 (zero): Hide columns
CTRL+SHIFT+) (closing parenthesis): Unhide columns

Keys for editing data
F2: Edit the active cell and put the insertion point at the end of the line
ESC: Cancel an entry in the cell or formula bar
BACKSPACE: Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents
F3: Paste a defined name into a formula
ENTER: Complete a cell entry
CTRL+SHIFT+ENTER: Enter a formula as an array formula
CTRL+A: Display the Formula Palette after you type a function name in a formula
CTRL+SHIFT+A: Insert the argument names and parentheses for a function, after you type a function name in a formula
F7: Display the Spelling dialog box

Keys for inserting, deleting, and copying a selection
CTRL+C: Copy the selection
CTRL+X: Cut the selection
CTRL+V: Paste the selection
DELETE: Clear the contents of the selection
CTRL+HYPHEN: Delete the selection
CTRL+Z: Undo the last action
CTRL+SHIFT+PLUS SIGN: Insert blank cells

Keys for moving within a selection
ENTER: Move from top to bottom within the selection (down), or move in the direction that is selected on the Edit tab (Tools menu, Options command)
SHIFT+ENTER: Move from bottom to top within the selection (up), or move opposite to the direction that is selected on the Edit tab (Tools menu, Options command)
TAB: Move from left to right within the selection, or move down one cell if only one column is selected
SHIFT+TAB: Move from right to left within the selection, or move up one cell if only one column is selected
CTRL+PERIOD: Move clockwise to the next corner of the selection
CTRL+ALT+RIGHT ARROW: Move to the right between nonadjacent selections
CTRL+ALT+LEFT ARROW: Move to the left between nonadjacent selections Keys for selecting cells, columns, or rows
CTRL+SHIFT+* (asterisk): Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)
SHIFT+arrow key: Extend the selection by one cell
CTRL+SHIFT+arrow key: Extend the selection to the last nonblank cell in the same column or row as the active cell
SHIFT+HOME: Extend the selection to the beginning of the row
CTRL+SHIFT+HOME: Extend the selection to the beginning of the worksheet
CTRL+SHIFT+END: Extend the selection to the last used cell on the worksheet (lowerright corner)
CTRL+SPACEBAR: Select the entire column
SHIFT+SPACEBAR: Select the entire row
CTRL+A: Select the entire worksheet
SHIFT+BACKSPACE: Select only the active cell when multiple cells are selected
SHIFT+PAGE DOWN: Extend the selection down one screen
SHIFT+PAGE UP: Extend the selection up one screen
CTRL+SHIFT+SPACEBAR: With an object selected, select all objects on a sheet CTRL+6: Alternate between hiding objects, displaying objects, and displaying placeholders for objects
CTRL+7: Show or hide the Standard toolbar
F8: Turn on extending a selection by using the arrow keys
SHIFT+F8: Add another range of cells to the selection; or use the arrow keys to move to the start of the range you want to add, and then press F8 and the arrow keys to select the next range
SCROLL LOCK, SHIFT+HOME: Extend the selection to the cell in the upper-left corner of the window
SCROLL LOCK, SHIFT+END: Extend the selection to the cell in the lower-right corner of the window
Tip When you use the scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to keep the same selection as you scroll, turn on SCROLL LOCK first.

Keys for extending the selection with End mode on
END: Turn End mode on or off
END, SHIFT+arrow key: Extend the selection to the last nonblank cell in the same column or row as the active cell
END, SHIFT+HOME: Extend the selection to the last cell used on the worksheet (lowerright corner)
END, SHIFT+ENTER: Extend the selection to the last cell in the current row. This keystroke is unavailable if you selected the Transition navigation keys check box on the Transition tab (Tools menu, Options command).

Keys for selecting cells that have special characteristics
CTRL+SHIFT+* (asterisk): Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)
CTRL+/: Select the current array, which is the array that the active cell belongs to
CTRL+SHIFT+O (the letter O): Select all cells with comments
CTRL+\: Select cells in a row that don't match the value in the active cell in that row. You must select the row starting with the active cell.
CTRL+SHIFT+|: Select cells in a column that don't match the value in the active cell in that column. You must select the column starting with the active cell.
CTRL+[ (opening bracket): Select only cells that are directly referred to by formulas in the selection
CTRL+SHIFT+{ (opening brace): Select all cells that are directly or indirectly referred to by formulas in the selection
CTRL+] (closing bracket): Select only cells with formulas that refer directly to the
active cell
CTRL+SHIFT+} (closing brace): Select all cells with formulas that refer directly or indirectly to the active cell
ALT+; (semicolon): Select only visible cells in the current selection

Keys for selecting a chart sheet
CTRL+PAGE DOWN: Select the next sheet in the workbook, until the chart sheet you want is selected
CTRL+PAGE UP: Select the previous sheet in the workbook, until the chart sheet you want is selected

Keys for selecting an embedded chart
Note The Drawing toolbar must already be displayed.
1. Press F10 to make the menu bar active.
2. Press CTRL+TAB or CTRL+SHIFT+TAB to select the Drawing toolbar
3. Press the RIGHT ARROW key to select the Select Objects button on the
Drawing toolbar.
4. Press CTRL+ENTER to select the first object.
5. Press the TAB key to cycle forward (or SHIFT+TAB to cycle backward) through
the objects until sizing handles appear on the embedded chart you want to select.
6. Press CTRL+ENTER to make the chart active.

Keys for selecting chart items
DOWN ARROW: Select the previous group of items
UP ARROW: Select the next group of items
RIGHT ARROW: Select the next item within the group
LEFT ARROW: Select the previous item within the group

Keys for working with a data form
ALT+key, where key is the underlined letter in the field or command name: Select a field or a command button
DOWN ARROW: Move to the same field in the next record
UP ARROW: Move to the same field in the previous record
TAB: Move to the next field you can edit in the record
SHIFT+TAB: Move to the previous field you can edit in the record
ENTER: Move to the first field in the next record
SHIFT+ENTER: Move to the first field in the previous record
PAGE DOWN: Move to the same field 10 records forward
CTRL+PAGE DOWN: Move to a new record
PAGE UP: Move to the same field 10 records back
CTRL+PAGE UP: Move to the first record
HOME or END: Move to the beginning or end of a field
SHIFT+END: Extend a selection to the end of a field
SHIFT+HOME: Extend a selection to the beginning of a field
LEFT ARROW or RIGHT ARROW: Move one character left or right within a field
SHIFT+LEFT ARROW: Select the character to the left
SHIFT+RIGHT ARROW: Select the character to the right

Keys for using AutoFilter
Arrow keys to select the cell that contains the column label, and then press
ALT+DOWN ARROW: Display the AutoFilter list for the current column
DOWN ARROW: Select the next item in the AutoFilter list
UP ARROW: Select the previous item in the AutoFilter list
ALT+UP ARROW: Close the AutoFilter list for the current column
HOME: Select the first item (All) in the AutoFilter list
END: Select the last item in the AutoFilter list
ENTER: Filter the list by using the selected item in the AutoFilter list

Keys for outlining data
ALT+SHIFT+RIGHT ARROW: Group rows or columns
ALT+SHIFT+LEFT ARROW: Ungroup rows or columns
CTRL+8: Display or hide outline symbols
CTRL+9: Hide selected rows
CTRL+SHIFT+( (opening parenthesis): Unhide selected rows
CTRL+0 (zero): Hide selected columns
CTRL+SHIFT+) (closing parenthesis): Unhide selected columns

Keys for the PivotTable and PivotChart Wizard
UP ARROW or DOWN ARROW: Select the previous or next field button in the list
LEFT ARROW or RIGHT ARROW: Select the field button to the left or right in a multicolumn field button list
ALT+C: Move the selected field into the Column area
ALT+D: Move the selected field into the Data area
ALT+L: Display the PivotTable Field dialog box
ALT+P: Move the selected field into the Page area
ALT+R: Move the selected field into the Row area

Keys for page fields in a PivotTable or PivotChart report
CTRL+SHIFT+* (asterisk): Select the entire PivotTable report Arrow keys to select the cell that contains the field, and then ALT+DOWN
ARROW: Display the list for the current field in a PivotTable report Arrow keys to select the page field in a PivotChart report, and then ALT+DOWN
ARROW: Display the list for the current page field in a PivotChart report
UP ARROW: Select the previous item in the list
DOWN ARROW: Select the next item in the list
HOME: Select the first visible item in the list
END: Select the last visible item in the list
ENTER: Display the selected item
SPACEBAR: Select or clear a check box in the list

Keys for laying out a PivotTable or PivotChart report
1. Press F10 to make the menu bar active.
2. Press CTRL+TAB or CTRL+SHIFT+TAB to select the PivotTable toolbar.
3. Press the LEFT ARROW or RIGHT ARROW key to select the menu to the left or
right or, when a submenu is visible, to switch between the main menu and submenu.
4. Press ENTER (on a field button) and the DOWN ARROW and UP ARROW keys to
select the area you want to move the selected field to.
Note To scroll to the top or bottom of the field list, press ENTER on the More Fields
or button.

Keys for grouping and ungrouping PivotTable items
ALT+SHIFT+RIGHT ARROW: Group selected PivotTable items
ALT+SHIFT+LEFT ARROW: Ungroup selected PivotTable items

Keys to use with the OLAP Cube Wizard
Use these keys to complete Step 2 in the OLAP Cube Wizard.
To move a field from the Source fields list to the Dimensions box
1. Press the TAB key to select the Source fields list.
2. Press the UP ARROW or DOWN ARROW key to select the field you want to move.
Because you cannot change the order of a field after you move it, select first the field
you want to appear at the top of the Dimensions box.
3. Press the TAB key to select the > button, and then press ENTER.
4. To move other fields, repeat steps 1 through 3.

To move a field to a lower or higher level in the Dimensions box
1. Press the TAB key to select the Dimensions box.
2. Press the UP ARROW or DOWN ARROW key to select the field you want to move.
3. Press CTRL+X.
4. Press the UP ARROW or DOWN ARROW key to select where you want to move the
field.
5. Press CTRL+V.