How To Shorten Excel Sheet

The problem is that you have too many columns and rows in your Excel sheet and you are wasting a lot of valuable space. You want to shorten it. So, we are here to help.

Below we will cover what you need, some good tips on how to proceed, and finally step-by-step instructions on how to shorten your spreadsheet.

Ways To Shorten the Spreadsheet

• Delete unnecessary cells e.g. empty columns, rows
• Hide unnecessary columns and rows
• Stretch the hidden columns and rows back so everything is compact again

Here are some tips for your reference:

Tip1: Use AutoFilter to quickly find the empty columns and rows

Tip2: Use the Conditional Formatting feature to help you easily find and delete unnecessary cells

You may want to delete some unnecessary cells quickly before you proceed with hiding some other columns and rows. Please use AutoFilter to quickly find blank cells.

• Start the search from the cell you want
• In the lower-left corner, choose the ‘Filter’ button
• Then click on Cell > Filter (the AutoFilter button)
• Choose the filter criteria. For example, if you want to find empty columns, use “Column” as the filter criteria and remember to apply it to all of your blank columns.

Tip3: Use conditional formatting to hide unnecessary columns and rows with one click.

If you want to hide unnecessary cells quickly, the conditional formatting feature will be helpful. Please start with normal Excel and then use the rest of the article to alter your spreadsheet and hide unnecessary columns and rows.

1. First, check if conditional formatting is available in your version of Excel. For example, if you have an older version of Excel (or a trial version), then you may not have it at all.
2. After that, select the cells to format.
3. In the ribbon, click the ‘Conditional Formatting’ button
4. On the ‘Conditional Formatting dialog box, choose a rule from the first drop-down list and choose a color from the second list. Additionally, you can adjust other parameters.
5. Click ‘OK’.
6. Check out your spreadsheet and you will find that the unnecessary cells are now hidden.

Resize Excel File

1. You can see this file size is 20.5MB. We are gonna convert this file.

1. Open the sheet and go to file

1. Click the save as

• Select Browse

• Select your output location

• Change the file name if necessary

• After that select ‘excel binary workbook’ in the ‘save as type

• Save the file

• This 20.5MB file turned into a 2.38MB file

• Open the file and check

This is the most straightforward way to shorten your excel sheet.

How To Set the End of an Excel Spreadsheet

Excel allows you to set the end of your workbook so that when you save it, only the data on the sheet is saved. You can set a different “End” for each sheet in your workbook. This way, if a sheet contains confidential information, will be hidden.

Setting Excel’s “End” is easy and only takes a few seconds to do. Let’s see how to do it for a single sheet as well as for all sheets in your workbook.

• Open your workbook.
• Select the sheet that you want to set the end for (e.g. sheet “A”) and then right-click on it.
• In the drop-down menu choose “End Of This Worksheet”
• Now you should see the end of your sheet set at this location
• Save the workbook with a new file name.
• Close it and open it again to see if the end of it has changed to where you clicked in Step 4 above as shown below:
• The End of Sheet “#A” has been changed at this location now!
• Now click on “File -> Save As…,” and save the file with a new name.
• Close it and open it again to see that the end of it has changed to where you clicked in Step 4 above.

Reduce Excel File Size Without Opening

Excel has a feature to compress/encrypt workbooks. When you enable password protection on your workbook, it also compresses the file.

The original data (and formatting) are maintained. Only the size is reduced. You can still open the file, it’s just smaller. You can surely read or update the data and format it in Excel.

Enable Encryption/Compression

• Click the File tab, then click Save As.
• Under Encryption (possible only if you have a version that supports this feature), select either 128-bit or 256-bit encryption.
• Select the current location or specify a new location, and then click Save.
• The file is saved in your specified location with a .xlsx extension. Now you can safely move it to different places, send it via email or store it on the computer or network drives.

Compress Excel Files Offline

If you want to compress/compress your Excel files offline, you can use the built-in ZIP function to achieve the same result.

NOTE: If you compress your Excel files offline, they can be read on any computer that has Office installed.

The ZIP function can be found in the menu on the right-hand side of the Excel ribbon.

• Click the Zip button after selecting the file.
• Your desktop will be saved with compressed files.

Excel File Size Limit

Excel provides a built-in feature that allows you to increase/decrease the file size limit of Excel for all files in Excel.

• Click File -> Options-> Save tab.
• Under General, limit the file size to < 4GB.
• To check it, save a workbook with a large number of rows and columns, close Excel, and re-open it. If the file size exceeds the limit, it should be deleted.

You can also increase/decrease the limit on a particular workbook by using this technique:

• Go to File: Go -> New, select Save As, and click the More Options button.
• Change the Maximum size field and click OK.
• Select the new workbook and close Excel.
• Open your new workbook which is now smaller than your may have wanted it to be, save it again, and re-open Excel.
• You can confirm that the file size is now smaller by using Save As.

Reduce the file size of your Excel workbook 

Why is My Excel File So Large

There are many reasons why your Excel file size is exorbitantly large, and the reason is generally not related to the data in the file. Your Excel file size could be full of junk data, and waste space or it could also be because you have some very valuable (but rarely used) features turned on that are contributing a large amount of unnecessary space.

The good news is, you can easily reduce your Excel file size without any loss of data, and with very little effort on your part.

Autoformatting can be a major culprit for large Excel files. The best way to control the mess in your spreadsheet is to disable Autoformats as soon as possible. This is because Autoformats are always changing (even if you don’t change them), and are also using up tons of unnecessary space.

FAQ

How To Reduce Excel File Size by Deleting Blank Rows?

Delete extra rows and columns to reduce your Excel file size.

You can use the Delete option to delete extra rows and columns, but this may affect the format of your spreadsheet.

• Select a blank cell or cell range (not containing any values or formatting)
• Press Ctrl + Del, duplicate rows or columns, and then delete the summarized cells.
• Check out your spreadsheet to ensure that you have not deleted any cells you wanted.
• Now you have the desired blank cells or cell range.
• Change the formatting and enter the data in the cells or cell range you want.

How Do I Remove Thousands of Rows in Excel?

If you have a large number of rows in your data file, you can try the following technique to reduce the number of rows.

• Select a cell in the bottom half of your data range
• Use Ctrl + Home and Ctrl +End to select all cells above and below this cell.
• Delete the selected range (all cells from where you are currently working downwards)
• Save and exit Excel.

How Do I Adjust the Length in Excel?

• Go to File: Go -> New, select Save As, and click the More Options button.
• Navigate to Tools -> Publisher and then select Custom Size.
• For Width and Height, enter an integer value. Do not include a unit or the value will change when you move the focus back to Excel.
• Save your new workbook and re-open it.
• You will see that the width of your workbook has changed to the specified width and the height for the new length you have entered.
• Now you can start filling the cells with data, or in this case, there is a very small number of rows that occupy a large portion of your Excel file (literally thousands), it would also be a good idea to delete them because these are just wasting space.
• Find a blank cell or a cell range that is not part of your required data, and then delete it.
• Update any formatting as required.

This technique can also be used to reduce the amount of data you are working with if you are trying to reduce the size of your Excel file by removing rows and columns.

Conclusion

Now that you know how to shorten your spreadsheet, you should be able to do so.

If you want to reduce the size of your Excel file, you should use the functions and features provided by Microsoft Office’s AutoFit feature. The AutoFit feature will allow you to customize the rows and columns in your spreadsheet so that it does not change when you open or save a file.

This reduces the amount of wasted space in your data because it will allow Excel to store only what is necessary. That’s how to do it easily.