![delete blank columns in excel and keep data delete blank columns in excel and keep data](https://i.ytimg.com/vi/qGZQIl9JJk4/maxresdefault.jpg)
This isn’t useful for everyone, but in some cases, it’s quite handy also. Now on the Home tab, click on Delete and then choose Delete Sheet Rows.Īs you can see, this deletes any row that has even a single blank cell in any column. You’ll see all the blank cells or rows will be highlighted in grey. Now select Blanks from the list and click on OK. This will bring up the Go To dialog, where you want to click on Special. To do this, select all the data on the sheet and press the F5 key. This is definitely not for everyone, but it could be useful if you want to delete rows that have only partial data. One other method I wanted to mention will delete any row that has either a completely blank row or any row that has even a single blank column. Once they are gone, just click on the Filter button again to remove the filter and all of your original non-blank rows should reappear. Now just select those rows, right-click and choose Delete. You will see the number for the blank rows are highlighted in blue. Note that if you get to a point where there are only blank rows showing, then you don’t have to select blanks for any of the remaining rows as it’s already checked by default. Now do the same thing for each column in the worksheet. Click on that and then uncheck Select All and check Blanks at the bottom. You’ll see a dropdown arrow appear next to each title in the header row. Just select all the columns that need to be checked for blanks and click on the Filter button.
![delete blank columns in excel and keep data delete blank columns in excel and keep data](https://d13ot9o61jdzpp.cloudfront.net/images/exclude_blanks_5.png)
Using this method, you don’t have to add in any extra column of anything like that. In a similar way to using the Sort function, we can also use the Filter option. Now you can delete those rows and then go ahead and resort the rest of the rows to get them back into the original order they were in. When you do this, only the rows where all four of the columns I added are blank will show up at the bottom. After adding the numbers, select all five columns, and clicking on Sort, you can see I added four levels to the Sort by option. If I just sort by column B, I won’t get all the completely blank rows at the bottom. Now I have several rows that are completely blank, but several rows that are only partially blank. Here’s another example I created:Īs you can see, this sheet is more complicated. Well, using this same method, you would just add all the columns in the Sort by dialog. So, what if you need to check on multiple columns rather than just one? Pretty easy right? What I like about this method is that it’s also really easy to delete rows that only have one column with blank values rather than the entire row being blank. So just sort again by column A to get the original order back. Once you delete the rows, now you can probably see why we added the column with the numbers? Before sorting, the order was “Cat, Dog, Pig, etc.” and now it’s “Bear, Cat, Cow, etc…”. As you can see, it’s easy to delete the blank rows because they are all at the bottom: Now your data should look like this below. If more than one column has blank values, just pick one. Note that you want to sort the column that have the blank values in it. Then click on the Sort button on the Data tab.įor Sort By, choose Column B and then click OK. Now select both columns and click on the Data ribbon in Excel. Here’s what the sheet should look like before we sort the animal name column: You might ask why we would want to do this? Well, if the order of the rows matters, when we sort Column A to get rid of the blank lines, there will be no way to get the rows back in the order they were before sorting. The first thing we’ll do is insert a column and number it consecutively. Let’s say we have the following set of data in Excel and we want to get rid of the blank lines:
![delete blank columns in excel and keep data delete blank columns in excel and keep data](https://i1.wp.com/www.alphr.com/wp-content/uploads/2018/12/111.pf4_.png)
Luckily, there is a simple and very effective way to delete blank lines without any macros. Note that these methods will work for any version of Excel, from Excel 2003 all the way to Excel 2016 and beyond. In this article, I’ll show you a couple of ways you can delete blank rows in Excel and the advantages and disadvantages for each method.
Delete blank columns in excel and keep data how to#
Also, if it doesn’t work properly, you may have no idea how to change the macro for your particular Excel file. However, if you’re not familiar with macros, this method can be difficult to implement.