vim

LevSelector.com

New York

home > Excel, Excel VBA (Visual Basic For Applications)

Excel, Excel VBA (Visual Basic for Applications)

On this page:

- resources
- Excel Tips and Shortcuts
- VBA (Visual Basic For Applications)
- more tips
-

Resources:

Books:

- Microsoft Excel 2000 Power Programming with VBA - John Walkenbach
- Excel Essentials: Using Microsoft Excel for Data Analysis and Decision Making (Video Tutorials)
by A. J. Hamish Taylor
- Excel 2000 for Windows Visual Quickstart Guide by Maria Langer
- Microsoft Excel 2002 Formulas (With CD-ROM) by John Walkenbach (Author)
- Special Edition Using Microsoft Excel 2000 by Patrick Blattner (Editor), Laurie Ulrich, Ken Cook, Timothy Dyck
- Troubleshooting Microsoft Excel 2002 by Laurie Ann Ulrich
- Excel Haks - by David & Raina Hawley
- Advanced modeling in finance using Excel and VBA
- Writing Excel Macros with VBA - 2nd ed., 2002 - by Steven Roman

Links:

http://www.personal-computer-tutor.com/excelshortcuts.htm - Excel shortcuts
http://www.chem.tamu.edu/class/majors/commonfiles/exceltutor.htm - short and nice one-page Excel Tutorial
http://www.exceluser.com/explore/questions/vba_textcols.htm - example

Excel Tips

Useful tips

Pressing <End> before pressing <Up> or <Down> arrow forces it to jump all the way up or down.
Example: to select all the cells in a column down to the first blank cell - select the top cell and presss <Shift><End><Arrow down>

Autofill handle - the blacksquare in the lower-right corner of the selection. Double-clicking - will autofill down. Try also dragging in different directions to autofill - or empty. Shift-dragging can be used to insert empty cells.

A1 - relative notation
$A$1 - absolute notation
' C:\mydir\[somefile.xls]Sheet1'!A1
Insert -> Name -> Define (or ctrl-F3) - to create names for cells or ranges
(or create a named constant, if you clear the "refers to" in the definition)
(or create a named formulas if you enter the formula in the "refers to" in the definition)

Connecting names via space - automatically finds intersection. Example: = Qtr2 South

Array formula: =A1:A5*B1:B5
=LEN(A1:A10)
press Ctrl-Shift-Enter

some useful functions: sum, count, counta, countblank, countif, sumif, isnumber, isblank, if( smth , then , else )
=countif(data,"<0")
=countif(data,"yes")
=countif(data,"*")       count cells containing any text
=countif(data,"*s*")   count cells that contain letter s or S
=countif(data,"???")   3-letter words
=sum(if(frequency(data,data)>0,1,0)) - count number of unique numeric values

B1 =trim(A1) - remove excess spaces
C1 =find(" ",B1,1) - locates the first space
D1 =find(" ",B1,C1+1) - locates the second space
E1 =if(iserror(D1),C1,D1) - uses the first spaces if no second space exists
F1 =left(B1,C1) - extracts the first name
G1 =reght(B1,len(B1)-E1) - extracts the last name
H1 =F1&G1 - concatenates the two names

One formula can not be longer than 1024 characters

