---

This site uses cookies. Read more.

 7 January, 2020

Even in 2020, many companies are still largely managed with spreadsheets. While working with data we have seen the full spectrum of use cases of Excel, including some truly scary ones. There is, however, a clear trend of companies switching to alternatives after losing money and time due to inevitable errors and ensuing chaos. In this post, I am going to explain my take on when it is proper to use spreadsheets and when it is desirable to switch to a more advanced technology.

We are going to take a closer look at some of the root causes of the widespread success of spreadsheets. We will then verify which of these are still relevant and which have changed as technology shifted.

There exist two main alternatives, which fit better into this contemporary landscape than spreadsheets – Shiny and Dash. 

Shiny is a web framework created for R which is relatively easy to learn. It is not unusual to see people familiar with R build something on their first day of playing around with Shiny. Learning R is significantly harder than learning Excel. Still, it is orders of magnitude easier than learning any other programming language. This is clearly visible from the number of people with different backgrounds using R.

This talk from UseR Conference by David Robinson can give more insights about many industries using R and its fast growth within each. 

Dash was introduced for Python with a very similar concept in mind. It allows its users to build analytical web applications from prebuilt components, solely in Python. Dash is built on field-proven technologies like Flask and React. It is probably a little more difficult to learn Python and Dash from scratch when compared to R and Shiny, but this might be the right choice for someone familiar with Python.

Reasons for success

Spreadsheets are an excellent tool and they proved their usefulness for decades in different contexts. The main advantages are flexibility paired with a simple and powerful reactive computational model. A reactive model means that everything is recalculated in real time and when you add new formulas you don’t need to think about the order of computations as in most programming languages.

Spreadsheet formulas are easy to create with only several more complex concepts like VLOOKUP or pivot tables. Part of the success is also the fact that What You See Is What You Get, which means a reduced level of abstraction.

Problems

And the story could end here. But the reality is that the use of spreadsheets has its own challenges and risks.

The internet is full of articles covering these problems. We read about CFOs discussing alternatives or JPMC losing more than $6 billion, because of a spreadsheet error. It’s easy to find similar stories appearing in the media periodically – try this or even this.

The recurring top problems are always errors and chaos.

What has changed while Excel remained essentially the same?

User interface got much better. Since VisiCalc, we no longer have small, monochromatic displays but powerful devices with graphical frameworks. With accelerating development, people have gotten used to better-looking designs from Facebook, Google and many SaaS services.

1979, Visicalc

The gap between the 2-dimensional grid and custom made applications gets larger and larger. A lot of business folks expect more than cells.

AJAX and WebSockets are technologies that allow for background communication from the browser to the server, making today’s website interactive. AJAX allows for one way communication – browser sending requests to the server, while WebSocket works both ways but scales with more difficulty. 

The way we use the web has changed as well. Nowadays it is a real-time, collaborative environment. Sometimes it is hard to imagine what the web had looked like without technologies like AJAX and WebSockets. These technologies sparked the change to real-time experience, but today, we take them for granted.

The size of data has increased. With the digital revolution moving forward we grew our datasets at exponential speed. In my career, I’ve seen spreadsheets with vast amounts of data and formulas and hundreds of sheets. The question is whether spreadsheets were intended to be used this way or perhaps a database would be better suited for such purposes?

VBA or Visual Basic for Applications is a programming language that allows for control of many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. The language itself is narrow and limited.

When are spreadsheets still a good fit?

Whilst the landscape for spreadsheets has changed, they do remain relevant under certain circumstances. Some examples include:

  • Correctness is not mission critical.
  • Data is small and formulas are not extremely complicated.
  • There’s no need for real-time updates or collaboration, or it is possible to use online spreadsheets (Office 365 or Google Docs) accepting their poor scalability. Microsoft suggests less than 30 concurrent users and Google also has its official limit of 100. Keeping well below this limit is necessary for everything to run smoothly.
  • Optimizing user experience and the interface is not important.
  • Solution is temporary, and the spreadsheet will not require maintenance.

