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.
David Robinson’s talk at the UseR Conference can give you more insight into the 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.
Want to make the leap from Excel to R Shiny, but don’t know how to get started? Read How to Switch from Excel to R Shiny: First Steps.
Spreadsheets are an excellent tool and they have proved their usefulness for many decades in a variety of different contexts. The main advantages of spreadsheets 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 to your spreadsheet, you don’t need to think about the order of computations (as in most programming languages). Simply put: you can do complex work with spreadsheets without having to understand the fundamentals of computer science or code anything. This is quite powerful.
Spreadsheet formulas are easy to create with only a handful of complex concepts like VLOOKUP or pivot tables. Part of the success of spreadsheets is the What You See Is What You Get (WYSIWYG) layout, which means that you’re dealing with a significantly reduced level of abstraction. Enter a number, enter an equation, and voila – you get results with no coding required.
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 spreadsheet problems. We routinely read about CFOs discussing alternatives or JPMC losing more than $6 billion because of a spreadsheet error. More recently, nearly 16,000 coronavirus cases went unreported in England due to a basic Excel formatting error.
The recurring top problems with spreadsheets are always errors and chaos. There is simply too much room for human error with Excel, and it is extremely easy to accidentally break a complex spreadsheet. You could be running your entire business based on fundamental miscalculations or missing data and never know it.
There’s also no option for version control with Excel. This means that once something is broken, it can be very difficult (and sometimes impossible) to fix it.
In general, the graphical user interface (GUI) has become much better over time. It’s been a long time since the launch of VisiCalc, and we no longer work with small, monochromatic displays. Rather, we have ready access to powerful devices with impressive graphical frameworks. With accelerating development, people have gotten used to better-looking designs from Facebook, Google, and many SaaS services. Most mainstream computer programs look better and are far more intuitive than when Excel was in its heyday.
The gap between the 2-dimensional grid and custom made applications gets larger and larger. A lot of business folks expect more than cells. They need interactivity and robust interfaces.
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.
Whilst the landscape for spreadsheets has changed, they do remain relevant under certain circumstances. Some examples include:
In practice that means that it might be a good idea to stay with spreadsheets, when:
An easy fix would be to just buy an existing alternative software solution. 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 may seem like attractive options, but they do not offer a real 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.
Though we recommend R Shiny, Tableau and PowerBI can be solid options for some use-cases. Read our Shiny comparisons Tableau vs. R Shiny and PowerBI vs. R Shiny to figure out which option is right for your specific needs.
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.
Want to see some impressive Shiny Dashboards? Visit Appsilon’s Shiny Demo Gallery.
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 Shiny’s biggest strengths, we have managed to scale Shiny applications to hundreds or even thousands of concurrent users for our clients. Scaling Dash seems even more promising as we’ve learned from our deployments.
User experience is also extremely important. Both frameworks allow you to build beautiful business applications. There is no comparison between the UX of spreadsheets and Python Dash or R Shiny frameworks. An intuitive and appealing interface means easier and faster adoption within your company. A tool is only useful if it is actually being used.
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. Your data analytics team needs at least one Shiny or Dash developer. Needless to say, once the tool is created, anyone can use it and get insights.
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 also entering this space and they’re actively working on their dashR package for R as well. We are excited to see competition in this area 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. If you’re looking to make the jump from Excel to Shiny but you don’t have much (or any) experience with R, read How to Switch from Excel to R Shiny: First Steps. We’ll walk you through how to get started with R Shiny from a standing start.
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.