Engineering with Excel

The tried-and-true Excel spreadsheet remains an engineering staple despite an eye-popping palette of state-of-the-art technologies, from advanced simulation to product lifecycle management (PLM), each intended to bring efficiencies and productivity gains to design workflows.

In fact, spreadsheets remain in widespread use throughout the engineering and manufacturing ranks, and there are few signs that usage is abating, according to a recent survey initiated by the NAFEMS Simulation Data Management Working Group, which was also sent to Digital Engineering readers.

Most NAFEMS and DE survey respondents who are using Excel for engineering design calculations and modeling said its use is remaining constant.

Excel spreadsheets are extensively tapped by large and small engineering groups as an early concept tool for preliminary design work and engineering calculations, but also for later-stage activities related to engineering-to-order, configuration and pricing.

Specifically, greater than 80% of respondents to the survey reported use of “critical” spreadsheets for engineering design calculations and modeling while 30% tapped Excel for engineering-to-order/configuration-to-order/configure-price-quote workflows. Thirty percent remained heavily reliant on spreadsheets as a data repository.

What was even more surprising was the frequency of spreadsheet use: The vast majority of respondents reported getting their hands on the organization’s most critical spreadsheet daily (23%) or weekly (36%). A third of respondents estimated that spreadsheet was accessed by more than 10 people in the organization.

Even with a plethora of new technologies within reach, respondents said spreadsheet use was actively promoted by many in engineering management. Thirty percent of respondents to the NAFEMS survey said their groups were being encouraged to maintain use of Excel as part of the engineering workflow and 84% of those polled expected usage of Excel to either increase or remain constant. Only 11% of those surveyed said spreadsheet usage was on the decline. Another sign of spreadsheets’ sustained longevity: The average life expectancy of the most critical engineering spreadsheets was estimated at between one and five years, while more than 35% of spreadsheets kick around and are active for more than five years.

“Excel as a tool in doing what it does best—sorting data, sharing data and doing calculations—is very important and will remain important,” notes Tom Maurer, senior director, strategy group for Siemens PLM Software, who cited specific Excel functions such as macros, pivot tables, charts and conditional formatting among the key features that keep the Microsoft productivity tool a go-to workhorse for today’s engineering workflows. “But using Excel as a bill of materials (BOM) or a so-called “poor’s man’s database”—that becomes far more of a challenge.”

From there, engineering groups may begin to broaden Excel usage for database-type functions, initially dumping a bit of data from a test rig into a spreadsheet and expanding from there until they end up with more than they may have bargained for. “Before you know it, they have lots of big spreadsheets packed full of data and it’s not always the best way to do things,” Dewhurst says.

Siemens has built tight integration between the spreadsheet and PLM, including the ability to map imported Excel columns to properties in Teamcenter. Image courtesy of Siemens PLM Software.

Familiarity with Excel, coupled with its ease of use, give spreadsheets a leg up over product lifecycle management (PLM) platforms, which are often viewed as expensive, less user friendly and typically require significant IT resources to mount a large-scale, enterprise deployment. Excel is good at things PLM isn’t—inputting massive amounts of data and easily formatting changes.

“The biggest competitor to PLM is Excel,” says Michael Finocchiaro, principal at Finocchiaro Consulting LLC, a PLM consultancy and a technology evangelist. “People have been burned in the past with PLM. That makes it incredibly difficult to deal with the inertia you have to overcome in order to move past the easy way of doing things with Excel.”

Yet too much reliance on Excel, especially for data management functions, can open the door to a host of problems, given the growing complexity of product design and the need to engage a greater number of stakeholders in the process, from other engineering functions and among outside design partners and suppliers. Excel can’t possibly match the change management and collaboration capabilities afforded by PLM as the software is still an individual productivity tool, and thus manages information in silos.

The Risk of Static Silos

Reliance on Excel spreadsheets within an organization is typically built incrementally, notes Sebastian Dewhurst, director of business development for EASA Software, a low-code development platform for enterprise-grade web apps and a survey co-sponsor. Using Excel for calculations before moving into detailed design and complex geometry makes a lot of sense, he contends, as most engineers are readily familiar with the program already on their desktop as part of the larger Microsoft productivity software suite.

EASA’s model deployment platform offers another way to democratize Excel files for engineers without the usual silo or security woes. Image courtesy of EASA Software.

Although 30% of survey respondents reported using spreadsheets as part of an overall PLM/simulation data management (SDM) process, three-quarters said they had no formal processes associated with spreadsheet use, with most relying on shared network drives or email for collaboration—an indicator there was little to no version control, authentication or security for intellectual property in place. This ad hoc approach to spreadsheet sharing was a definite source of problems—23% said the lack of spreadsheet governance is or could cause major problems.

Peter Schroer, CEO of Aras, says he routinely sees Excel being used as a Band-Aid for configuration and database management tasks, but cautions that it’s very hard to stay in sync with configuration rules when sales orders and other elements continuously change.

“Using Excel to configure something is not the problem, but using it as a database to store the results of configuration exercise is dangerous,” he explains. “You have different versions of the same file, which purchasing is using to buy parts or quality is using to identify problems on the shop floor. You lack one version of the truth.”

On the flip side, Schroer sees a lot of potential for using Excel as part of the simulation workflow, particularly when it comes to the early concept stage, when the goal is quick answers. “Sometimes all an engineer needs to do is run through some stuff in their head quickly and do some math, and Excel is good at that,” he adds.

Marrying Excel with PLM

Instead of fighting organizations’ desire to keep Excel an active player in engineering workflows, Aras and other PLM providers are doing what they can to create tighter integration and synergies between spreadsheets and their platforms. Aras, for example, has spent a lot of time evolving the user interface of its Innovator platform, in part to challenge Excel’s usability and to make it easier for engineers to move tasks over to PLM.

In one such example, the company last year introduced the Failure Mode and Effects Analysis (FEMA) service for Innovator, modeling it to look and behave as if the engineer was still in Excel, Schroer says. “It feels like they are in Excel, but behind the scenes, there is a real database and storage,” he adds.

Aras Innovator’s Failure Mode and Effects Analysis (FEMA) service has been architected to look and feel like Excel for optimum usability. Image courtesy of Aras.

PLM giants Siemens and PTC are also working on tighter integration between their software and Excel. Both offer import and export functions to make it easier to share data between the platforms. More importantly, the integration ensures that what’s currently stored and managed in Excel has a pathway to the digital thread that is emerging as engineering organizations move further into leveraging the internet of things (IoT) to achieve a closed-loop digital process for designing, manufacturing and maintaining products.

“When you are taking digital product data and combining it with IoT data to try to do some analytics and look for patterns on feature usage or how well a component is performing in the field, that information is not accessible out of an Excel spreadsheet,” explains Mark Taber, vice president, go-to-market and marketing for PTC’s IoT Solutions Group. “Excel data is disconnected, it’s not part of the thread and is inaccessible. The idea is to make the spreadsheet part of the overall process, and [integration] serves as a bridge to getting people to work inside of PLM.”

Dewhurst’s EASA Software promotes another way to integrate Excel into the engineering workflow and broader digital thread. Its low-code development platform allows spreadsheets and other models to be deployed as secure web apps that don’t expose the underlying Excel file, thus helping to promote information sharing without the common version control and security glitches associated with traditional spreadsheet sharing.

“You are fighting a losing battle if you’re trying to stop people from using Excel if it’s an appropriate tool,” he says. “The next step is to make sure people are using it appropriately.”

More EASA Coverage