Cataloging loop drawings, tags in a spreadsheet

How a refinery used Excel to document its automation infrastructure during a DCS upgrade. Some simple tools can keep it all in order.


Frontier El Dorado Refining Company often upgrades its process automation infrastructure to increase product throughput and meet environmental requirements. Such projects demonstrate the challenges of dealing with hundreds of signal wires, software tags, and control functions typical of a modern DCS (distributed control system).  To improve the speed and accuracy of developing DCS loop diagrams, Frontier developed a user-friendly, Microsoft Excel-based spreadsheet application that serves as a preliminary design, documentation, and index tool for DCS instruments. This imaginative solution for instrument tag tracking and loop drawing has worksheets that can be used to validate and index instrument tags, model the physical equipment installation, and produce junction box, I/O module, and loop drawings.


Located just outside El Dorado, KS, near the scenic Flint Hills, Frontier’s El Dorado refinery is the state’s largest petroleum refining operation. Spread over 1,000 acres, this fully integrated, modern facility currently employs over 400 Frontier personnel and 100 contractors.

At the El Dorado refinery, a collection of complex operations, including distillation, desulfurization, fluid catalytic cracking, alkylation, delayed coking, catalytic reforming, isomerization, and sulfur recovery, contribute to a daily production of 60,000 barrels of gasoline, 40,000 barrels of distillate fuels, 900 tons of petroleum coke, and 175 tons of elemental sulfur.

Refining began on the El Dorado site in 1917 following the discovery of huge oil reserves in the area. The facility then consisted of a primitive skimming plant with a daily capacity of 2,000 barrels of crude oil. Today, the refinery is rated at 110,000 bpd throughput, processing crude oil and natural gas liquids to serve customers in Plains and Rocky Mountains states.


Like other petroleum refining operations, Frontier faces increased global competition—requiring continual improvement of its process automation technology. The El Dorado facility has performed many control system conversions over the years, advancing from pneumatic controls and single-loop panel board instruments to a modern DCS.

In 2007, Frontier converted control of an operating crude unit from a legacy TDC2000 basic system to Honeywell Process Managers (HPMs). The refinery employed innovative techniques for hot cutover of its outdated control system to a new, faster DCS using the same local control network (LCN) operator interface.

The refinery is now controlled by a DCS with seven LCNs from two different control rooms. The overall plant automation architecture encompasses 147 LCN nodes, 35 redundant HPMs, and 172 TDC2000 basic controllers, all connected to a Uniformance PHD process historian. The refinery is currently upgrading its processes to meet environmental regulations and increase throughput. Honeywell Experion is an integral part of the plan. While this discussion is based on that specific upgrade experience, the same concepts are applicable to any number of similar platform changes.


For instrument and control engineers, loop diagrams illustrate automation strategies and wiring connections within the overall control system architecture. These diagrams are essential for installing equipment during the greenfield construction phase, and for troubleshooting and maintenance issues once the process is running. Loop diagrams can be as simple as one input and one output, or complex with a mix of several inputs and outputs.

For example, a pressure indicating loop diagram would show a pressure transmitter and the wiring and connections from the transmitter to an input module. A typical control loop would include a transmitter and wiring to a DCS input module as well as wiring and connections to a valve from an output module.

Modern DCS I/O signals may be analog or on/off (digital) and are displayed and manipulated in the control system through software configuration. Field instruments are scattered all over process units, and their signals are transmitted to the DCS by electric current carried on a pair of wires. For efficiency, multi-paired cables are installed from the DCS rack room to junction boxes strategically located in the process units where field instruments are connected.

In the rack room, cable wire pairs are fanned out and connected to input/output modules (IOMs), which complete the electrical circuit and make the field signals available to the DCS. Software tag names are assigned to the I/O module channels corresponding to connected field instruments.

Engineering challenges

In years past, preparing loop diagrams was a relatively straightforward task. All connections were hardwired and drawings conformed to ISA standards. But with the advent of DCS technology in the late 1970s, control system engineers were faced with configuring a myriad of software connections. This created a dilemma: What should a loop diagram look like? How much software should it show? What about hardware?

