© 2002 computerprep, inc. all rights reserved. excel 2000: module ii

© 2002 ComputerPREP, Inc. All rights reserved.

Excel 2000: Module II


Lesson 2:Using Names

Objectives

• Name cells• Name formulas• Check and list names and references• Create three-dimensional names• Work with ranges

Creating Cell Names

• You can create cell names using the:

– Define Name dialog box

– Create Names dialog box

• Naming conventions

– The first character must be a letter or underscore; the remaining characters can be letters, numbers, periods or underscores

– Cell references and spaces cannot be used

– Names can contain up to 255 characters

– Names are not case-sensitive

Using Cell Names in Formulas

• Cell names are always absolute• When you copy or move a formula containing a

cell name, you must change the cell name in the formula to return a correct result

• To place cell names in a formula:– Type the cell name, or– Access the cell name from the Paste Name

dialog box

Naming Formulas

• To name a formula:– Display the Define Name dialog box– Type the name you want to use– Press TAB twice– Type the formula

Checking and Listing Names and References

• Checking the references of names activates the referenced cell or range, enabling you to verify that the correct cell or range is referenced by the name

• To check the references of names:– Display the Define Name dialog box and click

on the name, or– Select a name from the Name Box drop-down

list in the Formula Bar

Checking and Listing Names and References (cont’d)

• You can paste a list of defined names and references into a worksheet to assist you in using the names in various cells, ranges and formulas

• To paste a list of defined names:– Display the Paste Name dialog box – Click on the Paste List button

Creating Three-Dimensional Names

• Three-dimensional name – a range name that refers to the same cell(s) in multiple worksheets

• To create a three-dimensional name:– Display the Define Name dialog box– Type the name you want to use– Group the worksheets you want to include– Select the desired cell(s)

Creating and Naming Ranges

• You can name ranges using:– The Define Name dialog box, or– The Name Box text box in the Formula Bar

• To name a range using the Name Box:– Select the cell(s) you want to name– Click on the arrow in the Name Box– Type the name you want to use

Formatting and Clearing Ranges

• You can apply formats to all the cells in a named range at one time

• To format a named range:– Select the range by clicking on its name in the

Name Box text box– Apply formatting as usual

• To clear a named range:– Display the Define Name dialog box– Select the name you want to clear– Click on the Delete button

Copying and Moving Ranges

• When you copy a range, the additional range is not named

• When you move a range, the name assigned to the range will move with the range


Lesson 3:Working with Advanced Functions

Objectives

• Use the NOW function• Use the DATE function • Use the VLOOKUP function• Use the IF function• Use the PMT function• Use the FV function

Using the NOW Function

• NOW function – returns a serial number that represents the current date and time– Syntax: NOW()

– The NOW function contains no function arguments

– The NOW function can be used in calculations

Using the DATE Function

• DATE function – returns the serial number that represents a particular date– Syntax: DATE(Year,Month,Day)

– Function arguments:• Year – a one- to four-digit number

representing 1900 to 9999• Month – a number from 1 to 12• Day – a number from 1 to 31

– The DATE function can be used in calculations

Using the VLOOKUP Function

• VLOOKUP function – locates values in a lookup table (table array) for use in another area of the worksheet– Syntax: VLOOKUP(Lookup_value,Table_array,

Col_index_num,Range_lookup)

– Function arguments:• Lookup_value – used to find a value in the lookup table• Table_array – the range that contains the resultant data• Col_index_num – the array column number containing

the resultant data• Range_lookup – a logical value (TRUE or FALSE) that

specifies whether the resultant value is approximate or an exact match

Using the IF Function

• IF function – evaluates a condition and returns a value based on the true/false status of the condition– Syntax: IF(Logical_test, Value_if_true,Value_if_false)

– Function arguments:• Logical_test – a mathematical expression• Value_if_true – the result if the Logical_test is

true• Value_if_false – the result if the Logical_test

is false

Using the PMT Function• PMT function – calculates the periodic payments

of a loan– Syntax: PMT(Rate,Nper,Pv,Fv,Type)– Function arguments:

• Rate – the periodic interest rate of the loan• Nper – the number of payment periods• Pv – the present value of the loan• Fv – the future value of the loan• Type – the payment type (0 = payment at end

of period; 1 = payment at beginning of period)

