|
How to Create a Microsoft Excel Database
by Chris Le Roy
The term Microsoft Excel Database has never really sat very well with me since
my primary role in the IT world has been in developing database and what is used
by the spreadsheeting fraternity is what I consider an over glorified list.
However, for the sake of peace, I want to outline some of the principles you
must follow in building what is known as the Microsoft Excel Database.
Let us review ...
A database in essence is what is considered a collection of information that is
related in some manner. For example if you were running a company and selling a
product you may have a database that simply lists all of the sales you have made
over a period of time. Storing this data in a database would make sense as the
company information and the selling of a product is related and as such would be
appropriate for the database.
There are many different types of databases available such as Microsoft Access
Databases, Oracle Databases, MySQL databases and so on but Microsoft Excel also
has a form of a database known as a database list.
The form of the list is virtually the same as the other databases as the data
is under column headings in rows, but after that common point, the Excel database
goes in its own direction.
See, to look for specific data within a Microsoft Excel Database or Excel List
we do not use the common database language of SQL, we actually use specially written
functions. These functions are custom written by you and are known as criteria.
So how do we create an excel database...
Well first off, there is one rule we must always follow and that is one excel
database per worksheet. Anymore and you just get yourself into lots of trouble.
In fact if you need to have multiple excel databases within your workbook simply
put each excel database onto a separate worksheet.
The next thing you must follow is that your database lists first row must contain
the heading of the list. That is the first row contains your field names. Plus
each of the field names must be 100 percent unique. You cannot have two field
names with the same name or again you will have a list that will not work.
The next issue you need to be concerned with is identifying the field names.
Excel databases have a simple rule, the field names or column names must be
unique. Now the way you identify them is easy, all you have to do is to ensure
the field names are many different data types, format, pattern etc. to the rest
of the database in your list. Generally what I do is to format my field names in
bold to satisfy this requirement.
One of the most important rules you must remember when you create an excel
database is that around the row and columns of the fields and data there must be
a blank row and column. What this means is that you can still have a heading at
the top of the fields, but there must be a blank row between the heading and the
fields as well as along the last column as well. The blank row rule also applies
to the bottom of the list as well.
| 
Free Downloads
RSS Feed