Software functionality can be difficult to illustrate. In many cases, plant standards for instrument documentation and format are not well defined. This situation is further complicated when several different design and construction contractors are involved in the work.

On most plant automation projects, contractors are required to provide loop diagrams as part of their bid package. However, loop diagrams usually come late in the project, and aren’t always available for configuration activities. Plus, tags are often shown on many different drawings, making them difficult to track. It is not uncommon to have duplications or omissions of instrument tags among the plethora of project drawings.

In a DCS project, there are thousands of pieces of data that must be tracked and relations coordinated. For example, tag numbers, cable pair numbers, I/O module channel numbers, and drawing numbers are all related to each other in some way. This related data can be viewed from different perspectives (such as tag number, cable pair number, or I/O module) and makes sense in that order. However, all related data looks random when it’s viewed out of order. For instance, a large amount of data ordered by tag number looks random from a cable pair number or I/O module perspective, while the same data sorted by cable pair number appears random from the tag number or I/O module perspective.

An Excel workbook database allows large amounts of data to be randomly entered and then to be sorted or filtered into logical order.

Installation method

Frontier’s method for field instrument installation, which is based on the Rule of One (i.e., one field instrument, connected to one homerun cable pair, connected to one FTA channel), has resulted in significant time and cost savings.

At the El Dorado refinery, control equipment is housed in a remote instrument enclosure (RIE), with 36-pair homerun cables extended to junction boxes in the process area. Every wire of the 36-pair homerun cables is landed on terminal strips on each end of the cable. For easy identification, the terminals are labeled by cable pair number + and -. Field instruments are connected to an assigned cable/pair in a junction box, then a jumper wire in connected between the corresponding terminals in the RIE to an assigned I/O module channel. 

The RIE home run terminal strips are the de facto demark point for contractor responsibility. The construction contractor brings cables into the terminal strips and simply terminates them in pair order. Cross-connect wiring from the RIE cable terminals to FTA channels can be done at the factory or in the field.

Spreadsheet application

To reduce the time and effort required for control system engineering, Frontier developed an Excel-based workbook application that serves as a preliminary design, documentation, and index tool for DCS-related information. Within this innovative spreadsheet application, data can be easily entered, then sorted or filtered to provide the most useful viewpoint for I&C engineers. Each worksheet has a specific purpose and is designed for ease of use. Some worksheets are designed to model the physical DCS I/O installation, and once set up, allow instrument tags to be assigned. Other worksheets serve as an index to information relevant to instrument loops, but found in other documents.

The spreadsheet tool uses named ranges and lookups extensively to populate graphic templates and for data validation. Templates are provided for field junction boxes, I/O modules, TI MUX boxes, and loop diagrams. These drawings are a valuable aid for engineering, construction, configuration and maintenance activities.

The Excel auto filter function is helpful for viewing selected data, and row counters are provided on each worksheet to indicate the number of rows selected by filter action. Tags entered on a worksheet are validated using a count function to check for duplicates and a lookup function to insure that the entry is registered on the P&ID worksheet. Lookups are also used to cross-reference tags to other worksheets.

Using the workbook

The Excel-based application was designed with a unique workbook structure providing I&C engineers with a faster, more accurate way to produce instrument junction box and IOM drawings. This application has an instrument database where data is logically entered and used in many different ways for configuring plant automation strategies.

Three worksheets provide an index of instrument tags to drawings. Information from the P&IDs (piping and instrumentation diagrams) and plot drawings is inserted into the P&ID and Plot Worksheets, respectively. Tags from electrical and detailed drawings are entered into the Reference Worksheet. This approach enables an effective cross-index from instrument tags to drawings.