Using the FV Function

• FV function – calculates the future value of an investment– Syntax: FV(Rate,Nper,Pmt,Pv,Type)– Function arguments:

• Rate – the periodic interest rate of the loan• Nper – the number of payment periods• Pmt – the payment made each period• Pv – the present value of the loan• Type – the payment type (0 = payment at end

of period; 1 = payment at beginning of period)


Lesson 4:Working with

Advanced Formatting Features

Objectives

• Use advanced number formats• Create custom number formats• Apply outlines

Using Advanced Number Formats

• Use the Number card of the Format Cells dialog box to apply advanced number formats, such as:– The Time format:

• Display time entries using a 12-hour or 24-hour clock

• Include the date with the time– The Fraction format:

• Display data as fractions, rather than as decimals– The Scientific format:

• Display lengthy numbers as a number between one and ten multiplied by a power of ten

Creating Custom Number Formats

• To create custom number formats:– Use the Number card of the Format Cells dialog

box to:• enter formatting placeholders in the Type

text box• edit built-in format codes found in the Type

list box

Applying Outlines

• Outline – categorizes rows or columns of worksheet entries as detail data or various levels of summary data

• Detail data – rows or columns of worksheet data, located above and/or to the left of summary data

• Summary data – rows or columns containing formulas that calculate or summarize detail data, located below and/or to the right of the detail data

• The summary data must be adjacent to the detail data

Applying Outlines (cont’d)

• To create an outline automatically:– Select any cell in the range you want to outline– Use the Auto Outline feature

• To create an outline manually:– Select each group of detail data– Use the Group dialog box to create the outline

• Outline symbols display to the left of or on top of the worksheet

• Click on outline symbols to hide or display various levels of detail

• Removing outlines does not alter the data; it removes only the outline groupings


Lesson 5:Locating and Updating Data

Objectives

• Find and replace data• Control recalculation

Finding and Replacing Data

• To find and replace text and numeric data:– Display the Replace dialog box, in which you can

specify:• The text for which to search• The text with which to replace the search text• To replace the current or all occurrences of the

search text with the replacement text• To find only the next occurrence of the search

text• Advanced search options, such as matching

case and finding entire cells only• The direction of the search: by column or by row

Controlling Recalculation

• By default, Excel recalculates formulas automatically when you change data in contributing cells

• To control recalculation:– Display the Calculation card of the Options

dialog box – Specify to calculate manually, or– Specify to calculate automatically except for

formulas in tables• After you set up manual recalculation, you must

press F9 to recalculate the worksheet


Lesson 6:Using Special Format and Print

Options

Objectives

• Format zero values• Change the display of negative values• Use print options

Formatting Zero Values

• To display zero values as blank cells:– Display the View card of the Options dialog box – Turn off the Zero values check box

• To display zero values as text:– Display the Number card of the Format Cells

dialog box – Create a custom number format to specify the

text to display in place of zeros

Changing the Display of Negative Values

• By default, all cell entries, including negative values, display in black

• To change the display of negative values:– Display the Number card of the Format Cells

dialog box – Choose a custom number format that displays

negative values in red

Printing Titles and Gridlines

• Titles – descriptive text entries that display above columns of data or to the left of rows of data

• Gridlines – horizontal and vertical lines that mark the boundaries of cells

• To print titles and gridlines:– Display the Sheet card of the Page Setup dialog

box– Specify the rows to repeat at top, and/or– Specify the columns to repeat at left– Turn on the Gridlines check box

Setting Print Areas

• Print area – a portion of a worksheet designated as the only area to print (remains in effect until you change or clear it)

• To override a print area:– Select the part of the worksheet you want to

print– Click Selection in the Print dialog box

Printing Multiple Worksheets

• You can print multiple worksheets by defining a three-dimensional print range

• You can also print multiple worksheets in their entirety by grouping the worksheets without specifying a print range

Previewing and Printing Workbooks

• To preview workbooks in the Print Preview window, you must group them

• To print workbooks in their entirety:– Group all worksheets and print them together,

or– Specify to print the entire workbook in the Print

dialog box


Lesson 7:Protecting Worksheet Data

Objectives

• Use comments• Use worksheet and range protection• Hide and display data• Use password protection

Creating Comments

