PRR 475 Labs – Week 4 - Database Features in Excel- Database procedures in Excel Sorting data - sort cases in alphabetic or numeric order, increasing or decreasing Filters - to find cases satisfying particular criteria, this is called a "query" in database lingo Forms - to enter, edit or view data one case at a time Subtotals - to compute subtotals for groups of cases - regions, market segments Validation - checking entries as you make them Pivot Tables - frequency counts, crosstabulations or comparing means across subgroupsIntroduction: Excel includes several basic database procedures, although not as many as a full-fledged relational database like Access. Excel's databases are limited to 65,000 cases and the procedures only work on a single table. Relational databases (Access) may contain many interrelated tables. Think of a database as a series of cases (or records) - the rows. For each record a number of distinct variables (fields) have been measured - the columns. Excel's database procedures require that the data be set up in a contiguous block on the spreadsheet - no blank rows or columns in the middle of data and include blank rows or columns to separate the database from any other information on the spreadsheet. To execute a database procedure in Excel you must (1) first select a singlecell inside the database - this identifies the database you want to analyze, (2) then choose one of the database procedures from the menu. We will demonstrate the database procedures using a small database of marinas in Michigan. Open the file Marina.xls from the course AFS space - labs99 folder. The marina database is located in the range A1:F78 on this spreadsheet. This database contains 77 records and six fields. The first row are the labels for the fields. The "Marina", "County" and "Size" fields are text fields, while the other three fields are numeric. The data type for each field determines what operations can be carried out - mathematical operations like averaging or summing only apply to numeric fields. Sorting Data: to change the order of the rows in a spreadsheet without changing the contents of the cells. Warning - always save you spreadsheet before sorting. Let's sort the data by marina name, region, or number of slips.1. First select a single cell inside the database - be careful not to drag and select a small range.2. To sort the entries in the spreadsheet alphabetically by marina name, select Data from the pull-down menus, and then select Sort.3. In Sort by field select MARINA and make sure the radio button next to Ascending is selected. Make sure there is nothing either of the Then by fields. Click OK.4. To sort the entries first by region number and then by marina name within the regions, select Data and Sort. In the Sort by field select REGION and in the first Then by select MARINA. Click the radio button next to both to change them to Descending. Click OK. This will place highest region number first and the marinas will be reverse alphabetically sorted within the regions.5. For simple single field sorts, use the speedsort buttons on the toolbar - click a single cell in the column you want to sort on and then select either the ascending (A to Z) or descending (Z to A) speedsort tool.Filters: to find records with specified characteristics. (querying the database). Let's find all marinas in Macomb county.6. Select a single cell inside the database7. On menu bar select Data, then Filter, and then AutoFilter from the pull-down menu. 8. Pull-down arrows should now be visible for each column in the first row of the database. 9. To find all marinas in Macomb County, click the filter arrow at top of the COUNTY column and then select MACOMB.10. All of the marinas (rows) that are not in Macomb County will be hidden from view.11. To turn off the filter and display all of the marinas again, click the arrow next to COUNTY and then select (All).12. A two variable filter - Let's find all the marinas in Region 2 with more than 100 slips. Filter the Region field for 2's, then select the filter at top of Slips Field. Choose Custom and fill in boxes - in left hand box, select drop down arrow and choose "is greater than", then enter 100 in the box at the right. Click OK13. Remember filtering is cumulative here so you can filter on as many fields as you like. To turn off filtering in a particular column, choose the "all" option at top of list.14. To remove all filtering, go back to menus, choose Data, Filter and uncheck the autofilter command. You should turn filtering off when you are done with database procedures.Forms: are a handy way to enter or edit data or perform simple queries.15. Choose a single cell in database, Select Data, then Forms from the pull-down menu. The first record will be displayed on the form. Use scroll bar to scroll through records. Buttons at right can be used to add cases (NEW), delete them, or to find cases satisfying particular criteria. Choose Criteria button and enter Macomb in County field. Then use Find Next button to find marinas in Macomb county. Page 1 of 316. To add a new marina to the database, select the NEW button - this simply scrolls to the end of records. Fill in the form with a new record a 1000 slip marina in Monroe County - MY MARINA, 48133, MONROE, 1, 1000 (all in uppercase letters) in the respective fields, and then press Close. Notice this record is added in row 79 on the spreadsheet. Subtotals: are useful when you want to aggregate information for subgroups of records. One of the fields in the database defines the subgroups and then you can summarize any of the other fields for these subgroups. Let's use this procedure to count the number of slips in each region.17. First, the data must be sorted on the field that will define subgroups. Select a cell in the Region column and click the speedsort tool. 18. To add subtotals for each region, Select Data, then Subtotals from the pull-down menu.19. To sum all of the slips within each region, fill in the boxes as follows: At each change in: select REGION from pulldown listUse functions: select Sum. Check those fields for which you want sums - will only apply to numeric fields SLIPS will be checked. Click OK.20. A row has been added at each change in Region and subtotals are entered on this row. At left, there is an outline that you can useto display just subtotals or individual records. Remove subtotals before proceeding with other analysis in Excel - see Step 21.