MICROSOFT EXCEL – Lab 3

SPELL-CHECKING THE WORKSHEET  - to spell-check worksheet:

Select Tools, Spelling or click Spelling icon

Works same as in Word

USING PASTE FUNCTION - steps you through the process of entering a function by letting you select the function name from a list, specify arguments, and then correctly places all components of the function in an easy to follow sequential manner.

Use the Function Wizard (Paste Function) to do an AVERAGE column (F):

Select Insert, Function or click fx in the formula bar

Category - displays a list of function categories; the one most recently used is highlighted

Name - displays an alphabetical listing of the last 10 functions used

Select the AVERAGE function (if it does not appear in the list, select STATISTICAL, AVERAGE)

Click NEXT

Specify cells by clicking on source cell and dragging (if you can’t see them, point to the title bar with the left arrow pointing up and drag it out of the way)

Click FINISH

 

USING THE FILL HANDLE (AUTOFILL) - The fill handle (+) in the lower right-hand corner of a selected range can be used to delete cell contents or to quickly copy a formula to an adjacent range of cells:

Move to the source cell

Preselect the range of cells including the source

Select Edit, Fill, Right or Down to copy; Fill, Up to delete

 

USING AN ABSOLUTE CELL REFERENCE - sometimes you do not want a formula to adjust when moved or copied; you want it to always reference a specific cell. A reference that does not adjust is called an absolute cell reference.

Make a PROPORTION column (G) next to AVERAGE: =E8/E12; Use AUTOFILL to show the problem when copying down.

An absolute cell reference is denoted by the dollar sign ($).

Use the F4 key to change from relative to mixed to absolute.

Absolute $H$7 - Neither column nor row adjusts when copied

Mixed $H7 - row will adjust, but column will not

H$7 - column will adjust but row will not

Relative H7 - both column and row adjusts when copied

FREEZING ROW AND COLUMN HEADINGS - a referencing formula allows you to copy information from one sheet to another. Then, if the contents of the referenced cells change, cells containing referencing formulas are automatically updated. To reference a cell in a different sheet:

Move to the cell that will contain the formula

Type an equal sign (=)

Enclose the sheet name in single quotes

Separate the sheet reference from the cell reference with an exclamation point

You can freeze column and row headings in place so they will remain in view when you scroll to distant areas of the worksheet. To free headings:

Move one cell to the right and one row below the headings you want to freeze

Choose Window, Freeze Panes (pane that contains the cell selector is the active pane)

Cntrl + Home will return to the first unfrozen cell

APPLYING STYLES – a style is a pre-defined set of formats applied to a cell or range of cells.

· Select Format, Style

· Click the style you want

· Click OK

NAMING A SHEET – Double-click the sheet tab

MOVING or COPYING A SHEET –

Select the sheet you want to move (hold down the CTRL key simulteanously if you want to copy)

Drag mouse along the sheet tabs

Release mouse when you reach destination

or

            Choose Edit, Move or Copy Sheet

 

USING WHAT-IF ANALYSIS - to change entries in a worksheet and evaluate the effect of that change. For example, you can see what effect an increase in sales would have on gross profit or what effect a reduction in taxes would have on net income.

To view two sheets in the same screen:

Choose Window, New Window (a second copy will be made of the active sheet)

Choose Window, Arrange, Tiled

Cntrl+F6 to cycle through open windows, active window will have highlighted title bar

To close an active window: double-click the control menu box

REFERENCING MULTIPLE WORKSHEETS - you can create a link from one workbook sheet to another to reference data between cells in different worksheets. A sheet reference consists of the sheet name, an exclamation point, and the cell or range reference.

The syntax of a 3-D SUM function is =SUM(‘Sheet Reference:Sheet Reference’!Cell Reference) or =SUM(‘Sheet Reference:Sheet Reference’!Range Reference) where the sheet references refer to the first and last sheets in the formula. The sheets must be enclosed in single quotes, separated from each other by a colon, and separated from the cell or range references by an exclamation point.

This is really tricky…you’ll have to try it a few times.

Move to the cell that will contain the formula

Click the AutoSum (Sum icon)

Click the cell in the active sheet containing the value that you want to sum

Hold down the SHIFT key, and click the sheet tab of the sheet that you want to sum (check the formula to make sure that it makes sense to you)

Release the SHIFT key and press enter

NOTE: Sometime you want to sum across multiple sheets (For example if you have four sheets, hold down the SHIFT, click the sheet tab of the first sheet; every sheet in between will be grouped)

USING GOAL SEEK – to find the value needed in one cell to change another. To use goal seek:

Move to the cell that represents the formula you want to solve to meet your goal

Choose Tools, Goal Seek

Type value in the "To value" text box

Type the cell reference in the "By changing cell" text box

Select OK

 

CHANGING PAGE ORIENTATION – to change the printing from portrait (vertical) to landscape (horizontal)

File, Page Setup

Click Landscape option button

ADDING HEADERS AND FOOTERS

    View, Header and Footer

PRINTING SELECTED SHEETS

Click the first sheet tab

Hold down the CNTRL key

Click the next sheet tab

Click the printer icon