Using Microsoft Excel as a Database

Excel is a spreadsheet program. It’s in fact the application of choice for the majority of business globally. Excel has therefore been primarily used for developing departmental budgets, expense reports and a host of other accounting or financial deliverables. What many long-time users of Excel do not know is that the software possesses excellent database tools built right into it. This article will reveal the database side of Microsoft Excel.In order to take full advantage of Excel’s database features it is important to structure you data properly. In the example below you will see that the column labels are on the top row with the data directly beneath it in the following rows. For professional looking databases / spreadsheets always use consistent formatting, e.g., using the same formats for the column labels (in this instance the text is bold, Times New Roman and underlined while the numbers below them are right aligned and normal).Student# Last Name First Name Total Points 2123 Arellano Maria 1702679 Black Michael 2922680 Chase Tonia 2801455 Davila Camilla 2592681 Gabriel Maria 1471270 Gonzales Juan 2853245 Lopez Maria 2521243 Miller Hailey 1321454 Monaco Nicole 1771878 Montoya Peter 150When working with large spreadsheets with many rows of data, it can be helpful to sort and filter the information to find what you are looking for. To sort and filter effectively, follow these rules when designing your lists:• Column labels must be in first row or be beneath at least one blank row• Data must be entered in contiguous rows and columns• List data must be separated from other entries by at least one blank row or one blank column.• Do not use duplicate field names• Define your records as a list: Select all existing records and field names, click Data on the Menu bar, point to List, click Create List, make sure My list has headers is checked, and then click OK.There are several ways to organize records for quick evaluation, using the Standard toolbar, the List toolbar, or the Data menu:Simple Sort: Organize all records in a list based on one field. With the active cell anywhere in the sort field column, buttons on the Standard toolbar can be clicked to arrange records in Ascending or Descending order.Custom Sort: Organize all records in a list based on up to three fields. With the active cell anywhere in the list, click the List button on the List toolbar and then choose Sort…, or, click the Data menu on the Menu bar and then click Sort… . Make your choices in the dialog box, and then click OK to perform the sort.AutoFilter: When a range has been defined as a List, arrows automatically appear at the right of each field name. To temporarily filter out all records which do not meet one specific criterion in one field (i.e. Level=Beginning 3), click the AutoFilter arrow for the field on which you wish to base your criterion, and then click on the data you are seeking. Records where your criterion does not appear in the specified field will be hidden until you click the AutoFilter arrow again and select Show AllCustom AutoFilter: If you wish to base your filter on two criteria (i.e. Level=Beginning 2 OR Level=Beginning 3), click (Custom…) in the AutoFilter menu to set And/Or criteria. AND criteria can be used to set a range for record selection (i.e. Total Score is more than 50 AND Total Score is equal to or less than 150.)Advanced Filter: If you wish to base your filter on more than one or two criteria in more than one field, and/or you wish to copy the filtered records to another location in the workbook, use an advanced filter. An advanced filter needs these steps: 1) Create a criteria range with the field name(s) involved and enter the appropriate criteria under the appropriate field name; 2) Decide in which cells the results should be placed (keep in mind that any data directly below these destination cells may be erased); 3) Position the active cell anywhere in the data list; 4) Click the Data menu, point to Filter, and then click Advanced filter; 5) In the dialog box, specify the list range, criteria range, and copy to range (be sure the Copy to another location option button is activated) and then click OK.One of the fastest ways to organize your data is to click once on a cell containing data, i.e., any cell below the column labels and clicking on the “Sort Ascending” or “Sort Descending” button on the Standard Toolbar. This will effectively alphabetize your records from A to Z or Z to A with a simple click of a button. Look closely at the data once you sorted it. The records will maintain their integrity, e.g., if you sort in descending order, Peter Montoya in our example above will move to the top of the list along with his personal information, i.e., student number and total points.Anthony M. Magno, president of MMC, Inc., has 10+ years experience developing and implementing successful corporate learning, leadership and development programs. Learn about getting a Free Needs Analysis at: http://www.MagnoConsulting.com Anthony can be reached at 949.350.2872 (e-mail: amm@magnoconsulting.com)

Anthony Magno