See also: alphabetical index
Excel spreadsheet is a program with a grid to display data in rows and columns. User can do calculations with the data inside the grid. In addition, users can create charts and graphs for business presentations and reports.
Examples of Excel grids are document with names and addresses, or a document to calculate next year's sales revenue based on prior years' performance of a company.
The Worksheet Screen: Toolbars, Worksheet, Cell, Contents Box, Status Bar.
Shortcut menu: right-click. Press Esc key to close.
Toolbars: standard, formatting, Drawing. Pausing the mouse pointer over will display the item description.
Worksheet data is made up of three components:
Entering a label: type some text, but not a number.
| Item | Quantity | Count |
Entering Dates: Excel considers dates to be values. Excel can do calculations with dates.
Saving, Closing, and Opening the worksheet.
Inserting Columns: Select column, Insert menu, Columns. The new column will appear in front of the selected one.
Inserting Rows: Select row, Insert menu, Rows. The new row will appear on top of the selected one.
Inserting Individual Cells: Insert, Shift Cells Right or Down. Dialog box pops up. Click OK.
Deleting Rows or Columns: Select row or column, Edit menu, Delete.
Deleting Individual Cells: Select cell(s), click Edit, Delete. The dialog box opens. Select Shift Cells Left or Up. Click OK.
Undo and Redo
Cutting and Pasting. Excel uses the Windows copy and paste feature to move data around. Select cells. Click Cut. Moving dashes, or marquee appears. Next, click on the cell which will be upper-left corner of the target location of the paste, and click the Paste button. Use Copy command to replicate information in your spreadsheet.
NOTE: In case if the marquee does not stop marching, press the Enter key.
The Fill: Enter two values. Select both. Position the mouse pointer at the lower-right corner of the cell. Mouse pointer changes to black cross. Press, hold, and drug the mouse over the cells you wish to fill.
To Auto Format, select your cells, click Format menu, then AutoFormat. The dialog box will open:
You can choose to display your values as currency, percentages, fractions, dates, and other formats.
Select the cells you want to format. Click Format menu. Click Cells. The dialog box opens. Choose the tab that you want, e.g. Number. Make your selection of the category, options, and click OK.
Excel uses a default font of Arial 10 points. You can change the typeface, size, style, and color. Add Bold, Underline, or Italics style, if you wish.
Click the Format menu, select Row or Column, then Height or Width. Enter the desired column width in points.
Select cells you want to format. Format, Cells, dialog box will open. Alignment tab, select necessary options. Wrap text feature will allow text to wrap inside the cell.
The dilog box also allows you to choose vertical alignment, such as bottom, center, or the top.
You may wish to add headings to your worksheets, just as you would do in a Word document.
Type heading text into first column. Select that cell and also cells you wish to merge. Now click Format menu, Format Cells, and set Merge option in the dialog box. You may also want to center the heading.
Although cells appear to be merged and the text is centered, the original cell remains the heading placeholder. Therefore, if you wish to edit the heading, click the original cell.
To undo the heading settings, click Format menu, Format Cells, and remove Merge or Center options in the dialog box.
To add borders to selected cells, and click Format menu, Format Cells, open Border tab of the dialog box, and set desired options.
Background color in Excel is referred to as Fill color. Use fill color toolbar button to set your colors.
Selecting a cell and pressing Del key will remove the contents of a cell but won't affect the formatting in that cell. If you want to remove formatting, such as the background color, you need to use a different method: from the click Edit menu, Clear, Formats. All formatting will now be removed from that cell.
Click File menu, and open Page Setup dialog box. Click Header or Footer tab. Set a predefined header and footer format, or choose Custom Header or Footer. Enter your text, and modify its attributes as you would do in a Word document.
All formulas must begin with the equal sign (=).
Cells appear in formulas as Excel references.
Arithmetic operators
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula.
An absolute cell reference in a formula, such as
$A$1, always refer to a cell in a specific location. If the position
of the cell that contains the formula changes, the absolute reference remains the
same. If you copy the formula across rows or down columns, the absolute reference
does not adjust. By default, new formulas use relative references, and you need
to switch them to absolute references. For example, if you copy a absolute reference
in cell B2 to cell B3, it stays the same in both cells =$A$1.
A relative cell reference in a formula, such as
A1, is based on the relative position of the cell that contains the formula
and the cell the reference refers to. If the position of the cell that contains
the formula changes, the reference is changed. If you copy the formula across rows
or down columns, the reference automatically adjusts. By default, new formulas use
relative references. By default, new formulas use relative references. For example,
if you copy a relative reference in cell B2 to cell B3, it automatically adjusts
from =A1 to =A2.
By default, Excel uses the A1 reference style, which refers to columns as letters (A through IV, for a total of 256 columns), and refers to rows as numbers (1 through 65,536). These letters and numbers are called row and column headings. To refer to a cell, type the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, type the reference for the cell that is in the upper-left corner of the range, type a colon (:), and then type the reference to the cell that is in the lower-right corner of the range.
Excel can also use the R1C1 reference style, in which both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful if you want to compute row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.
It is often advantagous to use the R1C1 refence method, because it allows more flexibility. MS Excel uses R1C1 reference style internally.
NOTE: F4 key changes (cycles thru) the absolute/relative/mixed reference type in a formula. To switch to R1C1 reference style (or back to A1 style), click Tools menu, Options, General tab. Under Settings, select or clear the R1C1 reference style check box.
Examples:
R[-2]C A row-column relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to the cell two rows up and in the same column
R[2]C[2] A relative reference to the cell two rows down and two columns to the right
R2C2 An absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) to the cell in the second row and in the second column
R[-1] A relative reference to the entire row above the active cell
R An absolute reference to the current row
A1 Single cell, Relative. A is column identifier, 1 is row identifier
A1:B3 Range, Relative. This references 6 cells
5:05 Range, Relative. All cells in row 5
B:B Range, Relative. All cells in column B
5:07 Range, Relative. All cells in rows from 5 to 7
B:E Range, Relative. All cells in columns from B to E
A$1$ Cell, Absolute. The cell A1. Cut-pasting formula to another location will not change the reference.
A$1 Cell, Mixed. The $ makes the column reference absolute, leaving the row reference relative
R1C1 Cell, Absolute. R1 is row 1, C1 is column 1
R1C1:R3C2 Range, Absolute. Same as A1:B3 as shown above
R Range, Absolute Reference to current row
C Range, Absolute Reference to current column
R[-2]C Cell, Relative. Cell two rows up and in the same column
RC[-1] Reference to a cell in the same row, but one column to the left.
RC Reference to a cell in the same row and column, which is the current cell itself. You would generally never do this because it would create a circular reference.
R[-2]C1 Cell, Mixed. Row reference is relative, column reference is absolute
R[2]C[3] Cell, Relative. Cell two rows down of current row and three rows to right of current column
R[-1] Range, Relative. Cells in the row above the current row
defined_name Range or Cell, Absolute. Named cells are always absolute
Sheet1!reference Either Range or Cell, Relative or Absolute. Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.
='C:\[sample.xls]Sheet1'!$A$1 Cell, Absolute. Refers to an external file - a worksheet in the file and an absolute position within that worksheet. Note the usage of various delimiters.
='C:\[sample.xls]Sheet1'!defined_name Cell, Absolute. Same as above. Refers to a defined name "defined_name"
Sheet1:Sheet3!A1:B3 Cell, Relative. Refers to all cells from A1 to B3 in all worksheets from Sheet 1 to Sheet 3. "Sheet1" and "Sheet3" are actual worksheet names. This type of reference is called 3D-reference, because it cuts across worksheets.
Double click formula and type your correction. Hit Enter to exit the edit mode of the cell.
Absolute reference is the cell reference that never changes as you copy formulas from cell to cell. For example, =B22*$B$24. Absolute references are created by the dollar sign.
There is a method to view the formulas in the cells. This is a valuable tool to troubleshoot a worksheet. Click Tools menu, then Options. In the dialog box, click View tab. Click Formulas checkmark. Click OK. Now the formulas will be displayed in each cell.
TIP: You can also print worksheet in formula view mode.
Ctrl + " [ " Selects all the cells that are directly referred to by the formula in the active cell (precedents).
Ctrl + Shift + " [ " Selects all the cells that are directly (or indirectly) referred to by the formula in the active cell.
Ctrl + " ] " Selects all the cells that directly refer to the active cell (dependents).
Ctrl + Shift + " ] " Selects all the cells that directly (or indirectly) refer to the active cell.
Very short error messages are displayed inside the cell.
Range of cell values is created by the colon sign entered between
cell references. For example, B2:B5 means that range of cells includes
Select cells you wish to name. Click Insert menu, Name, Define. Named range can be a single cell, contiguous group of cells, entire rows, or entire columns, or any combination of the above. Click Add, Close.
Why use range names?
For quick navigation, Excel provides a drop-down box on top of the worksheet.
It is impossible to remember the syntax of each function. To get help, click on the cell, and click Insert menu. Click Function. Excel automatically inserts the equal sign in the cell. Dialog box opens, and you may either choose a function from the list, or type a description of a function and click GO. Excel will then list functions that match your description. There is also category in the dialog box that you can select. List of functions in that category will appear. As soon as you click the function name, its syntax and description will show up. Click OK. The arguments dialog will show up. For each argument you may type either cell reference, range of cells, or an actual value. Description of each argument appears at the bottom of the dialog box.
Select cells you wish to hide. Click Format menu, then Row or Column, and choose Hide. Alternatively, right click on your selection, and choose Hide.
To display the hidden rows or columns again, click Format menu, Row or Column, and Unhide.
If your worksheet is too large to see all parts at the same time, click Window menu, Split, and the worksheet will appear to be divided into 4 panes. Drag around the split bar separators to the size you want.
When you are finished working in the split view, click Window menu, Remove Split.
You can freeze the column headings and row labels so they remain visible no matter where you are working in your worksheet.
To freeze columns, click one cell to the right of the columns you want to freeze.
To freeze rows, click one cell below the row you want to freeze.
Click Window menu, Freeze Panes.
To remove the freeze, click Window menu, Unfreeze Panes.
When you save an Excel file, it can have multiple worksheets. A file with more than one worksheet is called a workbook.
By default, a new blank worksheet includes three worksheets named Sheet1, Sheet2, and Sheet3. There is a worksheet tab at the bottom of the screen, where you can click to navigate to specific worksheet. Alternatively, you may press Ctrl+PageUp or Ctrl/PageDown to move around worksheets.
To add a new worksheet to your workbook, click Insert menu, Worksheet.
Order of the worksheets can be changed by dragging the tab to a desired position.
It is a good practice to give your worksheets descriptive names, even if there is only one worksheet in the file.
To rename a worksheet, double click on the tab of the worksheet, or right-click on a worksheet tab and select Rename. Type the new name and press Enter.
For easier recognition, you can also color-code your worksheet by clicking on the worksheet tab, then clicking Format menu, Sheet, Tab Color.
Click anywhere in the worksheet you want to hide. Click Format menu, Sheet, Hide.
To unhide the hidden worksheets, click Format menu, Sheet, Unhide, and select a hidden worksheet from the list.
Click anywhere in the worksheet you want to delete. Click Edit menu, Delete Sheet, and follow a few simple prompts.
Once you delete a sheet, the sheet is deleted permanently. You will not be able to undo the deletion.
Need to refer to the information stored in another worksheet? To create a link between worksheets in a formula, you have to precede cell reference by the name of the worksheet, followed by the exclamation mark: =Sheet2!B6. The syntax for this type of formula becomes =SHEET!Cell.
Alternatively, click the cell which will contain the formula. Type equal sign, = then click the tab of the worksheet you'd like to use in the formula, and then click the cell or a range of cells which you would like to reference in the formula. Hit Enter. Excell will create the formula for you.
It is also possible to have ranges of worksheets in a formula.
Why? This is useful if you have some uniform sheets with the same structure, so
ranges of cells will have the same structure in those worksheets. For example,
In case if your worksheet name contains spaces you will have to enclose your workbook
name in single quotes. For example,
If you're referencing a cell in another workbook, the syntax is
One of the areas that causes people the most problems is printing, because Excel spreadsheets are often not only too long for one page, but also too wide for one page; and, therefore, Excel inserts both horizontal and vertical page breaks, which can make your data print in all kinds of strange ways if you don't understand how it works. But, once you do understand it, it's pretty straightforward and easier to tweak to get what you want.
First of all, click on the Print Preview and you will see how your file is about to be printed. If you have a problem printing, then go to Page Setup and adjust the options. There are some good resources on the web explaining page setup options in detail: () by Linda Johnson.
Here is a cheatsheet of Excel shortcuts:
Alt + Shift + F1 Inserts a new worksheet into the active workbook
Alt + Down Arrow Displays the Pick From List drop-down list
Ctrl + "1" Displays the (Format > Cells) dialog box
Ctrl + Page Down Moves to the next worksheet in the active workbook
Ctrl + Tab Moves to the next workbook or window
Ctrl + Shift + Tab Moves to the previous workbook or window
Ctrl + Shift + Down Arrow Selects all the cells to the end of the current region
F11 Creates a chart (on a chart sheet) using the highlighted range
Shift + F9 Calculates just the active worksheet
F1 Displays the Office Assistant or (Help > Microsoft Excel Help)
F2 Edits the active cell, putting the cursor at the end*
F3 Displays the (Insert > Name > Paste) dialog box
F4 Repeats the last worksheet action (Edit > Repeat)
F5 Displays the (Edit > GoTo) dialog box
F6 Moves to the next pane in a workbook (if the window is split)
F7 Displays the (Tools > Spelling) dialog box
F8 Toggles whether to extend a selection with the arrow keys
F9 Calculates All the worksheets in All the open workbooks
F10 Toggles the activation of the Menu Bar
F11 Displays the (Insert > Chart) dialog box that creates a chart (on a chart sheet) using the highlighted range
F12 Displays the (File > Save As) dialog box
Shift + F1 Activates the context sensitive Whats this ? prompt (Help > Whats This)
Shift + F2 Inserts or edits a cell comment (Insert > Comment)
Shift + F3 Displays the (Insert > Function) dialog box
Shift + F4 Repeats the last Find, the same as (Edit > Find Next)
Shift + F5 Displays the (Edit > Find) dialog box
Shift + F6 Moves to the previous pane in a workbook (if the window is split)
Shift + F8 Toggles between switching Add Mode on or off*
Shift + F9 Calculates just the active worksheet
Shift + F10 Displays the (Shortcut) menu for the selected item
Shift + F11 Inserts a new worksheet into the active workbook (Insert > Worksheet)
Shift + F12 Saves, Displays the (File > Save As) dialog box if a new workbook
Ctrl + . (period) scroll thru corners of selected range.
Ctrl + F3 Displays the (Insert > Name > Define) dialog box
Ctrl + F5 Restores the size of the active workbook or window
Ctrl + F6 Moves to the next open workbook or window
Ctrl + F7 Activates the Move window command
Ctrl + F8 Activates the Resize window command
Ctrl + F9 Minimises the size of the active workbook or window
Ctrl + F10 Maximises the size of the active workbook or window
Ctrl + F12 Displays the (File > Open) dialog box
Alt + F1 Creates a chart (on a chart sheet) using the highlighted range
Alt + F2 Displays the (File > Save As) dialog box
Alt + F4 Closes all the workbooks (saving first) and exits Excel (File > Exit)
Alt + F8 Displays the (Tools > Macro > Macros) dialog box
Alt + F11 Toggles between the Visual Basic Editor window and the Excel window
Alt + Ctrl + F9 Calculates All cells on All worksheets in All workbooks
Alt + Shift + F1 Inserts a new worksheet into the active workbook (Insert > Worksheet)
Alt + Shift + F2 Displays the (File > Save As) dialog box
Alt + Shift + F10 Displays the drop-down menu for the corresponding Smart tag
Alt + Shift + F11 Activates the Microsoft Script Editor window
Ctrl + Shift + F3 Displays the (Insert > Name > Create) dialog box
Ctrl + Shift + F6 Moves to the previous open workbook or window
Ctrl + Shift + F10 Activates the Menu Bar or Displays the (View > Toolbars) dialog box
Ctrl + Shift + F12 Displays the (File > Print) dialog box
Insert + F4 Read spelling errors and suggestions ??
Ctrl + "0" Hides the columns in the current selection (Format > Column > Hide)
Ctrl + "1" Displays the (Format > Cells) dialog box
Ctrl + "2" Toggles bold on the current selection
Ctrl + "3" Toggles italics on the current selection
Ctrl + "4" Toggles underlying on the current selection
Ctrl + "5" Toggles the strikethrough of text on the current selection
Ctrl + "6" Toggles between hiding, displaying or displaying just placeholders (objects)
Ctrl + "7" Toggles the display of the Standard toolbar
Ctrl + "8" Toggles the display of Outline symbols on the active worksheet
Ctrl + "9" Hides the rows in the current selection (Format > Row > Hide)
Ctrl + Shift + "0" Unhides the columns in the current selection
Ctrl + Shift + "2" Enters the value from the cell directly above into the active cell
Ctrl + Shift + "8" Selects the current region (surrounded by blank rows and columns)
Ctrl + Shift + "9" Unhides the rows in the current selection
Ctrl + "A" Displays the formula palette given a function name or selects the whole worksheet
Ctrl + "B" Toggles bold on the current selection
Ctrl + "C" Copies the current selection to the clipboard (Edit > Copy)
Ctrl + "D" Copies the first cell in the selection downwards (Edit > Fill > Down)
Ctrl + "E" Goto current Error ??
Ctrl + "F" Displays the (Edit > Find) dialog box
Ctrl + "G" Displays the (Edit > GoTo) dialog box
Ctrl + "H" Displays the (Edit > Replace) dialog box
Ctrl + "I" Toggles italics on the current selection
Ctrl + "J" Toggles calculation between Manual and Automatic ??
Ctrl + "K" Displays the (Insert > Hyperlink) dialog box
Ctrl + "N" Creates a new workbook (File > New)
Ctrl + "O" Displays the (File > Open) dialog box
Ctrl + "P" Displays the (File > Print) dialog box
Ctrl + "R" Copies the leftmost cell in the selection to the right (Edit > Fill > Right)
Ctrl + "S" Saves, Displays the (File > Save As) dialog box if a new workbook
Ctrl + "U" Toggles underlining on the current selection
Ctrl + "V" Pastes the entry from the clipboard (Edit > Paste)
Ctrl + "W" Closes the active workbook or window (File > Close)
Ctrl + "X" Cuts the current selection to the clipboard (Edit > Cut)
Ctrl + "Y" Repeats the last action (Edit > Repeat)
Ctrl + "Z" Undo the last action (Edit > Undo)
Alt + "C" Move the selected field into the Column area (Pivot Table)
Alt + "D" Displays the (Data) menu
Alt + "E" Displays the (Edit) menu
Alt + "F" Displays the (File) menu
Alt + "H" Displays the (Help) menu
Alt + "I" Displays the (Insert) menu
Alt + "L" Displays the Pivot Table Field dialog box ??
Alt + "O" Displays the (Format) menu
Alt + "P" Move the selected field into the Page area (Pivot Table)
Alt + "R" Move the selected field into the Row area (Pivot Table)
Alt + "T" Displays the (Tools) menu
Alt + "U" Activates the AutoShapes submenu on the Drawing toolbar*
Alt + "V" Displays the (View) menu
Alt + "W" Displays the (Window) menu
Alt + Shift + "B" Report on the cell borders ??
Alt + Shift + "G" Report the gridline status ??
Ctrl + Shift + "A" Inserts argument names given a function in the formula bar.
Ctrl + Shift + "C" List cells in current column ???
Ctrl + Shift + "F" Activates the Font Name drop-down list on the Formatting toolbar
Ctrl + Shift + "H" Selects all the hyperlinks ?? where
Ctrl + Shift + "O" Selects all the cells with comments
Ctrl + Shift + "P" Activates the Font Size drop-down list on the Formatting toolbar
Ctrl + Shift + "R" List cells in the current row ???
Ctrl + Shift + "S" List all the worksheets ??
Enter Enters the contents of the active cell and moves to the cell below (by default)
Shift + Enter Enters the contents of the active cell and moves to the cell above (by default)
Tab Enters the contents of the active cell and moves one cell to the right
Shift + Tab Enters the contents of the active cell and moves one cell to the left
Alt + " = " Enters the SUM() function (AutoSum) to sum the adjacent block of cells
Alt + 0128 Enters the euro symbol (using Number keypad)
Alt + 0162 Enters the cent symbol (using Number keypad)
Alt + 0163 Enters the pound sign symbol (using Number keypad)
Alt + 0165 Enters the yen symbol (using Number keypad)
Alt + Enter Enters a new line (or carriage return) into a cell
Ctrl + " ' " Enters the formula from the cell directly above into the active cell
Ctrl + Shift + "2" Enters the value from the cell directly above into the active cell
Ctrl + " ; " Enters the current date into the active cell
Ctrl + Enter Enters the contents of the active cell to the selected region
Ctrl + Shift + " ; " Enters the current time into the active cell
Ctrl + Shift + Enter Enters the formula as an Array Formula
Shift + Insert Enters the data from the clipboard
Alt + Down Arrow Displays the Pick From List drop-down list
Esc Cancels the cell entry and restores the original contents
Delete Deletes the selection or one character to the right
Backspace Deletes the selection or one character to the left
Shift + Delete Cuts the selection to the clipboard
Ctrl + Delete Deletes text to the end of the line
Ctrl + " - " Displays the (Edit > Delete) dialog box
Ctrl + Shift + " = " Displays the (Insert > Cells) dialog box
Ctrl + " \ " Selects the cells in a selected row that do not match the value in the active cell
Ctrl + Shift + " \ " Selects the cells in a selected column that do not match the value in the active cell
Ctrl + " / " Selects the array containing the active cell ??
Alt + " ; " Selects the visible cells in the current selection
Ctrl + Enter Selects the first object / chart ??
Ctrl + Shift + "8" Selects the current region (surrounded by blank rows and columns)
Ctrl + " * " Selects the current region (using the * on the number keyboard)
Ctrl + " [ " Selects all the cells that are directly referred to by the formula in the active cell (precedents)
Ctrl + Shift + " [ " Selects all the cells that are directly (or indirectly) referred to by the formula in the active cell
Ctrl + " ] " Selects all the cells that directly refer to the active cell (dependents)
Ctrl + Shift + " ] " Selects all the cells that directly (or indirectly) refer to the active cell
Ctrl + Shift + Page Down Selects the active worksheet and the one after it
Ctrl + Shift + Page Up Selects the active worksheet and the one before it
Ctrl + Shift + Spacebar Selects all the objects on the worksheet when an object is selected or selects the whole worksheet
Ctrl + Backspace Selects the current active cell (scrolling if necessary)
Ctrl + Spacebar Selects the current column
Shift + Arrow keys Selects the active cell and the cell in the given direction
Shift + Backspace Selects the active cell when multiple cells are selected
Shift + Spacebar Selects the current row
Ctrl + Shift + Arrow Key Extends the selection to the next cell adjacent to a blank cell in that direction
Ctrl + Shift + End Extends the selection to the last used cell on the worksheet
Ctrl + Shift + Home Extends the selection to the beginning of the worksheet
Shift + Arrow Keys Extends the selection by one cell in that direction
Shift + Home Extends the selection to the first column
Shift + Page Down Extends the selection down one screen
Shift + Page Up Extends the selection up one screen
End, Shift + Arrow Keys Extends the selection to the next non-blank cell in that direction
End, Shift + End Extends the selection to the last cell in the current row*
End, Shift + Home Extends the selection to last used cell on the worksheet
Alt + " ' " Displays the (Format > Style) dialog box
Ctrl + Shift + " ' " Applies the Time format "hh:mm" to the selection
Ctrl + Shift + " 1 " Applies the Comma separated format "#,##0.00" to the selection
Ctrl + Shift + " 4 " Applies the Currency format "£#,##0.00" to the selection
Ctrl + Shift + " 5 " Applies the Percentage format "0%" to the selection
Ctrl + Shift + " 6 " Applies the Exponential format "#,##E+02" to the selection
Ctrl + Shift + " # " Applies the General number format "0.00" to the selection
Ctrl + " # " Applies the Date format "dd-mmm-yy" to the selection
Ctrl + Shift + " 7 " Applies the outline border to the selection
Ctrl + Shift + " - " Removes all the borders from the selection
Ctrl + Shift + " _ " Removes an outline border from the selection
Arrow Keys Moves to the next cell in that direction
Ctrl + Tab Moves to the next open workbook or window
Alt + Tab Moves to the next application open on your computer
Alt + Shift + Tab Moves to the previous application open on your computer
Enter Moves to the cell directly below
Tab Moves to the next cell on the right (or unprotected cell)
Home Moves to the first column in the current row
End, Arrow Keys Moves to the next non empty cell in that direction
End, Enter Moves to the last cell in the current row that is not blank
End, Home Moves to the last used cell on the active worksheet*
Page Down Moves you one screen of rows down
Page Up Moves you one screen of rows up
Shift + Enter Moves to the cell directly above (opposite direction to Enter)
Shift + Tab Moves to the cell directly to the left (opposite direction to Tab)
Alt + Page Down Moves you one screen of columns to the right
Alt + Page Up Moves you one screen of columns to the left
Ctrl + Home Moves to cell "A1" on the active sheet
Ctrl + End Moves to the last used cell on the active worksheet*
Ctrl + Up Arrow Moves to the first row in the current region
Ctrl + Down Arrow Moves to the last row in the current region
Ctrl + Left Arrow Moves to the first column in the current region
Ctrl + Right Arrow Moves to the last column in the current region
Ctrl + Page Up Moves to the previous worksheet in the workbook
Ctrl + Page Down Moves to the next worksheet in the workbook
Ctrl + Shift + Tab Moves to the previous open workbook or window
Ctrl + Backspace Moves to the display the active cell
Scroll Lock + Arrow Keys Moves the workbook or window one cell the corresponding direction
Scroll Lock + End Moves to the last cell in the current window
Scroll Lock + Home Moves to the first cell in the current window
Scroll Lock + Page Down Moves you down one screen (current selection unchanged)
Scroll Lock + Page Up Moves you up one screen (current selection unchanged)
Enter Moves from top to bottom within a selection
Tab Moves from left to right within a selection
Ctrl + " . " Moves clockwise to the next corner within a selection
Shift + Tab Moves from right to left within a selection (opposite direction to Tab)
Alt + Ctrl + Left Arrow Moves to the left between non adjacent cells in a selection
Alt + Ctrl + Right Arrow Moves to the right between non adjacent cells in a selection
= Starts a Formula
Ctrl + " ' " Toggles between the value layer and the formula layer
Ctrl + Delete Deletes to the end of the line
Ctrl + Insert Copies the current selection to the clipboard
Ctrl + Shift + " ( " Unhides any hidden rows within the selection
Ctrl + Shift + " ) " Unhides any hidden columns within the selection
Ctrl + Shift + " / " Copy value from cell above / select the array ??
Ctrl + Shift + " \ " Select unequal cells
Alt Toggles the activation of the Menu Bar
Alt + Shift + Left Arrow Displays the (Data > Group and Outline > UnGroup) dialog box
Alt + Shift + Right Arrow Displays the (Data > Group and Outline > Group) dialog box
Alt + Backspace Undo the last action (Edit > Undo) ??
Alt + Spacebar Activates the Control Box in the top left hand corner
Alt + " - " Displays the Excel application control menu
End Toggles between switching End Mode on or off
Scroll Lock Toggles between switching Scroll Lock on or off
Shift + Insert Pastes the entry from the clipboard
Ctrl + F2 Displays the Info Window (redundant)
Ctrl + F4 Closes the active workbook or window (redundant)
Ctrl + F11 Inserts an Excel 4.0 macro sheet (redundant)
Tutorials - Microsoft Excel, Copyright © 2004-2007 Better Solutions Limited.
Microsoft Excel > Shortcut Keys
Microsoft Excel Arrays: Getting Started
Tracing Cell References, Tracing Dependent Cells,
Tracer Arrows, Precedent Cells, Tracing References
to Other workbooks.
Advice:
The trace arrows always point in the direction of the data flow and can also be used to move around a worksheet.
A cell can be both a dependent and a precedent if the cell contains a formula and the cell is referenced by another formula in a different cell.
You can trace the cells that indirectly refer to the active cell by pressing the Trace Dependents button again.
Double clicking on any of the arrows will take you directly to the cell at the other end of the arrow.
You can trace the cells that supply values indirectly to the formula in the active cell by pressing the Trace Dependents button again.
(Ctrl + Shift + [ ) - Selects all the cells that are directly or indirectly referred to by the formula in the active cell.
(Ctrl + Shift + ] ) - Selects all the cells that directly or indirectly refer to the active cell.
Microsoft Excel: Named Ranges - Table of Contents
Microsoft Excel: Named Ranges - Getting Started
Insert menu, Name:
Define (or Ctrl + F3) - Allows you to define a named range. This can alternatively be done by typing directly into the Name box.
Paste - Allows you to enter names directly into the formula bar while you are in the process of entering a formula.
Create - Allows you to create name ranges for individual cells or ranges of cells.
Apply - Allows you to replace existing cell references with their corresponding named ranges.
Label - Allows you to automatically substitute any new cell references with natural language formulas.
Edit menu, GoTo (or pressing F5 key) will display named ranges in alphabetical order.
Excel Named Ranges - Naming Constants and Formulas
Functions - Lookup and Reference:
OFFSET( reference, rows, cols [,height] [,width] )
Returns the value in the cell which is an offset from a given cell reference, where
reference The reference from which you want to base the offset.
rows The number of rows, up or down, for the upper-left cell to refer to.
cols The number of columns, to the left or right, for the upper-left cell of the result to refer to.
height The number of rows that you want the returned reference to be. Must be a positive number.
width The number of columns that you want the returned reference to be. Must be a positive number.
For example,
|
Dynamic Named Ranges: you can create dynamic named ranges that automatically expand
or contract depending on the number of items.