Using Microsoft Excel as a Database

Excel is a spreadsheet program. It's in fact theorder.
application of choice for the majority of businessCustom Sort: Organize all records in a list based on up
globally. Excel has therefore been primarily used forto three fields. With the active cell anywhere in the
developing departmental budgets, expense reportslist, click the List button on the List toolbar and then
and a host of other accounting or financialchoose Sort..., or, click the Data menu on the Menu
deliverables. What many long-time users of Excel dobar and then click Sort... . Make your choices in the
not know is that the software possesses excellentdialog box, and then click OK to perform the sort.
database tools built right into it. This article will revealAutoFilter: When a range has been defined as a List,
the database side of Microsoft Excel.arrows automatically appear at the right of each field
In order to take full advantage of Excel's databasename. To temporarily filter out all records which do
features it is important to structure you datanot meet one specific criterion in one field (i.e.
properly. In the example below you will see that theLevel=Beginning 3), click the AutoFilter arrow for the
column labels are on the top row with the datafield on which you wish to base your criterion, and
directly beneath it in the following rows. Forthen click on the data you are seeking. Records
professional looking databases / spreadsheets alwayswhere your criterion does not appear in the specified
use consistent formatting, e.g., using the samefield will be hidden until you click the AutoFilter arrow
formats for the column labels (in this instance theagain and select Show All
text is bold, Times New Roman and underlined whileCustom AutoFilter: If you wish to base your filter on
the numbers below them are right aligned andtwo criteria (i.e. Level=Beginning 2 OR Level=Beginning
normal).3), click (Custom...) in the AutoFilter menu to set And
Student# Last Name First Name Total PointsOr criteria. AND criteria can be used to set a range
2123 Arellano Maria 170for record selection (i.e. Total Score is more than 50
2679 Black Michael 292AND Total Score is equal to or less than 150.)
2680 Chase Tonia 280Advanced Filter: If you wish to base your filter on
1455 Davila Camilla 259more than one or two criteria in more than one field,
2681 Gabriel Maria 147and/or you wish to copy the filtered records to
1270 Gonzales Juan 285another location in the workbook, use an advanced
3245 Lopez Maria 252filter. An advanced filter needs these steps: 1) Create
1243 Miller Hailey 132a criteria range with the field name(s) involved and
1454 Monaco Nicole 177enter the appropriate criteria under the appropriate
1878 Montoya Peter 150field name; 2) Decide in which cells the results should
When working with large spreadsheets with manybe placed (keep in mind that any data directly below
rows of data, it can be helpful to sort and filter thethese destination cells may be erased); 3) Position
information to find what you are looking for. To sortthe active cell anywhere in the data list; 4) Click the
and filter effectively, follow these rules whenData menu, point to Filter, and then click Advanced
designing your lists:o Column labels must be in firstfilter; 5) In the dialog box, specify the list range,
row or be beneath at least one blank rowo Datacriteria range, and copy to range (be sure the Copy
must be entered in contiguous rows and columnsoto another location option button is activated) and
List data must be separated from other entries by atthen click OK.
least one blank row or one blank column.o Do notOne of the fastest ways to organize your data is to
use duplicate field nameso Define your records as aclick once on a cell containing data, i.e., any cell below
list: Select all existing records and field names, clickthe column labels and clicking on the "Sort Ascending"
Data on the Menu bar, point to List, click Create List,or "Sort Descending" button on the Standard Toolbar.
make sure My list has headers is checked, and thenThis will effectively alphabetize your records from A
click OK.to Z or Z to A with a simple click of a button. Look
There are several ways to organize records for quickclosely at the data once you sorted it. The records
evaluation, using the Standard toolbar, the Listwill maintain their integrity, e.g., if you sort in
toolbar, or the Data menu:descending order, Peter Montoya in our example
Simple Sort: Organize all records in a list based on oneabove will move to the top of the list along with his
field. With the active cell anywhere in the sort fieldpersonal information, i.e., student number and total
column, buttons on the Standard toolbar can bepoints.
clicked to arrange records in Ascending or Descending