Microsoft Excel

1
Microsoft Excel

• Spreadsheet Software

2
What is a Database and what is it used for?

• A database is an organized collection of data
  related to a particular topic or purpose.
• The primary function of a database is to enable
  the user to organize and retrieve information in
  a manner defined by the user.

3
Flat-File vs. Relational

• A Flat-file database consists of a single
  database file or table which contains all the
  information about a topic. It does not
  physically link or point to other files.
• A Relational database consists of multiple tables
  linked together by at least one common field.

4
STUDENT INFORMATION TABLE
5
Products Table
Supplier Table
6
Basic Concepts
7
Basic terms

• Columns have letter headings
• Rows have number headings
• Intersection of a row and a column is called a
  cell
• Cells are basic building blocks of Excel

column
cell
row
8
Customizing the toolbar

• If menus show recent commands only
• Tools/Customize/Options to turn it off, delay, or
  reset
• If you cant see all of the standard format
  toolbars
• Tools/Customize/Options to break it into 2
  separate toolbars

9
Text and Numbers

• Type in cell, appears in formula bar
• Edit 3 ways
• Backspace (if youre still in cell)
• D-click inside cell
• Formula bar
• Text has no value
• Numbers (0-9) and symbols (, /, , -) have
  values, can use formulas
• Negative numbers use - or ( )

10
Inserting, deleting, shifting cells

• Insert/cells gt to insert
• R-click will do it too (context-sensitive)
• Always above and to the left
• Always reletters, renumbers
• Edit/Delete gt to delete

11
Cutting, copying, pasting cells

• Select first cell, then cut or copy
• Select destination cell, then paste
• For multiple pastes,Toolbars/Clipboard gt
  Clipboard
• 12 items stored on clipboard
• Screentips shows you which one

12
Text formats

• Adjust text formats with
• Font and size pulldowns
• B, I, U buttons
• Font color pulldown
• Fill color pulldown
• Font style
• Left, center, right buttons

13
Numeric formats

• Dont type in commas or dollar signs use
  Format/Cells/Number for numeric format options
• Buttons for and , formats
• Decimal increase, decrease buttons will round
  off, but dont change value

14
Alignment, size, rotation

• Defaults text left justify, numbers right
  justify
• Change with alignment buttons
• Format/Cells/Alignment for more alignment options
• Wrap, shrink to fit, merge checkboxes for sizing
  problems
• Rotate text with orientation box
• Merge and center button useful for headings

15
Sizing cells

• Drag rows and columns to proper size by pulling
  borders
• Choose all rows or cells and drag as one it
  will evenly widen size
• D-click border will auto-fit box to longest record

16
Sheet formats

• Format/Autoformat/Choose a style to automatically
  format a sheet
• Options button allows only certain aspects to be
  chosen
• Borders buttons to manipulate borders
• Style painter button will copy styles (1-click
  for 1 time use, 2-click to leave it on)
• Paste special will allow you to copy certain
  aspects

17
Formulas and logical functions
18
Formulas

• Always pick destination cell first
• Always click to begin (except autosum)
• Autosum (? button) will automatically add a
  column of figures
• If wrong, You can adjust by typing in formula bar

19
Copying formulas

• Drag by lower right-hand corner(cursor will be
  small black sign, not big white sign)
• Relative referencing (i.e. - D9) will change as
  cells are added or deleted
• Absolute referencing (D9) specified cell only
  will not refigure

20
Math functions

• 4 main functions (add), - (subtract), /
  (divide), (multiply)
• Type it just like an algebraic formula (e.g.
  e3f3 means to multiply the value in cell e3 by
  the value in cell f3)

21
Math functions (cont)

• For more complex formulas, click the button,
  then pull down a function in upper right
• Sum
• Average
• Min
• Max
• Count

22
Ordering

• 10 2 1 19 or 10?
• Order of preference which order excel will
  calculate expressions
• PEDMAS
• Parentheses
• Exponents
• Division
• Multiplication
• Addition
• Subtraction

23
Date and time functions

• Now function, today function to capture time
  or day, choose format after the fact in
  Format/Cells/Number
• Dates/times represent mathematical values
• Date due minus date delivered to track lag time

24
Logical functions

• If statements checks values and returns text on
  false and true statments
• Or statements - returns true if any cell fits a
  condition
• And statements returns true if all cells fit a
  condition

25
More functions

• Financial functions
• FV gt Future value
• PMT gt Payment functions
• Randomizing
• RAND
• Remember to set value, then cut and paste
• SUMIF to count only certain values
• COUNTBLANK to count blanks

26
Previewing and printing
27
Page setup

• File/Page setup
• Place to set up printing format Use Print
  preview to check
• Page setup 4 tabs
• Page (portrait vs. landscape, scaling, scale to
  fit)
• Margins set margins, center horz. And vert.)
• Headers/footers set standard or customized
  footers and headers here)
• Sheet (print all or part of book/sheet?, repeat
  rows?, gridlines?)

28
Setting and clearing print areas

• To print part of worksheet
• L-click and drag area you want
• File/Print area/Set print area
• Dashes show print area
• File/Print/Chose Selection
• Be sure and clear it when done

29
Web page preview

• File/Web page preview
• Opens in new window
• Tabs included
• HTML code (View/Page source)

30
Charts
31
Chart Wizard

