ExcelTutorials

Learn how to remove blank cells in Excel

2 Mins read

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.

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

How to remove blank cells in Excel?

We can do it by completing 2 stages:

  • Stage 1: Selecting those blank cells only
  • Stage 2: Deleting those blank cells

So let’s go through the following steps to solve this problem in Excel.

remove blank rows & columns cells in excel

Step 1: Create a table the same as in the picture above which has many unnecessary cells and rows blank in excel. We are going to 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 the Home tab. In the Editing Group click Find and Select.

go-to-special

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

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages) 

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

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

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.

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-row

Step 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!

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

15 Pivot Tables Tricks for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.

Most Popular Tricks are #3, #7 & #12

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…

2 Comments

Comments are closed.