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.

What are XLS and XLSX files?

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.

Why compress Excel files

File compression 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.

How to compress Excel files

You also must make sure that your data is transmitted intact. Some compression methods — collectively known as lossy compression — eliminate certain data elements to reduce file size. With Excel data, in general, avoid any lossy compression 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 compression 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 compression 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.

How to compress Excel files in WinZip

From File Explorer:

  1. From your File Explorer select all of the files you want to compress.
  2. Right-click on the selected files. WinZip > Add/Move to Zip File… This will open WinZip with options about how you want your file zipped.
  3. Select your file options, like name, location, encryption, and any other features you want to include.
  4. Select Add. This will create a zip file in the location you selected containing all your files.

From within WinZip:

  1. From within WinZip, locate the files you want to zip using the panel on the left.
  2. Select all of the files you want to compress.
  3. Select the Add to Zip button at the bottom of the left panel.
  4. After you add all of the files you want to the Zip file, select the "Save as" button in the right bottom corner. Name your file and select save.

Note: Certain file types do not compress as much as others. You must have WinZip installed on your computer to use these methods.

Conclusion

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.


Download Winzip and Encrypt Files Today