Does Excel still have a place in Analytics?

close-up of an excel spreadsheet

PHOTO: Wachiwit | adobe stock

Business leaders love reliability. It is essential to get results. A long-term love story that continues today is with Excel. Since its launch in 1987, Excel has built a reputation for reliability, with a decades-long history of proven usefulness in a variety of business cases. However, better alternatives have emerged, which begs the question: Does Excel still have a place in business operations?

If you are one of the Excel faithful, ask yourself: When is a good time to use the Excel spreadsheet? When should I use an alternative?

To answer those questions, take a look at how recent Excel applications have changed. A quick glance on the Internet or a chat with analysts will show a variety of opinions on the value that Excel spreadsheets provide. But the only thing that is clear is that innovations in the world of Excel spreadsheets simultaneously create new opportunities and risks for data accuracy.

Excel: no programming language needed

Spreadsheets are a table of lists that provide an easy way to add data and configure formulas. Unlike a programming language, with Excel you can focus on connecting data to the correct formulas.

Spreadsheets also allow you to take advantage of capable, yet basic computational functions. Data is recalculated in real time, so when you add new formulas to your spreadsheet, you don’t need to think about resetting a calculation. In contrast, data models created in most programming languages ​​such as Python, JavaScript, and R require an understanding of the fundamentals of programming syntax.

Related Article: Overcome the Spreadsheet Blues

How has Excel changed?

Several advancements have changed the workflow value of an Excel sheet. The first is the increase in the amount of data. New database structures have revolutionized the access that business users have to data. Spreadsheets previously contained large amounts of data, formulas, and tables. But with the advent of the cloud making data acquisition and storage more convenient, analysts should wonder if applying data to a spreadsheet is enough or if a database would be a better option.

A second is the increase in the number of custom applications for Excel. Some analysts became familiar with Visual Basic for Applications (VBA), the proprietary programming language for enhancing the features of Microsoft applications.

The developers have gone a step further by introducing Excel add-ins that add API and statistical functionality. I once used an add-in, Supermetrics, to import Google Analytics data into an Excel spreadsheet. It allowed me to audit my client’s metrics using more combinations of dimensions and metrics than in the original dashboards, revealing influential trends and increasing the quality of analysis.

Today’s business users clearly have higher expectations of spreadsheets. With the right plugin, spreadsheets can improve the quality of data ingested from a variety of sources.

Unfortunately, many managers are too comfortable with the way they have always used Excel and are slow to adopt solutions beyond the basic capabilities of Excel. Not everyone knows of competing Excel’s Google Sheets, much less Python or R for data mining. When companies rely on Excel spreadsheets without worrying about their limitations, costly mistakes occur. The Verge reported that 16,000 UK COVID-19 cases were omitted from pandemic records due to an Excel sheet reaching the upper limit of its capacity.

Related Article: The Problem of Adding AI to Excel

When do spreadsheets hurt analysis efforts?

Spreadsheets become a problem when the flat design of file sizes meets the size of the data set. An Excel file structure is more than sufficient for experimenting with data fields, but it is not ideal for large data sets. In that case, a program connected to a database would facilitate data analysis, especially if data values ​​are constantly changing and table concepts, such as key-value pairs, are important to queries.

That leads to a second precaution: version control. Spreadsheet files can be difficult to handle for historical data analysis if history is not accurately captured and displayed intuitively. When an organization decides to update the spreadsheet, it runs the risk of losing large amounts of historical data. There is also the risk of accidentally breaking a complex cell formula, which then passes incorrect information to a decision maker in turn.

When do spreadsheets help?

Despite advances in data management, spreadsheets are still relevant in some important scenarios. Consider a prototypical data audit. A simple audit eliminates the need for real-time updates and collaboration. In these cases, the audit is done to get an idea of ​​what numbers and calculations work. It is a temporary solution that will not require ongoing maintenance, such as updating data due to a change in the API specifications.

In general, analysts should use spreadsheets in the early stages to develop an initial idea with the goal of moving to a more stable software solution if necessary. Ideas can generate more programming needs if the data is applied to a larger set that is queried in SQL, Python, or R programming.

Excel spreadsheets can be a good stopgap measure for managing marketing data for analysis. By keeping a close eye on any changes or updates to your spreadsheets, it is hoped that you will avoid the hassles, data loss, and extreme costs that misuse can bring.

Pierre DeBois is the founder of Zimana, a digital analytics consultancy for small businesses. It reviews data from web analytics solutions and social media dashboards, then provides web development recommendations and actions that improve marketing strategy and business profitability.

Moz Introduces Moz Group, New Product Ideas at MozCon

Dentsu buys LiveArea in a $ 250 million bet on Merkle’s business expansion