09 Steps to Reduce Excel File size

09 Steps to Reduce Excel File size || Evaluate Existing Formulas ||

By

Every Excel user generally facing some common problems, likes – Excel file too large for no reason, Excel file too big to open, after opening Excel file slow to respond, cell movements become very sloth, Excel filtering very slow, overall Excel performance slowing down.

Based on that we can categorize the issues in three parts:

  • Type-1: Why would an Excel file increase in size? Why excel file so large?
  • Type-2: How to reduce excel file size? How to compress large files to small size?
  • Type-2: Why Excel is too slow? How do we speed up a large excel file? 

Accidentally, Excel size increases high without any reason and jumped from KB (Kilobyte) to MB (Megabyte). Sometimes, sending a large Excel file (>50 MB) via email is restricted due to its limitation.  This situation makes everyone very irritating to handle an Excel file and it is tough enough to make a report of this database/Excel file.

This is mainly happening due to the using of enormous Cell Formatting likes: various types of fonts, font color, background color, extends the borders up to the whole worksheet, inserts more text boxes, etc. If Excel file contains more data, formulas, pivot tables and images in it, definitely files become heavy in size.  

So, every Excel user should follow the below 09 steps to compress an Excel file, speed it up, evaluating the formulas linked to the main database easily.  

 

This Tutorial Contents:

HOW TO REDUCE EXCEL FILE SIZE & EVALUATING FORMULAS DEPENDING ON THE MAIN DATABASE

 

Step-1: Reduce Excel File Size || Create a New Workbook & transfer the Main Database

This step is very crucial to reduce Excel file size and speed up the Excel file as well. Simply follow step by step.

(01) Select the entire active database area

At first, select the whole active area of the main database by pressing Ctrl+Shift+ Right Arrow (→) and then Ctrl+Shift+ Down Arrow (↓); henceforth copy it by pressing Ctrl+C (Exclude unnecessary areas).

Reduce Excel File size-step1

 

(02) Create a “New Worksheet” within the Same Workbook

Create a new worksheet in the same workbook by pressing Shift+F11 or in the new workbook Ctrl+N.

Remember that we always prefer to create a new worksheet in the same workbook (by Shift+F11), if there are many worksheets interlinked with the main database worksheet either by formulas or pivot tables. This is because after replacing the main database from one worksheet to another worksheet, all interlinks are broken, the data source of Pivot Table is changed, worksheets with formula will show #REF! Error, worksheet with Pivot Table not updating after refreshing. In that situation, it is necessary to evaluate the formula, to build the interlinks, change the data source of the pivot table to automate the workbook.      

If the main database is present in a single worksheet, no interlinking with the other worksheets then we prefer to create a new workbook (by Ctrl+N) for replacing the main database.

 

Reduce Excel File size-2(Create New Worksheet)

 

(03) Use “Column Widths” under Paste Special dialog box 