• Use chart wizard icon
• Wizard leads you through decision process
• Choose chart type (press and hold for preview)
• Define data range
• Set titles, axes, gridlines, legends, labels
• New sheet vs. embedded

32
Modifying charts

• Click and drag to include more cells, chart will
  change
• L-Click on specific elements to choose them
  R-click to get format options change fill
  colors, fonts, gridlines, etc.
• Change cell numbers, chart will change

33
Inserting objects

• Chart is an object
• You can add others (pictures, clip art, logos,
  text boxes)
• Size (use sizing boxes) and move (click and drag)
  them afterward

This is a textbox, and an arrow pointing to
clipart.
34
Drawing toolbar

• Icon next to Chart Wizard, or R-click in toolbars
• Text box
• Size it, start typing
• Size and move when finished
• Line and arrow buttons
• Click and drag, use sizing boxes to manipulate
• Multiple objects group them before printing
  (hold down shift key, select all objects)

35
Printing charts

• If you only want to print chart, click chart,
  then File/Print Preview
• To print whole page, click away from the chart

36
Sorting and filtering
37
Sorting caveat

• Filtering masks, doesnt change numbering
• Sorting rearranges, changes numbering
• BE CAREFUL when sorting, click inside column,
  NOT in column letter
• it will strip column from data when sorting
• When filtering, you can use column

38
Sorting

• Click anywhere within column
• Click ascending, descending order buttons
• Data/Sort (for multiple sorts)

39
Auto-filter

• Select column letter
• Data/Filter/Auto-filter
• Click arrow, choose value, all records with that
  value will be shown
• Notice the row numbering changes and is in blue
• If you dont select column, Data/Filter/Auto-filte
  r, and you can filter by multiple columns

40
Restoring the List

• Restore all or some
• All gt Data/Filter/Autofilter again
• Some gt choose column, arrow, then all

41
Custom filter

• Two criteria filter
• Data/Filter/Autofilter/(Custom)
• 1st agument, 1st value, 2nd argument, 2nd value
• Wild cards ( and ?) can be used

42
Subtotals

• Choose cell within sheet
• Total, Subtotal
• Choose Column to separate by
• Choose math function
• Choose column with values
• OK
• Use levels on left to manipulate level of detail

43
Managing workbooks
44
Changing zoom setting

• Zoom window allows you to choose percentage from
  10-200
• Ctrl mouse wheel does it too
• Doesnt change document, or print size, just your
  view
• Zoom to any part of sheet by selecting it,
  choosing selection in zoom window

45
Freezing and unfreezing columns and rows

• Easy way to freeze certain areas of long
  spreadsheets
• Click cell where you want the page to break,
  then Window/Freeze pane
• Window will freeze columns and rows above and to
  the left of the cell you chose
• Doesnt change document, just your view
• Window/Unfreeze pane to unfreeze

46
Hiding and unhiding

• For confidential data (salaries), or data you
  dont want to see
• Hidden data doesnt print good way to shrink
  size of large spreadsheets
• Select column or row gt R-click/Hide
• To unhide, select columns or rows before and
  after hidden one, R-click, unhide

47
Inserting and deleting sheets

• Insert/Worksheet
• Will insert to left
• R-click on tab to delete

48
Copying, renaming, moving sheets

• D-click tab to rename, or Format/Sheet/Rename
• Click and drag tab to move
• To copy
• R-click on tab, choose move or copy
• Choose sheet, check make a copy
• Filename (2) will appear
• Click and drag Ctrl will copy as well

49
Linking workbooks
50
Linking formulas

• Used to create hierarchies of data
• 3-D formulas will reference cells from other
  sheets
• Good for summarizing large amounts of data spread
  over several sheets

51
Summarizing data from identical formats

• Create blank totals sheet, identical to ones
  you want to summarize
• Choose destination cell
• Click Autosum
• Select beginning cell, first sheet
• Shift ending cell, last sheet
• Hit Enter
• Fill across (drag by lower right corner) to apply
  to other columns
• Fill down to apply to other rows

52
Consolidating data

• Will add, average, multiply multiple cells from
  different sheets
• Select destination cell
• Data/Consolidate
• Choose function
• Select 1st cell, click add button, select 2nd
  cell, etc., then OK
• Will not fill in formula, just a value

53
Creating hyperlinks

• Easy way to impress your boss!
• Link to other documents, other places on same
  document, web page
• Type text heading, then R-click
• Select Create Hyperlink
• Type screen-tip (will show during mouseover)
• Select file to link to
• Existing file or web page
• Place in current document
• Email address

54
Workgroups
55
Adding and removing comments

• R-click inside cell/Insert comments
• Type in your comments, click outside cell
• Triangle appears in upper right to show a comment
  has been added
• Mouseover or R-click to see comments
• It will identify you (Tools/Options/General to
  change login name), any others who comment
• R-click to edit, add comments
• Will track a dialogue between users

56
Protecting data

• Tools/Protection/Worksheet or Workbook
• Password is case-sensitive
• BE CAREFUL gt No way to retrieve password
• Remove protection gt Tools/Protection/Unprotect

57
Shared workbooks

• Generally stored on network drive
• Tools/Share workbook/Check Allow changes
• Shared appears in title bar
• Anyone at anytime can now make changes, so track
  them
• Tools/Track changes/Highlight changes
• Changes appear in blue, triangle appears in upper
  left
• Screentip shows history of changes