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.

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.

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.

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.

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

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.

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

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

**Read More: ****Reduce Excel File Size Without Opening**

**2. Removing Conditional Formatting and Other Formats to Reduce Excel 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.

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

**Read More: ****How to Compress Excel File for Email**

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

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.

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

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.

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

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

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

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

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.

**Steps:**

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

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

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

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

Here, we have used a **Pivot Table** 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.

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

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

- Choose the
**Save As**option.

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

- Click on the marked portion in the following picture.

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

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

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

- Click on
**Save**.

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

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

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

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

**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!