The Cardinal Rules of Microsoft Access and Excel

I had a temp job once going around taking inventory. I was given a laptop with Microsoft Excel on it with an inventory list. I was to enter the numbers in the cells. I had a difficult time with it because the person, the moron who made up the spreadsheet broke every single one of the following Cardinal Rules.


I. If it can be subdivided it must be subdivided.
a. The more you subdivide information into separate columns, the more
versatile your database will be in the future when you want to develop
charts for presentations, interactive maps, and other codependent
documents like mail merge or XML.

II. Be specific when using column headers.
a. Make sure you can wake up the next morning and know exactly what
should go in a column by its header description. If need be, you can use
the “Insert Comment” to further describe the content requirements.

III. Never a full name in one cell.
a. A first middle and last name are three different things. Use three different
columns.

IV. Never first names first.
a. Why always start with last names first? I’m not sure. The reasoning might
be cultural in origin, but it is the official standard of phone books,
libraries, and the government.

V. If you must use nicknames, give them a separate column.
a. If you substitute someone’s formal first name with a nickname, later you
will never find Rick no matter how hard you search for Richard or Dick.

VI. Dividing punctuation requires dividing columns.
a. If you have a comma or semicolon in several cells, you’re in trouble. If
you know you will need three or more items as part of a record, for
example, the identity of multiple printers a person must access, then set a
limit of five and create five columns.

VII. Special requirements need special paperwork.
a. Create an external document reference column.

VIII. Three dimensions require three columns.

IX. Pick one way to describe something and stick with it.

X. Pick one column order and stick with it.

XI. Keep comments to the far right.
a. Columns labeled “Comments,” or “Reason,” are regularly ignored.

XII. Use complete dates.
a. Be sure to include the year, month, and day every time you use a date.

XIII. Keep time out of the date column.
a. The time and date format in Excel is too long to effectively sort and will
cause problems down the road.

XIV. Get all the names before you start.
a. Make sure you know exactly what you need before you start. If rows need
to be added in the process of data collection, you will add hours to the
process of reconciling the remote database with the central database.

XV. Keep a column for sequential record numbers
a. If you want to import your Excel spreadsheet into an Access database, you
will need a “Key” field. Each record should have a record number field
because you won’t be able to import the row number from Excel as your
key field in Access.

XVI. There will always be more rules, write them down as you discover them.