|
|
Senior Computer Tutor
Don Edrington Home Profile |
Creating an Address Book Database Using Excel with Filtering CapabilitiesA "database" can be many different things, but the one used by most of us is an address book (contact list) that includes names, addresses, email addresses, phone numbers, and other pertinent information about friends, relatives, or business contacts. Microsoft Excel is ideal for creating such a database, even though it is technically a "spreadsheet" program. Begin by typing headings into the top row, such as First Name, Last Name, and so on. Use File>Save As to name the worksheet and you will be ready to fill in the various data below the headers. At some point you will want to sort (alphabetize) the information, probably by Last Name. Click the alpha character above Last Name to highlight the column. Then click on Data>Sort. You’ll be asked if you want to expand the selection to keep the other columns synchronized. Click Yes, and then choose "My Data Range Has a Header Row." The sorted database can then be used as a source for printing envelopes or mailing labels. However, the actual printing would be formatted in Microsoft Word. More information on this can be found here. Using Filters to Extract Specific InformationFiltering options are available such as, say, displaying only the names in a certain zip code. For this example, click to highlight the Zip Code column and then click Data>Filter>AutoFilter. Click the little down-arrow that appears in the Zip Code cell and choose Custom. You will then see a collection of Boolean options such as, Is Equal To:, Is Greater Than:, Contains:, etc. Choosing Is Equal To: followed by typing 92025 will hide all entries except those with the indicated zip code. Removing Duplicate EntriesIn a large database it’s not uncommon to accidentally enter a particular row of data more than once. Well, Excel lets you weed out duplicate entries by clicking Data>Filter, and choosing Advanced Filter. Finally, click on Unique Records Only. This will remove all duplicated rows. Spreadsheets 101For those who are new to spreadsheets, here are a few tricks that can come in handy. Notice that each cell has a tiny black square in its lower right corner. Grabbing and dragging the square will cause different things to happen, depending on the data in the cell. For instance, type January into a cell and then drag the black square to the right or down to see all the other months' names fill in sequentially. You can also choose a cell in the middle of a page and drag upwards or the left. Start with, say, September, and see what happens. Guess what happens if you type in a number, such as 100 and drag the little square. Sequential numbering? No, 100 will simply be repeated in all the dragged-over cells. However, if you precede the number with any alpha characters, such as Item 100 or Parking Space 100 sequential numbering will occur. Nonetheless, you can create a series of sequential numbers by typing, say, 1 into a cell and 2 into an adjacent cell. Mouse-select both cells and drag the black square in the second cell. This will create a series of cells displaying 1, 2, 3, 4, and so on. You can also use these steps to establish cells that display numbers in increments of, say, 10 (10, 20, 30, 40, etc.). Do this by by typing 10 into a cell and 20 into an adjacent cell. Again, mouse-select both cells and drag the black square in the second cell as desired. |
If you would like to receive our weekly newsletter just send an email to Mary Hanson at: ComputerTutorTeam@gmail.com. 
There are no fancy sign-up forms – just mention newsletter in the subject line of your email.
We've been sending out this PC Help Newsletter since 1994, and it contains no advertising of any kind – just useful computer tips. Furthermore, your email address is safe with us – we don't share it with anyone under any conditions whatsoesver. |
