How to Remove Blank Rows in Excel?

Blank or empty rows in between a dataset can be cumbersome to look at and work on.

Sample dataset with blank or empty rows

We often encounter this type of format with datasets exported from a system or a database.

With the blank rows in between, it’s hard to go over the data as you are forced to scroll down just to see the remaining data entries.

You can’t even select all the data by pressing CTRL + A.

Example of what happens when you press CTRL + A to select all cells in the dataset

(Notice that Excel can only select the data entries before the blank rows.)

Also, if you try to apply the filter after selecting the headers, Excel is not able to capture all the records in the dataset (as seen in the example below).

Exmple of what happens when you apply filter after selecting the headers in a dataset with blank or empty rows.

If you want the filter to work correctly with this format, you will have to either select the entire columns or manually select all the records in the dataset – which can be very inconvenient.

In this tutorial, I’ll show you different ways to remove these empty rows so you can easily work on your data.

Use the “Go to Special” option to Remove Blank or Empty Rows in Excel

The first step to removing blank or empty rows is to select the entire dataset.

Since we can’t make use of CTRL + A to select it, we can do either of the following:

a) Select the entire columns of the dataset (as shown below).

Select entire columns of the dataset

b) Or select the first cell and scroll down until you find the last cell of the data set. Then, while pressing the SHIFT key, click on it.

Select the first cell and scroll down until you find the last cell of the data set. Then, while pressing the SHIFT key, click on it.

If you don’t have other data at the bottom of the data set, you may want to use the first option since that is the quickest way to select the cells.

After highlighting the cells, press CTRL + G.

The “Go To” menu should appear. Once you see it, click on the Special… button.

Click on the "Special" button from the "Go To" menu.

You will then see the Go To Special menu. Select Blanks and click OK.

Steps to select blank cells from the "Go To Special" menu

Notice that the blank or null cells are highlighted.

Example of what happens when blank or null cells are highlighted

Right-click on one of these highlighted cells and select Delete.

Right-click on one of the highlighted cells and select Delete.

The Delete menu will appear. Select Shift cells up and press OK. 

Select "Shift cells up" from the "Delete" menu and click OK.

And voila! You have now removed the blank rows from your dataset.

Note This

Remember to select the Shift cells up option (not Shift cells left).

This step is crucial to ensure that the remaining cells within your data move up after deleting the blank rows.

Example of what happens when you select "Delete Shift Cells Up"

If you select the Shift cells left option, the cells on the right will move towards your dataset.

Example of what happens when you select "Delete Shift Cells Left"

If the cells on the right don’t have any contents (same as the image above), it will look like nothing is happening in the data set. However, if you have other datasets on the right side of your sheet, the datasets will all be mixed up.

You can use the Shift cells left option if you’re trying to delete columns in between the data set.

Using the Auto Filter to Remove Blank or Empty Rows in Excel

Another way to remove blank or empty rows is to use the Auto Filter. We will copy the dataset to a different sheet or workbook. If that’s not a problem, then this would be a good option too.

Select first the cells within the data set.

Same as the steps specified above, you can either:

  • Select the entire columns of the data set; or
  • Select the first cell up to the last cell within the data set.

Once the cells are highlighted, apply the filters by going to the Data menu and selecting Filter.

You should then see the filters added to the header row.

Steps to add filter to dataset with blank rows

Once the filters are in place, add a filter so that the blank rows are excluded from the data set.

To do this:

  1. Select the filter in one of the fields.
  2. Uncheck (Blanks) – this will be the last item in the filters.
  3. Click OK.
Select the filter in one of the fields, uncheck "(Blanks)" and click OK.

You will now see the data set without the blank rows in between.

Once you see this, copy the filtered data set (CTRL + C) and paste it (CTRL + V) on a different sheet or workbook.

Using the FILTER() Function to Remove Blank or Empty Rows in Excel

This method is probably my favorite as it involves the least number of steps.

IMPORTANT:

FILTER() is a dynamic array function that only works on Office 365. If you’re using other versions of Office, I’m sorry, but this won’t work for you.

The FILTER() function uses the following syntax: =FILTER(Array, Include, [Empty])

  • “Array”is the data range to apply the filter to.
  • “Include” is the logical expression to use to filter the data.
  • “Empty” is the text you would like to appear if there are no results to display after applying the filter. This field is optional and can be left blank.