In practice that means that it might be a good idea to stay with spreadsheets, when:

  1. The solution is not going to be utilised across the company or its only role is being a scratchpad. Excel reports can be instantly generated by R or Python (and others) to allow business people to play with data.
  2. Spreadsheets can be useful at early stages to develop an initial idea with the goal of moving onto a more stable software solution if needed.
  3. There are no resources for a more bespoke solution.
  4. It is cheaper than building software, especially for tasks which require a significant amount of manual work, such as tedious and time-consuming data gathering tasks done by entry level workers.

An easy fix

An easy fix would be to just buy software. And this might be the right solution for standard, well defined, universal scenarios like CRM or Business Intelligence. Of course, these have some other consequences like sacrificing custom functionalities and the ability to make quick adjustments or experiments.

For selected groups of problems purchasing Salesforce or Anaplan might be a solution. Regardless, most companies are going to keep some custom spreadsheets they use as tools. Tableau or Power BI do not offer a solution since one of the most crucial functionalities of these spreadsheet tools is adding and modifying the data. Furthermore, just like spreadsheets, BI tools carry the risk of creating chaos in larger deployments.

The sweet spot

An ideal solution would provide for rapid development, validation of correctness, extendibility and adjustability while keeping the same reactive model as Excel.

It currently appears that Shiny and Dash are the most promising tools to fill this gap. They allow for quick iteration cycles and are much less heavy than other programming frameworks. Both require a person to develop the tool, but so does Business Intelligence software.

The most important features that Shiny and Dash share with spreadsheets are flexibility and a powerful reactive computational model. They are slightly more complex but in exchange, they solve most of the critical problems with Excel.

Source code, which Shiny and Dash apps have, is the only known way to avoid ambiguity and chaos in the long run. Writing code allows for encapsulating of abstraction and dividing working parts into modules.

Tests are the only effective way to minimize errors. At Appsilon we proved that both logic and the interface of Shiny and Dash applications can be tested automatically, catching mission critical errors faster and increasing iterations speed. Those tests can be easily automated and plugged in into continuous integration. As a result bugs are caught sooner, they don’t go into production and they don’t face final users. Fixing those types of errors is several orders of magnitude faster and cheaper.

Shiny and Dash are web frameworks. And the web is where you want to be. This gives you all the innovations at hand and independence from huge vendors. The web also means scale – while scaling is not one of the Shiny strengths, we have managed to scale applications to hundreds of users for our clients. Scaling Dash seems even more promising as we’ve learned from our deployments.

One of Appsilon's dashboards built with Shiny

User experience is just as important. Both frameworks allow you to build beautiful business applications. There is no comparison between spreadsheets and these. An intuitive and appealing interface means easier and faster adoption within your company.

Shiny and Dash are not tools for everyone, so not everyone can be a creator, but this group is much larger than you might expect. A developer doesn’t even have to be an Engineer – it can be an ambitious and curious Excel user or someone tired of the limitations of traditional Business Intelligence. You need a team of people with at least one Shiny or Dash developer. Needless to say, once the tool is created, anyone can use it and get insights.

What next?

Even though it seems that Shiny is a sweet spot now, we are excited to see upcoming innovations in this space. Delivery time for Shiny is a huge advantage, but performance and scale are important issues for corporate solutions as well. It takes some practice to write Shiny apps that scale effectively, and even for well written applications, their scalability has limits.

Dash from Plotly is already entering this space and they work on their dashR package for R as well! We are excited to see competition in this space as this is going to be beneficial for all of us. We expect to see improvements in Shiny both in terms of efficiency and capabilities in the near future. We hope for some movement in the Julia community as well. Escher for Julia proved that a reactive model can be implemented in Julia without much difficulty – sadly the project seems to be no longer maintained.

If you’re ready to try out some Shiny alternatives to Excel, I recommend this article by Marcin Dubel. He shows that you do not have to sacrifice Excel’s table-data functions if you make the switch. 

At Appsilon we are committed to delivering top-notch decision support systems and data analytics solutions for our clients. Don’t hesitate to reach out to us if you need help converting your business from Excel to a more modern solution.