Excel – Automate your Automation

Probably the most useful tool I have in my arsenal when doing preliminary design is Microsoft Excel. In many of the projects I have worked on over the last couple of years since I left Wright, the company I was contracted to didn’t have a template or guidelines for defining the project, there was simply a description of a machine or an electrical schematic, or the machine itself.

I am often amazed that a machine can be designed and built before the software is even concepted. The last company I did work for would usually have a machine almost completed by the time I got involved. The only documents I would be given were a set of AutoCAD drawings or .dwg files and the original quote to the customer. I would ask if anyone had an I/O list or software spec and get blank stares…

The first place in a machine’s design cycle where Excel is useful is the quote itself. Larger machine builders usually semi-automate the quote process by creating a list of parts for the proposed system with component pricing. The more items that can be accounted for on the front end, the more accurate the quote will be. Major components are easy to account for. PLCs, HMIs, enclosures, actuators, valves, framing and structural components, purchased subassemblies and test equipment make up a significant portion of the hardware list. Smaller items like terminal blocks, fasteners and small mechanical components can be estimated by category.

By having an Excel template with all of these categories listed one can ensure that nothing major is left out. Categories can also have labor estimating tools built into the spreadsheet. Each type of component takes a certain amount of labor to install or fabricate, and a lot of time can be saved by pre-assigning a number of hours by discipline; i.e. 0.5 hours per sensor for electrical design, 0.5 hours per input point for programming, 0.25 hours for each machining operation (cut, drill, etc.). As a component is entered, each of these categories can be populated automatically.

If a machine is already designed I usually start by doing a take-off of the electrical schematics. I list all of the inputs and outputs on the machine by address, including spares. The AutoCAD labels are entered as descriptions next to each address, unused addresses are labeled as “spare”. I then start adding columns for the I/O to generate concatenated names for each point. As explained in my previous Input and Output posts, each I/O point will have additional addresses assigned to them in the program; debounced inputs, HMI indicators and pushbuttons, messages and Faults.

Another place to look on the schematics are the communications drawings. If ethernet I/O or devices are used, the addresses can be listed on a separate page.

A useful tool in the spreadsheet is the concatenate command. This allows predefined labels such as HMI_PB (pushbutton) or HMI_IND (indicator) to be inserted into the tag or comment like this: Pusher_HMI_PB_Extend or Pusher_HMI_IND_Extended. Fault bits, message triggers and servo drive parameters can be treated in a similar way. This means that for every I/O point in the initial column, several tags can be generated. By sequencing bit addresses, memory registers can be similarly assigned (Bit_0.0-Bit_0.15, 1.0-1.15, etc.).

Obviously it helps to have a template where all of the columns are set up beforehand, but it still saves a lot of time to prepare the fields as you go. You can always erase the contents of your cells leaving the formulas and formatting to save as a template.

The pictures at the top of this page shows a lot of graphics that are used with or generated by Excel. Graphs and charts are common, but the possibilities go well beyond that. Visual Basic for Applications (VBA) allows the creation of user forms for data entry, pushbuttons and checkboxes, list selection tools and the embedding of ActiveX objects. Ten years ago when I had my machine-building company, an engineer who worked for me developed a complete HMI using Excel! He had found that objects on the spreadsheet could be linked to tags in the PLC by use of Allen-Bradley’s RSLinx driver. He placed various controls on a sheet and could operate the machine while at his computer even when his programming sofware wasn’t open. This saved him from having to get up and operate the machine from the touchscreen. (Thanks Larry Roberts!)

When using Excel along with VBA and OLE, OPC and ActiveX components, the possibilities are nearly endless. Excel can be used as design tool, a data concentrator and even an interface with machinery. There are a great many websites with tips on how to use formulas and VBA, so I am not going to go into detail in this post. Suffice it to say that it is probably my most useful tool outside of brand-specific programming software and AutoCAD.


Electrical Engineer and business owner from the Nashville, Tennessee area. I also play music, Chess and Go.

4 Comments on “Excel – Automate your Automation

  1. I’ve always wanted to get more into this. Would you mind sharing any files to that someone like me could as a references or template?

  2. It is true that you can use a spreadsheet to communicate to a PLC. Indeed Siemens provides add-ins to permit access to the PLC registers (Or at least they did for the S7-200 series). This is useful if you want to create a configuration / information sheet to update the project.
    In fact, years ago I used Lotus 123 with a communications add-in to a Westinghouse PLC such that the spreadsheet could display production information. Since I’d used the menu features of Lotus 123, I had created an HMI!

  3. It helps to have “a template where all of the columns are set up beforehand”.

    Can you upload an Excel spreadsheet example(s), and link it in your above content? Especially one you used to help specify a larger project? My projects seem to be moving in this direction in the not too distant future.

  4. Lots of folks asking for examples and templates for both quoting and I/O design. Unfortunately right now everything I have is either proprietary or actually belongs to someone else. I will try and create a couple for my new Automation Academy site, but it will take some time.