If you've ever used Microsoft Excel to crunch data for work, you know that Excel files can become enormous. And while Excel is a pretty robust spreadsheet application, its built-in functions don't provide all the functions many accountants, finance professionals, and data analysts need.
However, those needing to perform complex calculations, especially calculations involving repetition, can use a Microsoft-developed programming language known as Visual Basic for Applications (or VBA) to develop a program known as a macro. A macro is simply a set of actions that you expect Excel to take. Say, for example, you use shared Excel spreadsheets to track staff time and attendance. You might develop a macro that recalculates their available leave using your company's leave formula whenever they enter their time in a shared Excel spreadsheet.
Macros can be developed through two methods. One involves your recording a series of actions you take in Excel. This method has its limits, namely, that it is hard to customize these recorded macros. The second method, though more labor-intensive, is more robust. You can develop macros by coding them using VBA.
Excel files saved with macros and those containing significant amounts of data will be huge. But those aren't the only factors that can contribute to an Excel file's size. The file format you save your Excel file in may result in larger file size. If you save your file in XLS format — the default file format for Excel files before 2003, your data will be stored in binary format (specifically Binary Interchange File Format, or BIFF) file format.
XLSX files are stored in Open XML file format, and while you'll only be able to read XLSX files on versions of Excel released in 2007 or later, XLSX files are, in fact, a compressed version of XML files. So XLSX files are usually going to be smaller than XLS files. You can also edit XLSX files using a text editor.
However, the trade-off for reduced file size is that you can save an XLSX file with macros. If you try, you'll get an error message telling you that macros are not supported. You'll need to save your macro-enhanced file as either an XLS file (which will be large and incompatible with some features of later versions of Microsoft Excel) or an XLSM file. The latter file format will allow you to save Excel files with macros. However, XLSM files can still be large. Further, this file type is often used to spread malware and may be blocked by security software applications.
File compress is critical for Excel users. Whether you're building mathematical models or assessing sensitive financial information with your co-workers, you'll need to share your data. And while cloud solutions like Microsoft Excel 365 and Google Drive allow you to collaborate with others in real-time, there are still many times you'll need to exchange Excel documents via email.
When you've got auditing or other important deadlines to meet, you can't have large Excel files sitting in your Outbox — or wait for others to get you critical data. Firms with high-speed Internet access from reliable providers may have less to fear.
But more and more often, companies rely on offshore financial, HR, and other functional solutions from providers whose Internet connections may be prone to periods of instability. Still, your manager, clients, and regulators don't want to hear about your technical challenges. So you've got to take every step possible to make sure your data gets to where it needs to go.
You also must make sure that your data is transmitted intact. Some compress methods — collectively known as lossy compress — eliminate certain data elements to reduce file size. With Excel data, in general, avoid any lossy compress methods. Even seemingly superficial data may be critical to the integrity of your data transmission.
For example, if you're transmitting Excel files from Database A to Database B, some data you're sending will contain instructions for how Database B should receive the incoming data. Without those instructions, data in an "Addresses" field in the first dataset might inadvertently go into the "Name" field in the second dataset, among other possible errors.
With Excel files, you'll want to use lossless data compress techniques, which ensure that all of your data remains intact no matter how many times it is compressed.
You can muddle through the different Excel file formats, savings files alternately as XLS, XLSX, or XLSM to preserve your data integrity and storage space. Or you can use a third-party data compress tool, like WinZip, to compress and store all of your files. WinZip's widespread usage and compatibility mean not only that you can compress the full range of Excel and other files but that users inside and outside your organization can easily access any ZIP files you send.
From File Explorer:
From within WinZip:
Note: Certain file types do not compress as much as others. You must have WinZip installed on your computer to use these methods.
Your data is too vital to risk. And you don't have to when you use WinZip to compress and encrypt your files. Consider WinZip Enterprise for your business or organization to keep your information and your customer's information safe and secure.
