MacOS – How to delete empty rows in excel but not empty cells in rows with information in them

macmacosms office

I am working with a large excel spreadsheet and I need to delete all the rows that are completely blank.

I do not want to delete rows or cells with any information in any of the columns.

Thanks in advance

Best Answer

What I am assuming is that you have a large worksheet with gaps you need to close up, and that a simple sort would disrupt the order of your data.

Here's how I would do it: First add two new temporary columns, new A and new B. Select the range of cells in A from the first all the way to the last row you know has data. Use Edit > Fill > Series > Linear and number them from 1 to whatever.

In the next column select the first cell and enter a formula that checks to see if the cells to its right are empty. It could be as simple as adding them all up (=SUM(C1:K1)) to see if they are zero or it could be a function of logic (=AND(ISBLANK(C1), ISBLANK(D1)), etc.). Copy and paste this formula down all the cells in column B to the end of your range.

Now you have two keys with which to sort your worksheet. Sort on column B to find your empty rows and delete them en masse, then restore your worksheet's original sort order by sorting on the first column.

Related Question