There are many modern data visualization and data analytics tools used for manufacturing intelligence (MI).  Some, such as Microsoft’s PowerBI, have advanced data visualization capabilities that make products such as Excel seem rather dated; yet, Excel continues to be widely used. To understand why, let’s look at some examples of how Excel is currently being used by our clients:

• Whatever BI/MI tool is used, inevitably users want to export the data to Excel.  Users want direct access to the data so they can then build their own reports and charts, when they need something different than the predefined reports give them.  We have developed many reports using SQL Server Reporting Services, and most of these reports were designed to make it easy for users to export and use the data in Excel.
• The PowerBI Excel add-in is used to directly import data from databases. Within the PowerPivot data repository, tables can the joined in relationships and then used in pivot tables and charts.  PowerBI is a very useful for grouping, filtering, and drilling-down into data stored in multiple tables.  It can be a fast and interactive way to work with large datasets.
• The OSISoft DataLink add-in and the GE Historian add-in allows historian data to be pulled into an Excel worksheet from the data historian.   These add-ins work fine, but for an even more powerful solution, the add-in functions are used with the VBA programming environment.  We create reports using the VBA functions that make it very simple for users to get and report on historian data. 
• Directly link to database data sources and query the data into worksheets.  From there the data can be used directly or referenced from other sheets and pivot tables.  This is a frequently overlooked use of Excel to get read-only data from databases.   Recently, a client needed a more advanced use of this feature.  In this case, a rolling window of 10 years of data from hundreds of measurement points was needed for many charts and summary tables.   We provided a solution that pivoted the data and presented the data in a stable table view that always uses the same cells for each measurement point.  The user could then build tables and charts that absolute cell references and reliably link to the same measurement point.  The charts continue to work correctly after data refreshes or adding/removing points from the pivot table, since the data remains aligned correctly in the same location in the data sheet. 
• Connect to PLCs to read and write data patterns.  In cases where the data pattern can be used to change how the program behaves, this is a very powerful function. 
• Build a simple automatic data logger in Excel.  For sure, most people won’t ask to do this, but it has be done.  Using real data logging software is a much more robust option, but Excel can be setup to read data periodically and write it to a database.  You will of course you need to keep the workbook open.  On the other hand, you won’t need to buy more software; so this can be useful for a temporary data logging project.

Not everyone loves Excel of course, but it is ubiquitous so most engineers and data analysts have Excel and know how to use it.  It is easy to customize the workbook layout, tables, and charts, and to add calculations.  Once users have access to databases via Excel – they have control.  They also get the opportunity to build creative solutions for analyzing and visualizing data; therefore Excel continues to well-liked by many end users.

There may be better software than Excel for data analytics and data visualization, but Excel remains an excellent toolkit that continues to improve.  It provides a huge range of options for obtaining and working with data and it is still relevant for MI applications.  Excel is most suitable when used by trained engineers and power-users as part of a set of data reporting and analysis tools.  I don’t expect new reporting software will fully replace Excel, but users will find they have more options and can continue to use Excel when it is the right tool for the job.  The Hallam-ICS MI team will continue to work with clients to build and support useful data reporting applications using Microsoft Excel.

About the Author

Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.

Read  My Hallam Story  

About Hallam-ICS

Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.