Things to consider when planning a spreadsheet - best practice approach

Key things to consider when designing a spreadsheet

Somethings we need to consider when we plan to create a spreadsheet for our work:

1. We need to think about (plan) our spreadsheet before we start so that we can eliminate the time consuming effort to modify and edit the sheet later when we have encoded a lot of data into it.
2. When you decide on a consistent column naming convention for your sheet. There are three common conventions:
1. Snake Case
2. Camel Case
3. Pascal Case (Upper Camel Case)
3. We need to decide on what information you want in the sheet. Each type of data would be entered into a column. 
4. It is important to keep the data in the columns simple. Do not try to put too much information into a single column. 
5. We need to consider how useful is the data in each column and if we can use it for metrics (M&E).

Case Study: BSIF Computer Hardware Inventory

Column Naming Convention

As you can see the column names have "no" naming convention. Some are all capitals with space and some are first letter capital following lower case. Most have spaces between words and there is one in parenthesis (bad practice).

It is recommended that column names have: (i) no spaces; (ii) no special characters like ":" or "(" and ")". You can separate words with an underscore "_" or "-", or use Camel or Pascal Case to separate words with a capital letter.

Column Names using Pascal & Snake Case

I have added a row below the original column names and inserted the equivalent names in  Pascal Case which is highlighted in yellow. Below that row we have column names in Snake Case. This case is commonly used in Python programming.

Spreadsheet Data Content

It is important for you to be consistent with the way you enter data into the column fields of the sheet. For example under column A, DeviceType, there are two types Desktop and Laptop. If you decide that the first letter should be capitalized then "all" the records in that column should have the first letter of the word in caps. AssignedTo in Column D should be separated into two columns FirstName and LastName.

Staff Names

Staff names first and last names can be combined in one column, but a better practice is to separate them into two columns: FirstName and LastName. It makes it easier to search for a person's first or last name in the sheet and in the database it is uploaded as a table to one.

Mixed Content in a Column

It is not recommended practice to combine a lot of mixed information in a column field. For example PCSpecs in Column F contains a lot of information such as: (i) 64 or 32 bit processor; (ii) version of Windows OS (Win11); (iii) CPU version; (iv) RAM; (v) hard disk storage capacity (GB).  

It is not only difficult to be consistent in entering the combined data it is also very difficult to generate a query that will extract some information you may want in the mixed string, such as what is the average RAM of the hardware used by BSIF staff?

It is better to separate the data included in the PCSpecs column into five new columns: (i) Bit; (ii) WindowsOS; (iii) CPU; (iv) RAM; (v) HardDrvie. 

Additional description to these columns can be included in a data dictionary or the operations manual, such as Bit is 32 or 64; WindowsOS is XP, 7, 8, 10 or 11; CPU version is Gen i5, i7, etc., and RAM and HardDrive capacity is in GB.

Suggest ProcurementDay be ProcurementYear with the year of purchase only in the column cell. You do not need the next column PCAgeYears because you can easily estimate the age in years form the year the computer was purchased.

Similarly you can replace Department with Unit and just enter the Unit name in the cell. It will make it easier to search. 

A simple data dictionary and manual can be saved for computer inventory. The modified spreadsheet can easily be uploaded into a database management system. That process could be included in another tutorial.