Clear the applied filter: go to the Data tab and press the Clear button.Click OK in the " Delete entire sheet row?" dialog box.Right-click on any selected cell and choose " Delete row" from the context menu or just press Ctrl + - (minus sign).Select all the filtered rows: Press Ctrl + Home, then press the down-arrow key to go to the first data row, then press Ctrl + Shift + End.Apply the filter to the " Cust #" column: click the arrow in the column header, uncheck the (Select All) checkbox, scroll down to the end of the list (in reality, the list is quite long) and check the checkbox (Blanks) at the very bottom of the list.Add AutoFilter to the table: go to the Data tab and click the Filter button.Select the whole table, from the 1st to the last row (press Ctrl + Home, then press Ctrl + Shift + End).It is important to save the rows order, so we can't just sort the table by that column to move the blank rows to the bottom. For example, it can be a customer ID or order number or something similar. This method works if there is a column in your table which helps to determine if it is an empty row or not (a key column). If you want to save your time - go straight to the 3rd way. What if you are not so lucky or you do not have a backup copy?įurther in this article I will show you 3 fast and reliable ways to remove empty rows from your Excel worksheets. If you are lucky, you will discover the loss in a few hours, restore your workbook from a backup, and will do the job again. If you have a small table, you will notice a loss of data, but in real tables with thousands of rows you can unconsciously delete dozens of good rows. Row 4 (Roger) is also gone because cell D4 in the "Traffic source" column is empty: ( We want to remove rows 3 and 5 because they are empty.ĭo as suggested above and you get the following: The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.įor example, we have a table of customers, 6 rows altogether. This is a very bad way, use it only for simple tables with a couple of dozens of rows that fit within one screen, or better yet - do not use it at all. In the " Delete" dialog box, choose " Entire row" and click Entire row.Right-click on any selected cell and select " Delete…".In the " Go to special" dialog, choose " Blanks" radio button and click OK.In the dialog box click the Special… button.Highlight your data from the 1st to the last cell.
Never remove blank rows by selecting blank cellsĪll over the Internet you can see the following tip to remove blank lines:
#EXCEL DELETE BLANK ROWS KEYBOARD SHORTCUT HOW TO#
Video: How to remove blank rows in Excel.The fastest way to remove all empty rows - Delete Blanks tool.Delete blank rows if your table does not have a key column.Never remove empty rows by selecting blank cells.Anyway, if your goal is to remove all those empty lines to get a nice and clean table, follow the simple steps below. There may be various reasons why blank rows penetrate into your sheets - you've got an Excel workbook from another person, or as a result of exporting data from the corporate database, or because you removed data in unwanted rows manually.
So, every time you have to specify the boundaries of your table manually, otherwise you will get a wrong result and it would take hours and hours of your time to detect and correct those errors. You know that every so often blank rows appear in your worksheets, which prevents most built-in Excel table tools (sort, remove duplicates, subtotals etc.) from recognizing your data range correctly. If you are reading this article, then you, like me, are constantly working with large tables in Excel. All solutions work in Excel 2019, 2016, 2013 and lower. In this quick tip I will explain why deleting Excel rows via select blank cells -> delete row is not a good idea and show you 3 quick and correct ways to remove blank rows without destroying your data.
0 kommentar(er)
