MS Excel is one of the most popular electronic file formats. It has tremendously improved and facilitated work for accountants, financial auditors, bank employees, lawyers and many others who work with large volumes of data.
However, working with Excel files is not always easy, especially if they contain lots of information and are very large. The file size can make them unstable and difficult to share with others. The best way to avoid issues that come with large Excel documents is to reduce their size. There are many ways to do it and we’ll cover some of the most important:
1. Convert the Excel sheet to PDF
The PDF format is excellent for compressing multiple types of data into one compact file. So, creating a PDF from your Excel reports is a definitely a good idea.
Don’t worry about converting your data back to Excel, because Cometdocs can do it for free. Before you convert the spreadsheet to PDF format, head to your Print settings and adjust the rows and columns size, because you want them all to fit onto one paper.
2. Shorten text fields
If you use long cell, field, tab and file names, you’ll make the situation worse. Try to shorten them, or, if you can’t omit any words from these titles, use abbreviations instead.
3. Change Excel version
New software versions are created to improve the shortcomings of previous versions, and so, with every new MS Excel format version, the file size is reduced significantly. You can easily try this out, save your Excel 2003 file in Excel 2007 and see how the size changes. This is a quick way to make your file smaller.
4. Remove inactive cells
If you are an Excel user, you’re familiar with situations when you remove some rows or sections that you don’t need, but instead of deleting the entire row, you only delete information and the row is left blank. That way your document becomes larger than it needs to be. Using the shortcut CTRL + End is the best way to check what your last active cell is. You can then see how many blank rows stand between it and the last actually used cell, so you can delete them and make your file smaller.
5. Remove circular references
Circular references – formulas that refer back to themselves in a calculation – are usually a sign of miscalculation and make Excel files unstable. It is the best to remove them.
6. Quantity of complex formulas
When using complex formulas, such as Vlookup / Hlookup / Index / Match / Offset / SUMIFS, and apply these formulas on a very large number of rows, you unnecessarily make your Excel file heavier. If this is necessary, try using macros instead. Macros will make the right calculations and paste the results in Values. You can also use Excel add-ins, such as PowerPivot.
7. Remove excess items
A very quick and easy way to remove all unnecessary items in your Excel file is to use the Go to function. By analyzing your file, you can clean your file and reduce its size.
8. Pivot cache duplicates
If you feel like your Excel file is too heavy, this might be due to pivot cache duplicates. Each time you create a pivot table, Excel also creates its duplicate. If you have many pivot tables (and duplicates), you’ll fill the cache and thus increase your file size. To avoid this, you can set Excel not to create pivot cache, or you can choose to use the same cache for each new pivot table.
9. Check data connections
Sometimes when we are importing data from other files into Excel, the data import connections get saved in our file and makes the whole sheet heavier. Sometimes this is necessary if we are importing data from external web sources, and we need to refresh them regularly. But, we usually only need to import files once and never again. That is why you need to go to Data – Existing connections and check if there is something there. Delete all the connections you no longer need. Data will stay intact, so don’t worry about losing already imported data.
