Is there a formula to skip blank rows in excel?

June 29, 2016 at 05:24:21
Specs: Windows 7
I have a data set that looks like:


I want it to look like:


See More: Is there a formula to skip blank rows in excel?

June 29, 2016 at 05:56:12
A very easy way to do this would be to add a heading to the column, set a filter, filter on blanks, this will only show you the blank rows now, then delete the blank rows.

When you clear the filter all blank rows will have been removed and your data will be together.

Report •

June 29, 2016 at 06:46:45
Another quick way is to

Select your range of cells
Click F5
In the Go To dialog box,
Click Special.
Click the Blanks option
Click OK.

On the Home tab,
Click the Delete drop down
Choose Delete Cells.

Excel should display the Delete dialog box,
Select Shift Cells Up option

Click OK


message edited by mmcconaghy

Report •

June 29, 2016 at 11:16:45
This may not be the best solution in this instance, but it teaches you the concept of a Helper Column. Enter a column with 1,2,3,4, etc. next to your data. Sort both columns based on your data. This will group all the blank cells together.

Delete the blank cells and the numbers in the Helper Column, then resort on the Helper Column, then delete the Helper Column. Your values will return to their original order with no blanks in between.

A Helper Column is nothing more than a column that you can use as an "intermediate step" to get you to your final goal.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

June 30, 2016 at 23:46:49
Remove blank rows in Excel
This technique works if there's a column in your table that helps to see if it's an empty row or not (a key column). for instance, it are often a client ID or order range or something similar.

It is necessary to save lots of the rows order, thus we will not simply type the table by that column to move the blank rows to the lowest.

Use Filter Button for this issue and click here for better understanding with screenshots.

message edited by aren111

Report •

June 30, 2016 at 23:51:53

That is exactly what I suggested in post #1. Not sure the OP will be back though I assume they figured it out after posting here..

Report •

Ask Question