Vincennes Community School Corporation

1
Microsoft Excel

• Vincennes Community School Corporation
• Melanie Kocher
• Summer 2004

Version 2000
2
Topics to be Covered

• Terminology
• Short Cut Keys
• Selecting Cells
• Columns and Rows
• Formatting Cells
• Entering Text
• Editing Text

• Freeze Panes
• Formulas
• Grade Book
• Advanced Formula
• Charts
• Mail Merge
• Calendar

3
Terminology

• Spreadsheet
• A row and column arrangement of data used to
  enter, calculate, manipulate, and analyze
  numbers.
• They are used to prepare budgets, financial
  statements, and inventory management.
• Also, they are used for organization and almost
  anything containing numbers and data.
• By default containing 3 sheets but can be
  increased by right clicking on a sheet and insert
  or by going to the Insert menu and clicking on
  worksheet.
• Cell
• The point at which a column and a row
  intersects/meets.
• Column
• Vertical sections of a spreadsheet.
• Identified by letter/s.
• 256 columns in a spreadsheet.
• Ends with the letters IV.

4
Terminology Continued

• Row
• Horizontal area on a spreadsheet.
• Identified by number/s.
• 65,536 rows in a spreadsheet
• Gridlines
• The gray lines on a spreadsheet that work as
  guide for cell boundaries.
• By default are not set to print but can be
  printed by going to File menu, page setup, sheet,
  and gridlines.
• The color and design can be changed in Tools
  menu, options, view, and gridlines.
• Label
• Alphabetic text entered in a spreadsheet.
• Left justified.
• Value
• A number entered in a spreadsheet.
• Right justified.

5
Terminology Continued

• Formula
• is a statement that performs a calculation.
• must begin with the equal sign.
• can be copied to other cells.
• carries out any calculations that are necessary
  when any changes to the data in the spreadsheet
  are made.
• can be used worksheet to worksheet.
• Print Preview
• After going to print preview and back in regular
  view, dashed lines will appear. The dashed lines
  are the horizontal and vertical outline for the
  page break.
• Scaling
• Fitting all information onto one page
  automatically
• Go to File, Page Setup, Page tab, Scaling, Fit to
  page(s) wide by page(s) tall
• To go back to original size Adjust to normal
  size under Page tab

6
Short Cut Keys
7
Short Cut Keys Continued
8
Selecting a group of cells

• To select a block of cells
• Drag diagonally from top left corner of the cells
  down to the lower right corner
• To select an entire row of cells
• Click on the number (the row identifier) on the
  left side of the spreadsheet
• To select an entire column of cells
• Click on the letter(s) of the alphabet (the
  column identifier) at the top of the column
• To select several rows or columns
• Drag across the row or column identifiers
• To select all rows and columns
• Click in the gray box in the upper left-hand
  corner (where the column and row identifiers
  intersect) or
• Hold the control (Ctrl) key and the letter A on
  the keyboard

9
Columns Rows

• Column Width
• Default width 8.43
• To change width
• Format, column, width, change, or
• Right click on column identifier, column width,
  change, or
• Click on the line between two column identifiers
  and drag to desired width

• Row Height
• Default height 12.75
• To change height
• Format, row, height, change, or
• Right click on row identifier, row height,
  change, or
• Click on the line between two row identifiers and
  drag to desired height

If a row identifier is lost, highlight the row
above and below the lost row. Next, readjust the
row height and the lost row will reappear. (The
same can be done for a column.)
Double click the line between two column
identifiers and the column width will adjust to
the minimum size needed for all text to fit
10
Columns Row Continued

• Delete column/s
• Highlight column identifier/s
• hit delete on keyboard or
• right click and delete or
• edit menu and delete
• Insert column/s
• Highlight column identifier/s
• right click and insert or
• insert menu and columns or
• toolbar command option
• New column is inserted to the left of the active
  column

• Delete row/s
• Highlight row identifier/s
• hit delete on keyboard
• right click and delete
• edit menu and delete
• Insert row/s
• Highlight row identifier/s
• right click and insert or
• insert menu and rows or
• toolbar command option
• New row is inserted on top of the active row

11
Formatting Cells

• Format Menu, Cells
• Number
• To change the type of number (example Percent or
  Currency)
• Alignment
• Wrap Text makes all text stay in one cell
• Merge Cells combines multiple cells to equal
  one cell
• Font
• To change font, font style, color, etc.
• Border
• To insert line/s around a cell
• Color and style can be changed
• Once color and style are changed, the line
  selection/s must be clicked on in order for the
  change to occur
• Pattern
• Cell Shading

12
Entering Text
Editing Text

• To edit information already entered
• Double click in the cell
• Hit F2 function key on the keyboard
• Click in the cell, then edit in the edit formula
  bar

• Have the cursor in the cell where information is
  to be entered.
• Type the information and use the enter key, tab
  key, arrow keys, or mouse to go the next cell.
• To keep the column and/or row identifier/s visual
  while typing across the page, freeze pane needs
  to be used

13
Freeze Panes

• Keep row and column labels visible as scrolling
  across or down a spreadsheet
• On the Window menu, click Freeze Panes.
• To freeze the top horizontal pane, select the row
  below where the split is to appear.
• To freeze the left vertical pane, select the
  column to the right of where the split is to
  appear.
• To freeze both the upper and left panes, click
  the cell below and to the right of where the
  split is to appear.

Information from Excel Help feature
14
Formulas

• Excel performs the operations from left to right,
  according to the order of operator precedence,
  starting with the equal sign ().
• The order of calculation can be controlled by
  using parentheses to group operations that should
  be performed first.
• For example, the following formula produces 11
  because Excel calculates multiplication before
  addition. The formula multiples 2 by 3 and then
  adds 5 to the result.