P&IDs are the basis for instrument and control system requirements. The P&ID worksheet provides sufficient information to size and specify DCS components and most of the field wiring infrastructure. When the I/O requirements have been determined (i.e., number and type of input and output points), the engineer can design the system’s structure. Three worksheets provide an ordered list of all homerun wire pairs, I/O channels, and TI MUX channels. Every possible pair of wires in all of the homerun cables is listed in the Instrument Junction Box (IJB) Worksheet. The IOP Worksheet lists every input and output module channel, while the MUX Worksheet lists every possible TI multiplexer channel.

After the control system structure has been determined, the engineer begins the task of assigning tags to homerun cable pairs, input and output module channels, or TI mux channels. The workbook application has built-in crosscheck functions to identify tag duplications and validate the tag name to ensure it appears on the master P&ID worksheet.

The Excel application also provides graphic drawings suitable for printing. Loop drawings are made by copying an appropriate template from the templates worksheet and copying it to the diagrams worksheet. When a field tag is entered, Excel’s lookup function searches all of the other worksheets for information related to the tag, and automatically populates data fields in the diagram.

Typical examples

The following screen shots show the worksheet structure and layout of the Excel Workbook:

P&ID Worksheet: TAG registry, definitions and P&ID drawing index

P&IDs are the basis for instrument and control system requirements, and the blueprint for configuration. Using the P&ID worksheet, a manual take off of instruments can be quickly accomplished. When finished, sufficient information is available to size and specify the DCS system components and most of the field-wiring infrastructure.

Each row on the worksheet represents an input or an output to the control system. The “Field Tag” column is the key reference on this sheet. All other columns define the field tag in some manner. The person doing P&ID take-offs must possess knowledge of P&ID drawings, instrument loops, tagging methodology, field wiring practices, and DCS configuration.

After a device tag is entered on the P&ID sheet, it is defined by signal type, wired device, end device, and DCS tag. Drop-down selection lists are provided in most cells for ease and consistency. Loop number and P&ID number also categorize the tag. The sheet is sorted by “Loop No.” – “Field Tag” – “Signal Type.”  All data entered on this sheet can be printed on an appropriate loop diagram.

When the P&ID takeoff is complete, Excel data filter and row count functions may be used to quantify information. For example, when signal type AI is selected as a filter, only AI rows are displayed, and the row counter tells how many there are. As you can see, determining the total number of AI, AO, DI, or DO signals is quite easy. This information is useful in determining the quantity of I/O modules that are necessary. A wealth of information can be gleaned from the worksheet in a similar manner.

It is apparent that the P&ID worksheet serves as a registry of field I/O instruments and also provides a high-level definition of each one. Since duplicate “Field Tag” entries are not allowed on this sheet, an Excel function is used to check for duplicates and to provide a flag if a duplication is found. Excel lookup functions also show if the “Field Tag” is entered on other sheets. These simple lookups are a convenient way to make sure that all field tags are accounted for.

Description worksheet: Loop registry and functional description

This sheet serves as the loop number registry and contains a list of all possible loop numbers. When a number is assigned, a short functional description is entered. This activity should occur during the manual P&ID take-off. The format ensures that loop numbers are not duplicated, and makes it easy to find unused numbers. Functional descriptions are printed on loop diagrams, aiding in DCS point configuration.

An Excel count function shows how many field tags are associated with each loop no. on the P&ID Worksheet. The sheet is ordered by loop number and should not be sorted.

(Assumption: Loop numbers are five digits; the first two digits are the unit number and the following three digits are a serial number.)

Plot worksheet: Tag to Plot drawing index

Plot plan drawings illustrate where equipment and instruments are physically located within a process units boundary. Therefore, they are useful in determining where junction boxes should be located. The Plot worksheet serves as an index of field tags to plot plan drawings. Instrument field tag numbers are manually taken off drawings and entered on the worksheet along with the drawing number. This data is useful in determining where instruments are physically located and will be included on loop diagrams.

Tags can be entered randomly during the plot plan takeoff, and then sorted by Loop / Field Tag.

References worksheet: Tag index to miscellaneous reference drawings

