Easy methods to delete clean rows in Excel in 2 easy steps

In most spreadsheets, empty rows are undesirable. Microsoft Excel makes use of areas to delimit information ranges, and a clean row within the flawed place will inhibit many built-in options. Fortuitously, there may be a straightforward option to take away clean rows from an information vary in Excel – however this method can probably destroy information, so it’s good to watch out.

On this Excel tutorial, I am going to present you a fast and simple option to delete clean cells, which you would possibly consider as rows, in the course of an information set with out by chance deleting adjoining information. All through the article, I exploit the time period “row” to explain contiguous empty cells inside the information set.

I am utilizing Microsoft 365 on a Home windows 11 64-bit system, however you should use earlier variations. Excel for the online helps this simple approach.

Step 1: Choose clean cells in Excel

When you understand that your information set has empty rows (cells), you’ll in all probability wish to delete them. Step one is to pick out the empty rows, which is a tedious course of if finished manually. Fortuitously, you do not have to.

To pick out all of the empty rows in a dataset:

1. Choose the info vary A3:E14 – or your individual information vary in case you are utilizing a special information set.
2. Press F5.
3. Click on on Particular within the Test what follows dialog field.
4. Click on the Areas possibility after which click on OK. Doing so selects the empty cells within the chosen vary. On this case it’s A7:E7 and A10:E10.

With Excel’s Go To operate, you may shortly choose all of the empty rows within the choice. Now you’re able to delete these rows. Don’t do something to deselect the choice.

SEE: Discover these Excel ideas that each person ought to grasp.

Step 2: Delete empty rows in Excel

Now you’re able to delete the chosen cells. Up till now I’ve used the time period rows, however deleting the precise rows will delete the whole lot in that total row, not simply the chosen vary. This contains information which may be off-screen.

To delete the chosen cells—not rows—click on Delete (not the drop-down record) within the Cells group on the House tab. This could solely delete the chosen cells. But when you aren’t getting the specified outcomes, press Ctrl + Z to undo the deletion, then attempt the next:

1. With the empty rows 7 and 10 nonetheless chosen, click on the Take away drop-down menu within the Cells group on the House tab, after which select Take away Cells.
2. Within the subsequent dialog, click on Shift Cells Up.

3. Click on OK.

Excel will then delete the empty cells from the chosen information vary.

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

Deciding on Clear Cells removes solely the empty cells within the chosen vary. In the event you select Delete Sheet Rows in step two, you might destroy information on the best facet. Select rigorously when utilizing this feature to delete empty rows once you actually solely wish to delete the empty cells. It is easy to consider rows and select the flawed possibility.

In such a small information set, you may simply delete every row individually, however think about doing that in case you have dozens and even tons of of information to assessment for empty cells. This technique will prevent effort and time and get rid of the opportunity of lacking areas.

Easy methods to keep away from catastrophe when deleting rows in Excel

Deleting rows, whether or not empty or not, might be harmful. For instance, in case you have information off the display and out of sight, you will not understand you are deleting it once you delete the empty cells or rows simply in entrance of you. Additionally, be careful for hidden rows that may be by chance deleted.

Moreover, many built-in options don’t work as anticipated when empty rows are encountered. Let’s suppose you wish to convert the pattern information set to a desk object, so that you click on inside the info set and press Ctrl + T. It simply so occurs that you just click on D5 and anticipate Excel to pick out the total information set. Nevertheless, it will not. Excel selects the adjoining rows subsequent to the clicked cell, D5, which occurs to be A3:E6 – a partial choice.

In the event you’re fortunate, you will discover that Excel would not specify the complete information vary. In the event you do not discover, you will not get the outcomes you anticipate once you use Desk options. You have to 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?

Fortuitously, there’s a option to delete empty rows utilizing a mixture of keyboard shortcuts. As a substitute of following the steps above, you are able to do among the work along with your keyboard. Here is tips on how to do it:

1. Use Ctrl + A to pick out all information to keep away from manually highlighting the vary that accommodates empty rows.
2. Open the Go To dialog with Ctrl + G, then press Alt + S to pick out Particular.
3. You have to choose Blanks and press Enter within the Go to Particular dialog field.
4. Press Ctrl + – with the clean cells highlighted to attain the required deletion.

Is utilizing filters nonetheless a great way to delete empty rows in Excel?

Filters are one other option to obtain the identical objective in Excel. Here is tips on how to use filters to delete empty rows:

1. Click on the Filter drop-down arrow in a column of your dataset the place some rows are empty.
2. Within the Filter menu, deselect all choices by unchecking Choose All, then verify solely areas.
3. Click on OK to use the Filter, which shows solely rows with empty cells in that column.
4. Spotlight the rows by dragging your mouse down or urgent Shift and deciding on rows.
5. Proper-click any chosen row and choose Delete Row, or press Ctrl + 1 and choose Whole Row.

Remember to choose a column the place a clean cell means the complete row is irrelevant. As all the time, a superb rule of thumb is to again up your information by saving a replica first earlier than deleting rows.