Share

When you create a new Excel file, it has a used range that includes only cell A1.  But as you build your file it grows to include the cell in the lowest right corner of the spreadsheet.  (You can find out what Excel thinks is the lowest right corner of your spreadsheet by pressing Ctrl+End.)  If you have a lot of blank rows and columns they will weigh down your file, so if you can, delete unused space.

Sometimes without realizing it, you (or someone else who may have used the file) might mistakenly press Ctrl+right arrow to jump to the last column or Ctrl+down arrow to jump to the last row and add something to a cell or even apply formatting.  Now you’ve enlarged the used space.  Even after you delete the contents of that cell.

To shrink your spreadsheet to its legitimate boundary, press Ctrl+End.  In the following illustration you can see what happens if I at one time entered something in the last row and then deleted it.  Excel still takes me to that last row.

To delete Excel’s marker as the lowermost cell, right-click on the cell and select Delete from the menu.(Hitting the Delete key won’t work here.)

Now go to A1 and press Ctrl+End again.  If it still takes you to that “netherlands” cell, save your file and open it again.  Now try Ctrl+End.

Another thing to check is your cell formatting.  Make sure you apply it only to the actual cells involved.  A common method is to select an entire row or column to apply formatting to.  You can select all blank cells in your spreadsheet by selecting Find & Select from the Home tab and then Go To Special… from the drop-down menu.

Now from the Go To Special dialog box select Blanks.

When all blank cells have been selected, click on the Clear icon in the Editing area of the Home tab and select Clear All.  This will clear formats and comments from all of the blank cells.

Turning off automatic calculation will make your file faster, but sometimes it can also make your file smaller.   To turn on manual calculation use the Formulas tab and go to the Calculation Options section.

Set calculation to Manual.  You can calculate when you need to by pressing the F9 key. (Shift+F9 causes only the current sheet to be recalculated.)

Save your file at this point and and check its size.  If it’s not appreciably reduced in size, you can try saving it in binary format.  You won’t lose functionality and your file might get up to 50% smaller.   (Note that .xlsb file are not compatible with Excel 2003 and earlier.)

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Share

shares