7 Tips to keep Excel File Size Small

Structure of an Excel file is designed that consumes low memory space on drives. But sometimes contents file can enlarge size of file. More often you may have noticed delay while Loading a simple file or during normal Calculation. It seems frustrating but we can take precautions to avoid such delay by keeping the Excel file size optimized.

Smaller size of any excel file is very important for portability and fast access.
Here are some tips to keep or reduce file size using Excel 2007.

1. If excel file contains any image. Right click on the image and click compress or from the formatting tab click on Compress Picture (can be found in top left corner).
Uncheck "Apply to selected picture only".

Because images takes most of the space in any file reducing that can help to reduce excel file size up to 75%

2. Don't format your worksheet more than needed. Sometimes we select the entire row or column or sheet and select Fill color or Borders. This also increases the size of worksheet.
If you are using conditional formatting it also takes more time during opening an excel file. Remove the conditional formatting if it is not required.

Home - Conditional Formatting - Clear Rules - Clear Rules from Entire Sheet

Repeat above step on all the sheets.

3. Use paste special to convert formulas into Values so that it can be converted in values and reduce the size of file and also the loading time will be reduced but still recommended not to convert all the formulas as values, but recommended to convert all external links in to values so that your values will remain intact to the person you are emailing this file or if external link is not found.

4. If you have shared that workbook for multiple users then remove the sharing.

5. BEWARE Excel files might contain some viruses disable macros before opening (Macro - Macro Security - and select High security) until you are very sure that the macro contained in the excel file are safe don't enable it.

6. If your file contains simple data and still its size is huge try to copy all the data into another file and then save the file as another name.

7. One of the most ignored action in Excel files especially in 2007 or later versions that at the end of Column or Row we write something accidently and then we simply delete it. Excel treats the last most cell on which data entry is done as the end of the sheet.

To check this Press Ctrl + End and see the last most cell. If it is bottom or end of the row select the unnecessary Rows or Columns and from Select Home - Clear - Clear All.

This will help to reduce the size of the file.

Insert the Name of WorkBook in the Cell

Workbook Name is very rarely required value in excel But Workbook Name can be used in the formula like “Indirect” function to refer an internal or external address of any particular File.

Excel doesn't provide any inbuilt function to retrieve the filename in any cell. But it provides us a simple way to retrieve it through combination of formulas.

Here we illustrate how to retrieve the File Name

=SUBSTITUTE (LEFT (CELL ("FileName"), FIND ("]", CELL ("filename"))-1),"[","")

Here 4 function are used to achieve our goal

1. Substitute: this function simply finds a part of text in the complete sentence and replaces with another
like
=substitute ("ABCD","A","X")

in above example from "ABCD" the "A" will be replaced by "X", and the return value of the formula will be XBCD

2. Left: This simply return the leftmost Nth numbers of Characters from any text including spaces
Like
=Left("ABCD",2) *here we instructed excel to retrieve only 2 left characters from ABCD
Thus formula will return “AB”

3. Find: this function will simply give the Nth position of the searched text from sentence
=Find("B","ABCD") this function will return the position of B from the left so the value will be 2, as B is on 2nd position from left.

4. Cell: This function retrieves information from Excel Application and returns it to us.
There are several parameters provided to for different purposes. In our case we used it to retrieve the Name of file.

=Cell("FileName")
this will return the Location + Name of File (Enclosed within Square Brackets) + SheetName
like
C:\Amit\Office\[BlogData.xls]sheet1

using the find function we retrieved the position of  "]"
from the Left to till the position ] we retrieved the text using left function
and using substitute function we replaced [ as "" (2 double quotes continuously denote the blank) from the returned value.

We can use this formula in any cell and we can have the name of file in the cell we enter the formula
C:\Amit\Office\BlogData.xls

Run specified macro in specified time of interval throughEXCEL VBA

Open VBA Editor in Excel (Press Alt + F11)

in Project Explorer double click on "ThisWorkbook" and add the following code in it.

Public Sub Workbook_Open()
        Application.OnTime TimeValue(Now + TimeValue("00:15:00")), "DisplayAlarm"
        'specify your desired time. using Time Formula < TimeValue (Hours, Minute, Seconds)>
 End Sub

Now add a Module in the same Workbook and add following Codes

Sub DisplayAlarm()
        MsgBox ("Alarm Triggered")
        'add the additional codes as per your requirement
        'you can use shell command to run any external program
        Call ThisWorkbook.Workbook_Open
End Sub

now save, close and reopen the same excel file.
at the specified time displayAlarm Procedure will run.

Tip: if you add above code in Personal Macro Book then this macro will be available at all the time excel is running because personal macrobook automatically opens with excel