Microsoft Excel Lesson 6

1
Microsoft Excel Lesson 6

• Lexington Technology Center
• March 21, 2002
• Bob Herring
• On the web at http//www.lexington1.net/adulted/co
  mputer/microsoft_excel.htm

2
Excel Lesson 6
Review of Thursdays Lesson

• Linking worksheets
• Conditional formatting
• Sorting
• Filtering
• Pivot tables
• Freezing panes

2
3
Excel Lesson 6
Comments

• When additional information is necessary in a
  worksheet, a
• comment can be inserted
• Select Insert, then choose Comment

3
4
Excel Lesson 6
Deleting Comments

• If you only want to delete comments, select
  Edit, then Clear,
• and choose Comments

4
5
Excel Lesson 6
Page Break Preview

• When you are concerned about which columns and
  rows get
• printed on which pages, use Page Break
  Preview
• Select Edit, then Page Break Preview

5
6
Excel Lesson 6
Page Break Preview

• This is the Preview. It shows how Excel would
  break the pages
• To change it, click on the break lines with the
  mouse and drag
• them to the desired location

6
7
Excel Lesson 6
Worksheet Protection

• If you need to ensure that data in your
  worksheet cannot be
• changed without permission, you can protect it
• Select Tools, then Protection, and choose
  Protect Sheet

7
8
Excel Lesson 6
Worksheet Protection

• The Protect Sheet dialog appears
• The default is to protect all contents, but
  different levels can be selected
• A password may be added for further protection

8
9
Excel Lesson 6
Data Validation

• To help users know what to enter in the
  worksheet (and to prevent
• errors), you can attach data validation
  rules to cells in the worksheet
• Select Data, then Validation

9
10
Excel Lesson 6
Data Validation

• The Data Validation dialog appears
• Set up the validation rule under the Settings
  tab
• Click on the down arrow in the box that says
  Any value and pick
• Whole number

10
11
Excel Lesson 6
Data Validation, Continued

• Create an message for the user under the Input
  Message tab
• Type a title and a message that tells the user
  what to do

11
12
Excel Lesson 6
Data Validation, Continued

• Create another message to warn of errors under
  the Error Alert tab
• Pick a graphic from the combo box on the left,
  then type a title and
• an error message

12
13
Excel Lesson 6
Data Validation, Continued

• Data validation in action
• The input message appears, and when we try to
  enter an incorrect
• value, Excel tells us the acceptable range

13
14
Excel Lesson 6
Tables

• Tables are a quick way to calculate a range of
  inputs to a formula
• For example, if you were shopping for a
  mortgage, you might want
• to calculate what the payment will be for
  various interest rates, what
• the total interest cost will be, and what
  payments would be for
• different repayment periods
• On a blank worksheet, enter the following

Start in cell C2
D5
D6
D7
14
15
Excel Lesson 6
Tables

• In the cell beneath Payments, enter the
  following formula

D5
D6
D7
PMT(D5/12,D6,-D7)
15
16
Excel Lesson 6
Tables

• Highlight the cells outlined in red

Highlight here
16
17
Excel Lesson 6
Tables

• Select Data, then Table
• In the Table dialog box, enter D5 in the
  Column input cell box

D5
Data is in a column
17
18
Excel Lesson 6
Tables
Table is filled in
18
19
Excel Lesson 6
Tables

• Tables can be calculated using two variables
• In this example, the table will calculate
  payments based on both
• the interest rate and the period of the
  loan
• In the marked cell, enter this formula
  ABS(PMT(D5/12,D6,D7))

19
20
Excel Lesson 6
Tables

• Highlight the cells from the original formula
  through the full
• range of the table. (In the example, B11
  through F16)
• Type D6 in the Row input cell box, as
  shown
• The row input cell refers to the payment
  periods (360 months)
• Type D5 in the column input cell box, as
  shown
• The column input cell refers to the interest
  rates (10)

20
21
Excel Lesson 6
Templates

• A template is just a worksheet that has a
  format that you would
• like to use again
• To create templates, select File, then Save As
• In the File name box, type an appropriate
  name
• In the Save as type box, choose Template
  (.xlt)
• Be sure to clear the data before saving the
  final template

21
22
Excel Lesson 6
Text to Columns

• Excel has a way to break text data into columns
  on your worksheet
• Open Word, and open the file Text to
  Columns.doc
• Highlight the data and click Copy
• Change to Excel, click in cell A2, and then
  click Paste

22
23
Excel Lesson 6
Text to Columns

• Select Data, then Text to Columns

23
24
Excel Lesson 6
Text to Columns

• The Convert Text to Columns Wizard appears
• Since our data is not in fixed width columns,
  choose the Delimited
• radio button (the default)
• Click the Next button

24
25
Excel Lesson 6
Text to Columns

• In Step 2 of the Wizard, pick the separators
  (delimiters) in your data
• In this example, the data is separated by
  commas
• Click the Comma check box and Excel shows you
  this result
• Click on the Next button

25
26
Excel Lesson 6
Text to Columns

• In Step 3 of the Wizard, you choose the data
  format for the columns
• In the example, General is appropriate for the
  data we have
• Click the Finish button

26
27
Excel Lesson 6
Text to Columns

• This is the final result
• Double click in between columns C and D and D
  and E to expand
• columns C and D to show all the data
• Now highlight column E and separate the state
  and the zip code

27
28
Excel Lesson 6
Course Review

• Lesson 2
• Opening a workbook
• Basic formulas using operators (, -, , /, )
• Using the Fill Handle
• Copy and Paste formulas
• Change Font sizes, weights, appearance
• Using AutoFormat
• Center across columns
• Using the Name Box

• Lesson 1
• Starting and Quitting Excel
• Worksheets
• Menus and Toolbars
• Worksheet Cells
• How to Enter and Edit Text
• and Numbers
• Saving an Excel Workbook

28
29
Excel Lesson 6
Course Review

• Lesson 3
• Copying cells using relative
• and absolute cell addresses
• Functions
• Printing
• Page Setup
• Excel Options
• Correcting Errors
• Clearing Cells or Worksheets

• Lesson 4
• Creating Charts
• Custom Formats
• Design for Looks
• Office Assistant and Help

29
30
Excel Lesson 6
Course Review

• Lesson 5
• Linking worksheets
• Conditional formatting
• Sorting
• Filtering
• Pivot tables
• Freezing panes

• Lesson 6
• Comments
• Page Break Preview
• Worksheet Protection
• Data Validation
• Tables
• Templates
• Text to Columns

30