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 *

  • Thank you for providing very informative CPEs. I have enjoyed your courses for years now. Yours is truly my go-to CPE sources.

    - Lily

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry