Your Excel Problems: 5 Ways To Solve Them

2 years agoNo comment, be the first!
MS Excel has remained to be the undisputable champion of desktop tools for good reasons: it is accessible, flexible, customizable, and useful for all possible cases.

However, this very nature of Excel makes it vulnerable to errors and is the reason for every employee’s daily gripes. So, instead of fighting the almighty Excel, we are introducing a new way of using Excel that may just ease your day-to-day work.

1. You’re not working on the latest spreadsheet version.

You know that feeling when you’ve finally finished fixing that monster Excel spreadsheet, only to find out that you haven’t been using the “latest” version? Yes, we’ve all been there. So you cry a little, suck it up, and start over.

Pro Tip:
The easiest way to solve this problem is to enable Excel’s built-in Revision Tracking feature. Revision Tracking is located on the Review Tab of Excel 2010 and 2013. Enabling this feature highlights the changes that was made on your file, and provide details of who, when, and where the changes were made. While this feature is good enough for keeping track of edits, it does not prevent a team of users from working on multiple files.
A more technical way of solving this problem of versioning is by using VBAs, as discussed by TheSpreadsheetGuru on this post. Another alternative is to use 3rd party tools that were built just for version control- here’s a good thread from Slashdot with some options.

The CalcFusion Way: 
CalcFusion manages versions of your Excel file by storing them on the cloud and allowing only one version to be Active, for each type of computation. Therefore, if you don’t know which version is the latest, or you want to go back to an old version, you are able to download the correct one on CalcFusion. Furthermore, CalcFusion’s user rights management control who can upload, download, and delete your files so you are sure that your precious Excel is safe and untouched.

2. You do have the latest spreadsheet version,
but not the latest data.

Face it, data will always have to be updated. What’s worse than having to export data is having to do it again and again and again… If you want to stick to your current solution, you need to accept the fact that you will be working and re-working your data every day/week/month.

Pro Tip:
Microsoft has released Power Query for Excel, a free add-in for Excel 2010 and 2013 which will allow you to connect your Excel spreadsheet to external data. Power Query solves the need to have “fresh” data as it can run an automatic refresh or a scheduled refresh. Prior to this, techie users usually create a VBA program to import data. While Power Query is actually a very good tool, it does not address problems #3, 4, and 5.

The CalcFusion Way: 
The best way to avoid exporting data is by not storing data in your Excel. CalcFusion connects your Excel to your data source (your ERP, CRM, inventory system, etc.) via web services allowing your Excel to receive data in real-time. In effect, your data is always up to date.
Okay, so there may be some data that you want to keep in Excel. What’s great about CalcFusion is that you can combine your live data and Excel data to be used in your formulas.

3. Your Excel has limits.

MS Excel 2013 has a limit of 1,048,576 rows by 16,384 columns. Huge, right? But in the era of big data, it’s not much. Impossible as it may seem, your data will keep on growing and pretty soon, Excel will not be enough.

Pro Tip:
Splitting your data into multiple worksheets might be the best option if you absolutely want to keep your data in your Excel. However, this will consequently increase your file size and you could run into problem #4 if your file gets too big.

The CalcFusion Way: 
Once again, as CalcFusion directly connects to your enterprise software and uses data directly coming from its database, there is no need to store large data sets in your Excel.

4. Your Excel file takes ages to load.

Excel files hitting double digit megabytes are terrifying. Excel models that are designed to crunch numbers tend to be large as it contains a lot of formulas, which could only mean only one thing: it’s going to be slow.

Pro Tip:
If your file is .xls, try saving it as .xlsx as it will significantly reduce your file size.
Still slow? Consider removing unnecessary formatting and formulas, external links, charts. You may even want to set your Excel calculations to manual instead of automatic.
If you are encountering error messages regarding available resources, your need to check 2 things: Is your file more than 2GB? Is your Excel version 32-bit? If you answered yes to both questions, then consider upgrading to 64-bit. It’s because 32-bit can only address 2GB of memory, while the 64-bit scales with the amount of RAM available.

The CalcFusion Way: 
Eliminating large data sets leaves you with an Excel containing only the formulas and maybe a few local data. This significantly cuts your file size and improves the loading time of your Excel. On top of this, calculations on CalcFusion are run on a server instead of your desktop which means better and dedicated processors are allocated just for your Excel.

5. Nobody reads your Excel report.

Excel is brilliant because it can contain more information than a whitepaper can, but consider that your audience may not need to know all that information. Information derived from Excel needs to be published in the form that your audience can appreciate.

Pro Tip:
Excel can be formatted beautifully as to create a report that looks like a document. This may be enough for some of your reporting needs. A more sophisticated alternative could be to use a BI tool connected to your excel.

The CalcFusion Way: 
CalcFusion lets your audience trigger a computation and view the results from your enterprise software –systems that they are already using. CalcFusion uses your enterprise software as an interface while your Excel is being executed at the back-end. In effect, your audience is able to appreciate your computation without tediously switching back and forth between the system and Excel.

Conclusion:

MS Excel isn’t going anywhere.
Shiny new spreadsheet apps have attempted to replace Excel, but none (as far as we know) has reached the level of Excel.

For us, the best way to handle Excel issues is to elevate Excel from a desktop application to a cloud solution. This is essential as more businesses try to unify their software environment.
CalcFusion is a complementary tool for cloud solutions and legacy systems, as it integrates and centralizes Excel as part of their processes.

CalcFusion is a cloud solution that is customizable according to specific needs. It can also be deployed on an on-premise server. Register for a free account and receive a free consultation with CalcFusion’s team of experts:CalcFusion Sign-up

 

###

 

Keep on calculating!

 

Be the first to comment

Post a comment

START AUTOMATING YOUR EXCEL WORKFLOW TODAY NO CREDIT CARD NEEDED