While working with Excel, we often encounter slow and sluggish Excel experiences. In most cases, the main culprit behind this issue is the large Excel file size. In this article, we are going to learn 9 smart tips to reduce Excel file size without deleting data.
Download Practice Workbook
Why Reducing File Size Is Crucial in Excel?
Most of the time, Excel deals with large datasets with thousands of rows and columns. For such types of large datasets, Excel responds very slowly. Sometimes, the Excel window is even frozen which is unquestionably an annoying problem. So, we need to reduce the file size somehow to get rid of this issue. We can reduce file data by deleting data. But this is not an effective method of reducing the file size as we will lose a chunk of data. For this reason, it is crucial to know about the ways to reduce Excel file size without removing any existing data.
9 Tips to Reduce File Size Without Deleting Data
In this article, we will learn about some handy tips to reduce Excel file size without deleting data.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Removing Unnecessary Rows and Columns
In our Excel files, often there are some unnecessary rows and columns which aren’t related to our main dataset. But they increase the file size.
In the following dataset, we have the Yearly Sales Data of a tech store.
By scrolling down and right, we will be able to see that there are a lot of data in the worksheet which are not related to our main dataset.
The unneeded data at the bottom of our dataset is shown in the following image.
If you scroll to the right, you’ll get some unnecessary data also.
In some cases, you might have unnecessary rows and columns in your dataset. Let’s follow the steps mentioned below to remove these unnecessary rows and columns from our Excel file.
Step 01: Removing Unnecessary Rows
- First, from the bottom of the desired dataset, select the first cell of the unnecessary data. In this case, it is cell B20.
- Now, use the keyboard shortcut CTRL + SHIFT + Down Arrow Key (↓), to select all the cells up to the last row of the worksheet.
- After that, press CTRL + “-” (CTRL + Minus) .
Following that, the Delete dialogue box will open on your screen.
- Next, from the dialogue box choose Shift cells up.
- Afterward, click on OK.
Consequently, the unnecessary rows below our dataset will be removed as marked in the following picture.
Step 02: Removing Unnecessary Rows
- Again, select the first cell of the unnecessary data at the right of our dataset.
- After that, use the keyboard shortcut CTRL + SHIFT + Right Arrow Key (→) to select all the extra data up to the last column.
- Now, press CTRL + SHIFT + Down Arrow Key (↓), to select all the data under the selected column in the previous step.
- At this stage, press CTRL + “-”.
Afterward, the Delete dialogue box will open on your worksheet.
- Next, from the Delete dialogue box, select Shift cells left.
- After that, click OK.
Consequently, you will see that the unnecessary rows and columns from your worksheet are removed.
Read More: Reduce Large Excel File Size by 40-60% (12 Proven Methods)
2. Deleting Conditional Formatting and Other Formats
The presence of Conditional Formatting also increases the file size of an Excel file. So, we need to delete any conditional formatting from the workbook to reduce Excel file size without erasing data.
In the following dataset, we have Yearly Sales Data of a tech store where Conditional Formatting is applied.
Let’s follow the steps mentioned below to delete Conditional Formatting from the worksheet.
- Firstly, go to the Home tab from the Ribbon.
- After that, click on Conditional Formatting.
- Subsequently, from the drop-down, choose Clear Rules.
- Next, select Clear Rules from Entire Sheet as marked in the following image.
As a result, you will see the entire worksheet without any Conditional Formatting applied cells. Just have a look at the following screenshot.
Read More: How to Compress Excel File for Email (13 Quick Methods)
3. Compressing Picture to Reduce File Size
Another effective way to reduce Excel file size without deleting data is by compressing the picture (if there is any) on the Excel worksheet.
In the following dataset, we have 3 Website links and their associated QR Codes as pictures.
By following the steps mentioned below, we can compress the picture to reduce the excel file size.
- Firstly, click on any picture from the worksheet. Then, the Picture Format tab will be available in the Ribbon.
- After that, click on the Picture Format tab.
- Next, choose Compress Pictures from the Adjust group under the Picture Format tab.
- Subsequently, choose your preferred Resolution from the dialogue box.
- Afterward, press OK.
Similarly, you can follow these same steps for the other pictures to compress them.
Read More: How to Reduce Excel File Size with Pictures (2 Easy Ways)
4. Eliminating Complex Formulas to Reduce File Size
By eliminating the complex formulas, we can reduce Excel file size without deleting data.
In the following dataset, we have some names having a comma between the first name and the second name.
Here, we can see that we have used complex nested functions e.g. Nested IF to do this in one step. Rather, we can do it in multiple simple and easy-to-calculate steps.
- Firstly, create 2 columns named Position of Space and Replacing with Comma.
In the Position of the Space column, we will use a formula to find the position of the space in the string starting from the 1st character. To do this we will use the FIND function.
- Following that, insert the following formula in cell C5.
Here, B5 is the cell where you will search for the space.
- After that, press ENTER.
The position of the space for the first string will be available to you as shown in the image given below.
By using Excel’s AutoFill feature, we can get the rest of the data.
Now, in the Replacing With Comma column, we use the REPLACE function to replace the space that we found with a comma.
- Subsequently, enter the formula given below in cell D5.
Here, C5 is the starting number of the character that we need to replace.
- Then, hit ENTER.
We can obtain the remaining data by utilizing the AutoFill feature of Excel.
In this way, we can split a complex formula into several simple ones to reduce Excel file size without deleting data.
Note: There’s no denying the fact that the use of complex functions is unavoidable in some cases.
Read More: Why Is My Excel File So Large? (7 Reasons with Solutions)
5. Optimizing Used Formulas
By optimizing the formulas that we have used in our worksheet, we can reduce the Excel file size without deleting data.
In the following dataset, we have the Random Ticket Serial No of some people.
We have generated these numbers using the RANDBETWEEN function which is a Volatile function.
That means these values will be changed whenever we enter anything into any cell of this workbook. This leads to an unnecessary increase in computational load. To reduce the file size, we must avoid the use of Volatile functions.
Here, we have inserted 700 in the Rand Check column. As a result, all of the numbers in Column C are changed.
To avoid the use of the Volatile function, we will follow the steps mentioned below.
- Firstly, select the data that are generated by using the RANDBETWEEN function.
- After that, press CTRL + C to copy these data.
- Following that, go to the Home tab from the Ribbon.
- Next, select the Paste option.
- Subsequently, from the drop-down choose Values as marked in the following image.
As a result, these numbers will be pasted as values in Column C.
- Now, enter a number in the Rand Check column. In this case, we have entered 350.
After entering the number, you will see that the numbers have remained unchanged.
Read More: [Fixed!] Excel File Too Large for No Reason (10 Possible Solutions)
6. Avoiding Use of Pivot Table to Reduce File Size
In Excel we often use PivotTable. Although, PivotTable gives us a lot of options to work with our dataset. But it also takes up a large space and increases the file size.
In the following dataset, we have the Yearly Sales Data of a tech store. We need to find the sum of sales items.
Here, we have used a Pivot Table to find the sum of the sales.
But we can avoid using this PivotTable by using a simple SUM function. To do this, we will follow the steps discussed below.
- Firstly, create a new row as shown in the following image.
- After that, enter the following formula in cell C17.
Here, C5:C16 represents the range of Sales Data of the Smartphone.
- Following that, press ENTER.
Therefore, you will see the following output on your screen.
- Now. drag the Fill Handle to obtain the rest of the sums.
Read More: How to Reduce Excel File Size with Pivot Table
7. Using Save As Binary Format Workbook (XLSB)
We can reduce Excel file size without deleting data by saving the workbook in a Binary Format Workbook. To do this, we will use the steps mentioned below.
- Firstly, go to the File tab from the Ribbon.
- Afterward, choose the Save As option.
- Now, navigate to the folder you want to save this file.
- Following that, click on the marked portion in the following picture.
- Next, choose Excel Binary Workbook from the drop-down.
- Afterward, click on Save.
After saving the file in binary workbook format, you will see that the file size has been decreased as marked in the picture given below.
Read More: How to Compress Excel File More than 100MB (7 Useful Ways)
8. Utilizing Save As CSV Format Workbook
Another great way to reduce Excel file size without deleting data is saving the Excel workbook as CSV Format Workbook. We can do this by following the steps mentioned below.
- Firstly, follow the steps mentioned earlier to obtain the following drop-down list.
- Subsequently, from the drop-down choose CSV (Comma delimited) option as shown in the image given below.
- Following that, click on Save.
After saving the file in CSV Format, you will see that your file size is reduced to only 1 KB.
However, in some cases, you might need to convert the CSV file into Excel file without any distortion of the existing data.
Read More: How to Reduce Excel File Size with Macro (11 Easy Ways)
9. Compressing Excel File to Reduce File Size
We can also reduce Excel file size without deleting data by compressing the Excel file. Let’s follow the below-given steps to compress our Excel file.
- Firstly, right-click on the Excel file that you want to compress.
- After that, choose the Send to option as marked in the following picture.
- Now, select the Compressed (zipped) folder option.
Consequently, your Excel file will be compressed. Here you can see that the size of your Excel file has been reduced to 28 KB.
Read More: How to Zip an Excel File (3 Easy Ways)
💡 Things to Remember
- One thing to keep in mind is that the percentage of file size reduction depends on the initial size of your workbook. That is, the larger the size of your workbook, the more space it will save.
Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to reduce Excel file size without deleting data. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!