Exploring Microsoft Excel 2000 2003

1
Exploring Microsoft Excel 2000 2003

• Part I
• Introduction.
• 
  Natalia Mosina-2005

2
BOOKS
Robert Grauer and Maryann Barber
3
Introduction to Spreadsheets

• The Spreadsheet is the microcomputer application
  that is most widely used by managers and
  executives.
• There is a big diversity of business and other
  uses to which the spreadsheet model can be
  applied.

4
Introduction to Spreadsheets

• Spreadsheet a computerized equivalent of an
  accountants ledger.
• Divided into rows and columns
• Columns identified with alphabetic headings
• Rows identified with numeric headings
• Cell the intersection of a row and a column
• Cell reference uniquely identifies a cell
• Consists of column letter and row number (e.g. B3)

5
Rows, Columns, and Cells
Formula Bar to display formula Cell referenced
by column, then number
Name Box
Active cell surrounded by heavy border
Column headings above each column. Columns
designated with letters
Row headings to the left of each row. Rows
designated with numbers
6
Introduction to Microsoft Excel

• Excel has Common user interface with other Office
  applications
• Menus and toolbars are similar to Word and Power
  Point
• Workbook contains one or more worksheets
• Worksheet an Excel spreadsheet
• Advantage if you change some entries, the
  worksheet will recompute all the formulas
  automatically.
• Note Spreadsheet is a generic term Workbook and
  Worksheet are unique to Excel.

7
Toolbars

• Appear beneath the menu bar
• Contain buttons that perform commonly-used
  commands
• Standard toolbar buttons correspond to most
  basic commands in Excel
• Examples include opening, closing, and saving a
  workbook
• Formatting toolbar buttons correspond to common
  formatting operations
• Examples include boldface and cell alignment

8
An Excel Workbook
Menu bar gives lists of commands
Formatting toolbar
Title bar shows name of workbook
Standard toolbar
Worksheet tabs
9
The File Menu

• Contains most common commands related to Excel
  files
• Examples
• New command creates a new workbook
• Open command opens an existing workbook
• Save command saves a workbook
• Save As command saves a copy of an existing
  workbook under a different name or file type.
• Print command prints all or part of a worksheet

10
Opening a Workbook
Use the Look In list box to specify the folder
containing the file you want to open
Double-click the file you want to open
11
The Save As Command
Use the Save In list box to specify the
folder/disk the file will be saved in
Type the new file name
12
The Active Cell, Formula Bar, and Worksheet Tabs
Formula bar displays contents of active cell
Active cell is highlighted
Click tabs to move to a different worksheet
13
Using the Help System
Click the Help menu
Type a question and click Search
Select one of the search results and it will
appear in the Help pane
14
Types of Cell Entries

• To create a spreadsheet, one goes from cell to
  cell and enters either a constant or a formula.
• Constant an entry that does not change
• Can be a numeric value or descriptive text
• Function a predefined computational task.
• Excel contains a wide variety of functions that
  help you to create very powerful spreadsheets.
• Formula a combination of numeric constants,
  cell references, arithmetic operators, and
  functions
• Always begins with an equal sign.

15
Formulas

• Excel uses symbols
• for addition
• - for subtraction
• for multiplication
• / for division
• for exponentiation
• It follows normal rules for arithmetic
  precedence.

16
Formulas

• Here are some examples of formulas
• 5A5 C42C1
• C9 C5
• (B3B4B5)/3
• Last formula is equivalent to predefined
  function Average(B3B5)
• Not a formula B2B4
• You have to be able to translate arithmetic
  expressions to the Excel formula

17
Exercises

• You need to do exercises to master the material.
• Remember there are many different ways to
  accomplish the same task. We will start with
  basics and learn shortcuts later.

18
Exercise 1

• Title of Exercise Introduction to Microsoft
  Excel
• Objective to start Microsoft Excel to create a
  simple workbook.
• Input file New Excel File
• Output file Grade Book

19
Instructions

• Start Microsoft Excel
• Enter data (provided below) into your worksheet.
  Note how you can move from one cell to another
  (Use Tab key or arrows).
• Save you workbook as Grade Book in your My
  Exercises folder on your floppy disk.

20
(No Transcript)
21
Exercise 1 (continue)

• Calculate entries in Average column by entering
  corresponding formulas into the cells. E.g.
  (B3C32D3)/4
• (it counts final twice as much as the other
  tests)
• Calculate entries in the Class Average row using
  build-in function Average
• Save your workbook!

22
Enter Formulas exactly as they appear on the
screen. Notice the cell references. Colors help
you to see the correspondence.
23
Modifying the Worksheet.

• If you need to add extra columns and rows to your
  worksheet, you have to use the Insert Command to
  add individual cells, rows, and columns.
• You can delete rows and columns by using Delete
  command.
• Important Execution of either command
  automatically adjusts the cell references in
  existing formulas to reflect the insertion or
  deletion of the various cells.

24
Modifying the WorksheetThe Insert Command
Can be used to add rows, columns, or cells
25
Modifying the WorksheetThe Delete Command
If deleting a cell, specify whether to move other
cells up or to the left
Specify whether youre deleting cell, row, or
column
26
Page Setup
Margins tab is used to set top, bottom, left and
right margins
Page tab controls print orientation and scaling
The Page Setup command gives you complete control
of the printed worksheet.
27
Page Setup (continued)
Sheet tab is used to print gridlines
Header/Footer tab allows user to create headers
and footers for each printed sheet
28
Display the Cell Formulas
29
The Print Preview Command
View and adjust margins by clicking the Margins
button
30
Exercise 2

• Title of Exercise Modifying a Worksheet
• Objective to open an existing workbook to
  insert and delete rows and columns to display
  cell formulas and values.
• Input File Grade Book
• Output File Grade Book

31
Instructions

• Open your Grade Book file.
• Explore several ways to Delete row 6
• Several ways to delete a row
• Click any cell in row 6. Right-click and click
  Delete to display a dialog box. Click Entire Row.
  Click OK to delete row 6. (Undo command will undo
  your deletion)
• Click any cell in row 6.
• Go Edit menu-Delete. Same dialog box will be
  displayed. Click Entire Row. Click OK to delete
  row 6
• You can select the whole row by clicking on the
  row number at the left. Then do right-click and
  Delete or Edit-Delete
• Note If you press Del button, you will just
  clear the content of a cell or row instead of
  deleting it. It corresponds to Edit-Clear
  command. It does not adjust cell references
  throughout the worksheet.

32
Instructions (continue)

• Now youve deleted row with Moldof.
• Point to the row number 5 (select the whole row).
  Right-click and click Insert to add a row above
  the selected row.
• Enter data for a new student
• Coulter 85 95 100 (B5C52D5)/4
• Press Enter.
• Click row 6 and insert another student with the
  info
• Courier 75 75 85 (B6C62D6)/4
• Press Enter.

33
This is what you are supposed to have in your
worksheet now
34
Continue Exercise 2 (insert column)

• Point to the column heading for column B, then
  click the right mouse button to display a
  shortcut menu.
• Click Insert to insert a new column which becomes
  the new column B.
• Click in cell B1. Type Major.
• Enter majors cis, acc, fin, math, cis, fin.

35
(No Transcript)
36
Continue

• You can insert and delete a Worksheet.
• Insert - Worksheet.
• Worksheet is inserted as Sheet1
• You can copy and move sheets.
• Click and drag to move
• Click and hold Ctrl key while dragging to copy.
• Or just right-click on the tab and use context
  sensitive menu.

37
Complete Exercise 2

• Name your worksheet as class1 (rename the sheet
  tab)
• Save your worksheet.
• You will submit it as part of the Assignment 2.

38
Using Cell Ranges

• Every command in Excel operates on cell ranges.
• Range a rectangular group of cells
• May be a single cell or the entire worksheet
• May consist of a row (or part of a row), a column
  (or part of a column) or multiple rows and/or
  columns
• To select a range
• Click left mouse button at the beginning of the
  range
• Hold left mouse button as you drag the mouse
• Release left mouse button at the end of the range

39
Copying and Moving Cells

• Copy command duplicates the contents of a cell
  or range of cells
