Transcript XP Microsoft Office Excel 2003

XP
Microsoft Office Excel 2003
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
1
Identify major components
of the Excel window
XP
• Excel is a computerized spreadsheet, which is an important
business tool that helps you report and analyze information.
• Excel stores spreadsheets in documents called workbooks.
• Each workbook is made up of individual worksheets, or
sheets.
• Because all sorts of calculations can be made in the Excel
spreadsheet, it is much more flexible than a paper
spreadsheet.
• The Excel window has some basic components, such as an
Active cell, Column headings, a Formula bar, a Name box,
the mouse pointer, Row headings, Sheet tabs, a Task Pane,
Tab scrolling buttons and Toolbars.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
2
XP
A sample Excel worksheet
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
3
XP
Identify Excel components
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
4
XP
Descriptions of Excel components
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
5
XP
Navigation keystrokes
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
6
XP
The Active Cell
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
7
XP
Entering Data into a Worksheet
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
8
The Alignment tab of the
Format Cells dialog box
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
XP
9
XP
Examples of text formatting
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
10
XP
Examples of text formatting
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
11
XP
Worksheet with hidden cells
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
12
XP
A worksheet with a background image
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
13
XP
Entering Formulas
• A formula is a mathematical expression that
calculates a value.
• In Excel, formulas always begin with an equal
sign (=).
• A formula can consist of one or more arithmetic
operators.
• The order of precedence is a set of predefined
rules that Excel follows to calculate a formula.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
14
XP
Arithmetic Operators
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
15
XP
Order of Precedence Rules
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
16
XP
Microsoft Office Excel 2003
Working With Formulas and Functions
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
17
XP
Math and Statistical functions
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
18
XP
An example of Auto Fill
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
19
XP
Use relative references
• A relative reference is a cell reference that shifts
when you copy it to a new location on a
worksheet.
• A relative reference changes in relation to the
change of location.
• If you copy a formula to a cell three rows down
and five columns to the right, a relative reference
to cell B5 in the source cell would become G8 in
the destination cell.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
20
XP
Use absolute references
• An absolute reference is a cell reference that does
not change when you copy the formula to a new
location.
• To create an absolute reference, you preface the
column and row designations with a dollar sign ($).
• For example, the absolute reference for B5 would be
$B$5.
• This cell reference would stay the same no matter
where you copied the formula.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
21
XP
Use mixed references
• A mixed reference combines both relative and absolute cell
references.
• You can effectively lock either the row or the column in a
mixed reference.
– For example, in the case of $B5, the row reference would shift, but
the column reference would not
– In the case of B$5, the column reference would shift, but the row
reference would not
• You can switch between absolute, relative and mixed
references in the formula easily in the edit mode or on the
formula bar by selecting the cell reference in your formula
and then pressing the F4 key repeatedly to toggle through
the reference options.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
22
XP
Date Functions
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
23
XP
Use a formula to enter the date
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
24
XP
Financial Function descriptions
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
25
Use the Insert Function dialog box XP
to
enter function arguments
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
26
XP
Using the If function
• The arguments for the IF function are:
– IF(logical_test,value_if_true,value_if_false)
– For example, the function =IF(A1=10,20,30) tests whether
the value in cell A1 is equal to 10
– If it is, the function returns the value 20, otherwise the
function returns the value 30
– Cell A1 could be empty or contain anything else besides the
value 10 and the logical test would be false; therefore, the
function returns the value 30
• To insert an IF function, click the Insert Function button and
search for the IF function, then click OK.
• When the Function Arguments dialog box appears, simply fill in
the arguments.
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
27
XP
Creating Charts
• A chart, or graph, is a visual representation of a set of
data
• Select the data source with the range of data you want to
chart
• In the Charts group on the Insert tab, click a chart type, and
then click a chart subtype in the Chart gallery
• In the Location group on the Chart Tools Design tab, click
the Move Chart button to place the chart in a chart sheet or
embed it into a worksheet
New Perspectives
onExcel
Microsoft
New Perspectives on Microsoft Office
2007 Office Excel 2003, Second Edition- Tutorial 1
28
28
XP
Selecting a Chart Type
• Click the Insert tab on the Ribbon
• In the Charts group, click the Pie button
New Perspectives
onExcel
Microsoft
New Perspectives on Microsoft Office
2007 Office Excel 2003, Second Edition- Tutorial 1
29
29
XP
Creating a Combination Chart
• Select a data series in an existing chart that you
want to appear as another chart type
• In the Type group on the Chart Tools Design tab,
click the Change Chart Type button, and then click
the chart type you want
• Click the OK button
New Perspectives
onExcel
Microsoft
New Perspectives on Microsoft Office
2007 Office Excel 2003, Second Edition- Tutorial 1
30
30
XP
Creating a Combination Chart
New Perspectives
onExcel
Microsoft
New Perspectives on Microsoft Office
2007 Office Excel 2003, Second Edition- Tutorial 1
31
31
XP
Create a lookup table and use
Excel's lookup functions
• You can create a Lookup Table that will summarize data
but will allow you to perform lookups that will go to
particular workbook references to retrieve data.
– A lookup table organizes values that you want to retrieve into
different categories
– These categories are called compare values
– If you want to locate a particular value, you must supply a lookup
value that is matched against the compare value
– The lookup value and compare value are tested against each other
and the matching value is then returned from the workbook cell
reference
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
32
XP
Planning the Lookup
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
33
XP
The Function Arguments dialog box
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
34
XP
A formatted lookup section
New Perspectives
on Microsoft Office Excel 2003, Second Edition- Tutorial 1
35