• Comments – instructional or explanatory text that is attached to cells

• Comment indicators – small red triangles that display in the upper-right corner of cells containing comments

• To create a comment:– Right-click a cell and click Insert Comment– Type the comment in the comment text box

Displaying and Hiding Comments

• To display a comment:– Point to a cell containing a comment indicator– Right-click the comment and click Show

Comment– Select the Comment & indicator option in the

View card of the Options dialog box

Displaying and Hiding Comments (cont’d)

• To hide a comment:– Move the mouse pointer off of the cell

containing a comment indicator– Right-click the comment and click Hide

Comment– Select the None option in the View card of the

Options dialog box

Editing and Deleting Comments

• You can edit and delete visible and hidden comments

• To edit a comment:– Right-click the comment and click Edit

Comment– Make the changes and click outside the

comment text box• To delete a comment:

– Right-click the comment and click Delete Comment

Enabling Worksheet Protection

• Protection – the process of securing cells, worksheets and workbooks from unauthorized access or modification

• Enabling worksheet protection prevents data from being entered or modified in the worksheet

• To enable worksheet protection:– Display the Protect Sheet dialog box – Specify the worksheet components to protect– Click OK

Unprotecting Cells

• You can protect some cells and leave others available for modification

• To unprotect certain cells in a worksheet you otherwise want to “protect”:– Select the cells to unprotect– Display the Protection card of the Format Cells

dialog box– Uncheck the Locked check box– Enable worksheet protection

Hiding and Displaying Data

• You can hide certain worksheet elements so they do not display, including:– Formulas (when you hide a formula, the result

displays in the worksheet but the formula does not display in the Formula bar)

– Columns– Rows– Entire worksheets

• You display hidden data by reversing the process you used to hide it

Using Password Protection

• Password – a text string known only to authorized users of a workbook; the password is required to gain access to the workbook

• You can assign a password to a workbook file to prevent unauthorized users from opening or modifying the workbook

• Unauthorized users can save a password-protected workbook under a different file name

• You can remove a password by deleting it in the Save Options dialog box


Lesson 8:Manipulating the Screen Display

Objectives

• Work with multiple workbooks• Freeze worksheet panes• Adjust the zoom setting• Work with toolbars

Displaying Multiple Workbooks

• To open more than one workbook simultaneously:

– Display the Open dialog box

– Select the first workbook

– Press and hold SHIFT to select contiguous workbooks, or

– Press and hold CTRL to select non-contiguous workbooks

– Select the remaining workbook(s)

– Click the Open button

• Use the Arrange Windows dialog box to specify how the multiple workbooks will display

Navigating Between Open Workbooks

• To navigate between open workbooks:– Click the desired workbook (if they are all

visible on the screen), or– Display the Window menu and select the

desired workbook, or– Press CTRL+TAB

Freezing Worksheet Panes

• Freeze panes – enables you to lock rows and/or columns to prevent them from scrolling out of view

• Unfreeze panes – enables you to unlock rows and/or columns so that no portion of the worksheet remains stationary as you scroll through it

Freezing Worksheet Panes (cont’d)

• To freeze rows:

– Select the row just below the row(s) to freeze, then click Window, Freeze Panes

• To freeze columns:

– Select the column just to the right of the column(s) to freeze, then click Window, Freeze Panes

• To freeze rows and columns simultaneously:

– Select the cell just below the row(s) and just to the right of the column(s) to freeze, then click Window, Freeze Panes

Adjusting the Zoom Setting

• Zoom setting – controls the size of the worksheet displayed on the screen

• To adjust the zoom setting, use the Zoom box in the Standard toolbar:– Type a percentage value, or– Select a percentage value from the drop-down

list • You can also select a portion of a worksheet to

zoom in and display only the selected data

Displaying and Hiding Toolbars

• To display and hide toolbars:– Using the toolbar shortcut menu:

• Click an unselected toolbar to display it• Click a selected toolbar to hide it

– Using the Toolbars card of the Customize dialog box:

• Turn on a toolbar name to display it• Turn off a toolbar name to hide it

Moving Toolbars

• Floating toolbar – a toolbar that is not positioned in a toolbar dock

• Toolbar dock – the areas at the top, bottom, right and left sides of a worksheet where you can lock a toolbar into place

