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:
- 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.
- Lists used by engineers often undergo lots of revisions.
This is particularly true in an engineering design environmment.
Change management, is therefore, important.
- 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.
- 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.
- 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?
- 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.
- 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.
- Data validation rules should be applied to minimize keying
in invalid data.
- 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.
- 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.
- 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.