EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Remove (Delete) Blank Rows in Excel

Remove (Delete) Blank Rows in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 16, 2023

Delete Blank Rows in Excel

Remove Blank Rows in Excel Meaning

When we say “remove blank rows in Excel,” we mean deleting or removing any row in an Excel worksheet that does not contain any data or values. This means that all the cells in that row are blank, not providing helpful information.

These rows can sometimes be random or left behind after data manipulation, making the worksheet look messy and difficult to read. By removing these unnecessary rows, we can clean the worksheet and make it easier to read and study.

How to Remove Blank Rows in Excel?

Let’s understand the different alternatives for removing empty rows in Excel.

You can download this Delete Blank Rows in Excel Template here – Delete Blank Rows in Excel Template

There are six methods to remove empty rows in Excel.

1. Remove Blank Rows Manually
2. “Go to Special” Method
3. Excel Filter Functionality
4. Sort-by-column Method
5. Remove Blank Rows Using Power Query
6. Remove Blank rows in Excel using shortcut keys

Example #1 Remove Blank Rows Manually

The table contains information about employee sales through three columns: Employee Name in column A, Product Category in column B & Sales in column C. We need to delete empty rows in this table range manually.
Remove Blank Rows Example 1-1

Solution:
Step 1: Select Row 3 by clicking the number 3 (the blank row).
remove blank rows in excel-Example 1 Step 1

Step 2: Right-click anywhere on this row and select the Delete option.
Example 1 Step 2

Note: A shortcut key to delete a single row can be CTRL & ‘–‘
Result: Excel will remove that particular row and shift the next row in that place as per the below image.

Example 1 Step 2-2

Note: This method is only useful when the data is limited, and the selection is limited to a single row.

Example #2 Go To Special Method

The table contains Employee Name in column A, Product Category in column B & Sales in column C. We must delete empty rows in that table range with the “Go to Special” function In Excel.
remove blank rows in excel-Example 2

Solution:
Step 1: Select the entire table. Press the F5 function key.
The “Go to” dialog box appears.
Step 2: Click on ‘Special.’
Example 2 Step 2

The “Go To Special” dialog box appears.
Step 3: Click the ‘Blanks’ radio button and press OK.
remove blank rows in excel-Example 2 Step 3

You can see all the empty cells in blue color highlighted format.
Example 2 Step 3-2

Step 4: Now, click the Home Tab from the Cells section and Delete > Delete Sheet Rows.

Note: You should not click anywhere on the sheet during this process. Otherwise, the selection will disappear.

Example 2 Step 4

Result: It deletes all the blank rows in the table and moves up the data.

remove blank rows in excel-Example 2 Step 4-2

Note: One can locate “Go To Special” directly on the Home Tab at the far right corner by choosing the Find & Select option and then clicking “Go To Special.”

Example 2 Step 4-3

After this, the “Go To Special” dialog box appears per Step 3.

Example #3 Use of Excel Filter Functionality Method

The data in the following table represent Employee Name in column A, Product Category in column B & Sales in column C. We need to delete blank rows in the following table with the help of “EXCEL FILTER FUNCTIONALITY” in Excel.
Example 3

Solution:
We have to activate the Excel Data Filter for the data table.
Step 1: Select the entire data range or table range, and click the Filter button in the Data Tab in the Excel ribbon.

Note: You can also try the keyboard shortcut: Ctrl + Shift + L.

remove blank rows in excel-Example 3 Step 1

When you click Filter, each column in the first row will automatically have a small drop-down button or filter icon added at the right corner of the cell.
Example 3 Step 1-2

A drop-down arrow will be visible beside every column heading.
Step 2: Click the drop-down in the employee name column.
Example 3 Step 2

Step 3: Now, uncheck ‘Select All,’ check only the ‘Blanks’ option at the end, and click OK.
remove blank rows in excel-Example 3 Step 3

Note: Excel adds a funnel icon instead of the column’s drop-down list button indicating the FILTER function’s use on that column.

Once you have applied the Filter, only the entire blank rows will be visible.
Example 3 Step 3-2

Step 4: Select all the empty rows and remove them by pressing the Delete Sheet Rows button of the Home Tab.
Example 3 Step 4

Example 3 Step 4-2

Step 5: Clear the applied Filter by following the below-mentioned steps:
Go to the Data Tab > Sort & Filter group and click Clear.
Example 3 Step 5

Result: It will remove all the blank rows in the table. The blank cells no longer exist, and the table will look like this:

remove blank rows in excel-Example 3 Step 5-2

Example #4 Sort by Column Method

The below-mentioned table contains Employee Name in column A, Product Category in column B & Sales in column C. We must delete empty rows in the following table range using the Excel “SORT-BY-COLUMN” method.
Example 4

Solution:
We must insert a column before the Employee Name column and enter serial numbers. This procedure is necessary as getting rows back in order after using this method will be difficult.
Step 1: Select the data range or table, including headers, and click the Sort button in the Sort & Filter section of the Data Tab.
remove blank rows in excel-Example 4 Step 1

