How to Reduce Excel File Size Without Deleting Data (9 Tricks)

Get FREE Advanced Excel Exercises with Solutions!

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.

Why Reducing File Size Is Important 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. However, 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.


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 to Reduce Excel File Size

In our Excel files, often there are some unnecessary rows and columns that 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.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

By scrolling down and right, we will be able to see that there are a lot of data in the worksheet that are not related to our main dataset.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

The unnecessary 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.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

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

  • From the bottom of the desired dataset, select the first cell of the unnecessary data. In this case, it is cell B20.

  • Use the keyboard shortcut CTRL + SHIFT + Down arrow key (↓), to select all the cells up to the last row of the worksheet.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

  • After that, press CTRL + “-” (CTRL + Minus) .

Following that, the Delete dialogue box will open on your screen.

  • From the dialogue box choose Shift cells up and click on OK.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

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.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

  • After that, use the keyboard shortcut CTRL + SHIFT + Right arrow key (→) to select all the extra data up to the last column.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

  • Press CTRL + SHIFT + Down arrow key (↓), to select all the data under the selected column in the previous step.
  • At this stage, press CTRL + “-.

The Delete dialogue box will open on your worksheet.

  • From the Delete dialogue box, select Shift cells left and click OK.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

You will see that the unnecessary rows and columns from your worksheet are removed.

how to reduce excel file size without deleting data Removing Unnecessary Rows and Columns

Read More: How to Reduce Excel File Size by Deleting Blank Rows


2. Remove Conditional Formatting and Other Formats to Reduce File Size Without Deleting Data

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.

Steps:

  • Go to the Home tab from the Ribbon.
  • Click on Conditional Formatting.
  • From the drop-down, choose Clear Rules.
  • Select Clear Rules from Entire Sheet as marked in the following image.

how to reduce excel file size without deleting data Deleting Conditional Formatting and Other Formats

As a result, you will see the entire worksheet without any conditional formatting applied to cells. Just have a look at the following screenshot.

how to reduce excel file size without deleting data Deleting Conditional Formatting and Other Formats


3. Compressing Picture to Reduce File Size in Excel Without Deleting Data

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.

how to reduce excel file size without deleting data Compressing Picture to Reduce File Size

By following the steps mentioned below, we can compress the picture to reduce the Excel file size.

Steps:

  • 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.

how to reduce excel file size without deleting data Compressing Picture to Reduce File Size

  • 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


4. Get Rid of Complex Formulas to Reduce Excel File Size Without Deleting Data

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.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

Here, we can see that we have used complex nested functions e.g. Nested IF function to do this in one step. Rather, we can do it in multiple simple and easy-to-calculate steps.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

Steps:

  • 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.

  • Insert the following formula in cell C5.
=FIND(" ",B5,1)

Here, B5 is the cell where you will search for the space.

  • After that, press ENTER.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

The position of the space for the first string will be available to you as shown in the image given below.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

By using Excel’s AutoFill feature, we will get the rest of the data.

In the Replacing With Comma column, we use the REPLACE function to replace the space that we found with a comma.

  • Enter the formula given below in cell D5.
=REPLACE(B5,C5,1,",")

Here, C5 is the starting number of the character that we need to replace.

  • Then, hit ENTER.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

We can obtain the remaining data by using the AutoFill feature of Excel.

how to reduce excel file size without deleting data Eliminating Complex Formulas to Reduce File Size

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: How to Compress Excel File to Smaller Size


5. Optimizing Used Formulas to Reduce Excel File Size Without Deleting Data

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. 

how to reduce excel file size without deleting data Optimizing Used Formulas

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.

how to reduce excel file size without deleting data Optimizing Used Formulas

To avoid the use of the volatile function, we will follow the steps mentioned below.

Steps:

  • Select the data that are generated by using the RANDBETWEEN function.
  • After that, press CTRL + C to copy these data.

how to reduce excel file size without deleting data Optimizing Used Formulas

  • Go to the Home tab from the Ribbon.
  • Next, select the Paste option.
  • 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

how to reduce excel file size without deleting data Optimizing Used Formulas

  • 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.

how to reduce excel file size without deleting data Optimizing Used Formulas


6. Avoiding Pivot Table to Reduce File Size in Excel

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.

how to reduce excel file size without deleting data Avoiding Use of Pivot Table to Reduce File Size

Here, we have used a PivotTable to find the sum of the sales.

But we can avoid using this PivotTable by using the SUM function. To do this, we will follow the steps discussed below.

Steps:

  • Firstly, create a new row as shown in the following image.

how to reduce excel file size without deleting data Avoiding Use of Pivot Table to Reduce File Size

  • After that, enter the following formula in cell C17.
=SUM(C5:C16)

Here, C5:C16 represents the range of Sales Data of the Smartphone.

  • Following that, press ENTER.

how to reduce excel file size without deleting data Avoiding Use of Pivot Table to Reduce File Size

Therefore, you will see the following output on your screen.

  • Now. drag the Fill Handle to obtain the rest of the sums.

how to reduce excel file size without deleting data Avoiding Use of Pivot Table to Reduce File Size

Read More: How to Reduce Excel File Size with Pivot Table


7. Saving Excel Workbook in Binary Format to Reduce File Size Without Deleting Data (Save As 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.

Steps:

  • Go to the File tab from the Ribbon.

how to reduce excel file size without deleting data Using Save As Binary Format Workbook (XLSB)

  • Choose the Save As option.

  • Now, navigate to the folder you want to save this file.

how to reduce excel file size without deleting data Using Save As Binary Format Workbook (XLSB)

  • Click on the marked portion in the following picture.

how to reduce excel file size without deleting data Using Save As Binary Format Workbook (XLSB)

  • Next, choose Excel Binary Workbook from the drop-down.

  • Click on Save.

how to reduce excel file size without deleting data Using Save As Binary Format Workbook (XLSB)

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.

how to reduce excel file size without deleting data Using Save As Binary Format Workbook (XLSB)


8. Saving as CSV Format to Reduce Excel File Size Without Deleting Data

Another great way to reduce Excel file size without deleting data is saving the Excel workbook as a CSV Format Workbook. We can do this by following the steps mentioned below.

Steps:

  • Follow the steps mentioned earlier to obtain the following drop-down list.
  • From the drop-down choose CSV (Comma delimited) option as shown in the image given below.

how to reduce excel file size without deleting data Utilizing Save As CSV Format Workbook

  • Click on Save.

After saving the file in CSV format, you will see that your file size is reduced to only 1 KB.

how to reduce excel file size without deleting data Utilizing Save As CSV Format Workbook

In some cases, you might need to convert the CSV file into an Excel file without any distortion of the existing data.


9. Compressing Excel File to Reduce File Size Without Deleting Data

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.

Steps:

  • Right-click on the Excel file that you want to compress.
  • Choose the Send to option as marked in the following picture.

how to reduce excel file size without deleting data Compressing Excel File to Reduce File Size

  • Select the Compressed (zipped) folder option.

Your Excel file will be compressed. Here you can see that the size of your Excel file has been reduced to 28 KB.

how to reduce excel file size without deleting data Compressing Excel File to Reduce File Size

Read More: How to Zip an Excel File


💡 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.

Download Practice Workbook


Conclusion

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. Happy learning!


Related Articles


<< Go Back to Excel Reduce File Size | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo