Skip to main content

How to condense empty rows /columns in Excel?

Author: Kelly Last Modified: 2017-03-27

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.

Condense empty rows/columns from selected range

Condense empty rows from selected range or active/selected/all sheets

Condense empty rows/columns from selected range or active/selected/all sheets

Quickly delete unused blank rows or columns from selected range/active sheet/all sheets

With Kutools for Excel Delete Hidden (Visible) Rows & Columns utility, Excel users can quickly delete all unused rows or columns from specified selected range, active sheets, multiple selected sheets, or all sheets with ease in Excel.

ad delete blank rows columns 1


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:

doc condense empty rows columns cells 2

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:

doc condense empty rows columns cells 3

3. Click Home > Delete > Delete Sheet Rows. See screenshot:

Delete Sheet Rows feature
All blank rows have been deleted

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.

Delete all blank columns in the specified range

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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1. Click Kutools > Delete > Delete Blank Rows, and then specify the deleting scope from the submenu. See screenshot:

Excel addin: one click to delete all blank rows

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:

all blank rows have been deleted

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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.

Excel addin: Delete all hidden/blank/visible rows and columns
configurate to delete all blank 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.

dialog: show the total number of deleted columns

So far all empty rows/columns have been deleted from the specified range or active/selected/all sheets based on specified deleting scope.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations