Microsoft Office Lesson 4

1
Microsoft Office Lesson 4

• Lexington Technology Center
• October 12, 2004
• Bob Herring
• On the web at http//www.lexington1.net/adulted/co
  mputer/office.htm

2
Office Lesson 4
Review of of the Previous Lesson

• Worksheets
• Menus and Toolbars
• Worksheet Cells
• How to Enter and Edit Text and Numbers
• Simple Formulas
• Copying and Pasting Formulas
• Formatting, Inserting, and Deleting Rows and
  Columns
• Worksheet Tabs
• Copying and Renaming Worksheets
• Excels Built-In Calculator
• Centering Across Columns
• Saving an Excel Workbook

2
3
Office Lesson 4
Using the Name Box

• Cells and ranges can be named
• To got to a named cell, type it in the name
  box, or, select the dropdown

Click on dropdown to select defined names
Type a Name and press Enter to go to the Cell
3
4
Office Lesson 4
Using the Name Box

• To name, Click Insert on the Menu Bar and
  select Name, then Define

Type the name here
Insert
Name
Define
4
5
Office Lesson 4
Using Names in Formulas

• Enter the table shown in the picture below. (Use
  the Fill Handle to help)
• Use a formula to calculate the profit ( B2-B3 )
• Name cell B3 Jan, C3 Feb, D3 Mar, etc

Name the cells in this row
Use the names to calculate quarter totals
JanFebMar
5
6
Office Lesson 4
Absolute and Relative Cell Addresses

• Relative Addressing
• Cell addresses in formulas refer to the address
  of the data
• that the formula acts upon
• When formulas are extended, Excel changes the
  addresses
• so that the formula refers to the correct
  address
• Absolute Addressing
• In this case, new formulas continue to refer to
  the original
• data
• The dollar sign ( ) indicates an absolute
  address
• Example 20 A1 locks the formula to cell
  A1

6
7
Office Lesson 4
Absolute and Relative Cell Addresses

• Absolute addressing can be used for sales tax
  tables
• Changing one cell changes the whole table
• To use relative addressing here would mean
  changing all cells

A4C3
A40.07
7
8
Office Lesson 4
Help for Relative and Absolute References

• Select Help, then Contents and Index
• Click on the Find Tab and type
• difference in the entry box
• Select difference in the second
• box
• In the third box, scroll down to
• the topic

8
9
Office Lesson 4
Help for Relative and Absolute References

• Excels internal explanation of the difference

9
10
Office Lesson 4
Page Setup, Page Size and Orientation

• Select File, then Page Setup
• First Tab is for the page size and orientation
• Many spreadsheets are printed as landscape
  (page sideways)
• Use Fit to squeeze data onto the page the way
  you want

10
11
Office Lesson 4
Page Setup, Margins

• Use this Tab to set standard margins -- Or,
• Select Print Preview, then Margins to set them
  by eye

Text can also be centered
11
12
Office Lesson 4
Page Setup, Headers and Footers

• Use this Tab to format page headers and footers

Automatic date
Automatic page numbering
12
13
Office Lesson 4
Page Setup, Sheet Options

• Use this Tab to print row and column headings
  and gridlines

Row and Column Headings
Gridlines Check box
13
14
Office Lesson 4
List Options

• Lists can be used to fill in values (e.g., Days
  and Months)
• You can make your own lists to save time and
  typing
• Type in a list and click Add, or Import to
  get it from a worksheet

Add
Collapse Dialog Button
Import
14
15
Office Lesson 4
Adding a List

• Click in the List entries window
• Type each entry in the list. Press Enter
  after each entry, OR
• Separate entries with commas
• Click Add to add the list to the Custom lists
  window

Add
Type List Here
15
16
Office Lesson 4
Importing a List

• Click Import, and either type the list of
  cells to be imported or
• click the collapse dialog button
• Highlighting cells will copy them into the
  collapsed dialog
• Click the expand dialog button to return to
  List Options

Highlight Cells
Expand Dialog Button
Collapsed Dialog Box
16
17
Office Lesson 4
Importing a List, Continued

• Click Import to add the list to the Custom
  lists window

List Appears in Window
Import
17
18
Office Lesson 4
Correcting Errors

• Generally Speaking
• Beware of GIGO or, a computer program is only
  as good
• as its inputs
• Be cautious of the results -- know what answer
  to expect
• Errors in Data and Formulas
• To fix bad data, click on the cell and retype
  the number
• To fix bad formulas, click the cell containing
  the formula, then
• highlight the error in the formula bar and
  make corrections
• Excel Error Messages
• The program can help spot errors
• Excel error messages begin with

18
19
Office Lesson 4
Recognizing Errors

• Common Error Messages
• Cell isnt wide enough to show the data
• VALUE! Wrong type of data for a function
• DIV/0! Tried to divide by zero
• NAME? Cell name not defined or (usually)
  misspelled
• REF! Cell reference is not valid
• NUM! Function requires a number
• NULL! Called a non-intersecting range of cells

19
20
Office Lesson 4
Clearing an Entire Worksheet

• Select worksheet by clicking the row and column
  intersection
• Select Edit, then Clear
• Choose All, or Formats, or Contents, or Comments

Place Cursor at Row Column Intersection
20
21
Office Lesson 4
Clearing Cell Contents

• Select cells to be cleared by clicking left
  mouse button and dragging
• Select Edit, then Clear
• Choose All, or Formats, or Contents, or Comments

Cells to be Cleared
21
22
Office Lesson 4
Design for Looks

• Here is some (fictitious) data from an authors
  book tour
• Unformatted, it is hard to read and understand

22
23
Office Lesson 4
Design for Looks

• The same data, with some formatting
• Give this a try!

23
24
Office Lesson 4
Cell Formatting -- Number Tab

• General -- No specific format Excel decides
  how it will look
• Number -- Formats numbers positive, negative,
  decimal places

General Format
Number Format
24
25
Office Lesson 4
Cell Formatting -- Number Tab

• Currency -- Adds the dollar sign and sets two
  decimal places
• Accounting -- Aligns dollar signs and decimal
  points

Currency Format
Accounting Format
25
26
Office Lesson 4
Cell Formatting -- Number Tab

• Date -- Gives a choice of date formats
• Time -- Allows user to select time formats

Date Format
Time Format
26
27
Office Lesson 4
Cell Formatting -- Number Tab

• Percentage -- Multiplies number by 100 and adds
  percent sign
• Fraction -- converts decimals to fractions of
  various kinds

Percentage Format
Fraction Format
27
28
Office Lesson 4
Cell Formatting -- Number Tab

• Scientific -- Converts numbers to scientific
  format
• Text -- Treats all inputs, including numbers,
  as text

Note To enter formulas and symbols as text,
precede them with a single quote
Scientific Format
Text Format
28
29
Office Lesson 4
Cell Formatting -- Number Tab

• Special -- A few commonly-used formats
• Custom -- Create your own formats

Custom Format
Special Format
29
30
Office Lesson 4
Cell Formatting -- Alignment Tab

• The Alignment tab sets the location of the text
  within cells
• Text can be vertical as well as horizontal
• Use the Wrap text check box to keep column
  headers narrow

Wrap text
30
31
Office Lesson 4
Cell Formatting -- Font Tab

• The Font tab displays all the font commands in
  one dialog box
• Allows users to make subscripts ( CO2 ) and
  superscripts ( X2 )
• Underlines can be set for accounting (both
  single and double)

31
32
Office Lesson 4
Cell Formatting -- Border Tab

• The Border tab gives many options for
  highlighting cells
• Be careful using with the Gridlines option
  checked

32
33
Office Lesson 4
Cell Formatting -- Patterns Tab

• Shadings and patterns can be added to cells
  using the Patterns tab

33
34
Office Lesson 4
Cell Formatting -- Protection Tab

• Cells can be locked or hidden using the
  Protection tab
• This will not have any effect unless you use
  the Protect Sheet
• option under the Protection section of the
  Tools menu

34
35
Office Lesson 4
Sorting

• Excel can quickly sort your data by column
• Select Data, then Sort to bring up the Sort
  dialog box

35
36
Office Lesson 4
Sorting

• Excel allows the user to sort on up to three
  columns, with the option
• to sort ascending and descending
• The Options button lets you sort by days or
  months (or any list) and
• to sort lower case and upper case and left
  to right instead of top to
• bottom

Sort order for Case sensitive Symbols,
Numbers, Lower, Upper
36
37
Office Lesson 4
Freezing Panes

• Column and row headers can be immobilized on
  the screen
• Click in the cell whose upper left corner will
  be the intersection
• of the frozen area
• Select Window, and then Freeze Panes

37
38
Office Lesson 4
Panes Frozen

• Excel displays vertical and horizontal lines to
  mark the boundary
• of the frozen area

Horizontal Pane Marker
Vertical Pane Marker
38
39
Office Lesson 4
Review

• Using the Name Box
• Absolute and Relative Cell Addresses
• Page Setup
• Custom Lists
• Correcting Errors
• Design for Looks
• Cell Formatting
• Sorting
• Freezing Panes

39