Learn how to remove blank cells in Excel

How to remove blank cells in Excel? confused? Data doesn’t always import or paste into Excel as nicely as you want. Sometimes, all the information is there, but extra blanks and spaces are carried along with the data cells. Think that if you work with a large data set in an Excel Workbook or Excel Spreadsheet, there are times you might need to delete a blank cell or set of blank cells section.

The process can become time-consuming; if you have a lot of data. Instead of going through the workbook, and deleting sheet rows or each blank row or column individually, you can make multiple selections and let Excel do the work for you.

How to remove blank cells in Excel?

We can do it by completing 2 stages:

  • Selecting those blank cells only
  • Deleting those blank cells

So let’s go through the following steps to solve this problem in Excel. remove blank cells in excel Step 1: Create a table same as in the picture above which has many unnecessary cells and rows blank in excel. We are going remove blank cells from this table.

  • Select Range of Cells
  • Click on Home Tab
  • Click on Find & Select [Right-Hand Side]

find-and-select

Step 2: Select the whole table and go to Home tab. In the Editing Group click Find and Select.

go-to-special

Step 3: Under Find and Select list click Go To Special.

select-blanks

Step 4: Now Go To Special dialog box will appear. Check blanks and click on OK.

after selection

Step 5: After that, we can see in our Excel sheet that this method only selected blank cells from our selected range of the table. So our first stage is completed, now let’s go to the second stage and delete these blank cells.

select-delete

Step 6: In our second stage we need to delete only those selected blank cells. Now right-click on the selected cells. The Pop-Up Menu will appear. Select the delete option.

select-shift-cells-left

Step 7: Now Delete box will appear. Check Shift cells left to delete columns from the selection. Click OK and see the magic.

Read More: Learn Online Courses i.e. Become Data Analyst Course | Advanced Excel Course | Excel VBA Macro Tutorial

selected cells Step 8: Now we can see the blank Columns are deleted. Now we need to delete the blank Rows in excel.

select-entire-rowStep 9: Do again step 2 to step 5. Then right-click on the selected cells. The Pop-Up Menu will appear. Select Delete. Now from Delete box Check Entire Row to delete rows from the selection. Click OK.

remove blank cells result See, all blank cells are deleted now. Happy data cleaning!

Read More: Tricks of text to a Column in MS Excel

So, in this blog, you have learned how to remove empty rows in Excel with simple and small tricks.

Also, you can remove empty rows & column in excel by using this method.

2 Responses
  1. Dave Grant

    Thanks. Will this delete columns that may not be entirely filled with blank cells, but columns that have only one or two blank cells due to incomplete data, which I would NOT want to be deleted?

    Thanks,
    Dave

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.