Microsoft Excel Lesson 5

1
Microsoft Excel Lesson 5
Lexington Technology Center March 19, 2002 Bob
Herring On the web at http//www.lexington1.net/a
dulted/computer/microsoft_excel.htm
2
Excel Lesson 5
Review of Thursdays Lesson

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

2
3
Excel Lesson 5
Linking Worksheets, Part 1

• Open Forecast Example.xls on the floppy disk
• Click in cell D3 to view the formula
• C3 Replenishment!C4 - Expenditure!C4

Formula
Cell D4
On-Hand Worksheet
3
4
Excel Lesson 5
Linking Worksheets, Part 2

• To recreate the link
• Select cells C3 through O26, as shown
• Select Edit, Choose Clear, then Contents

4
5
Excel Lesson 5
Linking Worksheets, Part 3

• Select cell D3, then click in the formula
  bar
• Type C3
• Select the Replenishment worksheet, and click
  in cell C4

Formula
Cell C4
5
6
Excel Lesson 5
Linking Worksheets, Part 4

• Type -
• Select the Expenditure worksheet and click in
  cell C4
• Click OK

Formula
Cell C4
6
7
Excel Lesson 5
Linking Worksheets, Part 5

• Use the fill handle to extend the formula
  downward to row 26

Fill Handle
7
8
Excel Lesson 5
Linking Worksheets, Part 6

• Use the fill handle again to extend the formula
  to the whole worksheet
• Supplies on-hand can now be predicted by
  varying expenditure and
• replenishment rates

Fill Handle
8
9
Excel Lesson 5
Conditional Formatting

• Excel can be set to watch for certain values
  in your spreadsheet
• It responds to the values by changing the cells
  to a format you specify

9
10
Excel Lesson 5
Conditional Formatting

• Select Format, then Conditional Formatting
• The Conditional Formatting dialog box appears
• Conditions can apply to cells or formulas

Value
Cell or Formula
Set Format
10
11
Excel Lesson 5
Conditional Formatting

• Formats can apply to
• Font style (e.g., Bold)
• Underlining
• Font color
• Strikethrough

• Borders
• Border style
• Border color

• Fill color
• Fill patterns

11
12
Excel Lesson 5
Conditional Formatting

• Apply conditional formatting to Forecast
  Example.xls
• Highlight cells to be formatted (C3 to O26)
• Set values between 0 and 5 to be filled with
  red, 6 to 10 yellow, and
• 11 and above green

12
13
Excel Lesson 5
Sorting

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

13
14
Excel Lesson 5
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

14
15
Excel Lesson 5
Filtering

• Excel can also sort data by matching values
• Select Data, then choose Filter, and click on
  AutoFilter

15
16
Excel Lesson 5
AutoFilter

• After AutoFilter is selected, all columns
  appear with selector arrows
• Click on the Location down arrow and select
  X

16
17
Excel Lesson 5
AutoFilter, Continued

• Excel suppresses all rows not containing the
  selected criterion
• In the example, only office supplies at
  location X are displayed
• Notice the row numbers are no longer sequential

17
18
Excel Lesson 5
Pivot Tables

• Pivot tables display data summaries or
  cross-tabulations
• Pivots can be changed on the fly to display
  data in different ways

18
19
Excel Lesson 5
Pivot Table Wizard, Step 1

• Step 1 is to select the data source
• Data can come from Excel worksheets or Access
  databases

19
20
Excel Lesson 5
Pivot Table Wizard, Step 2

• Select data on worksheets by clicking the
  Collapse Dialog button
• and then highlighting the data

Collapse Dialog
20
21
Excel Lesson 5
Pivot Table Wizard, Step 3

• Drag the field buttons to the diagram to create
  the pivot table

Field Buttons
Diagram
21
22
Excel Lesson 5
Pivot Table Wizard, Step 3

• In this example, drag the Office Supplies
  field button to ROW
• Drag Location to COLUMN
• Drag Current to DATA

22
23
Excel Lesson 5
Pivot Table Wizard, Step 4

• The last step is to pick a place for the table
  to be displayed
• Select New worksheet and click Finish

23
24
Excel Lesson 5
Pivot Tables

• This is the resulting table
• Current office supplies are summarized by
  type and location
• Now click and drag the Location field button
  beneath the
• Office Supplies field button

24
25
Excel Lesson 5
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

25
26
Excel Lesson 5
Panes Frozen

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

Horizontal Pane Marker
Vertical Pane Marker
26
27
Excel Lesson 5
Panes Frozen, Continued

• Move the vertical and horizontal scroll bars to
  see the effect of
• freezing the panes

27
28
Excel Lesson 5
Review

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

28