Invalid & Valid Database Design in MS Excel

One of the most useful features in MS Excel is the ability of using it to handle small and medium Databases. We can analyse or summarise data by grouping them as we wish. The features such as Auto Filter & Advanced Filter, Sub Totals, Data Tables, Pivot Tables & Pivot Charts, Group & Outline make our life easier with Databases. On top of these, there are two wonderful features called Data Validation & Conditional Formatting.

Anyway, before using MS Excel features, we have to create a Proper Database. If we stick to the following “Rules” there won’t be any “unexpected” errors in handling a Database.

Rule 1 : 1st Row of the Database should contain Column Headings or Field Names. From 2nd Row onwards you can enter Data.
Rule 2 : Do not duplicate Field Names.
Rule 3 : Do not keep Blank Field Names.

Incorrect Design

Incorrect Design

Incorrect Design

Rule 1 is violated. Do not keep extra line in between headings and data. To have extra space, increase the Row Height.
Rule 2 is violated. Even though you have used two lines for headings, only 2nd row will be considered as headings. Note that there is NO heading for column 2,.
Rule 3 is violated. For columns 3 & 4, the field name is duplicated.

Correct Design

Correct Design

Correct Design

This entry was posted in 07. Spreadsheets. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s