Often times, instrument tags are shown on various detailed drawings. This worksheet is intended to serve as an easy reference by indexing two such drawings. A note field is also included for short free format reminders. Both reference drawings and the note are printed on the loop diagram.

Tags can be entered randomly, and then sorted by Loop/Field Tag.

The four worksheets just described provide the following:

  1. A list and rough definition of all field instruments, ensuring that there are not any duplicate tag names;
  2. A list of possible loop numbers and a functional description of each number assigned. The purpose is to eliminate duplicates, but of course, this is dependent on the skill of the individual preparing the P&IDs in the first place;
  3. An index to drawings, containing instrument tags; and
  4. A straightforward method to determine requirements for the DCS and field infrastructure.

When requirements are determined, you enter the system engineering phase.

Cable schedule worksheet: Cable registry

The Cable Schedule worksheet serves as the registry for homerun cable numbers. It contains a sequential list of possible cable numbers and corresponding junction box numbers. When a number is assigned, an entry is made listing “from” and “to” designations. This format ensures that cable numbers are not duplicated. A column is also provided for length, and this is invaluable information if an installed cable is damaged and needs to be quickly replaced.

Data in this worksheet is not included on loop diagrams. (Note: Cable numbers consist of two digits representing the unit, and two digits being a serial number)

System worksheet: DCS layout and physical arrangement

This Excel application is designed for Honeywell C300 controllers using PM input/output modules. Physical DCS limitations are 40 I/O modules on link one, and 24 modules on link two. In practice, I/O link two should be restricted to remote T1 MUX IOMs. This practice results in 40 slots in three files, which can be assigned analog or digital I/O modules. It is a good idea to look at the mix of I/O signal types on homerun cables when determining what type of module to put in each file slot. This practice simplifies installation of jumper wires between homerun cable terminals and I/O module FTAs.

The System worksheet lists up to 64 IOM file slot locations and includes a drop-down menu so the IOM types can be easily selected. A lookup function determines the FTA model number used with the selected IOM type, and standardized layout procedures dictate where FTAs are located on the marshalling wall. Information from this worksheet is useful in ordering and assembling the DCS, but is not included on loop diagrams.

IJB worksheet: Assign Tags to wire pairs

The IJB Worksheet is designed to model the physical homerun cable installation. Every wire pair of every multi-paired homerun cable is listed on the worksheet, ordered by cable and pair number. Instrument tags are assigned to wire pairs in accordance with physical proximity to junction boxes. During construction, field wires leading to field devices are labeled with a tag to identify them.

An Excel function is used to flag duplicate entries so errors can be corrected immediately. Lookup functions show if wire tags exist in the P&ID registry, and if they are assigned to IOM modules. Cable pair and junction box labels are included on loop diagrams. Tag names can also be pasted into a template to produce a junction box drawing that shows interconnect jumper wiring to FTAs.

IOM worksheet: Assign Tags to IOM channels

The IOM Worksheet is designed to model the actual DCS installation. Every input and output channel is listed on the worksheet, ordered by IOM and channel number. Instrument tags are assigned to module channels with consideration given to homerun cable loading. Jumper wires are then installed from the corresponding homerun cable terminals to the IOM channel on the FTA.

An Excel function is used to flag duplicate entries so errors can be corrected immediately. Lookup functions show if wire TAGs exist in the P&ID registry, and if they are assigned to homerun wire pairs. IOM channel designations are included on loop diagrams. Tag names can also be pasted into a template to produce a module drawing that shows interconnect jumper wiring to IJB terminals.

MUX worksheet: Assign Tags to TI MUX channels

The MUX Worksheet is also designed to model the installation. Every possible TI input is listed on the worksheet and ordered by IOM and channel number, which corresponds directly to MUX Box and channel number. TI tags are assigned to MUX channels in accordance with physical proximity to the field mounted boxes.

An Excel function is used to flag duplicate entries so errors can be immediately corrected. A lookup function indicates if the tag also exists in the P&ID registry.

