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.
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).
(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.
(03) Use “Column Widths” under Paste Special dialog box
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.
(04) Use ‘Values and number formats’ under Paste Special dialog box
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.
(05) Use ‘Formulas and number formats’ under Paste Special dialog box
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.
(06) 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.
(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.
(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.
(09) Use Custom Borders (Optional)
Alternatively, we can use custom borders instead of 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.
(10) Use the same “Heading Formatting” of 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.
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.
(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.
(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.
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
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.
Step-3: Reduce Excel File Size || Change Old Data Source to the 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.
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.
(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.
(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.
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.
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:
(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.
(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.
(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.
(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.
(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.
(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.
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.
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.
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 Shortcut: Press 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’.
(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’.
(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’.
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?
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%.
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.
- 90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key
- 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts
Latest posts by Sudipta Mondal (see all)
- Excel SUMIFS Function - October 27, 2019
- How to use INDEX MATCH Function in Excel with 04 Best Examples - October 13, 2019
- 09 Steps to Reduce Excel File size || Evaluate Existing Formulas || - September 5, 2019
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