Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • Use Windows Defender Offline Tool to Fix an Infected PC Computer Tips
  • How to Make a QR Code How-To
  • Check Disk and System Files in Windows 8/10 Windows 10
  • Best ir40t calculator ink roller, black/red 2020 black
  • Turn Off Admin Approval Mode in Windows 7 Windows 7
  • Best texas instruments ti-84 plus graphing calculator charger 2020

    Best texas instruments ti-84 plus graphing calculator charger 2020. 1 - TI-84 Plus C Silver Edition Charger, Cable, and Haxtor Brand - Battery Chang

  • How to Anonymously View Instagram Stories Without an Account How-To
  • How to Burn a DVD on a Mac Mac OS X

How to Delete Blank Lines in Excel

Posted on October 8, 2020 By blog_786 No Comments on How to Delete Blank Lines in Excel

One of the most common tasks in Excel is to remove blank lines. Regardless of the type of data that you may have in Excel, in many cases you will also have many blank lines throughout the file.

If you have thousands of lines, manually removing blank lines is a big problem and almost impossible. The most common way to remove blank lines in Excel, at least as mentioned on the web, is to use some kind of Excel macro.

However, if you are not familiar with macros, this method can be difficult to implement. Also, if it doesn’t work as expected, you might not know how to change the macro for your specific Excel file. In this article, I will show you several ways to remove blank lines in Excel and the advantages and disadvantages of each method. Note that these methods will work for any version of Excel, from Excel 2003 to Excel 2016 and later.

First method – add a sort column

Fortunately, there is a simple and very effective way to remove blank lines without any macros. This mainly involves sorting. Here’s how to do it. Let’s say we have the following dataset in Excel and we want to get rid of empty rows:

The first thing we’ll do is insert a column and number it sequentially. Why do we need to do this, you may ask? Well, if the order of the rows matters when we sort column A to get rid of empty rows, there will be no way to return the rows in the order they were in before sorting.

– /

This is what the table should look like before sorting the column with animal names:

Now select both columns and click the data ribbon in Excel. Then click the Sort button on the Data tab.

In the Sort By box, select Column B and click OK. Note that you want to sort the column that has empty values. If multiple columns have blank values, just select one.

Your data should now look like below. As you can see, removing blank lines is easy because they are all at the bottom:

After you remove the rows, now you can probably understand why we added a column of numbers? Before sorting, the order was “Cat, Dog, Pig, etc.”, and now it is “Bear, Cat, Cow, etc.” So just sort again by column A to revert back to the original order.

Pretty simple, right? What I like about this method is that it is very easy to delete rows that have only one column with empty values, and not the whole row is empty. So what if you need to check multiple columns, not just one?

Well, using the same method, you simply add all the columns to the Sort By dialog box. Here’s another example I created:

How to Delete Blank Lines in Excel

As you can see, this sheet is more complex. Now I have some completely blank lines, but some lines are only partially blank. If I just sort by column B, I don’t get all the completely blank rows at the bottom. After adding the numbers, select all five columns and by clicking Sort, you will see that I have added four levels to the Sort By option.

How to Delete Blank Lines in Excel

When you do this, only those rows will be displayed at the bottom where all four columns I added are empty.

How to Delete Blank Lines in Excel

You can now remove those lines and then use the rest of the lines to revert them to the original order they were in.

The second way – the use of filters

Similar to using the sort function, we can also use a filter parameter. Using this method, you don’t need to add anything like this to any additional column. Just select all the columns you want to check for spaces and click the Filter button.

How to Delete Blank Lines in Excel

You will see a dropdown arrow next to each heading in the title bar. Click on it and then uncheck the Select All box and check the Blanks box at the bottom.

How to Delete Blank Lines in Excel

Now do the same for each column on the sheet. Note that if you get to the point where only blank lines are displayed, you don’t need to select blank spaces for any of the remaining lines as they are already checked by default.

How to Delete Blank Lines in Excel

You will see the empty line numbers are highlighted in blue. Now just select those lines, right click and choose Delete. When they disappear, just click the Filter button again to remove the filter and all the original non-blank lines should reappear.

Method 3 – Delete any blanks

Another method that I would like to mention will remove any row that has either a completely empty row or any row that has at least one empty column. This is definitely not for everyone, but it can be useful if you want to delete rows that contain only partial data.

To do this, select all the data on the sheet and press the F5 key. This will bring up the Go To dialog box in which you want to click on Special.

How to Delete Blank Lines in Excel

Now select “Presets” from the list and click “OK”.

How to Delete Blank Lines in Excel

You will see that all blank cells or rows will be grayed out. Now, on the Home tab, click Delete and select Delete Sheet Rows.

How to Delete Blank Lines in Excel

As you can see, this removes any row in any column of which there is at least one blank cell.

How to Delete Blank Lines in Excel

This is not useful for everyone, but it can be quite useful in some cases. Choose the method that works best for you. Enjoy!

–

Share this:

  • Twitter
  • Facebook
Office Tips

Post navigation

Previous Post: Search Inside Multiple Text Files at Once
Next Post: Enable or Disable Windows Firewall from Command Prompt

Related Posts

  • How To Recall An Email In Outlook Office Tips
  • 3 Ways To Split a Cell In Excel Office Tips
  • How to Trace Dependents in Excel Office Tips
  • Fix Word Opens in Safe Mode Only Office Tips
  • Basic One-Column and Multi-Column Data Sorting in Excel Spreadsheets Office Tips
  • How to Delete A Microsoft Word Page Office Tips

Leave a Reply Cancel reply

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

Archives

  • October 2023
  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • Buying Bitcoin in India? Top 4 Indian Bitcoin Exchange Compared
  • TemplateMonster WordPress Theme Review – Build a Site With Ease
  • 6 Best FTP Clients For Android
  • MailTag: Real-time Email Tracking, Made Easy
  • Here are the Top 10 highest paid YouTubers of 2017
DMCA.com Protection Status

Recent Posts

  • 3 Sites like YouTube to Earn Money With Your Videos
  • Buying Bitcoin in India? Top 4 Indian Bitcoin Exchange Compared
  • TemplateMonster WordPress Theme Review – Build a Site With Ease
  • How to use Canva Docs to create documents online
  • PS4 won’t turn on but beeps? 9 Fixes Worth Trying

Recent Comments

  1. How to Cancel Your Wall Street Journal (WSJ) Subscription on How to Cancel Your New York Times Subscription
  2. How to Fix Spotify Lyrics Not Showing Up on How to Delete or Delete Downloads from Spotify
  3. 6 Best FTP Clients For Android on Disney Plus not working on Fire TV? Try These 8 Fixes
  4. Protect Your Facebook Account From Hackers (2017) on Top 5 Facebook Alternative Apps For iOS Devices
  5. 4 Ways to Share WiFi Without Giving Away Your Password on How to fix if PS5 Not Connecting to Internet?
  • How To Record a FaceTime Call & Other VOIP Calls On PC & Mobile Tutorials
  • How to use OpenShot Video Editor for beginners Reviews
  • Best ti-84 calculator charger 2020

    Best ti-84 calculator charger 2020. 1 - Charger for Texas Instruments TI-84 Plus CE Graphing Calculators, TI 84 Plus C Silver Edition, TI-Nspire CX,

  • How do i fix netflix error 113 Computer Tips
  • How to Send Secure Encrypted Email for Free Cool Websites
  • Best android office suites Software Reviews
  • How To Downgrade macOS OS X
  • How to find out which Kindle model you have Computer Tips

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version