Engineering Lists in Excel

One of the common uses that Engineers have for Excel is maintaining lists. The lists could be of tangible materials like a list of mechanical equipment (such as pumps) or that of intangibles like a list of signals over a communication channel.

Of course, lot of people use Excel for preparing lists, but the use of an Excel List in an Engineering environment has some unique characteristics:

  1. A list prepared by an engineer is often used by different disciplines. Everyone using the list should have a clear understanding of the list headings.

  2. Lists used by engineers often undergo lots of revisions. This is particularly true in an engineering design environmment. Change management, is therefore, important.

  3. Changes in one list often impact another list, example, change in the pipe size (on a piping list) can affect the specification of a flow meter on the line (the flow meter is listed in the instrument list). For many jobs, especially small ones, there is often no justification to purchase a specialised system for managing such linked data. An administrative procedure is often required to manage such changes across different Excel lists.

An Excel worksheet is essentially a flatfile database and cannot cater to all the needs of an engineering design function out of the box. However, a lot of time and unnecessary rework can be saved by following some good practices for designing and maintaining lists in Excel.

  1. First and foremost, a lot of thought must be put into the structure and work flow before creating lists. Clear responsibilities for creating, maintaining, checking and approving the lists must be assigned.

  2. Secondly, thought must be put into the design of the individual lists. What are the important fields? Which fields depend on other lists and how will this be managed? What kind of reports would be required from the lists?

  3. The list should be laid out such that it is easy to make data entries without having to navigate a lot around the spreadsheet. For example, it is good to keep calculatied fields towards the end of the columns where the data entries are to be made instead of having them in between columns where data has to be keyed in.

  4. If multiple people are in charge of data entry into the same list, it is important to ensure that all data is entered consistently. A common example is in the use of abbreviations. For instance, the term "Distillation Column" could be abbreviated as "Dist. Col.", "Dist Column" or "Dist-Col". Using different abbreviations makes it difficult to analyze the data at a later stage.

  5. Data validation rules should be applied to minimize keying in invalid data.

  6. It is not a good practice to have blank rows or columns to separate sections of data. End of row or column is usually treated as end of data by Excel and can make it hard to filter and sort data.

  7. Another bad practice is the excessive use of text or cell formatting to identify different kinds of data. For instance, some engineers strike through text to indicate that the entry is deleted. However, this is not the same as deleting the row and can cause issues in analysis. For example, a count of the entries in the lists will not exclude the cells that have the text struck through. A better option is to have an additional column with the text 'DELETED' against the rows that are to be considered deleted. Alternately, a separate sheet can be used for the deleted data.

  8. In the newer versions of Excel, it is possible to filter cells by colour but it is better to avoid using colours in lists. Conditional Formatting is better for highlighting data that meet certain criteria.

    My friend and programming wiz Mr. Hadi Ariakia has cautioned that the use of conditional formatting for very large lists (containing more than ~20,000 rows) can cause slow down in performance because of the amount of recalculation involved.

Also See:

Engineering Drawings in Excel