Diagrams worksheet: Loop Diagrams suitable for printing

The Diagrams worksheet is a collection of templates populated with data representing indication and control loops on the DCS. Each template indicates the signal path from the field device to the DCS, and simply shows the function of software-configured points. Space is provided for comments and references to other documentation.

Templates are copied from the Templates Worksheet and pasted into the Diagrams Worksheet. When a field tag is entered, Excel lookups populated fields with data from other worksheets. This simple concept allows data to be entered once, but used many times.

Commonly used loop diagrams are included on the Templates Worksheet. Occasionally, a template will need to be modified to accurately represent a control loops function. If the modification becomes frequent, a new template can be made for it.

Each loop diagram is given a diagram number for reference. Diagram numbers are shown in column A on the worksheet, and printed in the lower left corner of the diagram. (The corner location is convenient for finding sheets when filed in a three ring binder.) Diagram numbers are also used to trace signals among related loop diagrams.

Print page breaks must be manually entered, so the user has the option to print one or multiple loops per page. Functionality is built into the worksheet to facilitate page break entry.

Templates worksheet: Templates for copying to the Diagrams Worksheet

Ten templates are included that represent common instrument loops. Each template is assigned an Excel name for easy access via hyperlinks or the name box on the tool bar. Templates are simply copied then pasted into the Diagrams Worksheet where they are populated with looked up data to make printable loop drawings.

Occasionally, a template will need to be modified to reflect a loop’s function accurately. A symbol library is included for copying and pasting to facilitate the modification process.

Drawing worksheets: Template drawings of Junction Boxes, I/O modules, and TI Mux Boxes

Three templates provide printable drawings representing: Field junction boxes, DCS I/O field terminal assemblies, and TI MUX boxes. Each worksheet provides a list of hyperlinks to field tags, which can be easily copied then pasted into the drawing worksheet. Excel lookups are invoked to populate data fields relative to the drawings purpose. This simple concept allows data to be entered once, but used many times.

Examples of printable drawings:

End user advantages

For process industry plants, the Excel Tag Assignment and Loop Diagram spreadsheet application, in conjunction with a proven wiring method, offers a simple, reliable, and cost-effective way to design and install new and upgraded control systems.

