How to delete empty rows in Excel quickly

When importing and copying tables in Excel, empty strings and cells can be formed. They always district and interfere with the work.

Some formulas may not work correctly. It is impossible to use a number of tools for an incompletely filled range. We will learn how to quickly delete empty cells at the end or middle of a table. We will use simple tools available to the user of any level.



How to remove empty rows in the Excel table?

To show you how to delete extra lines, to illustrate the order of actions, take a table with conditional data:

conditional data.

Example 1: Sorting data in a table. Select the entire table. Open the "DATA" tab - "Sort and Filter" tool - press the "Sort" button. Another way is to right-click on the selected range and do the sorting «A to Z».

sorting.

Empty rows after sorting in ascending order are at the bottom of the range.

If the order of the values is important, then before the sorting, you need to insert an empty column, make a through numbering. After sorting and deleting blank lines, sort the data by the inserted column with the numbering again.

Example 2: Filter. The range must be formatted as a table with headers. We select the "cap". On the "DATA" tab, we click the "Filter" button ("Sort and Filter"). A down arrow appears to the right of each column name. Push - opens the filtering window. Remove the selection in front of the name "Empty".

Empty.

You can delete empty cells in the Excel line the same way. Select the required column and filter its data.

Example 3: Selecting a group of cells. Select the entire table. In the main menu on the "Edit" tab we click the button "Find and Select". Select the «Go To Special» tool. In the window that opens, select the «Blanks».

Blanks.

The program marks empty cells. On the main page we find the «HOME»-«Delete»-«Delete Cells».

Delete Cells.

The result is a filled range of "no voids".

Attention! After the removal, some of the cells jump upwards - the data can be messed up. Therefore, for the overlapping ranges, the instrument is not suitable.

Helpful advice! The shortcut to delete the selected row in Excel is CTRL + "-". And for its selection, you can press the hotkey combination SHIFT + SPACEBAR.



How to remove repeated rows in Excel?

To remove the same rows in Excel, select the entire table. Go to the tab "DATA"-"Data Tool"-"Delete Duplicates".

Delete Duplicates.

In the window that opens, select those columns that contain duplicate values. Since you need to delete duplicate rows, all columns should be highlighted. After clicking OK Excel creates a mini-report of the form:

creates a mini-report.

How to remove each second line in Excel?

You can use the macro to break the table. For example, this:

Sub Delete_Every_Other_Row()
' Dimension variables.
Y = False ' Change this to True if you want to
' delete rows 1, 3, 5, and so on.
I = 1
Set xRng = Selection
' Loop once for every row in the selection.
For xCounter = 1 To xRng.Rows.Count
' If Y is True, then...
If Y = True Then
' ...delete an entire row of cells.
xRng.Cells(I).EntireRow.Delete
' Otherwise...
Else
' ...increment I by one so we can cycle through range.
I = I + 1
End If
' If Y is True, make it False; if Y is False, make it True.
Y = Not Y
Next xCounter
End Sub

And you can do it by hand. We offer a simple way, accessible to each user.

  1. At the end of the table, make an auxiliary column. Fill with alternate data. For example, «N Y N Y», etc. We enter values in the first four cells. Then select them. "We catch" for the black cross in the lower right corner and copy the letters to the end of the range.
    2. copy the letters.
  2. Install the "Filter". Filter the last column by the value of "Y".
    3. Filter.
  3. Select all that is left after filtering and delete.
    4. filtering and delete.
  4. We remove the filter - only cells with "N" will remain.
remove the filter.

The auxiliary column can be eliminated and operated with a "decimated table".

How to delete hidden rows in Excel?

When the user had hidden some information in the rows he was not distracted from his work. I thought that later the data would be needed. It is not needed - hidden rows can be deleted: they affect the formulas, interfere.

In the training table are hidden rows 5, 6, 7:

training table.

We will remove them.

  1. Go to «FILE»-«Info»-«Check for Issues» the tool "Inspect Document".
    2. Inspect Document.
  2. In the opened window, put a tick in front of the "Hidden Rows and Columns". Click «Inspect».
    3. Inspect.
  3. After a few seconds, the program displays the result of the test.
    4. Remove All.
  4. Click "Remove All". A corresponding notification will appear on the screen.

As a result of the work done, the hidden cells are deleted, the numbering is restored.

Thus, remove the empty, repeating or hidden cells of the table using the built-in functionality of the Excel program.


en ru