Easy way to delete empty rows or columns without using macros in Excel?

To delete empty easily:

  1. In the right most column, put in the formula =counta([highlight everything left of the cell])
    Counta counts the # of cells that is not empty. So, if the entire row is empty, it will return 0.
  2. Drag and copy the formula down until the end of the area where you want to delete all empty rows.
  3. Highlight the entire column with the counta formula
  4. Turn on filter (On Excel 2010, its “Data”, then “Filter”)
    You should see a pull down menu box on top of the column
  5. Select it to show only 0
  6. Highlight all the rows with 0 and delete them
  7. Turn off the filter by clicking on the filter button again

All empty rows are now gone.

Caution:

  • Cells that are not truly blank but looks blank (for example, cells with 1 space character ” ” and nothing else) will be counted as non empty and will not be deleted by this process.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>