For in-person presentations, consider these communication tips: Some tips for effective communication in training include: …
Techniques and Tips for
Everyday Uses of Excel
Oakton Community College
Center for Professional Development Workshop
Deanne Rehmer, Facilitator
|Topic |Notes Steps |
|Create Multiple Lines in a |Enter the information to appear in the first |
|Cell |line |
| |Press [Alt] [Enter] |
| |Repeat for additional lines |
| |Press [Enter] after the last line |
|Enable/Disable AutoComplete |Choose Tools, Options The Options dialog box|
|for Cell Values |appears |
| |Click the Edit tab |
| |In the Settings area, enable or disable |
| |Enable AutoComplete for cell values |
| |Choose OK |
|AutoFill Shortcut Menu |Enter the first entry in a cell |
| |Move the
cell pointer to that cell and point to|
|Fill weekdays |the fill handle |
| |Drag with the right mouse button to include |
| |cells to be filled |
| |Release the right mouse button A shortcut menu|
| |appears |
| |Specify the desired fill type |
|Reverse the AutoFill Action |Enter an entry in a cell |
| |Move the cell pointer to the fill handle |
| |Press [Ctrl] and drag the fill handle Excel |
| |will reverse the increment or repeat action |
| |For example, when January is entered, it will|
| |repeat rather than increment When 1 is |
| |entered, it will increment rather than repeat |
|Automatically Fill Down in a|Enter entries in the columns to the left |
|Column |Place an entry ie formula in the top row
of|
| |the column to fill |
| |Select the cell to fill down |
| |Double click the fill handle to fill down as |
| |far as the adjacent columns to the left |
|Select a Large Range |Click the first cell to be included |
| |Scroll to the opposite corner |
| |Press [Shift] and click the cell in the |
| |opposite corner The entire range is selected |
|Move to a Different Sheet |Point to one of the tab scrolling buttons|
| |Click the right mouse button A list of sheet |
| |names appears |
| |Click the desired sheet name |
|Edit Cell Contents Directly |Double click the cell to be edited The |
| |insertion point appears |
| |Edit the contents of the cell |
|
|Click outside of the cell |
|AutoFit Multiple Columns |Select the columns to be AutoFit |
| |Position the mouse pointer to the column header|
| |area |
| |Double click one of the vertical divider lines |
| |between the columns |
|AutoFit All Columns |Click the Select All button |
| |Double click on the right vertical divider line|
| |of any column heading |
|Conditional Formatting |Conditional formatting applies a different font|
| |style, pattern, and border to cells whose |
| |values fall outside or within the limits |
| |specified |
| |Select the desired cells |
| |Choose Format, Conditional Formatting The |
| |Conditional Formatting
dialog box appears |
| |Specify the parameters of Condition 1 |
| |Click Format The Format Cells dialog box |
| |appears |
| |Select the desired options |
| |To add another condition, Click Add, and then |
| |repeat steps 3 “ 5 Up to three conditions can |
| |be specified |
| |Choose OK The Conditional Formatting dialog |
| |box appears |
| |Choose OK |
|Add Numbers Automatically |Click the cell where you want to sum a range of|
|with the Sum Function |cells |
| |Click the AutoSum button |
| |If the suggested range is incorrect, drag |
| |through the range you want and then press |
|
|[Enter] |
|Display Grand Totals with |Click the AutoSum button to find grand |
|AutoSum |totals where subtotals have been previously |
| |created |
|Entering a Formula |Click the cell where you want the formula value|
| |to appear |
| |Type and then type the expression that |
| |calculates the value you want For formulas |
| |that include cell references, you can type the |
| |cell reference or you can use the mouse or |
| |arrow keys to select each cell |
| |When the formula is complete, press the [Enter]|
| |key |
|Inserting a Function |Click the cell in which you will insert the |
| |function |
| |Click the
Insert Function button on the Formula|
| |bar |
| |Select the type of function you want from the |
| |select a category list box, and then select the|
| |function category; or type information about |
| |the function in the Search for a function text |
| |box, and then click the Go button |
| |Select the function in the Select a function |
| |list box |
| |Click the OK button to view the arguments for |
| |the selected function |
| |Enter values for each required argument in the |
| |Function Arguments dialog box |
| |Click the OK button |
|AutoCalculate |AutoCalculate displays the total value for a |
| |range of cells in the status
bar when a range |
| |is selected |
| |Select the desired range of cells The status |
| |bar displays the sum in the Sum area |
|Change the AutoCalculate |Right click the Sum area of the status bar |
|Function |A list of functions appears |
| |Click the desired function |
| |Select the desired range of cells The status |
| |bar displays the results of the selected |
| |function |
|Relative Cell References in |A relative reference is a cell reference that |
|Formulas |shifts when you copy it to a new location on |
| |the worksheet changes in relation to the |
| |change of location |
|Absolute Cell References in |An absolute reference is a cell reference that |
|Formulas |does not change when you copy the formula to a |
|
|new location on the worksheet To create an |
| |absolute reference, you preface the column and |
| |row designations with a dollar sign For |
| |example, the absolute reference for C10 is |
| |C10 |
| |Note: Instead of typing the dollar signs to |
| |change a relative reference to an absolute |
| |reference, you may use the [F4] function key |
|Split Window |Choose Window, Split |
| |Scroll in each Window as desired |
| |Choose Window, Remove Split |
|Freeze Panes |Click in the cell at the point below and to the|
| |right of where you wish to freeze |
| |Choose Window, Freeze Panes |
| |Scroll as you wish |
| |Choose
Window, Unfreeze Panes |
|Print Selected Cells |Select the cells to print |
| |Choose File, Print The Print dialog box |
|To print a different range |appears |
|each time the worksheet is |In the Print what area, choose Selection |
|printed, select the desired |Choose OK |
|cells and print the |Note: To print nonadjacent ranges, use the |
|selection |[Ctrl] key to select nonadjacent cell ranges, |
| |and follow the steps above Each cell range |
| |will print on a separate page |
|Page Break Preview |Choose View, Page Break Preview The Welcome |
| |to Page Break Preview dialog box appears |
|Page Break preview is an |Choose OK The current sheet appears as it will|
|editing view that displays a|print |
|worksheet as it will print |Drag the page break lines to the desired |
|Dragging page break lines to|location Excel automatically scales the
|
|the desired location can |worksheet to fit the columns and rows to the |
|move a page break |page |
| |Choose View, Normal to return to Normal view |
Source:msisac.org