On a new worksheet (or on an empty section in the current worksheet), select a cell and type the FILTER() formula.

Add the FILTER() formula to one of the cells.

In my example above, I have used this formula: =FILTER(A:D,A:A<>””)

  • Change “A:D” with the range address where your dataset is located.
  • Change“A:A” with a column that can be used for filtering out blank rows.

And that’s it! As you press enter, you will immediately see your dataset without the empty rows.

What’s so cool about this is that if there are new entries added to the specified range, they will be immediately reflected in the filtered dataset.

Sorting the Records to Remove Blank or Empty Rows in Excel

If the previous method didn’t work for you, there’s still an easy way to remove the empty rows with just a few steps.

IMPORTANT: This method requires reordering your dataset. If this is not an issue, please proceed.

1. Select the entire column (or all cells within your dataset).

Select the entire column (or all cells within your dataset).

2. Go to the Data tab and click on the Sort button inside the Sort & Filter section.

Go to the Data tab and click on the Sort button inside the Sort & Filter section.

3. In the Sort menu, select the field that we will use as the basis for sorting the records (it can be any field).

In the Sort menu, select the field to use as the basis for sorting the records (it can be any field).

You can also change the sort order to either ascending or descending order – it doesn’t matter.

Once done, click OK.

Sample output after sorting the records

And that’s it! Blank rows will “disappear” from your dataset.

Using Power Query to Remove Blank or Empty Rows in Excel

As mentioned, we mainly get empty rows from files exported from a system or a database.

If you regularly receive these kinds of files and would prefer to have the empty rows automatically removed, then this next method is for you.

1. Set up a Power Query to load the file.