A Sort dialog box appears.
Step 2: Select the option for Employee Name in the Sort By option and click OK.
Example 4 Step 2

After sorting, data appears as below.
Example 4 Step 2-2

The rows with serial numbers 2,5 and 7 are blank rows that shift toward the end of the table.
Step 3: Select the blank rows and click the Delete button on the keyboard.
remove blank rows in excel-Example 4 Step 3

After deleting the rows, the data will be like this.
Example 4 Step 3-2

Before using the sort-by-column method, the order of employee names was “John, Chris, Shaw,” and now it is “Bret, Chris, Harry, etc.”

Step 4: Repeat Steps 1 & 2, but this time select the option for S.No in the Sort By option and click OK.
Example 4 Step 4

Result: The final table will have no blank rows, as below, with proper order.

Example 4 Step 4-2

Example #5 Remove Blank Rows Using Power Query Editor

The following table provides a breakdown of sales information in three columns: Employee Name in column A, Product Category in column B & Sales in column C. We need to delete rows containing no data in the following table range with the help of POWER QUERY EDITOR in Excel.
remove blank rows in excel-Example 5

Solution:
Step 1: Go to Data Tab and click From Table/Range option from the Get and Transform Data section.
Example 5 Step 1

Step 2: Select the range or table (A1:C10) from which you wish to remove blank rows and click OK.

Note: When including the headers in the selection, tick off the My table has headers option.

remove blank rows in excel-Example 5 Step 2

The Power Query Editor dialog box appears.
Step 3: Select Remove Blank Rows from the Remove Rows option.
Example 5 Step 3

The Power Query Editor changes the data by excluding the blank rows.
Step 4: To Import the new data, close the power query editor by clicking “Click & Load To” from the Close & Load option.
Example 5 Step 4

Step 5: Select a desired cell (F1) to import the new data in the “Where do you want to put the data?” section of the Import Data dialog box and click OK.
remove blank rows in excel-Example 5 Step 5

Result: The new data with no blank rows appear in F1 to H7 cell range.

remove blank rows in excel-Example 5 Step 5-2

Note: “Close & Load” will close the Power Query Editor window and load the transformed data directly into a new worksheet. On the other hand, “Close & Load To” will close the Power Query Editor window, but it will give you the option to choose the cell location for loading the new data.

Example #6 Remove Blank Rows using Shortcut Keys

The table elaborates on the employees’ sales performance for different categories of products in three columns, i.e., Employee Name in column A, Product Category in column B & Sales in column C. We need to delete blank rows in the following table range with the help of SHORTCUT KEYS in Excel.

Note: Shortcut Keys can be effective in case of limited data.

remove blank rows in excel-Example 6

Solution:
Step 1: Select the first row that needs removal, i.e., 3rd, the one per the image below.
Example 6 Step 1

Step 2: Press CTRL and then continue to select the blank rows without leaving the hold on the CTRL key.
Excel will help you select multiple rows individually in case there are unnecessary rows in between, per the image below.
remove blank rows in excel-Example 6 Step 2

Note: The selection will disappear if you leave the CTRL key and click elsewhere.

Step 3:  Right-click anywhere on this row and select the Delete option.
remove blank rows in excel-Example 6 Step 3

Result: This will help delete all the selected rows in one go, per the image below.

Example 6 Step 3-2

Things to Remember About Remove Blank Rows in Excel

  • It’s always better to back up data (by making copies in other sheets) before deleting a blank row in Excel.
  • Blank rows in Excel worksheets or tables can cause problems using Excel tools and functions like sorting or removing duplicates.
  • Use the appropriate method for removing blank rows according to the length and complexity of your data.
  • Double-check your data formatting after removing blank rows to ensure consistency and accuracy.
  • Consult the team members before removing blank rows in a shared spreadsheet to avoid confusion or data loss.

Frequently Asked Questions(FAQs)

Q1. What are the shortcuts to open the “Go To” dialog box in Excel?
Answer: In Excel, you can use several shortcut keys to open the “Go To” dialog box, which allows quick navigation to specific cells. Here are a few options:

  • Press the “F5” key on your keyboard.
  • Press the “Ctrl” + “G” keys on your keyboard.
  • Press the “Ctrl” + “Shift” + “F5” keys on your keyboard.

Q2. How to select multiple rows?
Answer: In Excel, you can apply the following methods to select multiple rows:

  • Click and drag: Click the first row you require to select, and while holding down the left mouse button, drag your mouse down to select additional rows.
  • Shift key: To select multiple rows, click the first row and hold “Shift.” Click the last row to select every row between the first and the last cell.
  • Ctrl key: Click on each row to select all the non-consecutive rows by holding the “Ctrl” key.

Recommended Articles

This article is a guide to Remove Blank Rows in Excel. Here we discuss how to Remove Blank Rows in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. ROWS Function in Excel
  2. Remove Duplicates in Excel
  3. Cheat Sheet of Excel Formulas
  4. Excel Conditional Formatting for Dates
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Delete Blank Rows in Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Delete Blank Rows in Excel Template

EDUCBA

डाउनलोड Delete Blank Rows in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more