Reduce Excel File size-3(Use “Column Widths" under Paste Special Wizard)

 

Select the first cell, i.e., A1 of the new worksheet and then press Alt+E+S+W or Alt+Ctrl+V+W allow to select the ‘Column widths under the Paste Special dialog box Press Enter or click on OK.

As a result, Column widths are adjusted as same as the main database.

 
Reduce Excel File size-4(Use “Column Widths" under Paste Special Wizard)

 

(04) Use ‘Values and number formats’ under Paste Special dialog box

Reduce Excel File size-5(Use “Values and number formats” under Paste Special Wizard)

 

Henceforth in cell A1, press Alt+E+S+U or Alt+Ctrl+V+U allows selecting the ‘Values and number formats under the Paste Special dialog box Press Enter or click on OK.

As a result, all the cell formatting will remove from the main database. Number, date and currency formats remain intact otherwise, all the formats will be changed into number format. But one constraint is that all the formulas inside the database will convert to values. 

 

Reduce Excel File size-6 (Use “Values and number formats” under Paste Special Wizard)

 

(05) Use ‘Formulas and number formats’ under Paste Special dialog box

Reduce Excel File size-7 (Use “Formulas and number formats” under Paste Special Wizard)

 

To restore the formulas inside the database, press Alt+E+S+R or Alt+Ctrl+V+R in cell A1 that allows selecting the ‘Formulas and number formats under the Paste Special dialog box Press Enter or click on OK.

As a result, all the cell formatting will remove from the main database and all the formulas inside the database safely paste in the new sheet.

 

Reduce Excel File size-8 (Use “Formulas and number formats” under Paste Special Wizard)

 

(06) Use Font Size Drop-down box

Reduce Excel File size-9 (Use Font Size Drop-down box)

 

In the next step, suggest decreasing the Font size from default values 11 to 9.

First, select the database by Ctrl+A a press Alt+H+F+S to activate the Font size drop-down box either choose Font size from the drop-down list or manually type 9 press Enter.

 
Reduce Excel File size-10 (Use Font Size Drop-down box)

 

(07) Use Freeze Panes

In the next step, we would like to use Freeze Panes to freeze the heading row (Horizontal Freeze Pane) and freeze till the Other ID column (Vertical Freeze Pane).

Therefore, we select only cell E3 then press Alt+W+F+F.  

As a result, above E3 all row(s) and before E3 all column(s) have been frozen simultaneously.

Reduce Excel File size-11 (Use Freeze Panes)

 

(08) Use Borders

If we want to add borders to all the active database area, select the active database area press Alt+H+B+A  to apply borders over the entire active database area instantly press Alt+H+B+T to apply thick borders outside the main database area.

 

Reduce Excel File size-12 (Use Borders)

 

(09) Use Custom Borders (Optional)

Alternatively, we can use custom borders instead of borders.

Reduce Excel File size-13 (Use Custom Borders)

Select the active database area a press Ctrl+1 to open the Format Cells window go to the ‘Border tab select any color wants to apply the border from ‘Color’ drop-down box   select line style or keep the default style from the ‘Style box go the ‘Presets’ section, single click on Outline and single click on Inside box click on OK or press Enter

To apply outside thick borders follow the below steps: Select the active database area  to choose a thick line from ‘Style’ box click on the Outline under the ‘Presets’ section.

As a result, custom border applied over the selected database area.

Reduce Excel File size-14 (Use Custom Borders)

 

(10) Use the same “Heading Formatting” of the old database to the new database

Reduce Excel File size-17 (Use same Heading Formatting from the old database to the new database)

If different color combinations of subject heading are meaningful for working purposes, then we apply the same subject head formatting from the old database to the new database.

Reduce Excel File size-18 (Use same Heading Formatting from the old database to the new database)

First, we go to the old database worksheet, just copy the entire subject heading row by pressing Ctrl+C a  back to the new database worksheet a select the first cell in the subject heading (i.e., A2) and press Alt+E+S+T or Alt+Ctrl+V+T which allows selecting the ‘Formats‘ under the ‘Paste Special‘ dialog box.

Press Enter or click on OK.  

Reduce Excel File size-19 (Use same Heading Formatting from the old database to the new database)

 

(11) “Rename the New Tab” from the Old Database Tab

In the next step, rename the new tab from the old database tab (if mandatory to keep) or we can go with the new name. Remember that Excel only allows a unique tab name.

So, in that case, we first copy (Ctrl+C) the tab name of Old database by double-clicking on it and delete after that by pressing Alt+E+L and confirm delete by pressing Enter or select the ‘Delete’.

Go the New main database sheet and either double-click on the tab or press Alt+O+H+R for renaming and paste (Ctrl+V) the copied names. 

 

Reduce Excel File size-20 (Rename the New Tab as per the name of Old Database Tab)

 

(12) Color the Tab

If there are a number of tabs in a workbook, to identify the main database tab easily from others, we should color the tab.

 

Reduce Excel File size-21 (Color the Tab)

Benefits:  Due to the above process,  

01. Remove the Data formatting: As a result, unnecessary data formatting has been removed such as Font Style, Font Size (weigh), Font type (Bold, Italic, Underline) Font Color, Background Color, Borders, strikethrough, underline, etc.

02. Remove the Conditional Formatting: As a result, conditional formatting inside the active database area has been removed.

However, remove formatting creates a minimal impact on the file reduction, but it has been observed that Excel file activity becomes faster than previous.


Step-2: Reduce Excel File Size || Evaluate all the formulas in different worksheets dependent on the Main Database

Reduce Excel File size-23 (Evaluate the formulas in different worksheets dependent on the Main Database)

It is a second crucial step. As discussed in the above point, we will find that all the linked worksheets showing #REF! Error. We need to evaluate the formulas between the new replaced database with the different formulated worksheets. It speeds up Excel file enormously. 

Go to one of the existing formula worksheets press Ctrl+H to open the ‘Find and Replace’ dialog box just type #REF in front of ‘Find what:’ box. Additionally, in front of ‘Replace with:’ box either exact type the tab name of the new database or Copy-Paste (Ctrl+C – Ctrl+V) the tab name whatever easier. 

But it should be inside the single quotation mark, such as ‘Data’ then click on ‘Replace All button all replacements have been made and recalculate the formula by default, that means the worksheet has been automated now we can follow the same mechanism to all the formulated worksheets one by one.   

 
Reduce Excel File size-24 (Evaluate the formulas in different worksheets dependent on the Main Database)

 


Step-3: Reduce Excel File Size || Change Old Data Source to the New Data Source of a Pivot Table

Reduce Excel File size-25 (Change Old Data Source to New Data Source of a Pivot Table)

It is also an important step. After refreshing, pivot table data could not be updated as it doesn’t connect with the new data source. So, we need to change the Old Data Source to New Data Source of a pivot table.

Click anywhere on the Pivot Table ➪ will activate the ‘PivotTable Tools (Analyze Tab and Design Tab) in the Excel ribbon ➪ click on the ‘Change Data Source under the Analyze tab ➪ a small window of ‘Change PivotTable Data Source appears and select the new database range (under the ‘Data’ tab) in the box located on the right side of the ‘Table/Range:’ ➪ press Enter or click on OK. Pivot table perfectly linked with the new database and get updates after refreshing.  

Reduce Excel File size-26 (Change Old Data Source to New Data Source of a Pivot Table)

 


Step-4: Reduce Excel File Size || Delete/Convert into Values the Unused Pivot Table & Remove the Pivot Cache

When the Pivot Table is created from the main database, Excel creates the Pivot Cache automatically. Pivot Cache basically a duplicate copy of the main database.

If there is any unused/unnecessary pivot table in the workbook should be deleted or converted to values. Therefore, the pivot cache is deleted simultaneously. Thus, this step helps to reduce file size greatly. 

 

(01) Delete the Unused Pivot Table

Click any cell in the pivot table, press Ctrl+A to select the entire Pivot table ➪ press ‘Delete’ on the keyboard to remove the pivot table. 

Reduce Excel File size-26 (Delete the Unused Pivot Table)

 

(02) To Convert into Values the Unused Pivot Table

Click any cell in the pivot table, press Ctrl+A to select the entire Pivot table ➪ copy the entire Pivot table by pressing Ctrl+C and then press  Alt+E+S+V or Alt+Ctrl+V+V to select the option ‘Values’ under ‘Paste Special’ dialog box ➪ press Enter or click on OK.

Alternatively, click on any cell in the pivot table, press Ctrl+A to select the entire Pivot table ➪ copy the entire Pivot table by pressing Ctrl+C and then press  Alt+E+S+U or Alt+Ctrl+V+U to select the option ‘Values and number formats’ under the ‘Paste Special’ dialog box ➪ press Enter or click on OK.

Reduce Excel File size-28 (Convert into Values the Unused Pivot Table)

 

(03) Remove the Pivot Cache

If the Pivot Table is mandated to keep in the workbook, in that case, we should delete the Pivot cache to reduce the file size.   

Reduce Excel File size-29 (Remove the Pivot Cache)

Click anywhere, any cell in the pivot table ➪ Choose PivotTable Tools in the ribbon and click on ‘Analyze’ ➪ In PivotTable area, click ‘Options’ ➪ Go to the ‘Data’ tab of ‘PivotTable Options’ window ➪ uncheck ‘Save source data with file‘ and check ‘Refresh data when opening the file‘ ➪ Press Enter or click on OK.

Note: We should check ‘Refresh data when opening the file‘ option that ensures that the Pivot Table is refreshed while open the workbook and the Pivot Cache is automatically generated. Otherwise, we have to refresh the Pivot table manually to generate the Pivot Cache.

Reduce Excel File size-30 (Remove the Pivot Cache)

 


Step-5: Reduce Excel File Size || Remove Unnecessary Formulas from the worksheet

 

(01) Remove Unnecessary Formulas inside the main database area

If there are unnecessary formulas in the main database area, we should convert them into values. It does not effect to reduce file size directly, but speed it up the excel file extremely. We can do it in 2 steps:

 

(A) Find & Select all the Formulas inside the main Database Area

We can do it in 3 ways:

Reduce Excel File size-31 (Find & Select all the Formulas inside the Main Database Area)

(i) Using the Ribbon: Select the entire database by Ctrl+A Go to ‘Home click on ‘Find & Select‘ dropdown under ‘Editing’ Section click on ‘Go To Special then press Alt+F or select the ‘Formulas’ and keep all the checkboxes checked finally, press Enter or click on OK.

Alternatively,

(ii) Using Excel Shortcut: Select the entire database by Ctrl+A then press Ctrl+G to open the ‘Go To‘ dialog box press Alt+S or click on ‘Special then press Alt+F or select the ‘Formulas’ and keep all the checkboxes checked finally, press Enter or click on OK.

Reduce Excel File size-32 (Find & Select all the Formulas inside the Main Database Area)

 

Alternatively, 

(iii) Using Another Excel Shortcut: Select the entire database by Ctrl+A press F5 to open the ‘Go To‘ dialog box press Alt+S or click on ‘Special’ then press Alt+F or select the ‘Formulas’ and keep all the checkboxes checked finally, press Enter or click on OK.

 

Reduce Excel File size-33 (Convert the Unnecessary Formulas into Values )

 

(B) Convert Unnecessary Formulas into the Values

Columns with the formulas are getting highlighted and identified by the above process, select unnecessary columns among them and convert them into values. Similarly, we can do it in 3 ways:

(i) Using Excel Shortcut: After the selection of cells with formulas, press Ctrl+C to copy all of them ➪ then press Alt+E+S+V or Alt+Ctrl+V+V will promote opening the ‘Paste Special‘ dialog box and select the option ‘Values’ simultaneously ➪ press Enter or click on OK.

 

Alternatively, 

(ii) Using Alternative Excel Shortcut: Alternatively, Copy (Ctrl+C) the selected cells with formulas ➪ then using the Excel Shortcut Alt+E+S+U or Alt+Ctrl+V+U to select the option ‘Values and number formats’ under the ‘Paste Special‘ dialog box ➪ press Enter or click on OK.

 

Alternatively, 

(iii) Using the Mouse: Alternatively, Copy (Ctrl+C) the selected cells with formulas ➪ right-click on mouse anywhere in the selection ➪ Select ‘Values’.

 

(02) Remove Unnecessary Formulas Outside the main database area

For daily working purposes, we allow some columns outside the main database area for the formulation, basically using VLOOKUP, HLOOKUP for capturing data from other sources. We should remove them regularly.

  • Remove column(s) with formula by Ctrl+minus (-).
  • Remove all cell contents by pressing ‘Delete‘ Key but remember that in that case cell formatting still persists there.

 


Step-6: Reduce Excel File Size || Remove Unwanted or Unnecessary worksheets/tabs

 

If there are unused/unnecessary worksheet(s) or tab(s) in the workbook, simply delete them. It helps to reduce excel file size immensely. 

(01) In Case of Contiguous worksheets/tabs

If the worksheets/tabs are contiguous, then select the first tab to want to delete and press the ‘Shift‘ key and click on the last tab want to delete. This will make a group of contiguous worksheets and press Alt+E+L then Enter or click on ‘Delete’.  All contiguous worksheets/tabs will be deleted.

(02) In Case of Non-Contiguous worksheets/tabs

If the worksheets/tabs are non-contiguous, then select the tabs by pressing ‘Ctrl‘ Key.  Similarly, this will make a group of non-contiguous worksheets and press Alt+E+L then Enter or click on ‘Delete’.  All non-contiguous worksheets/tabs will be deleted.

Reduce Excel File size-34 (Remove Unwanted or Unnecessary Worksheets or Tabs)

 


Step-7: Reduce Excel File Size || Remove Hidden worksheets/tabs

 

Another possibility we should check further, are there any hidden worksheets in the workbook? Similarly, after deleting unwanted or unused hidden worksheet(s), it reduces excel file size a lot.  

Reduce Excel File size-35 (Remove hidden worksheets or tabs)-Right-click to Unhide the worksheet/tab
Figure: Right-click to Unhide the worksheet/tab

 

Just right-click on any open worksheet tab and click Unhide. If any unhide worksheet is there, this option is highlighted otherwise does not.

Alternatively, we can use Excel shortcut Alt+H+O+U+H to unhide the tab(s).

Then a new ‘Unhide’ window appears in front of us.

Remember that we can unhide only one worksheet at a time. If multiple worksheets are hidden there, so in that case, repeat the unhide process multiple times to restore them.

Reduce Excel File size-36 (Remove hidden worksheets or tabs) Excel Shortcut to Unhide the worksheet/tab
Figure: Excel Shortcut to Unhide the worksheet/tab

 

While all the worksheets are visible, grouping them by pressing the ‘Ctrl’ key. Press Alt+E+L will open a Microsoft Excel warning window and then Enter or click on ‘Delete’.  

Alternatively, after grouping by ‘Ctrl’ key, right-click anywhere in the selection and select ‘Delete’.

 


Step-7: Reduce Excel File Size || Convert Excel file (XLSX) to Binary Format (XLSB)

 

The XLSX is an open XML spreadsheet file format. Before closing the file/workbook, we should change the Excel file extension to XLSB format.  Now a question arises in the user’s mind: “what is excel binary workbook?” XLSB is an Excel Binary Workbook.

Therefore, the excel file reduces in size up to 50% smaller.

Additionally, accessibility (writing, cell movement, filtering) of XLXB file noticeably faster, even as much as 50%.

We can do this in 3 ways:

(i) Using Excel ShortcutPress Ctrl+F12 allow to open ‘Save As’ dialog box ➪ click on the drop-down list of ‘Save as type:’ located at the right-side ➪ select the ‘Excel Binary Workbook (*.xlsb)’ ➪ press Enter or click on ‘Save’.

 

Alternatively, 

(ii) Using Alternative Excel Shortcut: Alternatively, Press Alt+F+A to open ‘Save As’ option ➪ Click on ‘Browse’ to choose the folder where to save the file ➪ click on the drop-down list of ‘Save as type:’ located at the right side under the ‘Save As’ dialog box ➪ select the ‘Excel Binary Workbook (*.xlsb)’ ➪ press Enter or click on ‘Save’.

 

Alternatively, 

(iii) Using Excel Shortcut: Go to the ‘File’ tab ➪ click on ‘Save As’ option ➪ Click on ‘Browse’ ➪ click on the drop-down list of ‘Save as type:’ located at the right side under the ‘Save As’ dialog box ➪ select the ‘Excel Binary Workbook (*.xlsb)’ ➪ press Enter or click on ‘Save’.

Reduce Excel File size-37 (Convert Excel file (XLSX) to Binary Format (XLSB)

Step-7: Reduce Excel File Size || Compress images

 

This is an optional step to reduce excel file size. If there are many images, we need to optimize or smash them. Then the question arises in the user’s mind about how to reduce excel file size with pictures?

Reduce Excel File size-39 (Compress Images) using tiny.png
tiny.png

The best way is using an external application, like tiny.png (supported file: JPEG, PNG), Compressor.io (supported file: JPEG, PNG, GIF, SVG). It compresses images without losing quality by up to 65%.  

Reduce Excel File size-40 (Compress Images by compressor.io)
compressor.io

Conclusion

 

After following the above processes, Excel file size reduces a lot, speeding up performance, evaluating all the formulas in different worksheets, the workbook is automated and file performance improves extremely. 

 

Reduce Excel File size-38 (Convert Excel file (XLSX) to Binary Format (XLSB)

Learn-More-3

Sudipta Mondal
Follow AEF
Advance Excel Forum

Every Excel user generally facing some common problems, likes - Excel file too large for no reason, Excel file too big to open, after opening Excel file sl

Editor's Rating:
5

Leave a Comment

You may also like