Simple Databases 0) Introduction: 1) Finding Information in Fields: 2) Autofiltering: 3) Autofiltering in Multiple Columns: 4) Sorting: 5) Summary: 0) Introduction: This is the first of three lectures that discuss databases in Excel. An Excel database typically consists of one worksheet of "records". Each record is one row of information that describes some entity (say, a book in a database of books, or a student in a database of student performance in a class). Each column represents a "field" for a record. In a book database, the fields that describe a book might be the author, the pubisher, the copyright date, etc.; in a student database the fields might be the student's name, major, year in school, and mostly the scores on testing instruments; in a baseball season database the fields might be date played, home team, away team, and the scores for each team. The names of the fields typically appear in the top row of each column (row 1 if the worksheet has only data, which is common). Typically each record is unique: the values in its fields are not exactly the same as any other record. In a book database, two books will not have the same author, title and copyright date (although many books might have the same author or copyright date). In a student database every student will have a unique name (or at least a unique student ID). In a baseball database every date played, home team, and away team will be different: unless two teams play a double-header -two games on the same day- in which case we can add a game # field that can have values 1 or 2. We "query" the database for two main purposes: (1) to find all the records matching some criteria (e.g., freshman who scored over the course average); we often want to display these records and read them (2) to find some summary information for all the records matching some criteria (e.g., a count of the number of freshman who scored over the course average; or the average of all students who are freshmen). In both cases, we will learn how to specify complicated criteria for our database queries using relational operators and boolean functions. We we also learn how to sort a database (rearrange the ordering of the records in the rows) according to the information in one or more columns. 1) Finding Information in Fields: To start, we can click the Find & Select icon and then Find (or the ctrl/f) command to look for text either on the entire worksheet or restricted to a specific row or column (select the entire row/column by clicking on the row's number (the whole row will be highlighted) or the columns letter (the whole column will be highlighted). In fact, we can even restrict a search to a selected rectangular region as well. Most commonly we search a column: for a certain value in its field. In a movie database, if we wanted to examine every record representing a movie that was released in 2001 (assuming one column in the database is the release date), we could search the entire worksheet for 2001. Of course, we might also find titles that contained 2001 (e.g., "2001: A Space Odyessy"). If we wanted to restrict outselves to release dates in 2001, we should perform this find command restricting ourselves just to the "release date" column. To do this, we can click any column letter (at the top of the column, which selects all vales in that column) and then perform the find command. In this command, and others in a variety of computer software, we can use the special character ? to mean any single characters and * to mean any number of characters. So, searching for a release date of 1??7 would match any movie released in a year that started in 1 and ended in 7: e.g., 1957 or 1997 (and even 1897), but not 2007. Searching for book titles using M?cro Economics would find titles containing Macro Economics and Micro Economics. In a database of names, searching for Tom*Smith would match the text Tom E. Smith and Tom Edward Smith: it matches any text with Tom coming before Smith, with any number of characters in between. This user of ? and * presents a problem: what if we actually want to search for a value that is ? or * in some text? To do so, we need to preface that character with a ~ which tells the searching mechanism to NOT use the "one charater" or "multiple character" interpretation, but instead actually search for the ? or * character. So searching for ~? would search for text that had a question mark in it. Finally, in the Find and Replace pop-up window, options allows us to specify special options (1) Search worksheet or workbook (2) Search by rows or colums (affects order the searched values are found) by rows means search each column in first row, then in the second row, etc. (3) Whether to search values in cells, or formulas or comments in cells We can also require upper/lower-case letters match each other, and that we match only cells that contain everything we are searching for (with nothing else in that cell). Also, clicking the Find Next button highlights the next occurrence of the text. Clicking Find All shows all the locations, which we can then click to see what that record looks lie. -->Open the the books.xls workbook distributed with this lecture. -->Find to search for various kinds of text: Find books whose author is --> Guzdial; books that are autographed (see column G); books that contain --> a ? in the title. 2) Autofiltering: Excel provides one very "simple to use" method for selecting the criteria for records to display, called "autofiltering". The drawback of this method is that it is not very general: there are complicated queries that we cannot perform at all using this method. But, if we have a simple query, autofiltering is often the easiest way to perform the query. So, we will discuss autofiltering first. In the next two lectures, we will discuss how to perform more complicated queries: queries that are too complicated for autofiltering. Also, this method is of little help in querying how many records satisfy some criteria. Using autofiltering, the best we can do is manually count the records displayed after querying. We can use autofiltering in Excel to filter individual columns and combinations of columns. To begin autofiltering, we first select the column(s) for which we are interested in specifying criteria. We can select a single column (select the letter at the top of the column), or a range of columns (select a letter at the top of some column and drag left or right). If we don't know which column we will be querying, it is easiest to enable selection from all the columns in a database by selecting the range from the first to last column. Once we select the columns, we click the Sort & Filter icon, and then click Filter (which toggles on filtering for the selected columns). We can see for which columns autofiltering is active, because the first line in each column that allows autofiltering displays a pull-down list icon (discussed below). If we click the Sort & Filter icon and then click Filter again, it turns off autofiltering for ALL the columns that have it turned on (not just the column we might have selected), removing each of the pull-down list icons. If we want to change we columns we are autofiltering, we must remove autfiltering from all columns and then re-enable it for the new columns. In this way, the command toggles between having autofiltering on and off for some columns. Thus, we cannot easily "add" a column for autofiltering. Instead, we must first toggle (turn off) autofiltering for all columns and then select all the columns we want and toggle (turn on) autofiltering. For this reason, it is simplest to invoke this command on all the columns in a database first, and never have to toggle it again. When all columns allow autofiltering, we choose just to autofilter the important columns In the rest of this lecture, you might find it useful to click on the Sort & Filter icon, and then right-click Filter and add it to the Quick Access Toolbar because you will be turning on and off filtering frequently. After we start autofiltering, every pull-down list icon shows a triangle, meaning that we can, but are are not yet, actively filtering that column. When we do filter a column (see details below), its icon will change from a triangle to "funnel with a small triangle to its left": it is a bit hard to see; you might want to zoom the worksheet (on the View tab) to somehing bigger, say 120. This fact is useful to know when we want to unfilter all the columns: we will click every funnel icon and click either "Clear filter from ..." or click to put a check in the All box. Both mean we can autofilter that column, but we currently aren't. When we click each pull-down list icon, we can select from a few standard choices. (1) We can toggle Select All: when checked it means select all (and the box for every value is checked); when blank it means select none (and the boxes for no values are checked). (2) We can check or uncheck the box for any given values; doing so changes the Select All box to have a square in it (meaning the selection is provided by the boxes below). If any rows in that column are blank, there will be a checkbox named "(Blanks)" for specifying whether or not blank lines should be included (typically at the very bottom of the list). (3) If Select All has a square in it, we can click "Clear filter from ..." where the "..." is the name in the first row in the column (which typical describes that data field). (4a)IN A COLUMN THAT CONTAINS TEXT, we can click Text_Filters and choose one (or two) textual relationships. The text relationships are "Equals", "Does Not Equal", "Begins With", "Ends With", "Contains, "Does Not Contains", and "Custom". Custom allows relationships like "Greater Than", "Greater Than Or Equal To", "Less Than", "Less Than Or Equal To", "Does Not Begin With", and "Does Not End With". We can type any text into the box after the relationship, or select a value from the pull-down list (which includes all values in that column). Recall the meaning of the ? and * characters that we discussed above. If we choose 2 relationships, we can either specify And/Or between them. (4b)IN A COLUMN THAT CONTAINS NUMBERS, we can click Number_Filters and choose one (or two) numerical relationships. The numerical relationships are "Equals", "Does Not Equal", "Greater Than", "Greater Than Or Equal To", "Less Than", "Less Than Or Equal To", "Between", "Top 10" (which actually allows you to choose Top or Bottom and how many values to choose), "Above Average", "Below Average", and "Custom". Custom allows textual relationships (useful if the column also contains text). We can type any number into the box after the relationship (but NOT a formula), or select a value from the pull-down list (which includes all values in that column). If we choose 2 relationships, we can either specify And/Or between them. So, the selection criteria are similar to what is available in Conditional Formatting and Data Validation: a formula that produces a BOOLEAN value, here telling whether or not to display or elide the information for this record. After making our selection, Excel will show only the records (rows) that have values in that column that meet the specfied criteria, showing their row numbers on the left in blue. Note that if we specify a relationship, the box after it contains a sorted list (alphabetically or numerically) of all the values in this column. The smaller the number of values, the more useful this option. For example, in the books database there are too many authors to choose from (most authors are unique, or have published just a few books): but the Year and Category columns are much smaller (have lots of repeats), so these options are more useful in those columns. Note that if we click in the box containing the sorted list of text values, if we then press any letter, Excel will automatically scroll down that list to the first word that starts with that letter. If we press the letter again, it goes to the second word starting with that letter. This mechanism also works with the digits 0 to 9 in numbers. If we are trying to find one item in a large list, this mechanism can help us do so quickly. One word about blank values... Sometimes when we are "fixing" a database (in which we omitted values we didn't know at the time) we can look for blank cells to fill them in. As another example, in the "Comments" column (G in the books database) if we examine all the non-blank cells (by just clicking the (Blanks) box so it is not checked), we find out which books have been autographed. Right now I am using this general purpose column for that information. I could instead add another column named "Autographed", filter the non-blank values in the "Comments" column, put the boolean TRUE in exactly those records displayed (it is easy to copy and paste TRUE into all these records), and then get rid of the Autographed values in the Comments column, now that the "Autographed" column represents this information. Finally, let's return to the (Custom...) option, which is most useful for columns that store numeric data (as in the Year and Cost columns in the books database). With this option, we can specify how the values in this column compare to one or two specific values. For example, we could specify in the Cost column the two criteria: cost is "greater than" 20; cost is "less than or equal to" 50, and select AND. For such a criteria, records would be selected when the cost of the book is in the interval (20,50] - when the cost is >20 AND it is <= 50 (a reasonably priced book). We could specify in the Cost column the two criteria: cost is "less than" 20; cost is "greater than or equal to" 50, and select OR. For such a criteria, records would be selected when the cost of the book is NOT in the interval [20,50) - when the cost is < 20 OR it is >= 50 (a cheap or expensive book). Note that there is NO way to specify that the value in the record be greater than some other cell or computation. We can enter only values into the boxes available in the (Custom...) autofilter. So, if we wanted to display only those records whose costs were less than the TWICE THE AVERAGE, we would have to compute the average in some cell, memorize the number displayed, and then type that number into one of the boxes available in the (Custom...) autofilter. In the next lecture we will see better ways to do this. 3) Autofiltering on Multiple Columns: We can peform autofiltering on multiple columns. For example, suppose that we wanted to find all the books in the Theory category that were published by Springer. First, we could use the pull-down list icon in the "Category" column to specify the choice Theory. Then we could use the pull-down icon in the "Publisher" column to further specify the choice Springer. Thus, autofiltering allows us to display records that have some criteria for a first column AND some criteria for a second column AND some criteria for a third column, etc. Each added criteria further filters/restricts the records displayed (to display the same number or fewer records, but never more records). Of course withing autofiltering a single column, we can put a check on many options, meaning one OR the other: e.g., the Theory or Education category. It is also interesting to note that after we specify Theory in the "Category" column, when we use the pull-down list icon in the "Publisher" column, it DOES NOT display a list of all publishers that are in the database; it displays only a list of all publisher for the filtered books (all publishers of Theory books). If we wanted to know how many different publishers there are for Theory books, we could select Theory in the "Category" column and then click the pull-down list for the "Publisher" category and just count the number of entries there: each is a distinct publisher of a Theory book. If we selected Springer we would see a list of all Theory books published by Springer. Of course, we could have done the filtering in the reverse order: we could use the pull-down icon in the "Publisher" column to specify the choice Springer first, and then we could use the pull-down icon in the "Category" column to specify the choice Theory. -->Answer the following question about the books database: For the publisher --> Oxford, in how many different copyright years were books purchased? Also note that if we have a filted database, we can select some values and copy/paste them to another page; in this case, only the filtered records we selected are copied/pasted, not any of the records in-between the filtered ones. Finally, if we save/close a workbook with autofiltering, when we re-open it it will display the same autofiltered records. 4) Sorting: Excel also allows us to sort records in a database. We can do this within the autofiltering command (sorting the values ONLY IN THAT COLUMN, smallest to largest or largest to smallest), but doing so invalidates our records: the records stay on the same line with just the fields in one column changing. There is a more general and useful sorting tool available by clicking the Sort & Filter icon in the Editing group on the Home tab and then clicking Custom Sort... To use this command, we must first select the records (range of rows) that we want to sort. There are two standard ways to do this. (1) Selecting the row number of the first record to be sorted (to the left of the first column) and then drag and drop to the row number of the last recorded to be sorted. Click the Sort & Filter icon and Custom sort... and in the Sort pop-up window DON'T check the "My data has headers" checkbox. (2) Select all cells on a page (click the triangle to the left of column A and above row 1) Click the Sort & Filter icon and Custom sort... and in the Sort pop-up window put a check in the "My data has headers" checkbox. The simplest kind of sorting uses just one column. For example, we can choose to sort by the "Author(s)" specifying Column A in the Sort by box (or if we put a check in "My data has headers" specifying Author(s)). We then specify in the Order box either ascending, A to Z, or Descending, Z to A: for numbers it will say Smallest to Largest or Largest to Smallest. Click OK and all the records will be sorted, by name. We can also use multiple "levels" for sorting. Suppose that we want primarily the books to be sorted alphabetically by category (ascending); for books within the same category, we want the books to be sorted descending by year; and for books within the same year, we want the books to be sorted ascending by author. In the Sort pop-up window we would choose "Sort by" Column F (Category) and Smallest to Largest; then we would click the Add Level icon and choose "Then by" to be Column D (Year) and Largest to Smallest; then we would click the Add Level icon again and choose "Then by" to be Column A (Author(s)) and A to Z; finally we click OK. Actually perform this sort and notice all the AA&DS books appear first; within those AA&DS books published in the year 2008 (there are 4), they appear in alphabetically order by their author; same for the books published in the year 2007, and all the years down to 1996. If we wanted to sort first by year, then by category, and then by author, we could fill in all the boxes differently, or we could could click the "Then by" Year line, and click up the Up-Arrow icon, which moves soring by year into the primary position, then click OK. Do this and observe the results. Likewise, we could click the "Then by" Category line, and click the Delete Level icon, which moves this level, leaving the sort to Sort by Year and Then by Author only. When I am examining grades, I often store the information alphabetized by the students' names but find myself sorting from the highest to lowest number of points (or percentage of points) to examine the records more easily. The Custom Sort... command remembers its previous settings and presents them as the default the next time we start this. This allows us to refine our sorting as we see the results. If we want to sort the selected records simply, by the leftmost column, we can click the Sort & Filter icon and then click either "Sort Smallest to Largest" or "Sort Largest to Smallest", bypassing having to fill in options in the more general Custom Sort... pop-up window. For example, on the Grades sheet the student IDs appear in the leftmost column. If I select all the records (press on row 7 and drag-and-drop until row 60) and click the Sort & Filter icon and then "Sort Smallest to Largest" those records will be sorted by increasing student ID number. 5) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel, Know how to search to find specific values in columns and the entire worksheet. Know how to use autofiltering in one or multiple columns to search for information by restricting the records shown. Know how to sort records by one or multiple criteria.