jump to navigation

Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet July 28, 2009

Posted by computertrainer in Excel Tips.
Tags: , ,
trackback

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.

 Excel ctrl + end to empty column row

I would like to fix it so that CTRL + END will take me to the actual end of the data, in this case D45.

 SOLUTION:

  1.  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.
  2. Right-mouse click on the selected columns and press Delete.
  3. Immediately SAVE
    NOTE:  This is the critical step.  Without pressing SAVE at this point, you might as well have not done the previous steps.
  4. 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.
  5.  Right-mouse click on the selected rows and press Delete.
  6.  Again, SAVE immediately.

 Now you can test your work.

  1.  Press CTRL + HOME and you will be in the cell at the upper left of the active worksheet (A1 unless you have Frozen Panes).
  2.  Press CTRL + END and you would be in D45.

 Excel has finally forgotten that there was once data in row 75 and column G!

Comments»

1. Jr Miller - June 26, 2014

Did this so many times and wound up frustrated… who would have thought CTRL + S was all I had to do…

2. Gigi Contreras - June 18, 2014

Great solution. So simple. Many thanks.

3. Sofía Bacas - June 11, 2014

Thank you very much! I was missing the save part.

4. Marcus Abrahamsson - January 16, 2014

Thanks

5. zamanisgeek - April 16, 2013

Many thanks, it helped me alot when trying to import to sqlite

6. pcgoblin - February 6, 2013

Reblogged this on PCgoblin's Blog and commented:
Ever have blank rows or columns at the end of an Excel sheet. I just found this very easy way to remove them.

7. pcgoblin - February 6, 2013

Well done @computertrainer, this is exactly what I have wanted for a long time, and it is so easy.

8. Win Than Htut - May 15, 2012

I want to share easy way with MS Excel 2007 to Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet.

Just Select All, Goto Data Tab, click “Remove Duplicates” and Save it. Then try press Ctrl + End and check your actual end of data.

computertrainer - May 15, 2012

Win, Thank you. Gretchen

9. Paulo Sérgio Magalhães - May 14, 2012

it’s not working in Excel 2007, I got several empty rows, when I press CTRL + END it takes me to the last unwritten cell (wich I need to be unwritten, row nr. 7713), but there is blank cells until nr. 1048576 when I press CTRL + DOWN, wich I can’t delete.
Is there a solution for this?

computertrainer - May 14, 2012

Paulo,
Ctrl + End will take you to the bottom right of the data (or where data once was). Yes, you will have lots of empty rows below 7713. That is not an issue. The issue is when you once had data, for example, down to row 8000 and then you deleted that data. Ctrl + End would take you to row 8000. This post is to help Excel forget that data once was in row 8000 and that the end of the data is not in row 7713. Does this make sense?


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: