Delete hidden rows in Excel. One by one and all at once

In Microsoft Office Excel, you can quickly remove hidden, empty lines that spoil the appearance of a table array. How to do this will be discussed in this article.

How to uninstall hidden rows in excel

There are several ways to accomplish the task, implemented using standard program tools. The most common of them will be discussed below.

Method 1. How to delete rows in a table one by one through the context menu

To cope with this operation, it is recommended to use the following algorithm:

  1. Select the desired line of the LMB tabular array.
  2. Click anywhere in the selected area with the right mouse button.
  3. In the context menu, click on the word “Delete …”.
Delete hidden rows in Excel. One by one and all at once
Path to the Delete Cells Window in Microsoft Office Excel
  1. In the window that opens, put the toggle switch next to the “String” parameter and click on “OK”.
Delete hidden rows in Excel. One by one and all at once
Selecting the correct option to delete a row in a table
  1. Check result. The selected line should be uninstalled.
  2. Do the same for the rest of the plate elements.

Pay attention! The considered method can also remove hidden columns.

Method 2. Single uninstallation of lines through the option in the program ribbon

Excel has standard tools for deleting table array cells. To use them to delete lines, you must proceed as follows:

  1. Select any cell in the row you want to delete.
  2. Go to the “Home” tab in the top panel of Excel.
  3. Find the “Delete” button and expand this option by clicking on the arrow on the right.
  4. Select the “Delete rows from sheet” option.
Delete hidden rows in Excel. One by one and all at once
Algorithm of actions for deleting a selected line from a worksheet through a standard program tool
  1. Make sure that the previously selected line has been uninstalled.

Method 3. How to remove all hidden lines at once

Excel also implements the possibility of group uninstallation of selected elements of a table array. This feature allows you to remove empty lines scattered across different parts of the plate. In general, the uninstallation process is divided into the following stages:

  1. In a similar way, switch to the “Home” tab.
  2. In the area that opens, in the “Editing” section, click on the “Find and select” button.
  3. After performing the previous action, a context menu will appear in which the user will need to click on the line “Select a group of cells …”.
Delete hidden rows in Excel. One by one and all at once
Selecting all empty rows in an array at once through the “Find and Select” option in Excel
  1. In the window that appears, you must select the elements to highlight. In this situation, put the toggle switch next to the “Empty cells” parameter and click on “OK”. Now all empty lines should be selected simultaneously in the source table, regardless of their location.
Delete hidden rows in Excel. One by one and all at once
Selecting empty rows in the cell group selection window
  1. Right-click on any of the selected lines.
  2. In the context type window, click on the word “Delete …” and select the “String” option. After clicking on “OK” all hidden items are uninstalled.
Delete hidden rows in Excel. One by one and all at once
Bulk Uninstall Hidden Items

Important! The method of group uninstallation discussed above can only be used for absolutely empty lines. They should not contain any information, otherwise using the method will lead to a violation of the table structure.

Delete hidden rows in Excel. One by one and all at once
Table with broken structure in Excel

Method 4: Apply sorting

The actual method, which is performed according to the following algorithm:

  1. Select the table header. This is the area where the data will be sorted.
  2. In the “Home” tab, expand the “Sort and Filter” subsection.
  3. In the window that appears, select the option “Custom sorting” by clicking on it with LMB.
Delete hidden rows in Excel. One by one and all at once
Path to custom sort window
  1. In the custom sorting menu, check the box next to the “My data contains headers” option.
  2. In the Order column, specify any of the sorting options: either “A to Z” or “Z to A”.
  3. After completing the sorting settings, click on “OK” at the bottom of the window. After that, the data in the table array will be sorted according to the specified criterion.
Delete hidden rows in Excel. One by one and all at once
Required actions in the custom sort menu
  1. According to the scheme discussed in the previous section of the article, select all hidden lines and delete them.

Sorting values ​​automatically puts all empty lines at the end of the table.

Additional Information! After sorting the information in the array, hidden elements can be uninstalled by selecting them all and clicking on the “Delete” item in the context menu.

Delete hidden rows in Excel. One by one and all at once
Uninstalling empty lines that were automatically placed at the end of the table array after it was sorted

Method 5. Applying filtering

In Excel spreadsheets, it is possible to filter a given array, leaving only the necessary information in it. This way you can remove any row from the table. It is important to act according to the algorithm:

  1. Use the left mouse button to select the table heading.
  2. Go to the “Data” section located at the top of the main menu of the program.
  3. Click on the “Filter” button. After that, arrows will appear in the header of each column of the array.
Delete hidden rows in Excel. One by one and all at once
Applying a filter to a source table in Excel
  1. Click LMB on any arrow to expand the list of available filters.
  2. Remove the checkmarks from the values ​​in the required lines. To uninstall an empty row, you will need to specify its serial number in the table array.
Delete hidden rows in Excel. One by one and all at once
Removing unnecessary lines by filtering
  1. Check result. After clicking on “OK”, the changes should take effect, and the selected elements should be deleted.

Pay attention! The data in the compiled table array can be quickly filtered by various criteria. For example, by cell color, by date, by column names, etc. This information is detailed in the filter selection box.

Conclusion

Thus, in Microsoft Office Excel, uninstalling hidden rows in a table is quite simple. You don’t need to be an advanced Excel user to do this. It is enough to use one of the above methods, which work regardless of the software version.

Leave a Reply