|Do you have slow performing, large Excel files? Here is the solution.
This is a simple but very important pre-requisite for analyzing large amounts of data. Although technical people are aware of this requirement, most users are not aware about this.
Let us understand the importance of 64 Bit version of Office
What is the difference?
Office comes in many editions. For large data analytics, you ideally need Office 2013 – Professional Plus, Office Pro Plus installed from Office 365 or Office 365 C2R (click to run). In either case, you have 32 bit and 64 bit version of Office available. Office 2010 Professional Plus is also available in 64 bit edition. However, if you are really going to handle large amount of data regularly, Office 2013 is a much better choice.
(Why? Well that is material for another article. For now, just take my word for it!)
Needless to say, to use 64 Bit version of Office, you must also have 64 Bit version of Windows as well.
To simplify things, just remember one important concept. Large data analytics requires lots of memory and a fast processor. Even if you have a fast processor, memory can become a bottleneck. Even if you have lots of memory, 32 Bit version of Office (and Excel) cannot use all of it. It can only address 2 GB of virtual memory. This memory is available for Excel itself and all open workbooks and Add-ins. This can easily get full. Any memory you add beyond that is simply a waste from the point of view of Excel. It does not increase the performance.
If you use Office 64 bit version, life is good because it can use all the memory you throw at it. This means the same PC or laptop will run multiple times faster while crunching data just by changing the Office version from 32 to 64 bit (provided you have lots of memory)
Do you need to pay for 64 version?
If you have a license to use Office, you can use either 32 or 64 bit versions. So there is no additional expenditure required.
There is no upgrade possible
If you have 32 bit version of Office and you want to install 64 bit version, you must uninstall the older one completely and reinstall the 64 bit one. There is NO UPGRADE POSSIBLE.
While doing this remember to take backup of important data, templates, PST files, add-in files and so on.
File size is not an indicator of memory usage requirements
Some of think that if the file size is small, it will not require more memory. That is not true. The way data is stored is very different from the way data is processed, calculated, plotted and visualized. Therefore, even a modest file can consume all the memory available and slow down performance of Excel. Complexity and number of formulas, specific functions used, Linkages, dependencies, array formulas and many other factors affect memory usage.
If in doubt, open only Excel and the offending file and keep the Task Manager or other monitoring tools open to understand the memory and processor utilization pattern.
Some issues related to 64 bit versions of Office you should know about
VBA code compatibility needs to be checked
If you have macros written using VBA which use the DECLARE command you have to change the code a little so that it can run on 64 bit version.
You can ask your IT team to refer to this reference from Microsoft:
Assess Office 2013 compatibility
Third party Add-ins
If you have purchased some add-ins, those will also need to be available in 64 bit editions. You will need to check with the vendors about availability.
Better protection with 64 bit edition
It uses a special method called DEP (never mind what that means) which protects you much better against viruses.
Ask your IT team to look at this site for more details.
Who should get the 64 bit version
Everyone does NOT need the 64 bit version. If there is no specific reason, 32 bit version is recommended. Both versions cannot coexist on the same PC.
Therefore, 64 Bit version should be installed for users who routinely handle large amount of data and face performance issues on a daily basis. Such users end up wasting anywhere from 15 min to 40 min just looking at the progress bar of loading, saving or calculation processing!
Remember that you also need a very fast processor and lots of memory (8 GB and above) for Excel 64 bit to really shine and prove its mettle.
Have a few, shared high performance workstations
I often recommend this approach in cases where providing a powerful machine to all power users is not feasible.
If this is not feasible, you can use some server class machines, load standard applications along with Office 64 bit on them and keep them as shared workstations. Users can login to this machine and perform their data intensive operations by accessing their data shares.
DO NOT use remote desktop
A common mistake done in case of shared workstations is that users login using their id, and run Excel from their PC using Remote Desktop. This defeats the purpose completely because Excel is still running on the low powered PC with 32 bit edition.
Make sure users can login to this powerful workstation and use their data directly on the native 64 bit version of Excel
Power BI add-ins
Another thing to keep in mind is that all the Power BI add-ins like Power Pivot, Power Map and Power Query must also be of 64 version. This way you can handle very large amount of data using Excel.
Of course beyond a point Excel will still have limitations of processing data. In that case you must look for some server based or cloud based tools.