• 523
• In contrast, parentheses are used to change the
  syntax, Excel adds 5 and 2 together and then
  multiplies the result by 3 to produce 21.
• (52)3

Information from Excel Help feature
15
Formulas (Operators)

• Reference Operators
• (colon) range operator, which produces one
  reference to all the cells between two
  references, including the two references
• (B5C15)
• , (comma) union operator, which combines
  multiple references into one reference
• (SUM(B5B15,D5D15))

• Addition
• (plus sign)
• Subtraction
• -(minus sign)
• Multiplication
• (asterisk)
• Division
• /(forward slash)
• Absolute Cell Reference
• Does NOT allow cell reference to move when
  copy/pasting formulas
• (dollar sign)

16
Formulas Continued

• Print Formulas
• Unless otherwise noted, Excel will print only the
  results.
• To print formulas, go to Tools, Options, View
  Tab, Windows Options, and check mark Formulas, OK
• Column width may need adjusted to see whole
  formula
• Column width may also need adjusted to
  non-formula cells, so more text will fit on the
  page

• Copy and Pasting
• As a formula is copied, it will change the cell
  reference unless otherwise noted as an absolute
  cell reference
• Also, any cell borders will be included in the
  move

17
Grade Book

• Use an easy font to read
• Setup row with assignment names
• I make the font size very small so that more
  assignments will fit
• Also, text wrapping may be needed for long names
• Setup next row with points possible
• First couple of columns allow for students names
• Borders are a preference by creator
• Change page orientation to Landscape
• Setup headers and/or footers (if wanted)

See example of grade book
18
Grade Book Formulas!

• Total points
• Start with the first cell containing points
• Add to the last cell containing points
• Example formula C5T5
• Percentage
• Take the total points the student received (under
  the total points column) and divide by the total
  points possible (this cell must be made an
  absolute cell reference by using dollar signs if
  the copy and paste feature is going to be used)
• Example formula U5/U4

See example of grade book
19
Grade Book First 5 Weeks
20
Grade Book Next 4 Weeks and Semester Totals
21
Grade Book First 5 Weeks
Formula View
22
Grade Book Next 4 Weeks and Semester Totals
Formula View
23
Advanced Formula

• Formulas can calculate numbers from more than one
  spreadsheet at a time.
• Example
• Sheet2!C6
• b7Sheet3!B7
• Why create a formula using different worksheets?
• An example would be using separate worksheets for
  a grade book each quarter. After the two (2)
  quarters figuring the semester grade.
• Another example would be creating a expense
  record for each month and at the end of the year
  computing the expenses for the entire year.

24
Charts

• Highlight the cells that are to be included
• Insert chart
• Choose chart type, next
• Check data range, next
• Add titles, watch as they appear
• Select chart location
• To make changes, right click on the area, and
  change in the dialog box that appears

25
Mail Merge (Data Source)

• Create a Spreadsheet in Microsoft Excel
• Include a row for column headings
• Do NOT leave any blank lines
• Blank lines will print blank merge documents
• All columns do NOT have to be used in the merge
• Additional information can be added such as kids
  names, birthdays, phone numbers, etc.
• See Example below

26
Mail Merge (Address Labels)

• Open Microsoft Word
• Go to Tools menu
• Select Mail Merge
• 1 Main Document
• Create
• Mailing Labels
• New Main Document
• 2 Data Source
• Get Data
• Open Data Source
• Open file
• Enter at Entire Spreadsheet
• Set up Main Document
• Choose Address Label number, Ok

• Create Label
• Insert Merge Fields in the correct order and
  punctuation, Ok
• 3 Merge
• Merge
• Merge

,

NOTE The merge field is easiest to remember as
a word but it also can contain numbers. All
punctuation and spacing must be included as
needed!
27
Mail Merge (Form Letter)

• Go to Tools menu
• Select Mail Merge
• 1 Main Document
• Create
• Form Letter
• New Main Document
• 2 Data Source
• Get Data
• Open Data Source
• Open file
• Enter at Entire Spreadsheet
• Edit Main Document

• Type the letter
• Insert merge fields where needed
• Save the letter
• Click the Merge button on the tool bar
• The merge will take place!

All names included in data source should have
separate letters!
28
Example Form Letter
Vincennes Community School Corporation 300 North
Sixth Street Vincennes, IN 47591
,
Dear As you know
each training session this summer will be three
hours long. , I hope you will be able
to attend the sessions you signed up to attend.
Your first session, , will meet
at in Room 317 of the
Administration Building. If you have any
questions about class time, date, etc., please
call me at 812-885-1468. Have a wonderful
summer! Sincerely, Melanie Kocher Melanie Kocher
29
Mail Merge Notes

• Merge to a new document
• Merge to screen, not printer
• If there are errors, the document can be fixed
  and/or remerged
• Merge fields can be placed anywhere in the form
  document
• Example junk mail addressed to you personally
  in the letter
• Data source should not include punctuation
• Include punctuation in form document
• Sort data
• Save before starting any sort!!!!
• Highlight ALL information in worksheet
• Go to Data, Sort, put in the order to be sorted

30
Creating a Calendar

• Requirements
• Landscape Orientation
• 0.5 margins (all)
• Row Heights
• Month 57.00
• Days 26.25
• Rest 15.00
• Column Width
• All 17.86
• Font Sizes
• Month48 or smaller
• Days 14 or smaller
• Rest 12 or smaller
• Size depends on font

• Extras
• Use six (6) rows per date box with border
• See example below
• Border Designs
• Font Options

Example
31
(No Transcript)
32
Other Uses for Excel

• Create fun projects such as
• BINGO Card
• Word Search
• Crossword Puzzle
• Etc.
• Create work projects such as
• Maps and Directions
• Forms
• Expense Sheets
• Etc.