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 you.
Below we will cover what you need, some good tips on how to proceed, and finally detailed instructions on how to shorten your spreadsheet.
- Delete unnecessary cells, such as blank columns, rows
- Hide unnecessary columns and rows
- Stretch the hidden columns and rows so that everything is compact again
Here are some tips for your reference:
Tip 1: Use the auto filter to quickly find empty columns and rows
Tip2: Use the conditional formatting feature to find and delete unnecessary cells easily
You may want to quickly delete some unnecessary cells before proceeding to hide other columns and rows. Use the automatic filter to find blank cells quickly.
- Start the search from the desired cell
- In the lower left corner, choose the “Filter” button.
- Then click Cell > Filter (the AutoFilter button)
- Choose your filter criteria. For example, if you want to find empty columns, use “Column” as the filter criterion and remember to apply it to all empty 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 come in handy. Please start with regular Excel and then use the rest of the article to edit your spreadsheet and hide unnecessary columns and rows.
- 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), you may not have it at all.
- After that, select the cells to format.
- In the ribbon, click the “Conditional Formatting” button.
- In the “Conditional Formatting” dialog box, choose a rule from the first drop-down list and choose a color from the second list. Also, you can adjust other parameters.
- Click “OK”.
- Check your spreadsheet and you will find that unnecessary cells are now hidden.
- You can see that this file size is 20.5MB. We will convert this file.
- Open the sheet and go to the file
- Click Save As
- Select your exit location
- Change the file name if necessary
- After that select “excel binary workbook” in “Save as type
- This 20.5MB file has turned into a 2.38MB file
This is the easiest way to shorten your Excel sheet.
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 one “End” for each sheet of your workbook. This way, if a sheet contains confidential information, it will be hidden.
Excel setup “End” it’s easy and only takes a few seconds to do. Let’s see how to do this for a single sheet and for all sheets in the workbook.
- Open your workbook.
- Select the sheet for which you want to set the end (e.g. sheet “A”) and then right click on it.
- In the drop-down menu choose “End of this worksheet”
- You should now see the end of your paper set at this location
- Save the workbook with a new file name.
- Close it and open it again to see if the ending has changed where you clicked in step 4 above, as shown below:
- The end of sheet “#A” has been changed to this position 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 ending has changed to where you clicked in step 4 above.
Excel has a function to compress/encrypt workbooks. When you enable password protection on your workbook, it also compresses the file.
The original data (and formatting) is retained. Only the dimensions are small. You can still open the file, it’s just smaller. You can certainly read or update the data and format it in Excel.
- Click the File tab, then click Save As.
- Under Encryption (only possible if you have a version that supports this feature), select 128-bit or 256-bit encryption.
- Select your current location or specify a new location, then click Save.
- The file is saved in the specified location with an .xlsx extension. Now you can safely move it to different places, send it by email or store it on your computer or network drives.
If you want to zip / zip your Excel files offline, you can use the built-in ZIP function to achieve the same result.
NOTE: If you compress Excel files offline, they can be read on any computer where Office is installed.
The ZIP function is found in the menu on the right side of the Excel ribbon.
- Click the Zip button after selecting the file.
- Your desktop will be saved with compressed files.
Excel provides built-in functionality that allows you to increase / decrease the Excel file size limit for all files in Excel.
- Click File -> Options -> Save tab.
- In general, limit the file size to <4GB.
- To test this, save a workbook with a large number of rows and columns, close Excel and reopen it. If the file size exceeds the limit, it should be deleted.
You can also increase / decrease the limit on a particular workbook 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 you wanted it to be, save it again and reopen Excel.
- You can confirm that the file size is now smaller using Save As.
Reduce the file size of your Excel workbook
There are many reasons why your Excel file size is exorbitant and the reason is generally not related to the data in the file. The size of your Excel file could be full of junk data and wasting space or it could also be because you have some very valuable (but rarely used) features activated that are contributing to a large amount of unnecessary space.
The good news is that you can easily reduce the size of your Excel file without any data loss and with minimal effort on your part.
Automatic formatting can be a major cause of large Excel files. The best way to control spreadsheet clutter is to disable automatic formats as soon as possible. This is because the automatic formats change all the time (even if they aren’t changed) and also use tons of unnecessary space.
How to reduce Excel file size by deleting blank lines?
Delete extra rows and columns to reduce Excel file size.
You can use the Delete option to delete additional rows and columns, but this may affect the format of the worksheet.
- Select a blank cell or range of cells (containing no values or formatting)
- Press Ctrl + Del, duplicate rows or columns, then delete the summarized cells.
- Check your spreadsheet to make sure you haven’t deleted the cells you wanted.
- Now you have the blank cells or range of cells you want.
- Change the formatting and enter data in the desired cells or range of cells.
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 lower half of the data range
- Use Ctrl + Home and Ctrl + End to select all cells above and below this cell.
- Delete selected range (all cells where you are currently working down from)
- Save and exit Excel.
How can I adjust the length in Excel?
- Go to File: Go -> New, select Save As and click the More Options button.
- Go to Tools -> Editor and then select Custom Size.
- For Width and Height, enter an integer value. Don’t include a unit or the value will change when you return focus to Excel.
- Save your new workbook and reopen it.
- You will see that the width of your workbook has changed to the specified width and height to the new length you entered.
- You can now start filling the cells with data, or in this case there are a very small number of rows taking up a large portion of your Excel file (literally thousands), it would also be a good idea to delete them as these are just wasted space .
- Find a blank cell or a range of cells that is not part of the required data, then delete it.
- Update any formatting as required.
This technique can also be used to reduce the amount of data you’re working with if you’re trying to reduce the size of your Excel file by removing rows and columns.
Now that you know how to shorten your spreadsheet, you should be able to.
If you want to reduce the size of your Excel file, you should use the functions and features provided by the Microsoft Office AutoFit function. The AutoFit feature will allow you to customize the rows and columns in your spreadsheet so that they don’t change when you open or save a file.
This reduces the amount of wasted space in your data because it will only allow Excel to store what is needed. Here’s how to do it easily.