Do you dread opening a spreadsheet? Worried that its going to crash, act sporadically or just not even load?
If it does load, does it take a very long time to open? and when it does, is it sluggish and tiresome to use? Do you find yourself swearing at your spreadsheet?
These are complaints with Excel that I hear from potential clients every day, in my role as ‘Customer First contact’ at The Excel Experts Ltd. The reason I love my job, is because I can assure the caller, with absolute certainty, that these problems can be resolved. I can feel the relief on the caller’s mind which is so rewarding as I know how they feel only too well.
Is this a Big Data Problem?
When a caller starts to share these types of problem with me, the first question I ask myself is “Is this a big data problem?” The first sign of a big data problem is usually the size of the workbook. The newest versions of Excel can handle huge amounts of data, but if you want Excel to handle it efficiently, you may need some help from us.
In these cases, using a more powerful PC will not solve the problem. If you have too much data in Excel, Excel will start behaving oddly and even crash. Clients are often at the end of the tether and about to throw their computer and spreadsheet out of a window! They sometimes think the only solution is to move all of their data over to a custom-built programme but that is not the case. Thankfully, there are ways to solve this problem and keep using Excel.
Or inefficient coding?
Often, the time you spend waiting for Excel to perform a function is being increased because the code that it is running is not very efficient. In these cases, we can often speed up a macro considerably by rewriting parts, or sometimes the whole of the code.
Another option when dealing with big data is running the process overnight with an automated batch file, this means you can sleep soundly while your computer does all the hard work. A further solution that has helped many of our clients is to use a much faster machine that runs in the cloud, such as an Azure Virtual Machine. By allocating huge resources to a virtual machine to process, this speeds up the macro considerably.
Often a business will regularly add data to a spreadsheet, knowing very well that they are gradually compounding to a problem. In these cases, a different approach to loading the data can help matters considerably. Excel has some power tools such as PowerQuery and PowerPivot that enable it to deal with huge amounts of data, but in a very efficient way. Not for the faint hearted but that’s where we come in.
Another solution is to utilize a database of some kind. One of Excel’s biggest strengths is in its ability to connect to almost any data source. We have numerous ways that data can be leveraged so that the spreadsheet itself stays small but can still access huge amounts of data.
We know the power of efficiently and effectively utilising business intelligence and data and we also know how frustrating it is when too much of your time is taken up with slow computing processes. Big data brings huge benefits to your business but can also drive you crazy as described above. Please don’t despair and don’t give up on Excel yet! There is a silver lining, Excel does have the tools and abilities to handle this data quickly and efficiently, if you know how.
