CS 0131 Software for Personal Computing

1
CS 0131Software for Personal Computing

• Week 6
• Excel 1 Creating and Editing Spreadsheets

2
Excel 1 Lab Objectives (1)

• Introducing Excel.
• Creating New Worksheets.
• Enter, edit, and clear cell entries.
• Adjust column widths.
• Save, close, and open workbooks.
• Spell-check a worksheet.
• Use the thesaurus.
• Copy and move cell entries.

3
Excel 1 Lab Objectives (2)

• Specify ranges.
• Enter formulas and functions.
• Insert rows.
• Change cell alignment.
• Format cells.
• Enter and format a date.
• Preview and print a worksheet.

4
Excel 1 Lab Concepts

• Entries
• Text and Numeric
• AutoCorrect
• Column Width
• Spelling Checker
• Thesaurus
• Copy and Move
• Range

• Formula
• Relative Reference
• Function
• Recalculation
• Alignment
• Fonts
• Number Formats

5
Excel 2003 Main Window
6
Excel Interface Main Window
7
Excel Interface Workbook
8
Spreadsheets Worksheets

• Sheet displays financial data or charts
• Spreadsheet
• Rectangular grid of rows and columns
• Also called a worksheet
• 256 columns
• 65,536 rows
• Can move within it with the keyboard, mouse and
  also voice commands.

9
Workbook Terms
10
Using the Cell Selector
11
Concept 1 Text and Numeric Entries

• Data you enter in a cell
• Text
• Letters, numbers, spaces, special characters
• Numbers
• Only the digits 0 to 9
• Characters - () ,. / ?
• Formulas
• Number entries can be used in calculations
• See page EX1.15

12
Creating and Modifying Entries

• Move to the cell where you want data
• Enter information

• Clearing an entry
• Use DELETE key
• Use the x in
• the formula bar
• Editing an entry
• Ready mode
• Edit mode
• Use to change only a part of an entry
• Double-click on the cell to change

13
Entering Long Data
14
Concept 2 AutoCorrect

• A feature that makes some basic assumptions about
  the text you are typing
• Automatically corrects the entry
• Automatically corrects many common typing and
  spelling errors
• Makes corrections by
• Checking against a built-in list
• Looking for certain types of errors
• Very similar to the feature we had in Word.
• See page EX1.20

15
Concept 3 Column Width

• Size of column controls the amount of information
  displayed in a cell
• When numbers are entered in a cell, width is
  automatically increased
• When text larger than the column width is entered
  in a smaller cell, data many be interrupted
• Worksheet is printed as it appears on the screen
• See page EX1.24

16
Changing a Columns Width

• Dragging the column boundary
• Dragging to the left decreases the width
• Dragging to the right increases the width
• Using AutoFit
• Double-click the boundary to the right of the
  column heading
• Column width is resized to just slightly larger
  than the longest cell contents

17
Language Tools (as in Word)

• Spelling Checker
• Locates spelling and typing errors
• Suggests the correct spelling
• Thesaurus
• Suggests better words to clarify meaning
• The dialogs are essentially the same as they are
  in Word, providing much of the same capabilities
  and features.

18
Concept 4 Spelling Checker

• Locates
• Misspelled words
• Duplicate words
• Capitalization irregularities
• Proposes the correct spelling
• Compares words to a dictionary
• Main dictionary
• Custom dictionary
• See page EX1.33

19
Spelling Options
20
Concept 5 Thesaurus

• The Excel thesaurus provides
• Synonyms
• Antonyms
• Related words for a selected word or phrase
• Helps to liven up documents by adding interest
  and variety to the text
• See page EX1.35

21
Thesaurus Use
22
Concept 6 Copy Move

• Contents of worksheet cells can be duplicated
  (copied) or moved to other locations
• Entry that is copied leaves the original
• Called the source or copy area
• Entry at the new location
• Called the destination or paste area
• Entry that is moved is cut from original area
• Can use either System or Office Clipboard
• See page EX1.38

23
Copy and Paste
24
Concept 7 Range

• A selection of two or more cells on a worksheet
• Cells can be
• Adjacent A rectangular block of adjoining cells
• Nonadjacent A range consisting of two or more
  selected cells or ranges that are not adjoining
• Fill Handle usually is applied to a range of
  cells.
• See page EX1.41

25
Using the Fill Handle

• Fill handle is a black box in the lower-right
  corner of the selection
• Used to copy a selection quickly
• Can also copy formulas and modify them to match
  the desired result in many cases.

26
Concept 8 Formula

• An equation that performs a calculation in a
  worksheet
• Result is a variable value
• Can change if the data it depends on changes
• Formulas always begin with an equal sign ()
• Formulas use arithmetic operators
• See page EX1.47

27
Formulas Continued

• An operator is a symbol that specifies the type
  of numeric operation to perform
• Excel uses the order of precedence
• Percent, exponentiation, multiplication,
  division, addition, subtraction
• Can be overridden by using parentheses

28
Entering Formulas
29
Concept 9 Relative Reference

• A cell or range reference in a formula whose
  location is interpreted in relation to the
  position of the cell that contains the formula
• When a formula is copied, referenced cells
  automatically adjust to reflect the new location
• See page EX1.49

30
Concept 10 Function

• Function is a prewritten formula
• Performs certain types of calculations
  automatically
• Function name identifies the type of calculation
  to be performed
• Argument is the data the function uses to perform
  the calculation
• See page EX1.50

31
Using Functions SUM
32
Entering Formulas by Pointing
33
Concept 11 Recalculation

• When a number in a referenced cell in a formula
  changes, Excel automatically recalculates all
  formulas dependent upon changed value
• See page EX1.57

34
Formatting the Worksheet

• Format controls how entries are displayed in a
  cell
• Position of data in a cell
• Text font
• Color
• Patterns
• Borders
• Number formats
• Improves the appearance of data

35
Concept 12 Alignment

• Horizontal
• Affects text and number entries
• Left, right, center
• Vertical placement of cell contents
• Top
• Bottom
• Center
• Orientation of text changes angle
• See page EX1.59

36
The Alignment Tab
37
Indenting Entries
38
Centering Across a Selection
39
Concept 13 Fonts

• A font is a set of characters with a specific
  design
• Types
• Serif
• Sans serif
• See page EX1.64

40
Concept 14 Number Formats

• Number formats change the appearance of numbers
• Does not change the way the number is stored or
  used in calculations
• When a number is formatted
• Formatting appears in the cell
• Value without formatting appears in the formula
  bar
• See page EX1.68

41
Entering the Date

• When a date is entered as text, Excel converts
  the entry to numeric
• Can also use Excel's date and time functions

42
Previewing and Printing