Paste Special - Values
<ctrl>< ` > (single left quote) - flip between displaying cell values and displaying cell formulas
<F9> - Calculate all sheets in all open workbooks
<shift><F9> - Calculate the active worksheet
ctrl-C, ctrl-V - Copy/paste
ctrl-A - select All
ctrl-Z - undo
ctrl-O, ctrl-S, ctrl-P - open / save / print

<F11> or <alt><F1> - Create a chart that uses the current range
<ctrl><1> - Display the Format Cells dialog box
<F5> - Display the Go To dialog box
<ctrl><enter> - Fill the selected cell range with the current entry
<ctrl>< : > - Insert the current time
<ctrl>< ; > - Insert today's date

ctrl-HOME - Move to the beginning of the worksheet
ctrl-END - Move to the last cell of the last row

shift-SPACE - select the current row
ctrl-SPACE - select the current column

shift-F3 - paste a function into a formula

VBA

Open Excel, hit Alt-F11 - and another window will open where you can write code. Macros, Subroutines, Functions. You can manipulate data in your spreadsheet, add buttons and forms, import/export data from/to databases and files, do complex processing, show data in excel sheets (tables, graphs). You can even make a stand-alone scripts - although I wouldn't recommend this in production environment - better use C# or Perl.

Nowadays VBA is used less because C# is getting more popular - using "Microsoft Visual Studio Tools for Office" or other tools like ExcelDna, "Add-in-Express", etc. Still VBA is a powerful and useful tool.

Links:

http://www.add-in-express.com/ - Add-In-Express - very good, not free (but affordable)
VSTO = Microsoft Visual Studio Tools for Office ( also Getting Started article)- has shortcomings
http://exceldna.codeplex.com/, http://exceldna.typepad.com/ - ExcelDna is an open-source project to integrate .Net into Excel. (DNA = Dot Net for Applications). By Govert van Drimmelen.
http://www.managedxll.com/ - application development kit for creating high performance Excel add-ins
Microsoft Excel XLL SDK - old stuff, avoid it
http://xlw.sourceforge.net/index.shtml - XLW - Excel Wrapper for Excel C API
http://www.planatechsolutions.com/xllplus6-online/toolkit_overview.htm - XLL+ - toolkit for writing Excel add-ins in C++.
http://msdn.microsoft.com/en-us/library/aa168292(office.11).aspx - Excel object model (Office 2003, VS.NET 2003 - depricated managed code paradigm which was used before 2005)
http://www.tangiblesoftwaresolutions.com/ - Source Code Converters
http://www.suitellc.com/alib.htm - Analytics Library (ALib™) - expensive, but reliable library to be used from Excel, C/C++, C#, etc.
Bloomberg provides add-in which adds additional buttons and functions allowing you to pull data from Bloomberg directly into your spreadsheet.

Note:
Starting with 2005 Microsoft has introduced C++/CLI (Common Language Infrastructure) to enable working with .NET from C++. (The first depricated version of this was called MC++ (Managed C++)).

XLA, XLL - library files. You can create XLA using VBA, or XLL using C++, C#, or VB.NET

XLA: Open new excel file, go to VBA editor, enter one ore more functions or macros, save the file as Microsoft Excel Add-In (*.xla). It will usually go in a default directory like: C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns . To use your add-in in some project, go to menu Tools > Add-Ins - and select the add-in in the list. Once it is installed - it will show in VBA project explorer - so you will be able to edit it as needed. You can password-protect it (in VBA editor right-click on it in Project Explorer - go to properties, and select Protection tab). After enering the password don't forget to save from VBA window.
XLL: It is a special type of DLL which have certain functions to work with Excel. You can create DLLs / XLLs using Visual Studio and VSTO (Visual Studio Tools for Office - C++, C#, or VB.NET). But much better to use ADD-IN-EXPRESS. Fastest performance (by far) is achieved when using C#. When creating XLLs you have to take care of many problems, for example converting variable types between C++ and Excel, or to think about threading (Latest versions of Excel are multithreaded, whereas the default for XLL is singlethreaded).

Running VBA from command prompt:

It is possible to invoke excel macro from cmd line. You have to create a macro Workbook_Open() in your xls file. Then when you invoke the excel file - exel starts - and executes this macro.

Option Explicit

Private Sub Workbook_Open()
' do something here
Application.Quit
End Sub

Then save it as myfile.xls - and then run it from cmd line:

start myfile.xls

Note: if you start like this: start excel myfile.xls
then make sure to provide explicit path to your xls file, because otherwise excel will first search for it under "My Documents".

Note: You can use cmd options, for example:

/e - start without displaying the startup screen and creating a new workbook.
/embedded - not displayed on the screen, starts in the background.
/m - create a new workbook that contains a single macro sheet.
/o - re-register excel (in Windows Registry).
/p <folder> - Forces Excel to use the path you specify as the active path instead of the default path.
/r <filename> - Forces Excel to open the specified file in read-only mode.
/s - Starts Excel in Safe mode.
etc.

Some examples:

---

Misc. Links

http://www.exceluser.com/explore/questions/vba_textcols.htm - example - reading text file, parsing strings, writing to excel
http://www.tek-tips.com/faq.cfm?pid=707 - FAQ, many examples

More Excel Tips and Shortcuts

Question Answer

How to print part of a worksheet? Select the part that you want to print and then go to File, Print Area and Set Print Area.

How to enter months from January to December? Type in January, position your cursor over the right hand bottom corner of the active cell, your cursor should change into a cross. Dragging the cross down will automatically enter the rest of the months in the cells below.

How to automatically insert a series of numbers? Type in the first number and fill down to where the last number will go. Then click Edit, Select fill and choose the Series option. This allows you to choose from a number of of options.

How to copy a formula down a column quickly? Type the formula into the first cell of the column. Then place your cursor at the bottom right of the cell. When it turns into a black cross, double click (left mouse button). The formula will be copied down the column. This works only for columns.

How to remove formatting? Edit->Clear - and choose the Formats option.

How to move or copy a Worksheet into another book? Click Edit and then choose the 'Move or Copy' option.

How to insert another worksheet? Insert -> Worksheet.

How to highlight cells from the current cell to another cell? Press F5, then type in the second cell 'coodinates' (eg C12) and then hit Shift and Enter.

How to insert a chart? Select the cells that you need to draw the graph and then click on the Chart icon at the top of your screen. Or go to Insert and select Chart. Follow the prompts to produce your chart. In the final step, tell Excel to place the chart as a new sheet instead of as an object in the Worksheet.

How to find the average or standard deviation of a set of numbers? Click on Insert and Function and then select the function that you want.

How to calculate a sum of a column of numbers automatically? Place your cursor in a blank cell under the numbers that you want to add and click the Autosum icon twice (the Sigma symbol).

How to make text wrap in a cell? Select the cell(s) - and select Format -> Cells ->Align -> Wrap Text.

How to format numbers so that they show to two decimal places? Select the cells concerned and use the Increase Decimal icon in the formatting toolbar. Also Click Format -> Cells ...

How to change the format depending on the value? Format -> Conditional Format

How to auto-adjust the width of the column? Double click with your mouse on the right edge of the top of the column (where the cursor changes into a two headed arrow).

How to sort? Click on any cell that contains data and click 'Data' and then 'Sort'. You can make sort case sensitive or not - click on 'Options' ...

How to freeze the column so that it wouldn't scroll left? Highlight the column, select Window -> Freeze Pains

Keys for moving and scrolling in a worksheet or workbook

Keys for moving and scrolling in a worksheet or workbook
Note To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X. To restore the window to its previous size and location, press ALT+SPACEBAR and then press R. To print this topic, press ALT+O and then press P.

This topic lists:

Keys for moving and scrolling in a worksheet or workbook

Keys for moving in a worksheet with End mode on

Keys for moving in a worksheet with SCROLL LOCK on

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

Return to top

Keys for moving in a worksheet with End mode on
Press To
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)

Return to top

Keys for moving in a worksheet with SCROLL LOCK on
Press To
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

Return to top

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

Note To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X. To restore the window to its previous size and location, press ALT+SPACEBAR and then press R. To print this topic, press ALT+O and then press P.

Press To
CTRL+P or CTRL+SHIFT+F12 Display the Print dialog box

Work in print preview
Press To
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

Keys for working with worksheets, charts, and macros
Note To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X. To restore the window to its previous size and location, press ALT+SPACEBAR and then press R. To print this topic, press ALT+O and then press P.

Press To
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

This topic lists:

Keys for entering data on a worksheet

Keys for working in cells or the formula bar

Keys for entering data on a worksheet
Press To
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

Return to top

Keys for working in cells or the formula bar
Press To
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

Press To
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

This topic lists:

Keys for editing data

Keys for inserting, deleting, and copying a selection

Keys for moving within a selection

Keys for editing data
Press To
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

Return to top

Keys for inserting, deleting, and copying a selection
Press To
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

Return to top

Keys for moving within a selection
Press To
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 data and cells

This topic lists:

Keys for selecting cells, columns, or rows

Keys for extending the selection with End mode on

Keys for selecting cells that have special characteristics

Keys for selecting cells, columns, or rows
Press To
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 (lower-right 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

Return to top

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
Press To
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 (lower-right 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).

Return to top

Keys for selecting cells that have special characteristics
Press To
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 charts and chart items

Keys for selecting a chart sheet
Press To
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.

Press F10 to make the menu bar active.

Press CTRL+TAB or CTRL+SHIFT+TAB to select the Drawing toolbar.

Press the RIGHT ARROW key to select the Select Objects button on the Drawing toolbar.

Press CTRL+ENTER to select the first object.

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.

Press CTRL+ENTER to make the chart active.
Keys for selecting chart items
Press To
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

There are also keys to use with PivotChart reports.

Keys to use with databases and lists
Note To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X. To restore the window to its previous size and location, press ALT+SPACEBAR and then press R. To print this topic, press ALT+O and then press P.

This topic lists:

Keys for working with a data form

Keys for using AutoFilter

Keys for working with a data form
Press To
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

Return to top

Keys for using AutoFilter
Press To
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

Press To
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 to use with PivotTable and PivotChart reports

This topic lists:

Keys for the PivotTable and PivotChart Wizard

Keys for page fields in a PivotTable or PivotChart report

Keys for laying out a PivotTable or PivotChart report

Keys for grouping and ungrouping PivotTable items

Keys for the PivotTable and PivotChart Wizard
Press To
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

Return to top

Keys for page fields in a PivotTable or PivotChart report
Press To
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

Return to top

Keys for laying out a PivotTable or PivotChart report
Press F10 to make the menu bar active.

Press CTRL+TAB or CTRL+SHIFT+TAB to select the PivotTable toolbar.

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.

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.

Return to top

Keys for grouping and ungrouping PivotTable items
Press To
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
Press the TAB key to select the Source fields list.

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.

Press the TAB key to select the > button, and then press ENTER.

To move other fields, repeat steps 1 through 3.
To move a field to a lower or higher level in the Dimensions box
Press the TAB key to select the Dimensions box.

Press the UP ARROW or DOWN ARROW key to select the field you want to move.
Press CTRL+X.
Press the UP ARROW or DOWN ARROW key to select where you want to move the field.
Press CTRL+V.

=====

Question	Answer
How to print part of a worksheet?	Select the part that you want to print and then go to File, Print Area and Set Print Area.
How to enter months from January to December?	Type in January, position your cursor over the right hand bottom corner of the active cell, your cursor should change into a cross. Dragging the cross down will automatically enter the rest of the months in the cells below.
How to automatically insert a series of numbers?	Type in the first number and fill down to where the last number will go. Then click Edit, Select fill and choose the Series option. This allows you to choose from a number of of options.
How to copy a formula down a column quickly?	Type the formula into the first cell of the column. Then place your cursor at the bottom right of the cell. When it turns into a black cross, double click (left mouse button). The formula will be copied down the column. This works only for columns.
How to remove formatting?	Edit->Clear - and choose the Formats option.
How to move or copy a Worksheet into another book?	Click Edit and then choose the 'Move or Copy' option.
How to insert another worksheet?	Insert -> Worksheet.
How to highlight cells from the current cell to another cell?	Press F5, then type in the second cell 'coodinates' (eg C12) and then hit Shift and Enter.
How to insert a chart?	Select the cells that you need to draw the graph and then click on the Chart icon at the top of your screen. Or go to Insert and select Chart. Follow the prompts to produce your chart. In the final step, tell Excel to place the chart as a new sheet instead of as an object in the Worksheet.
How to find the average or standard deviation of a set of numbers?	Click on Insert and Function and then select the function that you want.
How to calculate a sum of a column of numbers automatically?	Place your cursor in a blank cell under the numbers that you want to add and click the Autosum icon twice (the Sigma symbol).
How to make text wrap in a cell?	Select the cell(s) - and select Format -> Cells ->Align -> Wrap Text.
How to format numbers so that they show to two decimal places?	Select the cells concerned and use the Increase Decimal icon in the formatting toolbar. Also Click Format -> Cells ...
How to change the format depending on the value?	Format -> Conditional Format
How to auto-adjust the width of the column?	Double click with your mouse on the right edge of the top of the column (where the cursor changes into a two headed arrow).
How to sort?	Click on any cell that contains data and click 'Data' and then 'Sort'. You can make sort case sensitive or not - click on 'Options' ...
How to freeze the column so that it wouldn't scroll left?	Highlight the column, select Window -> Freeze Pains