• About
  • Contact
  • Privacy Policy
  • Submit For Review
  • Template Gallery
  • Excel 2016 Udemy Course
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Mobile Apps
  • Software and Web Apps
  • Buying Guides
  • Show Search
Hide Search
Home » How To » How to Delete Blank Rows in Excel 2016

How to Delete Blank Rows in Excel 2016

October 13, 2016

Sometimes, when you copy and paste a ton of content onto your Excel worksheet, you end up with a lot of blank rows or columns. You can delete them by right-clicking the rows or columns and choosing the Delete option but that can become very trying if you have too many rows. Plus, there’s always the danger that you might lose content. That’s why you should be careful when you’re deleting data and in this Excel tutorial, I’ll show you how to delete blank rows in Excel 2016 the right way.

How to Delete Blank Rows in Excel 2016

Our worksheet contains some data on the team standings in the English Premier League. Some rows were inserted into the worksheet to fill in some additional information, but it was later decided to keep that out of the worksheet. The result is a bunch of unwanted blank rows sitting in the worksheet. To delete these blank rows here’s what we need to do.

1. Select all the data. Or if you like shortcuts, press Ctrl + Home and then press Ctrl + Shift + End.

select-all-the-data

2. Select the header row. Then select the Data tab. On the Sort & Filter group, click Filter to add the AutoFilter button to the worksheet header row.

filter-the-data

3. Click the Filter button in the Position column, uncheck Select and then scroll down, select Blank, and click OK.

filter-position-column

4. Click inside the worksheet and press Ctrl + Home. Then press the down-arrow key to go to the first data row, then press Ctrl + Shift + End.

select-filtered-rows

5. Right-click on the selected row and from the contextual menu, select Delete row.

delete-entire-sheet-row

6. In the Delete entire sheet row confirmation box that pops up, click OK to confirm the deletion.

confirmation-to-delete

Now remove the filters and you’ll find that all the blank rows are completely removed.

how-to-delete-blank-rows-in-excel-2016-method-1

This method works best when you have a column with serial numbers or IDs to identify the rows that are blank. There’s one drawback in this method that is also worth noting. If you have the first column, that is, the serial number column blank, but there is some data further down the row, Excel will assume the row is blank and delete it.

How to Delete Blank Cells in Rows with Partial Data

So if you have data scattered across different columns, and you need to delete only those rows that do not have any data whatsoever in any cell of that row, you need to first identify the rows that have no data and then do the filtering and deletion. For detecting the blank rows, you need to make use of the COUNTBLANK function. Let’s see how this works.

ALSO READ:  3 Excel Tips for Better Department Budget Management

In our worksheet, in Row, 4, let’s remove the data in columns C to G. Similarly, in column 6, lets remove the data in columns E to G.

Now, If we used the steps outlined above, our filter will show both Rows 4 and 6 lined up for deletion. That’s why we need to insert a column to identify fully blank cells and then filter only such cells. For this we apply the CountBlank function. Let’s see how this works:

1. In cell I1, enter the header title, Blanks.

2. In cell I2, enter the formula, =COUNTBLANK(A2:H2)

What we are doing here is identifying how many blank cells are there in the row. In row 2, that’s 0.

countblank-function

3. Now, we’ll use the AutoFill feature to drag this formula down to the last row to find out the number of blank cells in each row.

4. Select the header row. Then select the Data tab. On the Sort & Filter group, click Filter to add the AutoFilter button to the worksheet header row.

filter-the-countblank-cells

5. Click the Blanks Filter button. Uncheck the Select All option and choose 8. This will filter all rows which are completely blank. Click OK.

5. Now, click inside the worksheet and press Ctrl + Home. Then press the down-arrow key to go to the first data row, then press Ctrl + Shift + End. This will select all the rows for deletion.

select-the-rows-that-are-completely-blank

6. Right-click on the selected row and from the contextual menu, select Delete row.

7. In the Delete entire sheet row confirmation box that pops up, click OK to confirm the deletion.

Now all the rows that were fully blank will be deleted, while rows 4 and 6 that had partial information continue to be displayed in the worksheet.

How to Delete Blank Rows in Excel 2016 - method 2

If you found this tutorial helpful, be sure to check out more good stuff in our Learn Excel series. If you want to learn Excel from scratch, consider taking up our Udemy course, Excel 2016 for Beginners. It is available at a nominal price of just $10.

Tweet10
Share1
WhatsApp
Pin
Buffer
11 Shares

Filed Under: How To Tagged With: excel

You May Also Like

Ways to Use Excel Mobile - TATFI
10 Creative Ways to Use Excel Mobile in your Business
excel 2010 tutorials
How to Create Summary Worksheets in Excel 2010
excel 2010 tutorials
Remove Duplicate Rows in Excel 2010 in 4 Super Quick Ways

About Adeline Gear

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Hottest New Gadgets!

Best e Readers to Buy in 2023 - TATFI

5 Best eReaders to Buy in 2023

Amazfit Falcon Smartwatch - TATFI

The Amazfit Falcon is a Military Grade Smartwatch with Premium Looks and Great Features

Couchmaster Cyboss Ergonomic Couch Desk - TATFI

Couchmaster Cyboss is an Ergonomic Desk to Keep Off Neck Pain

Philips Hue Festavia String Lights - TATFI

Add a Bit of Sparkle to Your Christmas Tree with Philips Hue Festavia String Lights

Samsung Galaxy Tab A7 Lite Kids Edition - TATFI

This Kid Friendly Tablet from AT&T is a Thoughtful Gift for Your Little One This Christmas

Is An iPhone Really Worth It - TATFI

Is an iPhone is Really Worth It?

Devices You Might Want to Try - TATFI

4 Fantastic Devices You Might Want to Try

Popular Tags

android android apps android games android how to apple casino games chrome extensions excel excel how to facebook gadgets gaming gmail google instagram ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft music apps new releases outlook how to photo apps powerpoint powerpoint how to productivity Samsung security smartphone smartphones software tablet tablets twitter web browsers windows 10 word how to xbox youtube

Disclosure

TheAppTimes is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.
How to improve writing skills with apps - TATFI

How to Improve Writing Skills with Apps and Gadgets

Samsung Galaxy S10 Unlocked - TATFI

The Samsung Galaxy S10 Unlocked– Your Key to Total Freedom

Make Money Playing at Fairspin Online Casino - TATFI

Make Money Playing at Fairspin Online Casino

How Mobile Wellbeing Apps are Empowering Patients - TATFI

How Mobile Wellbeing Apps are Empowering Patients

Tips to Boost Your Ranking and Sales on Etsy - TATFI

5 Ways to Boost Your Ranking and Sales on Etsy

PunchBee Time Management App - TATFI

Know How You Spend Your Time with PunchBee

DevOps Model Explained - TATFI

The Complete Guide to DevOps: How the Model Works and Who It Benefits

Approaches to Digital Marketing - TATFI

Blending Technical and Content-Centric Approaches to Digital Marketing

Tech Tips For Startups - TATFI

Useful Tech Tips For Startups

Boox Tab X Android eReader - TATFI

Boox Tab X is an Android eReader That Helps You Do More Than Just Read

Best Translation Apps for Android - TATFI

5 Great Translation Apps for Android

Dynamic Island Feature on Your Android - TATFI

How to Get the Dynamic Island Feature on Android

TheAppTimes © 2023 // Runs on StudioPress