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

by on
34

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.

  • 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

  • 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! :-)