How to Delete Blank Rows in Excel in 2 Easy Steps

In most spreadsheets, clean rows will not be fascinating. Microsoft Excel makes use of white house to find out knowledge ranges, and a clean row within the fallacious location will inhibit many built-in options. Fortunately, there may be a straightforward solution to take away clean rows from a knowledge vary in Excel, however this system can doubtlessly destroy knowledge, so it is advisable to watch out.

In this Excel tutorial, I’ll present you a fast and straightforward solution to delete clean cells, which you would possibly consider as rows, in the course of a knowledge set with out by chance deleting adjoining knowledge. Throughout the article I exploit the time period “row” to explain adjoining empty cells inside the dataset.

I exploit Microsoft 365 on a 64-bit Windows 11 system, however you need to use older variations. Excel for the net helps this easy approach.

Step 1: Select clean cells in Excel

Once you understand that your dataset incorporates clean rows (cells), you’ll in all probability wish to delete them. The first step is to pick clean rows, which is a tedious course of if completed manually. Fortunately, this is not essential.

To choose all clean rows in a dataset:

1. Select knowledge vary A3:E14, or your individual knowledge vary in the event you use a special knowledge set.
2. Press F5.
3. In the ensuing Go To dialog field, click on Special.
4. Click the Blanks choice, then click on OK. This will choose the clean cells within the chosen vary. In this case, they’re A7:E7 and A10:E10.

Excel’s Go To perform permits you to rapidly choose all clean rows within the choice. Now you are able to delete these traces. Do nothing to deselect the choice.

SEE: Explore these Excel suggestions that each consumer ought to grasp.

Step 2: Delete clean rows in Excel

Now you’re able to delete the chosen cells. So far I’ve used the time period rows, however deleting the precise rows will delete your entire contents of the row, not simply the chosen vary. This consists of knowledge which may be off-screen.

To delete chosen cells, not rows, click on Delete (not the drop-down menu) within the Cells group on the Home tab. This ought to delete solely the chosen cells. But if you do not get the outcomes you need, press Ctrl + Z to undelete, then strive the next:

1. With clean rows 7 and 10 nonetheless chosen, click on the Delete drop-down menu within the Cells group on the Home tab, then select Delete Cells.
2. In the following dialog field, click on Move Cells Up.

3. Click OK.

Excel will then delete the clean cells from the chosen knowledge vary.

SEE: Learn analyze time values ​​in Microsoft Excel.

Choosing Delete Cells removes solely the clean cells within the chosen vary. If you select Delete Sheet Rows in step two, you could possibly doubtlessly destroy the info on the suitable. Choose fastidiously when utilizing this feature to delete clean rows while you actually wish to delete solely clean cells. It’s straightforward to consider traces and select the fallacious choice.

In such a small knowledge set, you could possibly simply delete every row individually, however think about doing that when you’ve got dozens and even tons of of information to evaluate for clean cells. This technique will prevent effort and time by eliminating the potential of lacking blanks.

How to Avoid Disaster When Deleting Rows in Excel

Deleting traces, clean or in any other case, could be harmful. For instance, when you’ve got knowledge off the display and out of sight, you will not understand you are deleting it while you take away the clean cells or rows proper in entrance of you. Also, watch out of hidden traces which may be deleted inadvertently.

Additionally, many built-in options don’t work as anticipated when encountering clean traces. Let’s say you wish to convert the pattern dataset to a Table object, then click on contained in the dataset and press Ctrl + T. What simply occurs is that you just click on D5, anticipating Excel to pick your entire dataset . However, it will not. Excel selects the contiguous rows adjoining to the clicked cell, D5, which seems to be A3:E6, a partial choice.

If you are fortunate, you may discover that Excel does not specify your entire knowledge vary. If you do not understand this, you will not get the anticipated outcomes when utilizing the desk options. You should delete clean rows to make use of a lot of Excel’s built-in options.

Can I additionally use keyboard shortcuts to delete clean rows in Excel?

Fortunately, there’s a solution to delete clean traces utilizing a mixture of keyboard shortcuts. Instead of following the above steps, you are able to do among the work with the keyboard. Here’s do it:

1. Use Ctrl + A to pick all knowledge to keep away from manually highlighting the vary containing clean rows.
2. Open the Go To dialog field utilizing Ctrl + G, then press Alt + S to pick Special.
3. You want to pick Blanks and press Enter within the Go to Special dialog field.
4. Press Ctrl + – with clean cells highlighted to attain the required deletion.

Is utilizing filters one other good solution to get rid of clean rows in Excel?

Filters are one other solution to obtain the identical objective in Excel. Here’s use filters to get rid of clean rows:

1. Click the Filter drop-down arrow in a dataset column the place some rows are clean.
2. In the Filter menu, deselect all choices by deselecting Select All, then choose solely the blanks.
3. Click OK to use the filter, displaying solely rows with clean cells in that column.
4. Highlight rows by dragging the mouse down or by urgent Shift and deciding on the rows.
5. Right-click any chosen row and select Delete Row, or press Ctrl+1 and select Entire Row.

Be positive to decide on a column the place a clean cell signifies that your entire row is irrelevant. As all the time, a superb rule of thumb is to again up your knowledge by saving a duplicate earlier than deleting rows.