As someone who has relied on Excel for over 20 years, I said I would write an account of my love hate relationship with it. At times, it has been my friend, but also my foe.
Many organisations are looking at moving away from Excel and using more function specific platforms. To do this requires not only a capital investment but also a psychological one.
The main question is, why do so many organisations use Excel?
I think there are two main reasons, Excel is both relatively easy to get to grips with and inexpensive.
Note: I called the article “The 7 Deadly Sins” to be catchy, but found it impossible to limit the discussion to just 7.
So, if someone is thinking of moving away from an Excel based process to a system based one, here are some points that might sway that decision one way or the other:
Accountability
Who did what? There is a limited audit trail available within Excel but it is certainly not as detailed as most organisations and auditors require.
Control
If your process requires a 4 or 6 eye review then Excel is not your friend. In addition to this, data can be easily fudged, unless you go to a lot of trouble to lock down the data. Even that might not be enough. By the same token, if you hand the process over to a 3rd party, there is every chance you might lose a lot of control that Excel had afforded you.
Transparency
All inputs should be available on demand. If you are linking spreadsheets and worksheets it can be difficult to see what is going on.
Redundancy
If your organisation goes to the trouble of backing up your data daily, then great. One of the issues that I encountered was spreadsheets that would corrupt. This happened for many reasons, such as spreadsheet size and compatibility issues between versions.
Human Error
There is no getting away from human error when discussing Excel. Humans are not particularly good at reading numbers and there is the “fat finger factor” where we simply press the wrong button.
Death by acronym!
Most modern platforms have a nice heads up display (HUD), with a management information system (MIS) giving key performance indicators (KPI’s).
VBA
Straight out the gate I would be the first to say that VBA is a terrific platform to automate a lot of Excel processes. As a language, it is relatively easy to learn. That said, key man risk is most certainly a problem, particularly if the writer has password protected the code and left the company. Another issue that I encountered was functions within VBA being de-commissioned. A solid example of this was the calendars available in VBA. They looked and worked great until Microsoft decided not to support them beyond a certain version. One tip I would give VBA users, instead of inserting a VBA button, simply insert a shape and assign your macro to that shape. I found this to be far less buggy and also you can create something that is easy on the eye.
Security
There is a myriad of unlocking tools available online for small money to by-pass Excel security.
Too generic
Being generic is one of Excels great strengths. But therein lies the problem. An awful lot of work must be done to get it to do specifically what you want.
Multiple Locations
I had a lot of issues working for a large organisation with offices around the world. Date formats changing on spreadsheets were a constant annoyance.
Support
Most enterprise solutions provide some level of support either in app or via e-mail. The same cannot be said of Excel. However, there are lots of freely available tutorials on line if you have the time. One site I got a lot of benefit from whilst using VBA was the VBA Express Forum http://www.vbaexpress.com/forum/forum.php. My advice to anyone using this is simple, post your code, and prove to whomever is reading your query that you have made a valid effort to solve your own problem, and are not simply relying on the community to bail you out.
Automatic Deployment
This can be tricky to do with Excel. You can use the Task Scheduler and VBS scripts to kick off your VBA, but I found this to be less than reliable.
Excel is not real time
When you are asleep, so is Excel.
Big Data V Little Excel
I find Excel to be excellent when performing calculations. It really struggles and will often crash out when the data gets any way big. It is worth remembering that Excel is not a data base, even though so many of us try to use it as one.
Final Thoughts
Anyone working in FinTech will tell you that their biggest competitor is Excel. In the financial world, there is a sea change in terms of people moving away from Excel processes for critical functions. Being perfectly honest, the limitations of Excel were one of the key factors in our setting up a FinTech company.
In summary, Excel has most certainly helped me more than it has hindered me throughout my career. Sometimes you need to make a judgement call. Would bringing in a new system to replace your Excel process be akin to swatting a fly with a canon? The other side of that question is, do I want to be good, or do I want to be great?
If you'd like to chat about Excel, VBA or automating data processing, please contact me at des@fundrecs.com.