• Source range the cell(s) you are copying from
• Destination range the cell(s) you are copying
  to
• You can copy to more than one destination ranges
• Move operation transfers the contents of a cell
  or range to another cell or range
• You must use both the Copy (or Cut) command and
  the Paste command

40
Cell Referencing (important)

• Absolute reference remains constant when copied
• Specified with dollar signs before the column and
  row B4
• Relative reference adjusts during a copy
  operation
• Specified without dollar signs, i.e. B4
• Mixed reference either the row or the column is
  absolute the other is relative
• Specified with a dollar sign before the absolute
  part of the reference, i.e. B4

41
Absolute and Relative References
Absolute references are used to refer to the
weight of each exam. These weights do not change
for each student, so absolute references are
needed to keep those references constant as the
formula is copied
Relative references are used to refer to each
students exam scores. These scores do change
for each student, so relative references are
needed to make sure each students average
reflects his/her scores
42
We will compute the Student Semester Averages
with weights using absolute and relative
addressing
Absolute and relative references used in formulas
Create the formula in cell E4 and copy to other
cells
43
It is important to isolate the Assumptions (cells
whose values are subject to change)
New student averages are automatically
recalculated
Enter new exam weights in row 13
44
Exercise 3

• Title of Exercise Creating another Workbook.
• Objective to create a new workbook to copy
  formulas containing relative and absolute
  references
• Input file Better Grade Book
• Output file Better Grade Book
• Instructions Handout

45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
Formatting Cells

• Format Cells command controls the formatting
  for numbers, alignment, fonts, borders, and
  patterns (color)
• Select-then-do
• Select the cells to which the formatting will
  apply
• Execute the Format Cells command

49
The Format Cells Command
Number tab allows you to specify appearance of
numbers
Alignment tab specifies vertical and horizontal
alignment
Font tab allows you to specify font type and size
Borders and Patterns tabs allow you to create
special effects
50
The Completed Worksheet
Shading is used to identify labels and
assumptions, and to show class averages.
51
Exercise 4

• Title of Exercise Formatting a Worksheet
• Objective to format a worksheet using boldface,
  italics, shading, and borders to change the font
  and/or alignment of a selected entry to insert a
  comment.
• Input file Better Grade Book
• Output file Better Grade Book
• Instructions Handout

52
The insert comment command You can add a
comment, which Displays a ScreenTip, to any cell
in a worksheet. Click in the cell, Pull down the
Insert menu, and click Comment to display a box
in Which you enter the comment. Tiny red
triangle will appear. When You point to the
cell, you will see the comment displayed.
53
Move operation.

• We didnt use Move operation in our Grade Book,
  but its understanding is essential.
• We will look at simple examples.

54
Here we have 2 simple number entries and a
simple Formula to perform addition. We will try
to move things Around to see what will happen.
55
We moved content (i.e. formula) of a cell A3 to
C3. Formula for adding A1 and A2 is unchanged as
we see.
56
Here, the formula itself remains in the same
cell, but one of the values it references Is
moved to a new location, i.e. the entry in A1 is
moved to C1. Notice the formula in cell A3 is
adjusted to follow the moved entry to its new
location.
57
Here, we moved all 3 cells. After the move, cells
C1 and C2 contain the 5 and 2 with the formula
in cell C3 adjusted to reflect the movement of
the contents of cells A1 and A2. Once again, the
source range (A1A3) is empty after the move is
completed.
58
Note we have an additional formula in cell B1,
which is dependent on cell A3. What will happen
if we move cell A3???
59
Cell reference adjusts to follow Moved entry
Moved formula is unchanged
The formula in C3 is unchanged after the move
because only the formula was moved, not the
values it referenced. The formula in cell B1
changes because cell B1 refers to an entry (cell
A3) that was moved to a new location (cell C3)
60
Lets see that specification of absolute
references has no meaning in a move operation.
61
Cell addresses are adjusted as necessary to
reflect the cells that have been moved. Moving
just a formula that contains an absolute
reference does not adjust the formula. Moving a
value that is specified as an absolute reference,
however, adjusts the Formula to follow the cell
to its new location. Thus, here, all of the
absolute references are changed to reflect the
entries that Were moved.