Article Categories
» Arts & Entertainment
» Automotive
» Business
» Careers & Jobs
» Education & Reference
» Finance
» Food & Drink
» Health & Fitness
» Home & Family
» Internet & Online Businesses
» Miscellaneous
» Self Improvement
» Shopping
» Society & News
» Sports & Recreation
» Technology
» Travel & Leisure
» Writing & Speaking

  Listed Article

  Category: Articles » Technology » Computers » Article
 

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% 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.

When you are entering data into your list, every cell in every record must contain some value even if it is simply blank (a blank value is still considered a value) and each record must contain the same number of fields. If there is no specific data for a field you simply leave it blank and move to the next field.

Ensure that when you are entering data into a field that you don't have spaces before the text or at the end of the text in the field. If you do have spaces, then what will happen is that sorting and search for data in the list will be compromised and you will get unexpected results.

Upper case and low case characters in the field do not affect the searches or sort orders unless you specifically tell the Microsoft Excel application it is an issue. You can also use formulas in a cell if required. Formulas can refer to cells within the Excel Database List or outside of the Excel Database.

Note also that you can edit and format the cells just like any other spreadsheet however the issue you must consider as a priority is that the field names must have a different format to the rest of the data in the database list. It is highly recommended that there be no other formatting in the list except for the field headings. This ensures that there are no miscalculations by the application as to what is a field heading in the excel database and what is not.

Now that you have setup your list in this way following these rules, you are now ready to interrogate the list by applying criteria. The easiest way that you can do this is by using the Form dialog box. To get into the Excel Database form you simply choose the Data menu and then choose Form from the drop down menu.

From the Excel Database Form you can simply choose the Criteria button, type the criteria you have for your data and choose the Find Next button and it will take you to the first record that satisfies the criteria you are searching. Excel Databases are particularly useful for summary data that is where the volume of records you have in your database doesn't exceed 65,536 rows.

If you follow these rules to create an excel database you will find that the functions associated with the excel database list will work in an effective and efficient manner.


 
 
About the Author
Chris Le Roy has developed a number of Microsoft Office Tutorials including a Microsoft Excel Tutorial and a Microsoft Excel Invoice Template.

Article Source: http://www.simplysearch4it.com/article/32455.html
 
If you wish to add the above article to your website or newsletters then please include the "Article Source: http://www.simplysearch4it.com/article/32455.html" as shown above and make it hyperlinked.



  Some other articles by Chris Le Roy
How Meditation Will Help You in Learning the Path to Enlightenment and Ascension
The world is made up simply of energy. That is right … what causes the beats in our heart, what makes our minds work, ...

Four Meditation Techniques New Meditators Should Know to Help Their Meditation Voyage
Learning to meditate is one of the most important skills you can master and the sooner you learn to meditate the sooner you will gain more control over your life. Meditation is ...

Four Things To Consider When Choosing Your Meditation CD's
Meditation is one of the most important activities you need to do each day. One of the most important tools you need when starting to learn how to meditate is the type of meditation music you use. ...

Guided Meditation vs UnGuided Meditation
The challenge new meditators have when learning meditation is whether to use a guided mediation or to use an unguided meditation. Our objective in this article is to look at both meditation types so that you can ...

What You Need To Do To Setup Your Own Meditation Group
Having your own meditation group is a great way to meet new people but it is also a great way to learn new meditation techniques. As you begin your ...

Outstanding Headline Reaps In $300,000 Cash
Let me start by asking … "What did you think of the headline?" Well it must have been pretty enticing for you to be reading this article. True. ...

  
  Recent Articles
Microsoft Word. The power is under the hood
by Robert Duval

How To Select The Best Gaming Laptops
by Tim Childree

How To Select The Best Business Laptops
by Tim Childree

High-Tech Computer Rental for Business Promotion
by Michael Braganza

Managing a Worldwide Print Infrastructure from a Single Workstation in a Central Location
by Rick Ferguson

A Sneak Peek Into The World Of Refurbished Laptops
by Javier Fuller

Battery information
by goodbattery

Tell Me About Computers
by Khal Nuwar

Setting Up Your Own Internet Radio Station
by Jason Cole

Understanding Solar Energy
by Anna Stone

Mac FAXstf X Pro Faxing Program for Mac OS X Users
by Nivea David

How to Retrofit a Picture or an Image into Desktop Wallpaper for Your Computer
by Steve Dimeck

Can't connect to database