Transcript Document

Excel 2003 Specialist
© 2001 ComputerPREP, Inc. All rights reserved.
Lesson 1:
Creating and Modifying
Workbooks
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
•
•
Enter data
Edit and delete data
Fill cells
Navigate worksheets
Find and replace data
Copy worksheet data
Move worksheet data
Overview of Workbooks and
Worksheets
• Workbook – the electronic spreadsheet file in
which data is stored
• Worksheet – the area of a workbook in which data
is entered and manipulated
• Cell – the intersection of each column and row
• Active cell – the cell that is affected by the action
you perform
• Cell pointer – the thick rectangular border that
encloses the active cell
Overview of Workbooks and
Worksheets (cont’d)
Cell reference
Active cell
Cell pointer
Entering Data
• Text – data that will not be used in calculations
• Value – data that contains only numbers or
mathematical operators; values are used in
calculations
• AutoComplete – completes a text entry based on
existing entries in the column containing the
active cell
• Date:
– Two-digit year under 30 is in the 21st century
– Two-digit year 30 or higher is in the 20th century
Editing and Deleting 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
To delete data:
• Select the data you want to erase and press
DELETE
Filling Cells
• AutoFill – drag a cell’s fill handle to adjacent cells
to copy the contents of the cell
Fill handle
• AutoFill is useful for copying data or for
completing text or number series, such as:
– Days of the week
– Months
– Fiscal quarters
– Dates
Navigating Worksheets
Action
Result
Click a cell
Selects the cell
Press the left, right, up or down 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 to the right of the scroll box in the
horizontal scroll bar
Scrolls the worksheet to the right one
screen
Press CTRL+HOME
Moves the cell pointer to cell A1
Press F5
Displays the Go To dialog box, which
you use to move to specific cells
Finding and Replacing Data
To find and replace data:
• Display the Find And Replace dialog box
In the Find And Replace dialog box, you can specify:
– the text for which to search
– the text with which to replace the search text
– to replace the current or all instances of the search
text with the replacement text
– to find only the next or all instances of the search
text
– advanced search options, such as matching case
and searching for formatting attributes
Finding and Replacing Data
(cont’d)
• The Research task
pane provides
reference information
you can use to
conduct research
about topics you
specify
Copying Worksheet Data
To copy data:
• Use the Office Clipboard – copy data to the Office
Clipboard, then click the item in the Clipboard
gallery to paste it to a new location
• Use AutoFill – drag the fill handle to adjacent cells
• 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
Lesson 2:
Manipulating Worksheet Data
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
•
•
Create formulas
Edit formulas
Use cell references
Use functions
Use the Function Wizard
Filter data
Sort data
Creating Formulas
• Formula – a cell entry that performs a calculation
– Formula entries begin with an equal sign ( = )
• Operator – a symbol used in formulas to perform
mathematical calculations:
– Addition ( + )
– Subtraction ( - )
– Multiplication ( * )
– Division ( / )
– Exponentiation ( ^ )
• Range Finder – identifies cell references in a
formula by highlighting them in different colors
Editing Formulas
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
Using Cell References
• Relative cell reference – specifies the location of a
cell relative to the cell containing the formula
• Absolute cell reference – specifies the exact
location of a cell without regard to the location of
the cell containing the formula
• Mixed cell reference – contains both relative and
absolute cell reference components
Using Functions
• Function – a predefined formula that performs
complex or advanced calculations
– Function name – a word or abbreviation used to
identify a function
– Argument – the data required in a function to
produce a value
• Function Argument tool tip – a pop-up box you can
use to access help information about function
components
AutoSum
• AutoSum – automatically sums the values in
columns or rows
The AutoSum drop-down menu includes:
– SUM (default)
– AVERAGE
– COUNT
– MAX
– MIN
AutoSum Functions
• AVERAGE – calculates the average of values in a
selected range of cells
• MAX – calculates the maximum value in a selected
range of cells
• MIN – calculates the minimum value in a selected
range of cells
AutoCalculate
• AutoCalculate – displays the average, count,
maximum, minimum or sum of the data in a
selected range (contiguous or noncontiguous)
without entering a formula or function
The AutoCalculate result is temporary and
displays in the status bar only
Using the Function Wizard
• Function Wizard - helps you determine which function
to use or assists you with providing the necessary data
for the arguments needed to complete the function
To activate the Function Wizard:
– click the Insert Function button in the Formula bar
– display the AutoSum drop-down menu and click
More Functions
– after beginning a formula, display the Name dropdown list in the Formula bar and click More
Functions
The NOW Function
• NOW function – returns a serial number that
represents the current date and time
– The NOW function contains no function
arguments
– The NOW function can be used in calculations
The DATE Function
• DATE function – returns the serial number that
represents a particular date
– 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
The IF Function
• IF function – evaluates a condition and returns a
value based on the true/false status of the condition
– 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
The PMT Function
• PMT function – calculates the periodic payments
of a loan based on constant payments and a
constant interest rate
– 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 (1 = payment at
beginning of period; 0 = payment at end of
period)
Filtering Data
• Filter – displays only worksheet data that meet a
set of criteria
• List – a series of rows containing related data. A
list contains no blank rows and the first row has
columns labels (the header row)
• AutoFilter – filters worksheet data based on
specified criteria
• Criteria – a set of search conditions to which data
is compared
AutoFilter
AutoFilter options:
• Sort Ascending – sorts the data in ascending order
by the items in the selected column
• Sort Descending – sorts the data in descending
order by the items in the selected column
• (All) – displays all rows in the list
• (Top 10…) – displays the top n rows in the list,
where n is any number you specify
• (Custom…) – specifies a custom filter
• List items – the current data items in the selected
column
Simple and Custom Criteria
• Simple criteria – consists of a single search condition
for a column of data. Simple criteria can be applied
once or multiple times
• Custom criteria – a set of search conditions (consisting
of a comparison operator and a value) to which data is
compared. You can:
– specify one or two custom criteria at a time
– specify whether matching entries must meet both or
either criteria
• Comparison operator – a mathematical expression
such as “is greater than”
Sorting Data
To sort data, the data must be in list format
To sort a list based on one criterion:
• Select a cell in a column in the list
• Click the Sort Ascending or Sort Descending
button in the Standard toolbar
• Click the Sort Ascending or Sort Descending
option in the AutoFilter drop-down list
Sorting Data
(cont’d)
To sort a list based on
multiple criteria:
– Display the Sort dialog
box
– Specify sort keys and sort
orders using the Sort By
and Then By drop-down
lists and options
– Specify whether or not
the list contains a header
row
Lesson 3:
Restructuring and Formatting
Worksheets
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
• Insert and delete cells
• Insert and delete rows and
columns
• Adjust column widths and
row heights
• Hide and unhide rows and
columns
• Apply numeric formats
• Align cell data
• Change fonts and text
attributes
•
•
•
•
Add borders
Add background shading
Use styles
Find and replace cell
formats
• Add worksheet
backgrounds
• Apply AutoFormats
• Create workbooks from
templates
Inserting and Deleting Cells
When you insert cells,
surrounding cells shift to the
right or down to
accommodate the added cells
When you delete cells, cell
contents are deleted and the
surrounding cells shift to the
left or up to fill in the gap
Inserting and Deleting Rows
and Columns
• When you insert rows or columns, existing data
moves down or to the right
• When you delete rows or columns, the remaining
data moves up or to the left, and all data within the
deleted rows or columns is also deleted
• When you insert or delete rows or columns, the
total number of rows and columns in the
worksheet remains constant
Adjusting Column Widths and
Row Heights
To adjust column width:
• Use the Column Width dialog box – specify an
exact amount (automatically rounded to the
nearest one-seventh of a character)
To adjust column width or 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 fit the
data contained therein
Hiding and Unhiding Rows
and Columns
• Hide rows or columns to:
– Prevent others from viewing certain data
– Work on two columns or rows that do not
appear in the same window
• Unhide rows or columns to redisplay the hidden
data
Applying Numeric Formats
• Number format – displays numbers with a
specified number of decimal places
• Percent format – displays numbers as percentages
(%) with a specified number of decimal places
• Comma format – displays numbers with thousands
separators (,) and two decimal places
Applying Numeric Formats
(cont’d)
• Accounting format – displays numbers with dollar
signs ($) and thousands separators. Dollar signs
are vertically aligned at the cell’s left edge.
• Currency format – displays numbers with leading
dollar signs and thousands separators
• 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
Rotating Text in Cells
Use the Orientation
options in the
Alignment tab of the
Format Cells dialog
box to rotate text in
cells (you can rotate
text up to 90 degrees
in each direction)
Merging and Splitting Cells
To merge cells:
• 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 upperleft-most data in the selected range.
To split merged cells:
• Click the Merge And Center button
Indenting Text in Cells
To indent text in cells:
• Use the Increase Indent and Decrease Indent
buttons in the Formatting toolbar
Changing Fonts and Text
Attributes
• Font – the typeface and type size of data
• Attributes – the characteristics of text formatting
that you can apply to data
To change fonts and text attributes:
– specify attributes in the Format Cells dialog box
– click formatting buttons in the Formatting
toolbar
– use the Format Painter to copy existing
formatting to other cells
Adding Borders
To add (and modify) borders:
• Use the Borders toolbar to specify style and color,
then select the cell(s) with the border drawing tool
to draw the border
• Use the Borders button in the Formatting toolbar
to specify border options
• Use the Border tab of the Format Cells dialog box
to specify border options
Adding Background Shading
To add background shading:
• Use the Fill Color button in the Formatting toolbar
to display and add color fills
• Use the Patterns tab of the Format Cells dialog box
to specify background colors and patterns
Using Styles
• Style – a set of formatting characteristics
identified by a style name
• Use styles to format cells quickly and consistently
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
Using Styles
To apply a style, select
the cell or range to which
you want to apply the
style, then select a style
from the Style dialog box
Note: The style will
override any formats you
previously applied to the
cell(s)
(cont’d)
Finding and Replacing
Cell Formats
You specify cell format search and replacement
criteria using the advanced search options in the
Find And Replace dialog box
Finding and Replacing
Cell Formats (cont’d)
Once the find and replace operation is completed,
remove the cell format search and replacement
criteria prior to closing the Find And Replace dialog
box, or else the criteria will remain in effect the next
time you attempt to find and replace data
Adding Worksheet
Backgrounds
• Worksheet background graphics are repeated to
fill the entire worksheet
• To add worksheet backgrounds:
– display the Sheet Background dialog box
– insert the graphic you want to use as the
worksheet background pattern
• Apply a solid background shading to the cells
containing data to make the data easier to read
Applying AutoFormats
• AutoFormat – a feature that applies pre-existing
formatting attributes to worksheet cells
To apply AutoFormats:
– select the range of cells you want to format,
display the AutoFormat dialog box, then select
an AutoFormat
Creating Workbooks from
Templates
• Template – a workbook containing formatting
attributes, text, and other worksheet elements
specific to a particular theme that can be used as a
base for new workbooks
• Access templates from:
– the Spreadsheet Solutions tab of the Templates
dialog box
– templates stored on your computer
– templates stored in My Network Places
– the Microsoft Office Web site
Lesson 4:
Manipulating and Printing
Worksheets and Workbooks
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
•
•
•
•
Split and freeze worksheets
Manipulate multiple worksheets
Create and modify hyperlinks
Manipulate multiple workbooks
Save and organize workbooks
Preview and print worksheets
Control the appearance of print pages
Print multiple worksheets
Print entire workbooks
Splitting and Freezing
Worksheets
• Split panes – enables you to split a worksheet into
two panes so that you can view two portions of the
same worksheet simultaneously
• 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 Rows and Columns
To freeze rows:
• Select the row just below the row(s) to freeze, and
activate the freeze panes feature
To freeze columns:
• Select the column just to the right of the column(s)
to freeze, and activate the freeze panes feature
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, and activate the
freeze panes feature
Navigating and Repositioning
Worksheets
• 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
• Drag a worksheet tab to a new location to
reposition the worksheet
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
Renaming Worksheet Tabs
• You can rename worksheet tabs with descriptive
names to better identify the worksheets
– Names can contain up to 31 characters
– Names cannot contain: asterisk ( * ), slash ( / ),
backslash ( \ ), colon ( : ), square brackets ( [ ] )
or question mark ( ? )
Coloring Worksheet Tabs
Use the Format Tab
Color dialog box to
color worksheet tabs
Hiding and Unhiding
Worksheets
• Select Format |
Sheet | Hide to hide
worksheets
• Use the Unhide
dialog box to
redisplay hidden
worksheets
Creating and Modifying
Hyperlinks
• Hyperlink – a link that, when clicked, automatically
accesses or opens another area or location
• Hyperlinks link worksheet data or objects to:
– another area of the current worksheet
– another worksheet
– another workbook
– a file in another application
– a Web page
– an e-mail address
Arranging Multiple Workbooks
Use the Arrange
Windows dialog box to
specify how multiple
open workbooks will
display
Navigating Among Open
Workbooks
To navigate among 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 to cycle from one workbook to
the next, causing each one to activate in turn
Hiding and Unhiding
Workbooks
• Select Window | Hide
to hide workbooks
• Use the Unhide dialog
box to redisplay
hidden workbooks
Saving Workbooks
To save a workbook:
• Click the Save button in the Standard toolbar
• Use the Save As dialog box
With the Save As dialog box, you can:
– save a workbook with a different file name
– save a workbook with a different file format
– save a workbook in a new location
– save a worksheet or workbook as a Web page
Organizing Workbooks
• You can save a workbook in a different location by
creating a folder during the save process
• Use the New Folder dialog box to create a new
folder:
Previewing Print Pages
With Print Preview, you can:
• Display the next or previous pages of a multiplepage worksheet
• Toggle between full-page and magnified views
• Specify page setup options
• Specify margin settings and column widths
• Display the worksheet in Page Break Preview to
view and modify page breaks
Previewing Web Pages
With Web Page Preview, you can:
• Preview how a worksheet will display in your
browser without first saving the file as a Web page
and manually opening it in your browser
Previewing Page Breaks
With Page Break Preview, you can:
• Modify page breaks by dragging the blue dashed
page break lines to different locations
• Insert or remove horizontal and vertical page
breaks
Printing Worksheets
To print a worksheet:
• Click the Print button in the Standard toolbar (prints
the entire worksheet)
• Access the Print dialog box to specify print options
Print dialog box options include:
– Printer – specifies the 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
Adjusting Page Scaling
and Orientation
• Scaling – adjusts print size based on a percentage
of a worksheet’s normal size
• Orientation – the position of the worksheet relative
to the height and width of the printed page
• Orientation can be:
– Portrait
– Landscape
• Use the Page tab of the Page Setup dialog box to
adjust page scaling and orientation
Changing Margin Widths
• Margin – the white space between the edge of the
page and the printed data on the page
To change margins:
– drag margin markers in the Print Preview
window, or
– Use the Margins tab of the Page Setup dialog
box to:
• specify top, bottom, left and right margins
• specify header and footer positions
• center data horizontally and/or vertically on
the page
Creating Headers and Footers
Header – text that prints at the top of every page
Footer – text that prints at the bottom of every page
To create headers and footers:
• use the Header/Footer tab of the Page Setup dialog
box to:
– select built-in headers
– select built-in footers
– create custom headers
– create custom footers
Inserting Page Breaks
• Use Page Break Preview to modify page breaks, or
• Use the Insert | Page Break command in Page
Break Preview or Normal view
Setting Print Areas
• Print area – a portion of a worksheet designated as
the only area to print
• When a print area is set, you can also specify a
separate print range to print
• The print area remains in effect until you change
or clear it
Printing Repeating
Rows and Columns
• Labels – descriptive text entries that display above
columns of data or to the left of rows of data
• Use the Sheet tab of the Page Setup dialog box to print
repeating rows and columns so that row and column
labels will appear on each printed page
You can also use the Sheet tab to:
– print gridlines
– print row and column headings
– print color data in black and white
– print using Draft quality
Printing Multiple Worksheets
and Printing Entire Workbooks
• Print portions of multiple worksheets by defining a
3-dimensional print range
• Print multiple worksheets in their entirety by
grouping the worksheets without specifying a print
range
• Print workbooks in their entirety by:
– grouping all worksheets and printing them
together, or
– specifying to print the entire workbook in the
Print dialog box
Lesson 5:
Using Comments, Graphics,
Diagrams and Charts
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
Work with comments
Work with graphics
Work with diagrams
Work with charts
Inserting Comments
• Comments provide additional information about
individual cells
• Comment indicators (small red triangles) display
in cells containing comments
• To insert a comment:
– right-click a cell and click Insert Comment
– type text in the comment box
– click outside the comment box to close it
Displaying and Hiding
Comments
• To display a comment:
– point to a cell containing a comment indicator
– right-click the comment and click Show/Hide Comments
– select the Comment & Indicator option in the View tab of
the Options dialog box
• 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 tab 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 change and click outside the comment
box
• To delete a comment:
– right-click the comment and click Delete Comment
Inserting Graphics
You can insert:
• Clip Art images (use the Clip Art task pane)
• Pictures from a scanner or digital camera
• Graphics files stored in one of the standard
graphics formats
Moving and Resizing Graphics
To move a graphic:
• drag it to the desired location
To resize a graphic:
• drag a corner sizing handle (resizes the graphic
proportionally)
• drag a side sizing handle (resizes the graphic
without maintaining proportions)
Inserting Diagrams
Organization
chart
Radial
diagram
Cycle
diagram
Venn
diagram
Pyramid
diagram
Target
diagram
Manipulating Diagrams
To move a diagram:
• drag a diagram border to move the diagram to the
desired location
To resize a diagram:
• drag a corner sizing handle (resizes the diagram
proportionally)
• drag a side sizing handle (resizes the diagram
without maintaining proportions)
To modify a diagram:
• use the tools in the Diagram toolbar
Creating Charts
• Chart – a graphical representation of selected data
in a worksheet
• Chart Wizard – a series of dialog boxes that
prompt you for information necessary to create 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
Creating Charts
Y-axis labels
Tick mark
Title
Data series
X-axis labels
Gridlines
(cont’d)
Legend
Changing Chart Types
To change a chart type:
• select Chart | Chart Type
• select the desired chart type
Chart Types
•
•
•
•
•
Column
Bar
Line
Pie
XY (Scatter)
•
•
•
•
•
Area
Doughnut
Radar
Surface
Bubble
•
•
•
•
Stock
Cylinder
Cone
Pyramid
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