© 2002 computerprep, inc. all rights reserved. excel 2000: module i
Post on 04-Jan-2016
215 Views
Preview:
TRANSCRIPT
© 2002 ComputerPREP, Inc. All rights reserved.
Excel 2000: Module I
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 1:Getting Started with Excel 2000
Objectives
• Understand spreadsheet uses• Define workbooks and worksheets• Start Excel and use the Office Assistant• Navigate in worksheets• Select ranges• Understand toolbars and menus• Exit Excel
Exploring Spreadsheet Uses
• Excel is a spreadsheet application used to manage, analyze and present data– Spreadsheet – a grid of rows and columns in
which you enter data and perform numerical calculations
• Spreadsheets have a variety of uses; for example:– Creating a home budget– Tracking business expenses– Charting and analyzing sales trends– Tracking inventories
Defining Workbooks and Worksheets
• Workbook – the electronic file in which you store your data
• Worksheet – the area of a workbook in which you enter and manipulate data
• Worksheets have 256 columns and 65,536 rows• Cell – the point at which a column and row
intersect• Cell reference – a column letter and row number
that identifies a cell’s location in a worksheet
Starting Excel and Using the Office Assistant
• You can start Excel:– From the Start menu via the Programs submenu– From a desktop shortcut– By opening an Excel workbook
• When you start Excel, Sheet1 of Book1 displays• Cell A1 is the active cell and Sheet1 is the active worksheet
– Active cell – the cell affected by the action you perform. The cell pointer highlights the active cell.
– Active worksheet – the sheet in which data will be entered or actions performed. The active worksheet contains the active cell.
Starting Excel and Using the Office Assistant (cont’d)
• The Office Assistant suggests help topics related to tasks you are currently performing and provides tips on using features more efficiently
• You can:– Specify to enable or disable the Office Assistant– Change the graphic that represents the Office
Assistant• The Office Assistant is a shared feature among all
Office 2000 applications; changes you make to it in one application apply to the other applications
Moving Around Worksheets
Action Result
Click a cell Selects the cell
Press the arrow keys Moves the cell pointer one cell in the indicated direction
Press ENTER or TAB Moves the cell pointer down one cell or right one cell, respectively
Click below the scroll box in the vertical scroll bar
Scrolls the worksheet down one screen
Click right of the scroll box in the horizontal scroll bar
Scrolls the worksheet right one screen
Press F5 Displays the Go To dialog box, which you can use to move to a specific cell
Press CTRL+HOME Moves the cell pointer to cell A1
Selecting Ranges
• You must select a cell or ranges of cells before you can perform actions on them– Range – a series of two or more cells– Contiguous range – a range that contains
adjacent cells– Non-contiguous range – a range wherein not all
cells are adjacent– Range address – the cell references of the first
cell and last cell in the range, separated by a colon (:)
Selecting Ranges (cont’d)
Action Result
Drag from one cell to another Selects a contiguous range
Click one cell, press and hold SHIFT, then click another cell
Selects a contiguous range between two cells
Select a cell or range, press and hold CTRL, then select another cell or range range
Selects a non-contiguous range
Click a column heading Selects all cells in the column
Click a row heading Selects all cells in the row
Click the button in the upper-left corner of the worksheet
Selects all cells in the worksheet
Exploring Toolbars and Menus
• Toolbar – contains buttons that represent shortcuts for commonly used features. By default, the Standard and Formatting toolbars appear on the same row.
• Menu – contains commands you execute to perform tasks. When you first display a menu, the default commands display.
• Floating toolbar – a toolbar that is not docked, or anchored, to an edge of the application window
Exiting Excel
• You can exit Excel by:– Clicking the close button in the title bar– Clicking the File, Exit command– Double-clicking the Excel control menu button
in the title bar
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 2:Creating Worksheets
Objectives
• Create new workbooks• Enter data• Edit data• Save and close new workbooks
Creating New Workbooks
• To create a new workbook:– Start Excel– Click the New button in the Standard toolbar, or– Click File, New… and double-click Workbook, or– Press CTRL+N
Entering Data
• Text – data that will not be used in calculations
– Text can consist of any combination of letters, numbers and spaces
– By default, text entries are left-aligned
• AutoComplete – completes a text entry based on existing entries in the column containing the active cell
• Value – data that contains only numbers or mathematical operators, and can be used in calculations
– You can enter positive or negative numbers
– By default, numbers are right-aligned
Entering Data (cont’d)
• You can enter dates using slashes or hyphens• Excel automatically assigns a date format to dates• Excel assumes that:
– A two-digit year lower than 30 is in the 21st century
– A two-digit year of 30 or higher is in the 20th century
• To indicate a century other than the default, enter a four-digit year
Editing Data
• Data can be edited by:– Completely replacing existing data with new
data– Using Edit mode to modify existing data
To activate Edit mode:– double-click the cell– select a cell and click in the Formula bar– select a cell and press F2
Saving and Closing New Workbooks
• Use the Save As dialog box to:– Specify where the file will be saved– Give the workbook a name
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 3:Modifying Worksheets
Objectives
• Locate and open existing workbooks• Copy worksheet data• Move worksheet data• Copy and move data from multiple sources• Delete data and reverse actions• Save existing workbooks• Save workbooks with different names
Locating and Opening Existing Workbooks
• Use the Find feature (on the Tools menu in the Open dialog box) to locate and open a file if you are uncertain of the file’s exact location or name
• Opening an existing workbook loads it into your computer’s memory for viewing or modification
Copying Worksheet Data
• To copy data:– Use the Clipboard – copy data to the Clipboard,
select a destination cell, and paste the data from the Clipboard into the destination cell
• The Clipboard can store as many as 12 items at one time
• The Clipboard toolbar displays if two or more items reside in the Clipboard
• If the Clipboard toolbar is full and you copy another item to it, the first data item you copied will be removed
Copying Worksheet Data (cont’d)
• To copy data (cont’d):– Use AutoFill – drag the fill handle to
automatically copy cell content or complete a series in an adjacent cell or range
– Use drag and drop - press and hold CTRL, drag the selected cell or range from one location and drop it into another
Moving Worksheet Data
To move data:• Use the Office Clipboard - cut data to the Office
Clipboard, then paste it to a new location• Use drag and drop - drag the selected cell or range
from one location and drop it into another
Copying and Moving Data from Multiple Sources
• You can copy or cut data from multiple sources, then paste them individually or as a group into a worksheet
• Multiple sources can include other Excel workbooks or other applications
• If you paste all the data as a group, each of the items on the Clipboard will be pasted, in order, starting at the active cell, with the next item pasted in the cell beneath the active cell
Deleting Data and Reversing Actions
• To delete data:– Clear cells – remove cell contents but retain the resulting
blank cells– Delete cells – delete the cells themselves so the
surrounding cells shift to fill in the space• To reverse actions:
– Click the Undo button to reverse the last action– Click the Redo button to reverse an undo action
• You can reverse as many as the last 16 actions performed• You cannot reverse actions such as printing, saving, closing a
workbook or clearing the Clipboard
Saving Existing Workbooks and Saving Workbooks with Different
Names• To save an existing workbook:
– Click the Save button in the Standard toolbar• To save a workbook with a different name:
– Display the Save As dialog box – Type the name of the new workbook– Press ENTER
• You can use the Save As dialog box to specify a different name, location or format for a revised workbook
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 4:Formatting Worksheets
Objectives
• Insert and delete rows and columns• Change column widths and row heights• Use numeric formats• Change the alignment of data• Use conditional formatting
Inserting and Deleting Rows and Columns
• When you insert rows or columns, existing data moves down or to the right to accommodate the new rows or columns
• When you delete rows or columns, all data within them is deleted
• When you insert or delete rows or columns, the total number of rows and columns in the worksheet remains constant
Changing Column Widths and Row Heights
• By default, columns are 8.43 characters wide and rows are 12.75 points high (one inch is equal to 72 points)
• To change column width or row height:– Use the Column Width dialog box – specify an exact
amount by which to change column width– Use the Row Height dialog box – specify an exact amount
by which to change row height– Use the mouse – drag column or row borders to increase
or decrease column width or row height – Use AutoFit – double-click the right border of a column or
the bottom border of a row to precisely accommodate the data within a selected cell or range
Using Numeric Formats
• Number format – displays numbers with a specified number of decimal places
• Currency format – displays numbers with leading dollar signs ($) and thousands separators (,)
• Accounting format – displays numbers with dollar signs and thousands separators. Dollar signs and decimal points are vertically aligned in the column.
Using Numeric Formats (cont’d)
• Percent format – displays numbers as percentages (%) with a specified number of decimal places
• Comma format – displays numbers with thousands separators (,) and a specified number of decimal places
• Date format – enables you to specify the appearance of a date entry
Changing the Alignment of Data
• By default, text is left-aligned and numbers are right-aligned within cells
• Use the alignment buttons in the Formatting toolbar to left-justify, center or right-justify the data within cells
Using Conditional Formatting
• Conditional formatting – formats a range of cells to display in a manner you determine based on criteria you specify
• To apply conditional formatting:– Display the Conditional Formatting dialog box – Specify the criteria by which cell contents will
be evaluated– Specify the formatting to apply to the cells that
meet the criteria
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 5:Printing Worksheets
Objectives
• Preview print jobs• Use Page Break Preview• Define page setup options• Print worksheets
Previewing Print Jobs
• With the Print Preview window, you can:– Display the next or previous pages of a
multiple-page worksheet– Toggle between full-page and magnified views– Specify print settings– Specify margin settings and column widths– Display the worksheet in Page Break Preview to
view and modify page breaks
Using Page Break Preview
• With Page Break Preview, you can:– Modify page breaks by dragging the blue
dashed page break lines to a different location– Insert or remove horizontal and vertical page
breaks
Defining Page Setup Options
• Page setup options affect only the way worksheets look when printed
• Display the Page Setup dialog box to define page setup options
• You can use page setup options to change:– Scaling – the print size based on a percentage
of a worksheet’s normal size– Orientation – the position of the worksheet on
the page. You can specify portrait (the page is taller than it is wide) or landscape (the page is wider than it is tall) orientation.
Defining Page Setup Options (cont’d)
• You can use page setup options to change (cont’d):– Margins – measurements that determine how
far from the edges of a page printed text will appear
– Headers – text that prints at the top of every page
– Footers – text that prints at the bottom of every page
Printing Worksheets• To print a worksheet:
– Click the Print button in the Standard toolbar (prints the entire worksheet)
– Display the Print dialog box to specify print options
Print dialog box options include:
• Printer – specifies which printer to use
• Print range – specifies the pages to print
• Print what – specifies the portion of the worksheet to print
• Copies – specifies the number of copies to print
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 6:Working with Basic Formulas and
Functions
Objectives
• Use formulas• Use cell references• Use functions• Use AutoCalculate
Using Formulas
• Formula - a worksheet entry that performs a calculation (formula entries begin with an equal sign (=) to differentiate them from text entries)
• Operator – a symbol used in formulas to perform mathematical calculations
• To create a formula:– Type an equal sign– Click cells to specify cell references– Type operators to specify the types of
calculations to perform
Using Formulas (cont’d)
Operator
Description
+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponentiation
Using Formulas (cont’d)
• To edit formulas:– Type over (replace) the existing formula– Activate Edit mode, highlight the cell reference
you want to change, then click the correct cell• When you activate Edit mode, the Range Finder
feature is automatically activated– Range Finder - identifies cell references in a
formula by highlighting them in different colors
Using Cell References
• Relative cell reference – specifies the location of a cell relative to the cell containing the formula
– When you move or copy a formula, a relative cell reference adjusts automatically to reflect the new location
• Absolute cell reference – specifies the exact location of a cell without regard to the location of the cell containing the formula
– When you move or copy a formula, an absolute cell reference always references the same cell
• Mixed cell reference – contains both relative and absolute cell reference components (the row or column component can be relative or absolute)
Using Functions
• Function – a predefined formula that performs special or advanced calculations
• Function syntax – Function Name (argument1, argument2)– Function Name – a word or abbreviation used to
identify a function– Argument – the data required in a function to
produce a value• Formula Palette – provides assistance for entering
cell ranges and references into a function or formula
Using Functions (cont’d)
• AVERAGE – calculates the average of values in a specified range of cells
• MAX – calculates the maximum value in a specified range
• MIN – calculates the minimum value in a specified range
• AutoSum - automatically totals the values in columns or rows
• Paste Function Feature – a tool that guides you through creating a function so you do not have to memorize the function syntax
Using AutoCalculate
• AutoCalculate – displays the average, count, maximum, minimum, or sum of the data in a selected range (contiguous or non-contiguous) without entering a formula or function
• The AutoCalculate result is temporary and displays in the Status bar only
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 7:Using Multiple Worksheets
Objectives
• Work with multiple worksheets• Create three-dimensional formulas• Rename worksheets• Preview and print multiple worksheets
Working with Multiple Worksheets
• With multiple worksheets, you can:– Move between worksheets– Insert and delete worksheets– Move and copy data between worksheets– Move and copy worksheets– Group worksheets
Moving Between Worksheets
• The active worksheet contains the cell pointer• The active worksheet tab displays in bold• Click a worksheet tab to activate the associated
worksheet• Click the tab scrolling buttons to view worksheet
tabs not visible in the workbook window• Right-click a tab scrolling button to display a
menu of all worksheets in the workbook
Inserting and Deleting Worksheets
• New worksheets are inserted to the left of the active worksheet
• New worksheets are labeled sequentially, regardless of their order in the workbook
• If you delete a worksheet containing data, the data is also deleted
Moving and Copying Data Between Worksheets
• Use cut, copy and paste to move or copy data between worksheets the same way that you move or copy data within a single worksheet
• When you move or copy formulas to a different worksheet, relative cell references adjust to reflect the new location
Moving and Copying Worksheets
• You can move and copy worksheets within the same workbook or to different workbooks
• To move worksheets:
– Use the Move or Copy dialog box
– Use drag and drop – drag the worksheet to the desired location
• To copy worksheets:
– Use the Move or Copy dialog box
– Use drag and drop – press and hold CTRL, then drag the worksheet to the desired location
Grouping Worksheets
• Grouping multiple worksheets allows you to perform the same action on all worksheets simultaneously
• To group contiguous worksheets:
– Activate a worksheet
– Press and hold SHIFT
– Click the worksheet tab of the last worksheet in the group
• To group non-contiguous worksheets:
– Press and hold CTRL
– Click the desired worksheet tabs
Creating Three-Dimensional Formulas
• Three-dimensional formula – a formula in one worksheet that references cells in other worksheets
• To enter a range of worksheet references into a three-dimensional formula, you must group the worksheets
Renaming Worksheets
• You can rename worksheets with descriptive names to better identify their contents
• Each worksheet in a workbook must have a unique name– Names can contain as many as 31 characters– Names cannot contain: asterisk (*), backslash
(\), colon (:), square brackets ([ and ]) or question mark (?) characters
Previewing and Printing Multiple Worksheets
• You can preview and print multiple worksheets by selecting the worksheets before clicking the Print button or Print Preview button in the Standard toolbar
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 8:Enhancing Worksheets
Objectives
• Enhance text• Add and modify borders• Shade ranges• Create and modify three-dimensional objects• Insert, resize and move pictures
Changing Text and Font Attributes
• Font – the typeface and type size of text• Attributes – the characteristics of text formatting
that you can apply to text• To change text and font attributes:
– Specify attributes in the Format Cells dialog box– Click formatting buttons in the Formatting
toolbar– Use the Format Painter to copy formatting from
one or more cell(s) to other cells
Merging Cells• To merge cells:
– Select the range of cells to merge– Click the Merge and Center button in the
Formatting toolbar (centers the text in the merged cell)
• You can merge cells only one row at a time. Merging multiple rows will keep only the upper-left data in the selected range.
Rotating Text and IndentingText in Cells
• To rotate text:– Use the Orientation options in the Alignment
card of the Format Cells dialog box (you can rotate text up to 90 degrees in each direction)
• To indent text in cells:– Use the Increase Indent and Decrease Indent
buttons in the Formatting toolbar
Adding and Modifying Borders
• To add and modify borders:– Use the Borders button in the Formatting
toolbar to specify border options– Use the Border card of the Format Cells dialog
box to specify border options, such as:• How borders should display• Border style• Border color
Shading Ranges
• To shade ranges:– Use the Fill Color button in the Formatting
toolbar to display and add color fills– Use the Patterns card of the Format Cells dialog
box to specify background colors and patterns
Creating and Modifying Three-Dimensional Objects
• To create a three-dimensional object:– Click the object you want to draw in the
Drawing toolbar– Click and drag to draw the object– Use the 3-D palette in the Drawing toolbar to
apply a three-dimensional effect to the object
Creating and Modifying Three-Dimensional Objects (cont’d)
• To modify a three-dimensional object:– Use the Direction palette on the 3-D Settings
toolbar to change the direction of the object– Use the Lighting palette on the 3-D Settings
toolbar to determine the brightest side of the object
– Use other buttons on the 3-D Settings toolbar to change the tilt, depth and color of the object
Inserting, Resizing, and Moving Pictures
• You can insert:– Clip art– Pictures from a scanner or digital camera– Graphics files stored in one of the standard
graphics formats• When you select a picture, sizing handles display
on its sides and corners
Inserting, Resizing, and Moving Pictures (cont’d)
• To resize a picture:– Drag a corner sizing handle (resizes a picture
proportionally)– Drag a side sizing handle (resizes a picture
without maintaining proportions)– Use the Size card of the Format Picture dialog
box to resize a picture with precise measurements
• To move a picture:– Drag it to the desired location
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 9:Using Styles and AutoFormats
Objectives
• Use styles• Use AutoFormats
Using Styles
• Style – a collection of formatting attributes stored under one name
• Six built-in styles are available:
– Comma – Comma format with two decimal places
– Comma(0) – Comma format with no decimal places
– Currency – Accounting format with two decimal places
– Currency(0) – Accounting format with no decimal places
– Normal – the default format
– Percent – Percent format with no decimal places
Creating Styles
• You can create styles:– By example – select a cell containing the
formatting you want, then assign a name to the group of formatting attributes
– By definition – specify the formatting attributes you want to include in the style using the Format Cells dialog box
Formatting Cellswith Styles
• To apply a style:– Select the cell or range to which you want to
apply the style– Select a style from the Style dialog box (the
style will override any formats you previously applied to the cell(s))
Modifying andDeleting Styles
• When you modify or delete a style, the change(s) affects all cells to which you have applied the style in the current workbook– When deleting a style, the cell(s) to which the
style was applied will lose the formatting associated with that style
• Styles can be modified by example or by definition, using the Style dialog box
Using AutoFormats
• AutoFormats – a set of predefined table formats• You can use AutoFormats to automatically turn a
range of cells into a formatted table• To apply an AutoFormat:
– Select the range of cells to which you want to apply the AutoFormat
– Display the AutoFormat dialog box and select an AutoFormat
© 2002 ComputerPREP, Inc. All rights reserved.
Lesson 10:Working with Charts
Objectives
• Create charts• Format charts• Modify charts• Print charts
Creating Charts
• Chart – a graphical representation of selected data in a worksheet
• Chart Wizard – A tool that prompts you for information necessary to create or modify a chart
• To create a chart:– Select the range of cells you want to chart– Click the Chart Wizard button– Proceed through the Chart Wizard
Formatting Charts
• To format a chart using menu commands:– Right-click a chart component to display the
Format dialog box– Specify formatting attributes in the Format
dialog box• To format a chart using the Chart toolbar:
– Display the Chart toolbar– Click appropriate Chart toolbar tools to apply
the desired formatting
Changing Chart Types and Repositioning Legends
• To change a chart type:– Display the Chart toolbar– Display the Chart Type palette– Select the desired chart type
• To reposition the legend:– Drag the legend to a new location, or– Use the Placement card of the Format Legend
dialog box to specify the legend placement
Resizing and Moving Charts
• To resize a chart proportionally:– Drag a selection handle while pressing SHIFT
• To resize a chart from its center:– Drag a selection handle while pressing CTRL
• To move a chart:– Click a blank area of the chart and drag it to the
desired location
Editing Chart Data
• After you have created a chart, you can change the data in the worksheet
• Changing the data in the worksheet will update the chart automatically
Placing and Modifying Data Tables in Charts
• Data table – a table that is placed within a chart and includes only the data used in the chart
• To place a data table in a chart:– Click a blank area of a chart– Click the Data Table button in the Chart toolbar
• To modify a data table:– Format a data table in the same way you format
any other chart object
Printing Charts
• To print a chart by itself:– Select the chart– Display the Print dialog box– Click the OK button
• To print the chart with the worksheet data:– Deselect the chart– Print the worksheet
top related