PETE

1
PETE C 2005 Organizing Data Using Excel

• Karen Jogan kjogan_at_alb.edu
• Michele Mislevy mmislevy_at_alb.edu
• Albright College

2
Organizing Your Clutter

• Types of clutter
• Books, videos, websites, professional resources,
  student projects
• Need for organization
• Consider a database!

3
What is a Database

• A collection of related information.
• Information can be organized different ways
  (sorting)
• Specific information can be isolated away from
  the entire list (filtering)
• While Excel is a spreadsheet program, it does
  have basic database capabilities.

4
Database Choices

• Access
• Off-the-shelf data tracking programs
• My Database, MS Works
• Microsoft Word Table
• Excel

5
Using Excel

• Advantages
• Low learning curve
• Easily accessible software program
• Ability to sort and find items easily
• Manipulates both text and numbers easily
• Simple setup
• Disadvantages
• Not as powerful as a dedicated database program

6
How to Set up an Excel Database

• Any list of data can be a database in Excel, as
  long as it is entered according to the specific
  rules
• Column Headings
• Must appear at the top of the list
• Must be formatted differently than the data (i.e.
  bold, all caps, italicize, border on the bottom)

7
How to Set up an Excel DatabaseCont.

• Data must begin directly below column headings
• Blank rows/columns cant split apart the data
• The data must be surrounded by blank columns
• Row 1 and Column A are the exception

8
Database Terminology

• Sorting
• putting your list in an order
• Filtering
• Isolating specific pieces of information in the
  list
• Records
• Each row in an Excel database
• Fields
• Criteria for organizing data

9
How do I sort an Excel Database?

• Click anywhere in your listDO NOT SELECT THE
  ENTIRE LIST
• Choose Data-Sort from the Menu
• Choose how you want to sort. You have the option
  of breaking 3 ties.
• Click OK
• Tips There are sorting buttons available on the
  toolbar

10
How do I filter an Excel Database?

• Click anywhere in your listDO NOT SELECT THE
  ENTIRE LIST
• Choose Data-Filter-AutoFilter from the Menu
• Each column heading has drop-down arrow

11
Filtering Tips

• Use these arrows to isolate specific records
• Applying more than one criterion to each column
  will result in an AND search and locate fewer
  records

12
Filtering Tips cont.

• To perform searches on ranges of values and to
  perform a limited OR search, choose Custom under
  the appropriate filter arrow

13
The Subtotal Function?

• Works hand-in-hand with databases
• Returns subtotals/grand totals from values in a
  list
• List can be sorted using database functions
• Subtotal function creates an outline for easy
  analysis of data

14
Why Organize Websites Using an Excel Database?

• Match Web resources to tasks
• Identify and group according to common criteria
• Create hyperlinks
• Find the needle in the haystack!

15
Task How could you organize?

• Task 1 Find a partner
• Task 2 With your partner, indicate the kinds
  of websites you would organize
• Task 3 On the paper, create a list of column
  headings you would use to organize the data
• Task 4 Share your design with another group

16
Categorizing Your Websites

• Topic
• Course
• End User
• Resource type
• Date (year)
• Language

• Grade level
• Content summary
• Rating system
• Your comments
• Limitless possibilities

17
Tips for Creating your Database

• Copy/Paste the link from the browser address bar
  into the spreadsheet
• If hyperlink doesnt function
• Place the cursor at the end of the cell entry
• Press ENTER
• Format the cells
• Column width, wrapping text
• Watch those typos

18
Activities for Students

• How can the sortable Excel database be used to
  support student activities?
• With your partner, brainstorm possible activities

19
2 Minute Paper

• What information are you taking from this session
  to help you get organized?