PETE & C 2005 Organizing Data Using Excel

1. PETE & C 2005 Organizing Data Using Excel Karen Jogan – kjogan@alb.edu Michele Mislevy – mmislevy@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 can’t split apart the data • The data must be surrounded by blank columns • Row1 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 list…DO 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 list…DO 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. Topic Course End User Resource type Date (year) Language Grade level Content summary Rating system Your comments Limitless possibilities Categorizing Your Websites

17. 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

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?