The Cardinal Rules of Microsoft Access and Excel

Originally posted elsewhere on January 22, 2009, this post was inspired by my temporary assignment to Horace Mann Insurance in Springfield, Illinois. They encourage a cutthroat competitive culture and we temps had two managers. Can you guess where this is going?

The lead manager assigned me and another temp to retrieve service numbers from all the old PCs, so the data could be transferred to brand new PCs. Instead of backing up the data, replacing the old PC and then installing the old data on the new PC on an individual basis, they decided to retrieve everyone's data first, bulk load the data on new PCs in another part of the building, then at night bring carts loaded with PCs up to the offices and replace them.

The trick is knowing exactly which PC goes to which employee's desk. Knowing the exact location of a specific employee's old PC hinged on a Microsoft Excel database held on a Dell laptop with a bar code scanner. We had to scan hundreds of PCs service tags, and getting to those service tags often required climbing under desks or shutting down and undocking laptops.

Scanning issues:

We did the scanning during the day when employees were present, yet we replaced the PCs at night. We had to verify the employees information for the database. Some moved to different areas within the building, others were out for the day or left the company.

Then one day the lead manager decided to take two weeks off. The second manager decided he wanted me and my associate to do other things for him instead of completing the scanning, so we kept getting pulled away from our primary task.

I finally insisted that I needed to complete my primary task, but because I defied him in favor of the lead manager's instructions (while the lead manager was away on his vacation) Express Temporary Professionals called me one morning and told me that they were told I was a bad worker and didn't want me back.

In the beginning of the assignment, the lead manager led me to an unoccupied desk in a storage area where I was to lock away the laptop while it wasn't in use. The key was kept somewhere else at the office so other employees could gain access to it. I later got a call from Express asking if I had stolen the laptop. I had to explain where it was so they could find it. I don't know what happened to my associate who knew where the laptop was located.

Now, finally, the original post that dealt with the Microsoft Excel spreadsheet we used:

The Cardinal Rules of Microsoft Access and Excel
January 22, 2009 at 12:59pm

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