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

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.

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 which are not related to our main dataset.

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

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.

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

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

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.

  • Next, from the dialogue box choose Shift cells up.
  • Afterward, click on OK.

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

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.

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

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

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

Consequently, 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: 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.

Steps:

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

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 cells. Just have a look at the following screenshot.

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

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.

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:

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

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

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

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 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:

  • 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.
=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 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.
=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 utilizing 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: 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. 

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:

  • Firstly, 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

  • 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

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

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.

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

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.

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

Steps:

  • Firstly, go to the File tab from the Ribbon.

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

  • Afterward, 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)

  • Following that, 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.

  • Afterward, 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)

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.

Steps:

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

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

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

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

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.

Steps:

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

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

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

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

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.

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


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo