Question – what is the world’s most popular software development tool?
Answer – Microsoft Excel

It’s true! Excel really is a development environment, and its powerusers are developers (whether they know it yet is another question). If you don’t know whether this applies to you, ask yourself these questions:

1. does my Excel spreadsheet contain multiple nested IF-statements?
2. does my Excel spreadsheet use macros?
3. do I commonly use pivot tables?
4. do I ever solve my business problems by starting from a blank Excel spreadsheet and working from the ground up?

If you answered yes to any of these questions – you’re a developer!

Excel is awesome, but..

Excel is powerful and accessible and awesome. But it is also the most abused workplace productivity tool in the professional world. Excel-abuse causes a downward spiral of inefficiency, disruption and poor (and expensive) decision-making.

So, in this post I am summarising what I call the 6 circles of Excel Hell (“Exc-Hell"), and how businesses can avoid this hellish landscape by embracing software tools purpose-built for their workflows.

The 6 circles of Exc-Hell

1. When you use Excel as version control
   Do this file naming convention look familiar to you? C:\My Documents\Project ABC\Working_v1_(RP)_draft2.xlsx. Every time you collaborate on someone else’s spreadsheet, just append your initials or increment the version number in the filename and send it back over Outlook. This is horrible, and so common! There is no way to tell when a spreadsheet/tab/cell was edited and by whom.
   
   
2. When your Excel spreadsheets are so big they crash your computer
   I once worked in an office where we had a dedicated high-spec PC reserved specifically for running a particular Excel spreadsheet. The reason was that this spreadsheet always caused our relatively weak laptops to crash. If you find yourself in this situation; firstly, you’re probably also in several of the other circles of Exc-Hell. Secondly, remember that one of the awesome benefits of Excel is its shareability and accessibility. If you cannot share your spreadsheet, then maybe it’s time to reconsider why you are using Excel
   
   
3. When you cannot change an Excel spreadsheet without it breaking entirely
   The biggest problem with Excel spreadsheets is that the layout and business logic are intrinsically tied together. Formulas refer to cell-positions (not the contextual meaning of the underlying value). If you’ve ever broken a spreadsheet by simply inserting a column, you will know what I mean.
   
   
4. When you cannot replicate your results
   In software development there is the concept of “unit testing”, which entails breaking your logic into small, simple pieces of code and writing input-output tests to check that each piece performs as intended. This mindset does not exist in Excel. As a result, mistakes in your calculations will be made and they won't be noticed. The cost of these mistakes can range from mild embarrassment in a client meeting, to multi-million dollar snafus.
   
   
5. When you don’t know if a cell value is hard-coded or calculated
   One of the best and worst features of Excel is that it is very easy to manually edit raw data. This is great for sandboxing, but horrible for reproducability because there is no history or artefact to inform your colleagues (or yourself in 6 months) that you hard-coded a new value into the cell.
   
   One simple workaround I recommend is to separately colour-code raw data, manually-changed data and formulas. But enforcing this in your team is no easy task!
   
   
6. When your workflow often requires repeated copy-pasting of data into Excel
   Merging several source worksheets into one is fine. Merging dozens of source worksheets is doable, but time-consuming. But when you have hundreds of source worksheets … ?
   
   

Purpose-built software can be your salvation!

Let’s get one thing straight - software is great, but advocating that your team stop using Excel is a non-starter in most business contexts.

Instead, you need to find ways to integrate software into your Excel workflows. Three approaches I’m excited about are:

1. Doing computation in the cloud, and accessing the results with APIs – With this approach, you write your complex business logic in a progamming language (e.g. Python), run it in the cloud (with AWS Lambda) and pull the end results into Excel using a data API.
   
   This sounds complicated, but it’s really not! AWS Lambda is essentially a virtual machine in the cloud that can run your code and make the results available at a web URL (e.g. https://abc123.aws.com/my_excel_data/). It removes the hassle of having to install Python or Java or whatever on yours or your colleagues’ computers.
   
   Excel actually has an awsome tool to load data directly from a web URL: “Get Data – From Web”. This CodeMentor post provides a great summary. A great approach is to create an Excel template which loads the data, and produces charts and analysis outputs. This ensures that the underlying data and business logic is unadulterated, but provides the end result in a format with which your non-techy colleagues can interact.
   
   
2. Writing scripts for commonly repeated tasks using Python and Pandas – it will cost you 6 hours today, but save you 30 mins every week for the rest of the year. This is perfect for those tasks you have to do every week (e.g. copy-pasting sales data, producing charts, publishing to a PDF report). If you rely on charts frequently, I recommend using an interactive plotting library like Plotly which expands your charting options beyond just line-charts, bar-charts and pie-charts!
   
   
3. Acknowledging Excel's limitations and using purpose-built software tool where appropriate – sometimes Excel is simply not fit for purpose. A common example is using Excel as a living database: as a Customer Relationship Management (CRM) tool, a risk management tool, a HR management tool, etc.
   
   Excel is not a replacement for a database and should not be used in any application that requires any level of data provenance. In these situations it is cheaper, easier and safer to use a purpose-built web-application. And if such a web app doesn’t exist, well that’s a great reason to reach out to discuss how I can help you streamline your business workflow!
   
   

Summary

I find it awe-inspiring just how much of the world's corporate and national finances, science and research is built on Excel. Our tacit shared understanding of Excel allows collaboration regardless of location and language barriers. However, like all good tools, Excel has its limitations. Right now, I'm optimistic that the surge in interest in data-science will help bring business leaders over the line in terms of understanding the limitations of Excel, and increase their willingness to implement software tools into their workflows.