Transcript Organizing Data Using Excel

PETE & C 2005
Organizing Data Using Excel
Karen Jogan – [email protected]
Michele Mislevy – [email protected]
Albright College
Organizing Your Clutter
• Types of clutter
– Books, videos, websites,
professional resources,
student projects
• Need for organization
• Consider a database!
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.
Database Choices
• Access
• Off-the-shelf data tracking programs
– My Database, MS Works
• Microsoft Word Table
• Excel
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
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:
1. 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)
How to Set up an Excel Database
Cont.
2. Data must begin directly below
column headings
3. Blank rows/columns can’t split apart
the data
4. The data must be surrounded by
blank columns
-
Row 1 and Column A are the exception
Database Terminology
•
Sorting
–
•
Filtering
–
•
Isolating specific pieces of information
in the list
Records
–
•
putting your list in an order
Each row in an Excel database
Fields
–
Criteria for organizing data
How do I sort an Excel Database?
1. Click anywhere in your list…DO NOT
SELECT THE ENTIRE LIST
2. Choose Data-Sort from the Menu
3. Choose how you want to sort. You
have the option of breaking 3 ties.
4. Click OK
Tips: There are sorting buttons
available on the toolbar
How do I filter an Excel
Database?
1. Click anywhere in your list…DO NOT
SELECT THE ENTIRE LIST
2. Choose Data-Filter-AutoFilter from
the Menu
3. Each column heading has drop-down
arrow
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
Filtering Tips
•
cont.
To perform searches on ranges of values
and to perform a limited OR search,
choose Custom under the appropriate
filter arrow
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
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!
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
Categorizing Your Websites
•
•
•
•
•
•
Topic
Course
End User
Resource type
Date (year)
Language
•
•
•
•
•
Grade level
Content summary
Rating system
Your comments
Limitless
possibilities
Tips for Creating your
Database
• Copy/Paste the link from the
browser address bar into the spreadsheet
• If hyperlink doesn’t function:
– Place the cursor at the end of the cell entry
– Press ENTER
• Format the cells
– Column width, wrapping text
• Watch those typos
Activities for Students
• How can the sortable Excel database
be used to support student activities?
• With your partner, brainstorm
possible activities
2 Minute Paper
• What information are you
taking from this session to
help you get organized?