remove blank cells in range in Excel

Q107. In Excel, how can I quickly remove all blank cells in a data range?

For example, I want to remove all the blank cells in the range so that data is continuous in all columns.

excel remove blank cells in range

To do it in Excel, here is the answer:

a) Select the range of data, press F5 and in the "Go To" dialog that pops up, click on "Special" (Alternatively, click on "Find & Select" under "Home". Click on "Go To Special").

excel excel remove blank cells in range

b) In the "Go To Special" dialog box, click on "Blanks", click OK. This will select all the cells that are blanks in the range selected.

excel excel excel remove blank cells in range

c) Click on "Delete" under "Home" tab. Click on "Delete Cells".

excel excel excel excel remove blank cells in range

d) In the "Delete" dialog that pops up, select "Shift cells up".

excel excel excel excel excel remove blank cells in range

e) All the blank cells will be deleted as shown below.

excel excel excel excel excel excel remove blank cells in range

 

You can find similar Excel Questions and Answer hereunder

1) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

2) Is there a way to easily shade alternate rows in a data range for improving readability?

3) How can I clear all formats (formats alone not data) from a range of cells?

4) How can I find the slab in which a given value fits?

5) How can I remove hyperlinks from a range of cells?

6) How can I identify cells with Circular Reference in a WorkSheet?

7) Remove the apostrophe cell text values in Excel

8) How can I get an estimate of Standard Deviation of a sample dataset?

9) How do I find the median salary of employees with the same skillset in my Organization?

10) How can I remove Outline from my WorkSheet?

11) How can I sort data using multiple criteria?

12) How can I set up a dynamic named range that expands automatically when new items are added to the list?

13) How can I remove display of Gridlines in my worksheet using VBA?

14) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

15) How can I add a Prefix to a set of numbers in a range?