Using Excel As A Database
Access is a great database application, but let’s face it – sometimes it’s just a “bit”
complicated! There are a lot of times when it would be nice to have some of the
capabilities of a database without all the hassles. This is where Excel really comes in
handy!
If you design your Excel workbook correctly, you can use Excel to look at your data in
many of the same ways you would use a database application – you can sort data in
many different ways, filter it so you see only data that matches specific criteria, and you
can reorganize your data in any way you want it “on the fly” as your needs change.
To use an Excel workbook as a database, your data needs to be structured in the list
format. The techniques we’ll talk about in this session won’t work on data that’s not
structured as an Excel list, but don’t worry – this is not hard to do!
An Excel list consists of columns and rows of data structured in a specific way:
• Each column contains the same category of data in every row in the column
(similar to a field in a database)
• Each row in the list contains all of the fields of data for one entity (a person,
organization, object, etc.) similar to a record in a database, but called sets of data
in Excel’s terminology
• The first row of the list must contain a unique name at the top of each column.
This first row does not need to be the first row of the worksheet
• The row containing the column headings must be formatted differently from the
rest of the list (i.e. bold, larger font, italicized, etc.)
• There can be no blank rows in the list (there can be blank cells in a column, but
the entire row cannot be empty)
• Data in a column must be in the same format for every row in the column (i.e.
numbers can’t be spelled out in one row and entered as digits in the rest of the
rows in that column)
• Other data in the worksheet can be located outside the boundaries of the list
Here’s an example of an Excel list:
The cells in the first row of the list that con