Trimming Excess Cells in Excel

Question: I use Excel for the church accounts. I have several worksheets for one workbook/file. On one of the worksheets there have somehow got over 14,000 rows below the area I actually use. I have tried to delete them by highlighting the unwanted rows and selecting delete rows to no avail. It makes it very tricky using the slide bar accurately, so how can I get rid of the unwanted lines please? Thank you. Kind regards, Mary

Answer: Mary, let me tell you there is no magic bullet for this one. But there is a way to get rid of the extra empty space at the end of your workbook. This is the solution I know works.

Let me review a few short-cut keys I use all the time and how I used them to work this issue out. The first one is Ctrl+End. This keyboard combination will take you to the “end” of the worksheet, however far it may be. Ctrl+Home will bring you right back to the beginning of the worksheet. Cell A1. Armed with this information, add the Shift key and you have a powerful tool for selecting a wide range of cells all at once.

For example, if you want to select the range of cells A1 to the end (or all the cells that occupy space in your worksheet, place your cursor at A1 by clicking on it or hitting Ctrl+Home. Now hit Shift+Ctrl+End and you will notice that ALL your work is highlighted – including the seemingly empty cells at the end of your document.

Unfortunately, highlighting the empty cells and deleting them or emptying them does not bring the end of the document to the end of your work. Excel for some reason keeps track of the space you have used and stretches your worksheet each time you insert cells, rows or columns, but it does not shrink to size once you’ve deleted content.

Here’s what I suggest you do. First insert a new worksheet in your workbook by clicking on Insert then Worksheet . Come back to your original worksheet and place your cursor at the end of your work. You can either mouse over to the last column and row, or you can use your arrow keys to move to that cell. Now click Shift+Ctrl+Home and all your work will be selected. Now press Ctrl+c. This command copies the selected area.

Finally, move to the new worksheet. You will notice that the cursor is on A1 by default. Press Ctrl+v. This command will paste the content copied from the previous worksheet starting at A1. The good news is no extra empty space will exist at the end of this worksheet. Your scroll bars will be thick and manageable.

To get the column widths exactly like your previous spreadsheet, do the following operation: click on edit, then paste special.  At the pop-up menu select column widths and click ok.

Once you are satisfied with your new worksheet. You can delete the old one by right-clicking on its tab at the bottom of your workbook and selecting Delete.

This operation takes a little bit of work, actually, but well worth it.

Leave a comment or question