Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet July 28, 2009Posted by computertrainer in Excel Tips.
Tags: delete empty columns, delete empty rows, Excel
If you have entered data in columns/rows at the right or end of your spreadsheet’s data area and later deleted that added data, Excel will remember that there was once something in that column or row … and remember … and remember … until you take action.
In the example below, the data ends at D45 but when I pressed CTRL + END the mouse went to G75. At one time there was data in column G and row 75.
I would like to fix it so that CTRL + END will take me to the actual end of the data, in this case D45.
- Select the empty columns that you need to remove.
(Remember to check each column to be sure it is empty … start in E1 then press CTRL + the down arrow. If you end up at the bottom of the spreadsheet – row 65536 in Excel 97 to 2003 or 1048576 in Excel 2007 – then the column is empty. Then move over one column and press CTRL + the up arrowto continue checking – you should be in row 1. Repeat as necessary.)
In this example you need to check then select columns E, F, and G.
- Right-mouse click on the selected columns and press Delete.
- Immediately SAVE.
NOTE: This is the critical step. Without pressing SAVE at this point, you might as well have not done the previous steps.
- Now select the empty rows 46 to 75. Since you can see these rows over to column D (now the last column with data) you do not need to check each row to be sure it is empty.
If you want to check … just to be sure, start in A46 then press CTRL + the right arrow. If you end up in the right most column (IV in Excel 97 to 2003 and XFD in Excel 2007), then the row is empty. Go down one row and then test that row with CTRL + the left arrow. You would continue until you checked down to row 75 in this example.
- Right-mouse click on the selected rows and press Delete.
- Again, SAVE immediately.
Now you can test your work.
- Press CTRL + HOME and you will be in the cell at the upper left of the active worksheet (A1 unless you have Frozen Panes).
- Press CTRL + END and you would be in D45.
Excel has finally forgotten that there was once data in row 75 and column G!