Excel has been a valuable tool for businesses seeking to collect and gain insights from data — and spreadsheets will undoubtedly continue to serve their important purpose. But as companies seek to gain a better understanding and make more effective decisions with the vast troves of information they collect, the risks that come from relying on spreadsheets alone are becoming more and more apparent.
As companies embark on digital transformation, rapid and user-friendly data wrangling and analysis will be essential. In this article, you’ll learn about the limitations of spreadsheets and how visual programming can offer a more streamlined approach to managing data that:
- Makes it easier for teams to collaborate on tasks
- Allows for changes that won’t affect overall data quality
- Delivers modular workflows that reduce potential points of failure
Table of Contents
Content Summary
The limitations of spreadsheets
An improved approach with visual programming
Efficiency and intelligence with visual programming
Achieving the promise of data-driven decisions
Jason Denzin has heard different versions of the same story in his years of conversations with large enterprise customers across North and South America. “Every serious Excel user that I’ve spoken with remembers the first time they broke it,” he says.
Denzin works as Head of Field Operations in the Americas for KNIME, a company that builds software for creating and applying data analytics and AI solutions. “They can take you to the day and the project when they were pushing [Excel] beyond its boundaries. It’s a powerful tool, but it does have limits.”
To be clear, Denzin doesn’t expect Excel spreadsheets to disappear anytime soon; they still serve an important purpose. But his experience with a wide variety of customers and business units in multiple industries has uncovered how relying too much on spreadsheets can increase a company’s inefficiency and risk—and reduce the possibility of making smart and timely business decisions based on the large amounts of data corporations collect.
For example, Denzin once worked with a retailer that ran its business using a combination of emails, spreadsheets, and a database that housed its master data. Like most retailers, the company’s margins were thin, so any potential inefficiency or overpayment for merchandise would be problematic. Unfortunately, relying on spreadsheets and the eyeballs of employees didn’t give the retailer much confidence.
“Their buyers would go abroad and order seasonal merchandise for Halloween or Thanksgiving or Christmas, and they would bring back quotes to check against their historical data. The retailer wanted to make sure they were not paying too much,” Denzin says. “They were doing it 100% manually, with somebody going through spreadsheets and checking that they weren’t getting gouged. But doing that is an extraordinarily error-prone process, especially when you get to large volumes of merchandise.”
“Every serious Excel user that I’ve spoken with remembers the first time they broke it.” – JASON DENZIN, Head of Field Operations in the Americas, KNIME
The limitations of spreadsheets
Spreadsheets aren’t going anywhere, but it’s important to be clear about their limitations. One of their main drawbacks is the huge amount of time required to perform manual tasks. For example, think about the number of steps required to produce monthly reports for, say, the marketing department.
For most marketing departments, it’s a painful process of exporting CSVs, PDFs, and other data from a CRM system, databases, email marketing tools, and multiple other data sources into an Excel spreadsheet. To collect and pull data from all of these different formats and sources into one coherent spreadsheet, the data has to be cleaned and made consistent. This is a tedious, time-consuming but ultimately necessary process to start building charts and other visuals that tell a story about how a department is performing.
But there are other pitfalls to consider before a business analyst can tell stories using all the data they collect. One problem is errors, which only people familiar with the data may even spot. For instance, many spreadsheets rely on formula dependencies and pivot tables, and an error in one can have a cascading impact on everything else.
Almost 90% of spreadsheets contain errors. – Source: MarketWatch
Collaboration in ways that don’t introduce errors can also be problematic. Excel doesn’t offer permissions that allow a user to edit just one cell, which means that granting someone the ability to edit one small piece of a spreadsheet always carries the risk of introducing errors into the entire spreadsheet. That danger multiplies when work in one spreadsheet relies on data coming from 20 others.
Proof that overreliance on spreadsheets is costly, risky, prone to errors, and a waste of valuable time and talent is not just anecdotal. A famous study conducted over a decade ago found that almost 90% of spreadsheets contain errors.
Plenty of research also confirms what so many people have experienced personally: Spreadsheets sap a huge amount of time and money from organizations. According to market intelligence company IDC, $6 billion in employee hours are squandered each year working in spreadsheets. IDC also found that employees spend eight hours per week—a full workday—repeating the same data tasks.
All the inefficiencies and uncertainties associated with spreadsheets affect the business users of Excel, whose ultimate goal is to make better decisions with the data available. For example, the finance department is required to produce an earnings statement that executives can have confidence in. At the most basic level, earnings statements depend on dozens, hundreds, or even thousands of spreadsheets to have accurate and up-to-date information. Accurate earnings statements also require that data from multiple sources and in differing formats be combined and then shaped in a way that represents the most relevant information.
None of these steps are easy in Excel, and they are totally out of reach for the many employees who aren’t expert users of the software. “In a finance department, someone is lucky if they get a whole bunch of garbage data in different Excel files. They will spend half a day putting it together and cleaning it up, and by the time they’re done with it, the information is probably out of date already,” Denzin says. “More frequently, what they’ll get is a bunch of data in all kinds of formats— CSVs, PDFs, even XML. Then they have to grab data from their data lake, and Excel is what they have to clean and stitch it all together.”
An improved approach with visual programming
All these challenges with spreadsheets are the reason many companies are aggressively pursuing more sophisticated digital tools that allow them to easily collect and manipulate data and rapidly use that information to make smarter decisions.
“Business is changing dramatically fast, and it’s no longer survival of the fittest, but the survival of the fastest,” says Benjamin Hemminger, a Key Account Manager at KNIME who previously worked for IBM. “Spreadsheets are common in the control and finance areas, but I also talk to customers whose manufacturing machines are logging their data in Excel, CSV, or XML and have thousands of files lying around and they can’t gain valuable insights out of it.”
A desire to more fully leverage data explains why digital transformation is the norm today. Harvard Business Review recently reported that 77% of CEOs accelerated their digital transformation as a result of the COVID-19 pandemic.
“Business is changing dramatically fast, and it’s no longer survival of the fittest, but survival of the fastest.” – BENJAMIN HEMMINGER, Key Account Manager, KNIME
This makes sense. Data and analytics, including the use of artificial intelligence and machine learning, are seen as critical to achieving a wide range of business objectives. A report by consultancy PwC found that executives saw data analytics as essential to their pursuit of priorities ranging from managing risk to automating mundane tasks to helping employees make better decisions.
The question for decision-makers, though, is how to move beyond the many limitations of spreadsheets to achieve the promise of data and analytics. One way is through the use of visual programming, a visual and intuitive workflow-based approach that allows organizations to move beyond spreadsheets and supports even the most complex work of data scientists in transforming their efforts into actionable and valuable insights that benefit the enterprise.
Two of the foundational elements of KNIME are that it is open source and codeless. “The cool thing about KNIME Analytics Platform is that it’s open-source and free so a business user can simply start using it to tackle their business challenges. The business users have the process and data understanding and only need data access to solve their challenges. Customers regularly tell me that they currently describe the business problem to IT which then creates a report, data view, or data extract for them. This process is time-consuming and needs to be repeated when the business situation changes. But why should IT get into the details of those business problems when it is possible to empower the business user to solve this by themselves by getting access to necessary data? Many IT departments see this as a great chance to provide all relevant information to the business users so they can help themselves, and IT can focus on their home turf. This is a very agile and sustainable solution for business users and IT departments ensuring a faster time-to-solution, long-term efficiency, and joint success.” Hemminger says.
“Why should IT get into the details of those business problems when it is possible to empower the business user to solve this by themselves by getting access to necessary data?” – BENJAMIN HEMMINGER, Key Account Manager, KNIME
Let’s explore what visual programming for data science means for the data scientists and business units that need to work together to harness information more effectively. Traditionally, data scientists have used their unique computing skills in a vacuum separate from the business analysts who will ultimately use their findings. Using tools like Python, they gather data, clean it and produce insights based on the patterns and gaps they identify. To share their findings with business units, they produce APIs or create reports.
While certainly valuable, the work of data scientists can’t be scaled to meet the objectives of all the employees who want to make data-driven decisions. This is where visual programming comes in. It provides a tool to scale the work of data scientists by making it repeatable and, importantly, quickly understandable and valuable to business users. That’s possible because visual programming is a visual interface—think of it like a bridge—that illustrates the work of data scientists and fosters collaboration with the business users who ultimately will make decisions based on that data.
Efficiency and intelligence with visual programming
Numerous features of visual programming make it possible for business users to spend their time-solving problems and making decisions rather than wrestling with the complexity of multiple spreadsheets and other sources of data. For example, visual programming allows users to start with very simple tasks. “You can simply join two Excel files as a start. But you can also deploy visual programming on a big data cluster and utilize AI. You can start small and grow into highly sophisticated use cases within the same platform,” Hemminger says.
One reason this is possible is that visual programming eliminates the headaches, complexity, and time required to blend data. Visual programming makes it easy to connect with databases without SQL or Python and allows for a workflow using API or a web application or both. Another helpful feature of visual programming is that it allows users to create their easy-to-follow and replicable visual workflows; this means even non-technical business users can work with data easily without expert knowledge of Excel. Not only does visual programming allow for the creation of visual workflows, but users can also take advantage of workflows already created and proven by other users, and start collaboration by sharing and reusing internal expertise and best practices.
Other important aspects of visual programming make it a far more streamlined way to tap the power of data than by relying exclusively on cumbersome spreadsheets. Visual programming permits teams to collaborate easily on tasks, allows for changes that won’t affect overall data quality, and delivers modular workflows that reduce potential points of failure.
“You can start small and grow into highly sophisticated use cases within the same platform.” – BENJAMIN HEMMINGER, Key Account Manager, KNIME
But the best way to understand the value of visual programming is to examine how companies pursuing the greater efficiencies and intelligence that come with digital transformation are using it. The retailer that tracked buyer purchases abroad using spreadsheets began using KNIME’s visual programming tool to automate and inject intelligence into its review of vendor pricing.
The company opted to combine its use of Excel with KNIME’s visual programming capabilities. “They had an Excel-based quote template. A buyer would come back from a trip with 6,000 of them, and instead of looking over them all manually, the buyer was able to feed them into the KNIME process, which compared these quotes against their historical master and flagged exceptions. Then, someone would then look at them and say this is acceptable or this is not acceptable. That is driving out hours of effort for the business, but it’s also helping them with margin protection and to increase revenue,” Denzin says.
The automotive manufacturing company Continental has also improved its ability to use data to make decisions by lessening its reliance on Excel and embracing the use of KNIME’s Analytics Platform. Like a lot of companies, Continental once relied on Excel to handle all its data. But as the volume of data increased, the company needed to increase the automation of mundane data processing and reporting. Continental began with a rollout of KNIME in its chassis and safety division, where it used the platform to generate Kanban boards using data that had been exported from the company’s issue-tracking system.
“We’re gaining more insights from data and freeing human capacity from mundane data processing and reporting tasks.” DR. ARNE BECKHAUS, Head of Big Data and Digital Transformation, Continental’s Chassis and Safety Division
This initial rollout helped business users more easily tap the power of data without the time-consuming processing involved with Excel. The initial success was used as a springboard to improve automation and analytics across the entire company. Today, Continental uses KNIME for production planning, reporting, project management, and supply chain visibility. Because business users don’t have to spend time wrestling with the challenges of data integration and processing in Excel, they have time to derive insights based on the information. Continental benefited from an 80% time savings in its initial pilot projects with KNIME, and the lead time of its month-end controlling tasks was reduced from two days to just 30 minutes.
The benefits of the deployment are profound. “Since rolling out KNIME at Continental, we’re making better decisions everywhere,” says Dr. Arne Beckhaus, Head of Big Data and Digital Transformation for Continental’s Chassis and Safety Division. “We’re gaining more insights from data and freeing human capacity from mundane data processing and reporting tasks.
Achieving the promise of datadriven decisions
Excel has been a valuable tool for businesses seeking to collect and gain insights from data, and spreadsheets will undoubtedly continue to be important. But as companies seek to gain a better understanding and make more effective decisions with the vast troves of information they collect, the risks that come from relying on spreadsheets are becoming more and more apparent. From the C-suite to business units responsible for everything from financial reporting to logistics to marketing, rapid and user-friendly data wrangling and analysis will have a meaningful effect on achieving companies’ digital transformations. Using visual programming can help.