How To Delete/Remove Blank Rows & Columns In Excel 2010 Spreadsheet

Here is a small tip for those using Excel 2010 who want to remove all blank rows in an easy way without having to remove each blank row manually. Please note that this tip applies to removing multiple columns as well.

First select the portion of the spreadsheet from where you wish to remove the blank rows and then hit the Home tab. Here navigate to the Editing options and choose the Go To Special option under the Find & Select option.

Go To Special

In the Go to Special dialogue box, choose Blanks and hit OK.

Blanks Excel 2010


All the blank rows will be selected.

Now choose the Delete Sheet Rows option under the Delete drop down box.

Delete Sheet Rows

If you want to remove blank columns, select the portion of the spreadsheet from where you want to delete the blank columns, follow all steps as listed above and finally in the end select the ‘Delete Sheet Columns’ option.

For more, learn how you can delete duplicate rows in Excel.

Advertisement
  • GVENO

    I followed your instructions and all rows were deleted, not just the blank ones.

    • Burt

      That may be because there were some empty cells in the rows that got selected.
      Row A was deleted when i first tried because of one blank cell.

  • Steve

    Excellent. Worked exactly as you said. Thanks

  • Gopi

    It works. Thanks for the tip.

  • Brian

    Appears to only work if there are no blanks in a given row, otherwise any row containing one blank cell gets deleted. Anyone know of a way to delete only those rows that are completely blank and leave the rows with some content and some blank cells?

    • Kate

      Seek out asap-utilities.com. It has a delete all blank rows command that deletes all the blank rows in 3 EZ selections, with no “editing,” as in the command above.

      • Anonymous

        can’t/don’t want to buy more stuff……
        thanks anyway

  • rizwan

    what if i want to delete only those cells which are completely blank instead of partially blank ones??? need urgent help

  • LL

    Was going nuts trying to delete MANY hidden blank columns, great tip, worked like a gem, thanks!

  • kjk

    2010 didn’t delete rows. Vertical scroll bar still goes to Row 1048576. Even after save, exit, reboot, restart and re-open.

    I think there is a bug.

  • Big D

    DANGER! DANGER, WILL ROBINSON, DANGER! DO NOT USE THIS IF YOU HAVE ANY ROWS IN THE SELECTED AREA THAT ARE ONLY PARTIALLY BLANK!

    This works . . . BUT . . . it will also remove rows in the selected area that have ANY blank cells in the row . . . if 1 or more cells has information, BUT there is ONE BLANK CELL . . . IT WILL REMOVE THE ENTIRE ROW . . . you can lose lots of information.

  • evelynm

    There is not a shortcutkeys or quick command to delete all the blank rows in Excel. Though the steps are provided in detail above, if you can find a way to delete them in on click, that will be great. Kutools for Excel is the Excel application i get in the search. It provides a button to delete blank rows, very fast and intuitive. Besides, it is a collection of tools that will deal with some repetitive work in Excel, such as rename multiple sheets, sort sheets , and so on. I am still keeping in trying the app.

  • Anonymous

    following these instructions, deleted EVERYTHING.
    thanks for nothing.

  • Excel guru 2

    In my report, I only select the blanks in one column.  This column included blanks in the rows I wanted to delete.  Then you can right click and delete rows.  All is well.

  • Da

    awsome

  • Chargin

    Ladies/Gents
     
    If it is deleting too much just select a column that always has a value in it (eg a description) instead of selecting everything and then follow the steps above. It will then delete only the rows that are totally blank.
     
    If none of your columns always have at least one value in it just follow these steps:
    1. Insert a new column A
    2. Put in the following formula: CONCATENATE(A1,B1,etc for all your columns)
    2. Drag it down for all your rows
    3. Copy and paste special (values)
    4. Follow the steps in this article-only highlighting column A
    5. Delete column A

    • Liz Schreur

      thanks that works great!!

  • Nospamplease

    Great tip!  Work just as advertised.

  • Guest

    Another thing you can do is select all your data, and sort by one of the columns. The blank row will go to the end.

  • Heeeheee

    Wow – that just saved me about 2 hours on a 25,000 line spreadsheet!! Thank you!!

  • http://www.befoodsmart.com/ dsb

    Thanks! Very helpful. Exactly what I was looking for. Saved me so much time!

  • gettingeducated

    This only works if each row with entries no blank cells. If any row with entries has a blank cell, this will delete that entire row. It seems the only way blank rows across multiple columns while leaving the partially blank rows intact is to use macros code. :<

  • Steve

    i need to remove excel blank rows on a mac but not just any rows all blank ones scattered in the sheet! How do i do this i have the mac here with me but no idea where to start… please help me find the Go To Special bar from the very first step!!! THANK YOU THIS WILL MEAN ALLOT TO ME.

  • http://profile.yahoo.com/KZKXNGIAWKKSCCDOBIDOE6AR4Y Michelle M.

    I did it and it wont delete my blank ones….. :-

  • emtedee

    I dont bother deleting blank rows – this is way too hard to remember. I highlight the area I want to keep and copy it into a blank sheet and then delete the sheet with all the unwanted rows and columns and voila the job is done and the spreadsheet is manageable again

    • Name

      this is the only way I found to get rid of the one million lines I don’t want. How the heck did they get there in the first place?

  • Adam

    It doesn’t really work. If you have a load of blank lines at the end of your sheet, and you delete them, Ctrl-End will still jump you to the end of the blank areas, not the end of your data. What can’t Excel just delete lines when told too, not just clear the content

  • Mike

    DON’T SELECT “DELETE ROWS”! As others have noted, that will delete everything, because when you select all the blank areas, it’s selecting all the empty cells to the right of the cells you’ve typed stuff into.

    Instead, select “Delete Cells” after doing the Go To Special. This worked for me (Excel 2010).

  • Tiago Moreira

    Cool!!! Thanks man! ;-)

  • dina

    That worked quick and perfect. saved me a ton of time.

  • Annonymous

    Wonderful! Saved a lot of time

  • umair ahmad

    nice

  • gdawn3296

    Anybody know how to delete rows without deleting formulas

  • sunnyd1972

    I only wanted to fix sections of my spreadsheet and not the whole document so when I tried selecting the rows I wanted to apply this function to it sort of worked for me, however, it removed my first (selected) row even though it had data in it and no blank spaces. If I put a blank row above it and tried again, it deleted the whole selection. I could not figure out why it would delete the first row. I got frustrated so I copied that row and pasted it above the selection, did it again knowing it would disappear then moved it back when I was finished. Furthermore, it only worked when I deleted my empty columns first (there were only a few so it wasn’t a big deal) then I was able to use these instructions to delete empty rows. Ugh, what a pain! Wish I can go back to the older excel version. Too much got messed up.

  • Jack

    Great! My “learn one new thing every day” for today!

  • http://www.boldinternet.co.uk/ Adrian Bold

    Thanks! :-)

  • scrapn4me

    I followed the directions and it deleted ALL the rows I highlighted, not just the blank ones.

  • Guillermo

    Excelent, It works for me :) Thanks!

  • Mark Sullivan

    this tip is errneous all the rows were deleted

  • MG

    Ok I’ve got it! – to remove ONLY the rows that are blank but leave the rows with blanks in them do this:

    1. Select a whole column that has values/data in it throughout the entire workbook (i.e. invoice number) a.k.a. the important values needed even if they have blank cells in them.

    2. (Repeat above steps) Find & Select > Go to special > select blanks > OK

    3. Cells > Delete > Delete Sheet Rows. It should only remove the blank rows instead of important rows full of information.

    This worked for me, let me know if you guys see any problems with this.

    • Nicola

      This worked for me, eventually with a bit of playing around.
      I’d been trying to delete 1,700,000+ rows from the bottom of a sheet (which are added when a whole column is formatted). Scrolling problems occur because of the extra rows in the spreadsheet.
      Make sure that you check what has happened before you save it again, overwriting your origianl saved copy.
      Note that what is said above is true, that is to say, if you select the whole of a populated area of a spreadsheet, and then go to Find and Select>>Go to Special and select blanks, that this will select any rows with any blanks, so doonly select a column that is always populated with something.
      Once again, many thanks, I have been looking for a solution to this problem for months, and will save a printed copy of these instructions in case I need it again!!! :0)

  • pradip

    Thank you very much great work

  • Vicky

    Perfect, thank you very much

  • matt

    this method deletes rows that have both blank cells and cells with data. Find special won’t simply select the empty rows, just the cels.

  • matt

    I think enough negative comments warrants revision of the post as it is misleading to the outcome of the technique

  • Jim Lad HarrrHarrr

    As some have already noted, this method may delete rows that contain data and that you ot wish to keep.
    I have routinely imported tables of data from the web to Excel 2010. Not all of the cells in a given table contain data, but there is at least one column that has data in each cell of the table.
    When I have used this method exactly as described, I find that any rows that have at least one blank cell will be deleted, even though other cells in that row are not blank.

    I have found a workaround for this problem. But it requires that, in the block of cells that I wish the method to work upon, there is at least one column that has no blank cells in any row.
    So, this is what I do:

    1: Ensure that, for each row that you wish to keep, there is a single column that has no blank cells.
    TIp: If there is no such column, it’s easy to insert a new column before existing column A, and put a formula into each of its cells that tests all the other cells in that row for blank cells and, if there is none, inserts a string or number into the cell if there is any data on any of the other cells in that row. Example:
    =IF(OR(ISBLANK(B1), ISBLANK(C1), ISBLANK(D1)),”",”data in this row”)

    2: Select only that column.

    3: Do the “Find & Select>Go To Special>Blanks” procedure.

    All rows will be selected for the cells in that row that are blank.

    4: Do a “Delete Rows”.

    All the blank rows will be deleted. Check to make sure.
    If you created a column as in 1 above, just delete the entire column and the job will be done.

    It might seem a long way around, but once you have done it, it really isn’t. And if you’re working on a 300 row table which has blank rows scattered around one to every ten rows, it’s easier than deleting each row manually.

    There are other workarounds but, in my experience, they each require more steps than the above.

    • Jim Lad HarrrHarrr

      Some of my comment above might be misleading.

      Several times I mentioned having a column that has no blank cells in any row.

      What I meant was “a column that has no blank cells in any of the rows that I want to keep”.

      The formula I gave in (1) was correct, though. It inserts a string into the cell if there is any other cell in the range B1:D1 that is not blank. You should be able to alter that formula to test for other ranges of cells in that same row.