MicrosoftExcel Basics

1
Microsoft-Excel Basics

• Introduction to MS-Excel
• MS-Excel common tools
• Create a student grade book
• Modify the grade book
• Calculations on the grade book
• Create charts on the grade book

Yi Guan Educational Technology Specialist qv_yguan
_at_commnet.edu QVCC (860) 774 - 1160 TRCC (860)
383-5215 MXCC (860) 343 - 5783 http//www.commnet
.edu/QVCTC/people/yi On-line Workshop
Handouts http//www.commnet.edu/qvctc/people/works
hops
2
Introduction to MS-Excel

• A spreadsheet (worksheet) a piece of paper in
  which data can be manipulated by the computer
  stored in rows and columns
• A workbook (Excel file) has multiple sheets
• MS-Excel
• Record and organize information in a row
  (record)-and-column (field) format
• Make calculations and simple statistical analysis
  across a row or a column
• Create charts based on the data

3
Introduction to MS-Excel

• An address book
• Name, phone numbers, e-mail, address, ...
• A travel log
• Date of travel, Departure, Destination,
  Mileage,...
• A student record book
• Name, addresses, phone number, exam grades,
• Experiment Records
• Spreadsheets and charts can be used in MS-Word
  and PowerPoint
• Spreadsheets and charts can be converted to web
  pages

4
MS - Excel

• Copy work files from the floppy disk to the hard
  drive C
• Create a folder with your name on My Documents in
  C drive.
• Start-Programs-Windows Explorer
• On the left window, look for My Document folder
  and double-click.
• File-new-folder
• On the right window, type your name as the
  folder name.
• Copy all the files from the floppy disk to your
  folder.
• On the left window, look for A drive and double
  click the drive icon
• Drag all files on the right window to your folder
  on the left window.

5
Excel Workbook Window

• Open the MS-Excel
• Start-Programs-Microsoft-Excel
• Click on Start using Excel
• Double-click on an Excel file (travel)
• Excel Workbook Window
• Workbook (file) and Worksheets (pages)
• Cell, the name box will show the cell label.
• Minimize, restore, close
• Change the window size by dragging the
  bottom-right corner
• Scroll view part of the worksheet

6
Introduction to MS-Excel

• Excel Workbook Window
• Title bar-name of an Excel file
• Menu/Command bar-all the Excel commands
• Standard toolbar (view-toolbar-standard toolbar)
• Formatting toolbar (view-toolbar-formatting)
• Formula bar
• Column headings and row headings
• File paths

7
Column Headings
Row Headings
Minimize
Restore
Close
Cell
Sheets
File Path
Resize
8
Excel Common Tools

• Standard Tools
• New, Open, Save, Print, Print Preview, Spelling
• Cut, Copy, Paste, Painter, Undo, Not-undo, Insert
  Hyperlinks, Web Tools, Chart Wizard, Map,
  Drawing, Zoom, Help
• Sum, Function, Sort (list the record on
  alphabetical order)
• Formatting tools
• Fonts, Size, Bold, Italics, Underline, Left,
  Center, Right, Merge
• , , Increase Decimal, Decrease Decimal,
  Borders, Fill Color, Font Color

9
Excel-Create a New File

• File-New
• Click on the icon
• Type in the column label
• Adjust column width
• Drag the border between the two columns
• Select the columns
• Format-column-width-type in the number 30
• Adjust row height
• Drag the border between the two rows
• Select the rows
• Format-row-height-type in the number 15
• Type in the data in a cell

10
Excel-Modify a Travel Log

• Open the file named travel by icon
• Modify the column labels with Time New Roman,
  12p, Bold, center
• Select the first row, use font icons
• Align the cells to left, center, right
• Select the entire columns and use alignment icons
• Put the year 99 travel to the second sheet
• Select the first row
• Copy, click sheet2, paste, adjust the
  column widths.
• Select the 99 travel rows, copy, sheet2,
  paste, adjust the column widths.

11
Excel-Modify a Travel Log

• Add a day of 3/18/99 travel
• Select the row label where you want to insert a
  row
• Insert-row
• Type in the departure, destination, miles, and
  date
• Copy the two rows of 3/11/99, paste, change the
  date to 3/18/99
• Add title Travel Log with Your name and date on
  headings
• View-Header and Footer-
• Add the number of pages and page numbers on the
  footers
• View-Header and Footer-

12
View-Header and Footer
Header Set Up
Footer Set Up
13
Custom Header
Sheet Name
File Name
Font
Pages
Page
Date
Time
14
Custom Footer
15
View of Header and Footer
16
Excel-Modify a Travel Log

• Change the name of a sheet
• Double-click on the name of the sheet until the
  name is highlighted
• Type in the new name
• Change the order of a sheet
• Select the sheet tab by clicking on the name of
  the sheet, click a cell in the sheet
• Edit-move or copy sheet ...-select the sheet you
  would like to put afterwards
• View the file with a page break
• View-Page Break Preview
• Drag the blue line/page break line to set up
  the page break

17
Change the Order of a Sheet
Select where the sheet moves to
Change the sheet name by Double-Clicking the
name and then type in a new name
18
Excel-Modify a Travel Log

• Calculate the total travel mileage for 99
• Label the Total row by typing in Total on the
  first cell of the row
• Click on total mileage cell, sum icon
  (put a sum formula in the cell)
• Select the cells of all 99 mileage, enter
• Calculate the reimbursement for 99 travel with
  rate of .33/mile
• Label the amount for the reimbursement row
• Click on the reimbursement cell, select function
  icon , select all in the left window and
  value on the right window
• Select the total cell, type .33
• Pasting the formula allows automatic calculations
  when data are changed
• Change mileage cells and see what happens

19
Click on Function Icon

• Select value

20

Calculate the reimbursement
21
Excel - Create a Student Address Sheet

• Create an address sheet for the following
  students
• with four columns labeled as First Name, Last
  Name, Address, Home Phone
• Type in one persons address or copy the
  address from the sheet1 in the file named
  record

Teddy Bear, 56 Bear Road, Cave, CT 03944, (860)
858-3034 Cute Cat, 84 Cat Road, House, CA 95948,
(949) 834 - 3454 Peking Duck, 90 Peking Road,
Peking, IN 94853, [phone] Fruit Banana, 85
Banana Road, Fruit, IL 94858, [phone]

• New-save as student1 in your folder
• Open the file named student
• Adjust all column widths to show entire labels
• Drag the column side-borders
• Adjust the height of the label row to 18 points
• Select the first row
• Format-row-height-type in 18

22
Excel - Create a Student Address Sheet

• Change column labels to Time New Roman, 14p, Bold
• Select the label row and use formatting tools.
• Type in the records and adjust column width.
• Use tab key to change cells.
• Change the fonts to Arial, 12p, plain and adjust
  the column width
• Select row2-row5 and use font tools
• Adjust the height for all records to 16 point
• Select the row-2-row5
• Format-row-height-type in 16
• Align the records to left or right or
  center
• Select all cells, left alignment icon

23
Excel - Create a Student Record Sheet

• Add a student record before Cute Cat
• Monkey Lee, 87 Monkey Road, Monkey, MO 74754,
  [phone]
• Select Cute cell, Insert-Rows
• Type in the record
• List student record in an alphabetical order
  sorted by first name.
• Select the row2-row6
• Data-sort-select first name or last
  name-select ascending or descending, OK.

24
Sort with Last Name and Then First Name

• Data-Sort...

25
Excel - Create a Student Record Sheet

• Add headings with a title Student Records
  (Arial, 14, Bold), your name on the left and date
  on the right (Arial, 10, Plain, Italic).
• View Header and Footer-
• A icon, select Arial, 14, Bold, Type the title
  in the center window.
• A icon, select Arial, 10, plain, Type your name
  in the left window.
• Date icon
• Add number of pages and page numbers on the
  bottom right (Arial, 10, plain, italic)
• View Header and Footer-
• A icon, select Arial, 10, plain, number of
  page icon, -, page icon
• Print Preview
• Adjust your name and date if necessary

26
Excel -Add Students Grades on the Record Sheet

• Copy the first name and last name columns onto
  the sheet2
• Add Quiz1 and Quiz2 columns on the sheet2
• Type in grades for each student at 100 scale
• save it as grade
• Calculate average scores of quiz1 and quiz2 for
  all students
• Select the average score cell, click on
• on the left window, select statistical
• on the right window, select average
• Select quiz1 scores for all students, hit enter

27
Function -Statistical-Average
28
Select the Grades of all Students
29
Calculation in a Grade Book

• Calculate final points with 10 on Quiz1, 10
  on Quiz2, 20 on Midterm, 30 on Final, and 30
  on Paper
• Select the final points cell for a student cat,
  , value
• Select Quiz1 cell, .1, select Quiz2 cell,
  .1, select Midterm cell, 0.2, select
  Final cell, 0.3, select Paper cell, 0.3,
  OK.
• Copy this students final points cell to other
  students final points cells
• You are coping the formula, and Excel will run
  the formula to get the result automatically.

30
Choose Value in the Function Window for
Calculating Final Grade
31
Formula for the Final Grades
32
Charts in a Grade Book

• Student grade charts
• Select five students records with seven columns,
  click on
• Select clustered column, next
• Select Row or Column to define the chart layout,
  next
• Type in the title and X axis label and Y axis
  label
• Select where you would like the chart to locate,
  on a new sheet or on the existing sheet

33
Select a Chart Type

• Select the Insert Chart icon
• Select a type of the chart
• Click on Next

34
Select Row Series Layout
35
Select Column Series Layout
36
Type in Title and Labels
37
Select Where the Chart Locates