This tutorial will outline the methods for reducing the file size of an Excel workbook as presented in the provided video excerpt.
1. Remove Unused Rows and Columns
- Excel workbooks can become unnecessarily large if they contain formatting or data in rows and columns beyond the used range.
- To identify if this is the case, press Ctrl+End to jump to the last cell in the sheet. If this is beyond the area you are actually using, you have an opportunity to reduce the file size.
- To clear unused rows and columns:
- Press Ctrl+Home to go back to the top of the sheet.
- Use Ctrl+Down Arrow to reach the last row of your data set.
- Move down one more row to the first unused row.
- Select all unused rows with Ctrl+Shift+Down Arrow and then Ctrl+Shift+Right Arrow to select all unused rows.
- On the Home tab, click Clear and then Clear All.
- Repeat this for unused columns.
- You must close and reopen your spreadsheet to see the effect on the file size.
2. Modify Pivot Table Cache Settings
- When you create a Pivot Table, Excel creates a “Pivot Cache,” which is a hidden copy of your data that can increase file size.
- To prevent Excel from storing this cache:
- Click inside your Pivot Table.
- Go to the Analyse tab and click the Options button.
- Select the Data tab.
- Untick the Save Source Data with File option and tick the Refresh Data When Opening File option.
- With these settings, the Pivot Cache will not be saved with the file but will regenerate when you open the file.
3. Compress Images
- Images in your workbook can significantly increase the file size.
- To compress all images in your workbook:
- Select one of the images.
- Go to the Picture Format tab and click Compress Pictures.
- Untick the Apply only to this picture option to apply the settings to all images.
- Choose a lower resolution for the images. You will see some degradation in image quality, but this will reduce the file size. 150 PPI is suggested as a compromise.
- Click OK.
4. Save as an Excel Binary Workbook
- You can save your workbook in a different file format that can reduce file size without impacting functionality.
- Go to File > Save a Copy.
- In the dropdown list of file types, select Excel Binary Workbook.
- Click Save.
By following these steps, you can effectively reduce the file size of your Excel workbooks.