Deleting Blank Rows

Yes- Everyone knows how to delete a blank row  but what if you have downloaded a lot of data and see that you have sporadic blanks throughout rows and rows of data?  For instance,in the example below, I have 8 Customers but only sales for 3 and and a lot of extra blank rows too. What if you only wanted to see Customers who had sales. Sorting or filtering would help but still leave you with rows that you don’t want and could be time consuming.  The most efficient way to do this would be to select the Sales column as that is what you are most Excel datasheetdeleting blank rowsinterested in and then go to Find and Select on the Home tabSelect Go To Special...and select Blanks. Click OK.
Excel selects and displays in gray all the blank cells.
blank cells selected


Once all the blank cells have been selected, go to Delete on the Home tab and click the dropdown arrow and select
Delete Sheet Rows.delete cells


Excel deletes all the empty cells in the Sales Column that you selected so you end up with the 3 sales numbers and the corresponding Customers. Cullen and Lambert for example are deleted as they did not have any sales.

remaining rows

You do want to be careful though.If you had data that you wanted to keep in Column C, for example, that might get deleted.


Why did I only select the Sales column? In this particular example, it would not have mattered if I had selected both Column A and Column B; however, if I had different data and selected both columns I might have a problem. For example, in the screen shot below, I added sales at row 6 without a corresponding name. What do you think happens if I select both Column A and Column B and deleted all blank rows?  Yes, you guessed it- I would get the same answer as above and only have the 3 names and 3 sales figures. Cell A6 is blank so Excel deleted that sheet even though there were sales. If you selected multiple columns and then delete the blanks, Excel will end up only displaying cells that contain data in all the corresponding columns.excel datasheet



2 Responses

  1. Twtybrd71 says:

    In this example, you lose the line that had 45 in the sales column

Leave a Reply

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

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper

  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas

  • “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”- 

    - Cheryl