To reduce the overall size of an Excel workbook, you may like to try one/more of these suggestions:
- Don't save as dual format file (Excel 95/97); use the most recent version of Excel you (and your intended) audience will have access to - for instance, "Excel 2002", or "Excel 2000"? However, save in the oldest copy of MS-Excel you can, then re-save in the latest revision you can. Doing this may reduce overall size.
- If you notice that the vertical or horizontal scroll-bar(s) go(es) past the end of your data, you can clear the blank rows & columns beyond the extent of your data:
a) Go to the bottom row of data & then select the entire next row by clicking on the row number (in the "margin"). Use [Ctrl]+[Shift]+[Down Arrow] to select all the remaining rows in the worksheet, then click the "Edit" / "Clear" menu item to clear the rows of values & formats.
b) Go to the far right column of data & select the entire next column by clicking on the column letter. Use [Ctrl]+[Shift]+[Right Arrow] to select all the remaining columns in the worksheet. As before, use the "Edit" / "Clear" menu item to clear the columns of values & formats.
Repeat the above steps for each worksheet in your workbook where you can scroll past the end of your data. Finally, save the workbook, and close it. Upon re-opening the scroll-bars should now extend only as far as the data.
- Format your cells in continuous ranges (blocks) rather than individually setting the formats of distinct cells. For example, if all of row 1 is "size 12, arial, bold" do not set A1, B1, C1, D1, E1 (etc) separately, just select row 1 & apply the same style to each cell at once. Formatting in this manner can also be used with colo[u]rs (cell foreground & background), fonts, font sizes, font styles. Try to apply the same formatting to single or multiple columns, and/or rows, and make individual cells the exceptions, rather than apply formatting to individual cells one by one.
- Refresh any queries or pivot tables with (select critieria that references) the least amount of data possible.
- Research (via Help) & use inbuilt bespoke functions rather than writing your own code.
- Reduce the length of Range names so that they are still meaningful, but not as verbose.
- If you have any (auto) filtering enabled in your workbook, across one, or more, worksheets, remove this, or reset it back to "(All)" prior to saving.
- Remove any unused worksheets, or any unused VBA code (or pre-recorded macros).
- You may like to try exporting (right-click tab and select "Copy To" 'New Book') all your worksheets into a new workbook & then saving this newly created book as a different filename. When comparing the two files you may notice a vast difference.
- If you regularly save data across a network (i.e. is the workbook stored on a network), or is it 'Shared'? If 'Shared', try unsharing & saving locally, re-share & then save back to the network.