• You can move a docked toolbar by dragging its anchor to another location

• You can move a floating toolbar by dragging its title bar to another location

• You can dock a floating toolbar in its previous toolbar dock by double-clicking its title bar

Customizing Toolbars

• By default, the Standard and Formatting toolbars display on one line

• You can customize toolbars by:– Forcing the Standard and Formatting toolbars

to display on two separate lines– Adding, removing and rearranging buttons by

displaying the Commands card of the Customize dialog box


Lesson 9:Sharing Worksheet Data

Objectives

• Import text files• Export data to other applications• Link workbooks

Importing Text Files

• Data originally created in another database or spreadsheet program must first be saved as a text file before being imported into Excel

• Text file data must contain fields that are either of fixed width or delimited

• Delimiter – a character, such as a space, comma or tab, which separates items of data

• To import a text file, use the Text Import Wizard

Importing Text Files (cont’d)

• You can import a text file into a refreshable range that enables you to refresh data in Excel whenever the source text file changes

• To refresh data after changing the source text file:– Click on Data, Refresh Data– Double-click on the text file in the Import Text

File dialog box

Exporting Data to Other Applications

• To export Excel data to other applications:– Use copy and paste

or– Use the Save as type list box to select the file

type to which you want to export the workbook, then save the file

Linking Workbooks

• Link – a dynamic connection between two workbooks that allows the sharing of data between the workbooks

• Source workbook – a workbook containing source data referenced by a dependent workbook

• Dependent workbook – a workbook containing links to other workbooks via external references

• When you make changes in a source workbook, the dependent workbook is updated automatically

Linking Workbooks (cont’d)

• To create links:– Select cells in the source workbook– Copy the cells to the Clipboard– Activate the dependent workbook– Use the Paste Special command to paste a link

to the source data

or– Write an external reference formula in the

dependent workbook

Linking Workbooks (cont’d)

• To update links:– If the dependent workbook is open, external

reference formulas update automatically

or– If the dependent workbook is closed, you can

choose to update the data when you open the dependent workbook


Lesson 10:Automating Your Work

Objectives

• Work with existing templates• Create templates• Work with macros• Create custom lists

Customizing Templates

• Template – a workbook that you use as a pattern for new workbooks

• To customize a template:– Open an existing template– Make the desired changes – Save the template

• Changes you make to a template will apply to new workbooks you create based on the template

• Previous workbooks created using the template will not be affected

Creating Workbooks Based on Templates

• You can create a new workbook that contains all the formatting information, text and other spreadsheet objects in a template

• To create a workbook based on a template:– Display the General card of the New dialog box– Double-click a template– Enter data in the data entry cells and save the

workbook with a new name

Creating Templates

• To create a template:– Open (or create) the workbook from which you

will create the template– Include the sheets, text, formulas, formatting

and macros that will display in all workbooks you create with the template

– Unlock data entry cells and activate worksheet protection

– Display the Save As dialog box, click Template (*.xlt) in the Save as type drop-down list, type a name for the template, then click Save

Recording Macros

• Macro – a set of commands or actions that you record or write to automate repetitive or complex tasks

• Module sheet – a worksheet containing Visual Basic code defining a macro

• To record a macro:

– Display the Record Macro dialog box

– Type the macro name, type a shortcut key, specify the location in which to store the macro, add a description, then click OK

– Perform the actions to record; when finished, click the Stop Recording button

Executing Macros

• When you execute a macro, the recorded commands execute automatically

• To execute a macro:– Press the shortcut key you assigned when you

recorded the macro, or– Display the Macro dialog box and double-click

the macro name

Editing Macros

• To edit a macro:– Display the Macro dialog box and click Edit to

open the Visual Basic application and display the module sheet

– Make changes in the module sheet– Save the Visual Basic code and close the

module sheet

Creating Custom Lists

• Create custom lists for any kind of data you may need to enter regularly

• To create a custom list:

– Display the Custom Lists card of the Options dialog box

– Specify to create a new list and enter the list entries

– Click Add to create another list or OK to close the dialog box

• After the custom list is created, you can add the list to a worksheet by typing any one entry from the list, then using AutoFill to enter the remaining entries

© 2002 computerprep, inc. all rights reserved. excel 2000: module ii

Documents