(If you have an existing Power Query already set up on your data, you can skip this step and add the rule specified in step #4.)

On a new workbook, go to Data >> Get Data >> From File >> From Excel Workbook.

Go to Data >> Get Data >> From File >> From Excel Workbook.

2. The Import Data menu will appear. Select the file that contains your dataset and click Import.

Select the file to import from the "Import Data" menu of the Power Query.

3. In the Navigator menu, select the sheet that contains your dataset and click on the Transform Data button.

In the Navigator menu, select the sheet that contains your dataset and click on the Transform Data button.

4. Wait for the data to load.

Once the Power Query Editor appears, click on Remove Rows >> Remove Blank Rows.

Once the Power Query Editor appears, click on Remove Rows >> Remove Blank Rows.

5. You will see the empty rows filtered out from your dataset.

Once you see that, click on the Close & Load button.

Once you see the empty rows filtered out from your dataset, click on the "Close & Load" button.

And that’s it! Your Power Query Table is all set up.

Sample output after removing blank rows using Power Query

If you receive a new export file, place it in the same folder as the current file and replace the existing one. Make sure that the filename is the same.

Then, open the workbook with the Power Query Table, right-click on the table and select Refresh to get the latest set of data. The Power Query will automatically remove the empty rows from the dataset.

Conclusion

Blank or empty rows within a dataset are at times inevitable especially if you’re working on an exported file. I hope with the methods suggested above, you will find it easy to remove these unnecessary rows from your dataset.

How to Remove Leading Zeros in Excel?

Excel, by default, automatically removes leading zeroes whenever you enter numbers. So, you might be wondering why there are leading zeroes in the Excel file you received.

Well, there are two possible reasons for that. It could be because:

  1. The cell is in text format.

This can happen if there’s an apostrophe at the start of the cell. Apostrophe (‘) is a special character that tells Excel to treat a cell as a text. If you are working on an Excel file exported from a system, there’s a great chance you’ll encounter this.

Remember that adding an apostrophe is just one way of converting a cell into a text. A cell can also be converted into text by simply changing its number format – so check on that too.

  • The cell is formatted to have a fixed number of digits.

If you have a 3-digit number entered in a cell that was formatted to always have 5 digits, Excel will automatically fill in the missing 2 digits with leading zeroes.

There are plenty of ways to remove the leading zeroes in Excel. They vary depending on the reason for having them. I’ve arranged the methods below to tackle first the most common causes. I suggest you go through each of them and see what works on your data.

Error Check to Remove Leading Zeroes in Excel

Excel will typically warn you if you have cells that contain numeric values but a formatted as text. You will notice this when you see small green triangles added on the left side of these cells (as shown below).

Small green triangles on the left side of cells appear whenever the cells contain numeric values but are formatted as text

If you don’t see them, it’s most likely that Background Error Checking is disabled on your Excel.

To enable it, simply go to File >> More >> Options.

Go to File >> More >> Options.

In the Excel Options menu, select Formulas.

Go to the Error Checking section, tick the “Enable background error checking” checkbox, and click OK.

Steps to enable background error checking in Excel

You should now see the small green triangles on the left side of your cells.

(If you don’t see them still, please proceed to the method that follows after this).

Select all these cells, and a yellow warning icon will crop up on the first cell selected.

A yellow warning icon crops up after selecting cells with green triangles.

Click on that icon and select Convert to Number.

And that’s it! All leading zeroes should now disappear.

Sample output after letting Excel convert cells to number format

With this approach, we have taken advantage of Excel’s default setting of removing leading zeroes from numbers.

Changing the Number Format to Remove Leading Zeroes in Excel

If the previous method didn’t work for you, it might be because your cells are in a “Special” format where leading zeroes are automatically added so that it reaches a particular number of digits.

To change this:

1. Select all the cells with leading zeroes.

Select all cells with leading zeroes

2. Go to the Home tab. Look at the current number formatting of these cells from the Number Section. If it is “Special”, change the number formatting to General (or any other number formatting appropriate for your data).

Steps to change selected cells' number formatting

And that’s it! You should no longer see any leading zeroes in your selected cells.

Sample output after changing cells' number formatting to General

Using “Paste Special” to Remove Leading Zeroes in Excel

If you don’t want to alter the cells containing leading zeroes and would only want the numbers on a different column, then you can do the following:

1. Select a blank cell on the worksheet and press CTRL + C to copy it.

Select a blank cell and copy it

2. Next, select all the cells with leading zeroes.

Select all cells with leading zeroes.

3. Then, press CTRL + ALT + V to open the Paste Special menu.

Select the Add Operation from the Paste Special menu and click OK.

Select Add under the Operation section and click OK.

And that’s it! You should now have removed all the leading zeroes from your cells.

Sample output after using the Paste Special option to remove leading zeroes.

This method worked because it added a zero (or a blank cell) to all the cells with leading zeroes, which in the process, converted the cells into numbers.

Using the VALUE Function to Remove Leading Zeroes in Excel

Another way to remove leading zeroes without altering the original column is to use the VALUE() function.

VALUE() is a text function in Excel that converts a text string that looks like a number into an actual number.

On a new column, type the following formula: =VALUE([range])

Enter the VALUE() formula

As you press enter, the leading zeroes will disappear.

Copy this formula to the remaining rows, and voila! No more leading zeroes for the entire column.

Using Text to Columns to Remove Leading Zeroes in Excel

If you have a lot of rows to remove the leading zeroes from, this may be the best option for you.

1. Highlight the entire column that contains leading zeroes.

Select the entire column with leading zeroes.

2. Go to the Data tab. From the Data Tools section, click Text to Columns.

Steps to open Text to Columns wizard.

3. The Convert Text to Columns Wizard will appear.

Select Delimited and click Next.

In the Text to Column Wizard, select Delimited.

4. Uncheck all the Delimiters checkboxes and click Next.

In the Text to Columns wizard, uncheck all "Delimiters" checkboxes.

5. In the Column Data Format, select General.

Notice there’s a note in the Wizard that says:

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

This is the Text to Column feature that we are making use of.

In the Text to Column wizard, select the General format.

Click Finish to close the Wizard.

You should now see all the leading zeroes removed from your selected column.

Sample output after running the Text to Columns wizard

If you have used Text to Columns before and worry that this may affect other columns alongside the current field, don’t worry. Only the selected column gets updated with the settings we selected in the Wizard.

Remove Leading Zeroes from an Alphanumeric Data

The methods described above involved the conversion of the cell into a number format.

If you are working on data with alphanumeric characters (a combination of both letters and numbers) and you only want to remove the leading zeroes, then this next method is for you.

1. On a blank column, type this formula:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Change all instances of “A2” with the range where your alphanumeric data is.

Add formula to remove leading zeroes from a cell with alphanumeric characters.

2. Once you click enter, the cell should result in an alphanumeric character with no leading zeroes.

3. Copy this formula to the remaining rows in your cells, and you’re all set up.

Conclusion

Leading zeroes are often intentionally added for a particular purpose. However, there are instances when we need to remove them. I hope the methods provided above have helped you do so with ease.