If the large file size is due to a large number of rows (say, more than 100,000), consider using Power Pivot to store the data instead.
If you find your model getting out of control (and you’re using standard Excel, without the help of Power Pivot or any other add-ins), here are a few tried-and-tested tips you can use to keep that file size down:
- Remove any unnecessary formatting. Colors and formatting should only apply to the necessary range, not an entire row or column. Avoid manual formatting and use Styles instead.
When you’re clearing cells that you’re no longer using, you probably do so by selecting the cell and pressing the Delete key. This action clears the contents and formulas, but it doesn’t clear the formatting. If you suspect this is a problem, you can press Ctrl+A to select all cells; then on the Home tab of the Ribbon, in the Editing section, click the Clear drop-down and select Clear Formats.
- Make sure formulas are referencing only the range they need to (not selecting the entire row or column). If your formulas are referencing more cells than they need to, this will use more memory. For example, use the formula =SUM(A1:A1000) to allow for additional rows instead of =SUM(A:A). Alternatively, you can format the data as a table and refer to the automatically expanding table ranges in your formula instead.
- Remove (or at least check the size of) any logos or images that you’re using. A single JPG file inserted in a model can easily add 10MB to an Excel file size.
- Avoid PivotTables. PivotTables really chew up memory. If you have multiple PivotTables, make sure they’re using the same data source, and not creating a whole new one.
- Remove external links to other files. To check if there are any external links in your model, go to the Data tab on the Ribbon and click Edit Links in the Connections section. If the Edit Links button is grayed out, that means there are no external links. If they exist, click the button and when the Edit Link dialog box appears, click Break Links to paste the data from all external links as values, and the external links will be removed. If you absolutely have to use external links, have the source file open at the same time; this will speed things up.
- Check for redundancies in inputs and calculations. Sometimes a model evolves, and there may be parts that are no longer used or parts where information has changed.
- Avoid volatile functions. Some of the most commonly-used volatile functions are OFFSET, INDIRECT, RAND, NOW, TODAY, ROW, and COLUMN. Overuse of these particular functions in your model can really slow down your calculations. If you must use them, try to limit their appearance in the model. For example, =TODAY() is very useful for giving today’s date, but instead of using it multiple times in a formula, have it in one single cell and continually reference back to that one cell for today’s date.
- Make sure you aren’t using the XLS file type. XLSX is a much more compact file type, and you’ll see a huge difference in speed and file size if you use it. XLSB is an Excel binary workbook file type that is even more compact than XLSX.
Finally, as a last resort, a little trick is to leave one cell at the top of the column with the live link and paste all the other cells as values. Copy the cell down and recalculate when you need to refresh. This certainly isn’t a preferred option, because it’s time-consuming and prone to error, but that’s why it’s a last resort.