My Blog

1. Remove formats wherever it is not necessary

Removing colors, shading, and borders from the cells where is it not necessary would drastically reduce the size of a workbook. ( For example, the above picture shows borders even on empty cells.)

Selecting Clear > All from Edit menu would help you remove all formats applied to a cell along with any content.

2. Clear All Empty Cells

You need to select all empty cells and apply Clear > All or Delete them to remove any accidently applied formats.

To clear all empty cells:

1.   Go to the first completely blank column at the right of your data. Select the column by clicking on its letter. Hold Shift+Ctrl and then hit the right arrow key until you have selected all the way out to Column IV. Select Edit menu and choose Clear > All or From Edit menu, select Delete.

2.   Go to the first completely blank row at the bottom of your data. Select the row by clicking on its number. Hold Shift+Ctrl and then hit the down arrow key until you have selected all the way down to Row 65536. Select Edit menu and choose Clear > All or From Edit menu, select Delete.

3. Reduce the workbook size by compressing pictures

You can tell excel to compress the images to reduce the file size. (This will force saving the images at lower resolution of 96dpi instead of standard 200dpi )

Select one or more images in a file and then:

1.   On the Picture toolbar (To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Compress Picture..

Note: If you do not see the Picture toolbar, point to View, then point to Toolbars, and then click Picture.

2.   Select each of the option shown below to see the difference.

4. Modify Pivot Tables settings.

If your workbook contains multiple PivotTables, all based on a single data source, Excel may create an intermediate dataset for each PivotTable, instead of using one intermediate dataset. This, of course, could increase the size of your workbook very rapidly.

You can also instruct Excel to not save your intermediate data tables in the same disk file with the workbook. This will make the size of your workbook file much, much smaller, but it will also require that PivotTables be refreshed every time you open your workbook.

Follow these steps:

1.   Run the PivotTable Wizard to create your PivotTable as you normally would.

2.   When you get to the final screen of the PivotTable Wizard (the one with the checkered flag on it), click the Options button to display the PivotTable Options dialog box.

3.   Clear the Save Data with Table Layout check box.

4.   Choose the Refresh on Open check box.

5.   Click on OK to close the PivotTable Options dialog box.

6.   Finish the steps in the PivotTable Wizard.

Trackback This Post | Subscribe to the comments through RSS Feed