How to condense empty rows /columns in Excel?
For example you created/received a large workbook, and now you want to condense this workbook by deleting empty rows/columns, any idea? In this article, we describe several easy methods to get it done easily.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
This method will guide you to select all blank cells by Go To Special feature, and then delete empty rows or columns by Delete Sheet Rows feature or Delete Sheet Columns feature in Excel. Please do as follows:
1. Select the range where you want to condense all empty rows/columns/cells, and click Home > Find & Select > Go To Special. See screenshot:
2. In the opening Go To Special dialog box, please check the Blanks option, and click the OK button. See above screenshot:
Now only blank rows are selected in the specified range. See screenshot:
3. Click Home > Delete > Delete Sheet Rows. See screenshot:
So far all blank rows are removed and condensed from the specified range. See above screenshot:
For condensing and removing all empty columns, please click Home > Delete > Delete Sheet Columns after selecting all blank cells.
Note: Make sure there are only blank rows or blank columns in your selected range before applying the Delete Sheet Rows or the Delete Sheet Columns feature, if the range is mixed with blank rows and columns, some contents will be deleted at the same time.
Kutools for Excel supports the Delete Blank Rows utility to help Excel users to quickly delete all blank rows from a selected range, or delete all blank rows from active/selected/all worksheets in Excel.
1. Click Kutools > Delete > Delete Blank Rows, and then specify the deleting scope from the submenu. See screenshot:
Note: For deleting all blank rows from the specified range, please select the specified range before clicking Kutools > Delete > Delete Blank Rows > In Selected Range.
2. And then a dialog box pops out and shows how many blank rows were deleted successfully. Just click the OK button to close it.
Now all blank rows have been deleted and condensed from the specified scope, such as active sheet, all sheets, etc. See screenshot:
Kutools for Excel provides another Delete Hidden (Visible) Rows & Columns utility to delete all empty rows or columns from the selected range, or from active/selected/all worksheets in Excel easily.
1. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns.
Note: For deleting all empty rows or columns from the specified range, please select the specified range before clicking Kutools > Delete > Delete Hidden (Visible) Rows & Columns.
2. In the opening Delete Hidden (Visible) Rows & Columns dialog box, please do as follows:
(1) Specify the deleting scope from the Look in drop down list. In our case, we selected the In All Sheets;
(2) Check the Rows option or Columns option which you will delete in the Delete Type section;
(3) Check the Blank columns (or Blank rows) option in the Detailed type section;
(4) Click the Ok button.
3. And then a dialog box pops out and shows how many blank columns (or rows) have been deleted. Just click the OK button to close it.
So far all empty rows/columns have been deleted from the specified range or active/selected/all sheets based on specified deleting scope.