The wiring concept is simple:

  • A signal put on a wire pair at one end, must be taken off at the other end;
  • Cable and pair number (##** - ##) is the only piece of information necessary to identify termination points on either end of a multi-paired cable;
  • Installation is easy. Homerun cables are landed in pair order; therefore, drawings are not necessary for electricians to terminate the cables;
  • Homerun cables are easily replaced in the event of damage. Simply pull new cables and land both ends in pair order; and
  • Field instruments, both analog and digital, can be connected to the closest junction box.

Documentation is consistent and simple:

  • Data can be entered randomly, then sorted in a logical order;
  • Tags are indexed to drawings, validated, and checked for duplication;
  • Data is entered once, but used in multiple ways; and
  • Accurate drawings are produced by populating standard templates.

Using this engineering tool, plant personnel can quickly create, view, or print a graphical image of their control system from different data perspectives. They have the option of viewing control loops, including wiring, devices, and tags, from either a junction box, IO module, or loop perspective. The tool can also enable faster, more accurate AutoCAD drawings of the plant automation architecture.

Frontier Refining Company has reduced the engineering time and effort required for control system migrations and greenfield projects thanks to this easy-to-use application. As demonstrated at the El Dorado facility, appropriate installation, labeling, and tagging can greatly simplify system documentation and increase understanding of the plant I/O infrastructure.

Leland (Lee) Borgelt is a Senior Project Engineer at the Frontier El Dorado Refinery in El Dorado, KS. His primary duties include engineering and documentation of the refinery’s extensive Honeywell DCS systems. Lee was a Honeywell field service engineer when the TDC2000 was first released to the market and has over 32 years of instrumentation experience. He graduated from Pittsburg (Kansas) State University in 1972 and holds a B.S. degree in Industrial Electronics and an MS degree in Industrial Technology.

JERRY , OH, United States, 04/13/13 11:50 AM:

Those anyone have a contact for Leland(Lee) Borgelt. Need some help with CAP cert.
Karren , United States, 03/25/14 06:11 PM:

Thanks for the info! I have also found <a href="">P&ID Diagram Examples</a> through lucidchart that are very easy to use!
Vilas , India, 10/19/14 02:52 PM:

I am also trying to reach out LeLand desperately for sharing my work on similar lines. LeLand, if you see this message please write to me on
Devin , CT, United States, 02/24/15 09:32 PM:

Has anyone seen a copy of his spreadsheet? Its looks like a very powerful tool.
The Top Plant program honors outstanding manufacturing facilities in North America. View the 2015 Top Plant.
The Product of the Year program recognizes products newly released in the manufacturing industries.
Each year, a panel of Control Engineering and Plant Engineering editors and industry expert judges select the System Integrator of the Year Award winners in three categories.
2017 Lubrication Guide; Software tools; Microgrids and energy strategies; Use robots effectively
Prescriptive maintenance; Hannover Messe 2017 recap; Reduce welding errors
Safety standards and electrical test instruments; Product of the Year winners; Easy and safe electrical design
Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
Mobility as the means to offshore innovation; Preventing another Deepwater Horizon; ROVs as subsea robots; SCADA and the radio spectrum
Future of oil and gas projects; Reservoir models; The importance of SCADA to oil and gas
Diagnostic functions for system safety; Specifying industrial enclosures; Effective decision support for a crisis
Transformers; Electrical system design; Selecting and sizing transformers; Grounded and ungrounded system design, Paralleling generator systems
Natural gas for tomorrow's fleets; Colleges and universities moving to CHP; Power and steam and frozen foods

Annual Salary Survey

Before the calendar turned, 2016 already had the makings of a pivotal year for manufacturing, and for the world.

There were the big events for the year, including the United States as Partner Country at Hannover Messe in April and the 2016 International Manufacturing Technology Show in Chicago in September. There's also the matter of the U.S. presidential elections in November, which promise to shape policy in manufacturing for years to come.

But the year started with global economic turmoil, as a slowdown in Chinese manufacturing triggered a worldwide stock hiccup that sent values plummeting. The continued plunge in world oil prices has resulted in a slowdown in exploration and, by extension, the manufacture of exploration equipment.

Read more: 2015 Salary Survey

Maintenance and reliability tips and best practices from the maintenance and reliability coaches at Allied Reliability Group.
The One Voice for Manufacturing blog reports on federal public policy issues impacting the manufacturing sector. One Voice is a joint effort by the National Tooling and Machining...
The Society for Maintenance and Reliability Professionals an organization devoted...
Join this ongoing discussion of machine guarding topics, including solutions assessments, regulatory compliance, gap analysis...
IMS Research, recently acquired by IHS Inc., is a leading independent supplier of market research and consultancy to the global electronics industry.
Maintenance is not optional in manufacturing. It’s a profit center, driving productivity and uptime while reducing overall repair costs.
The Lachance on CMMS blog is about current maintenance topics. Blogger Paul Lachance is president and chief technology officer for Smartware Group.
The maintenance journey has been a long, slow trek for most manufacturers and has gone from preventive maintenance to predictive maintenance.
Featured articles highlight technologies that enable the Industrial Internet of Things, IIoT-related products and strategies to get data more easily to the user.
This digital report will explore several aspects of how IIoT will transform manufacturing in the coming years.
Maintenance Manager; California Oils Corp.
Associate, Electrical Engineering; Wood Harbinger
Control Systems Engineer; Robert Bosch Corp.
This course focuses on climate analysis, appropriateness of cooling system selection, and combining cooling systems.
This course will help identify and reveal electrical hazards and identify the solutions to implementing and maintaining a safe work environment.
This course explains how maintaining power and communication systems through emergency power-generation systems is critical.
click me