Adnan Masruf
If you use Microsoft Excel frequently, you may have run into the problem of a messy and cumbersome worksheet with a lot of empty cells that are useless. This can not only make your spreadsheet look unorganized, but it can also negatively impact how well your Excel workbook functions in general. But don’t worry! We’ll go into detail about how to remove unused cells in Excel in this article and give you useful advice on how to effectively handle this issue.
In this article, we’ll examine a number of approaches and procedures for locating and eliminating empty cells in Excel. You can improve the performance of your Excel workbook and better organize your data by learning how to purge unused cells from your Excel worksheet by reading the information in this article.
So let’s get started and discover how to permanently delete unused cells from Excel!
Below is the overview image of removing unused cells in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.
How to Remove Unused Cells in Excel: 10 Easy Methods
In the following section, we will use 10 effective methods to remove unused cells in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Using the Go To Special Command to Remove Unused Cells in Excel
Using the Go To Special option, we can exclude any cells from a range that aren’t being used. Let’s walk through the following steps to complete the task.
📌 Steps:
- To bring up the Go To dialog box, first, select the data range (B4:E12) and press F5 or Ctrl + G. Press Special from the dialog box after that.
- The Go To Special dialog box consequently appears. Select Blanks from the list of options, then click OK.
- After you press OK, all the blank cells in the range are highlighted. Now, press Ctrl + – from the keyboard to bring up the Delete dialog.
- Then depending on your data and requirement, any of the delete options. We have chosen Shift cells up. Press OK again. This option will delete the blank cells and move non-empty cells up.
- Consequently, you will be able to remove unused cells as shown below.
Note:
- You must select the right dataset, or the aforementioned process will alter the meaning of your sequence by removing empty cells and replacing them with the values of other cells.
- You can bring the Delete dialog by right-clicking on the selection or following the path: Home > Cells > Delete > Delete Cells.
2. Applying Filter Option to Remove Rows with Unused Blank Cells
In this method, we will make use of the Filter command. Follow the steps written sequentially below to achieve the goal. It’s important to keep in mind that if you use this method, every row that contains unused cells will be deleted.
📌 Steps:
- Select the range at first and press Ctrl + Shift + L to apply Filter on it. As the Filter option is applied, the drop-down arrow will show up.
- Now, suppose, we will filter the 3rd column of the range (B5:E12) based on Date. To do that, click on the drop-down icon from the Date column, put a checkmark only on the Blanks option, and press OK.
- As a result, all the rows that contain the blank cells will be filtered. Now, select all the rows and right-click on the selection, and click Delete Row.
- After that, the Microsoft Excel message box will ask for the confirmation of row deletion. Click OK.
- Then withdraw the filter by pressing Ctrl + Shift + L again. Consequently, you will be able to remove unused cells as shown below.
3. Applying Advanced Filter Feature to Eliminate Unused Cells
A fantastic way to remove empty cells in Excel is to use the Advanced Filter feature. Let’s say you want to remove the blank cells from two of the dataset’s columns (Date and Sales). It’s important to keep in mind that if you use this method, every row that contains unused cells will be deleted. Check out the Advanced Filter option to see how to accomplish that.
📌 Steps:
- First, You need to type the not equal to (<>) symbol in Cell G5 and H5.
- Next, go to the Data tab and select the Advanced option.
- Consequently, the Advanced Filter dialog appears.
- Now from the dialog box, choose Copy to another location, specify List range (B4:E12), Criteria range (G4:H5), and Copy to (B14).
- Next, press OK.
- Therefore, the range is filtered to another location as shown below (empty cells deleted).
4. Remove Unused Blank Cells from a Vertical Range (Single Column)
In this section, we’ll show you how to eliminate empty or unused cells from a vertical range of fruit names by combining the IFERROR, INDEX, SMALL, IF, MIN, ISBLANK, and ROW functions.
- First, write down the following formula in Cell D5.
=IFERROR(INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),"",ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))),"")- Next, press Enter.
- Therefore, you will get the below result.
- Now drag down the Fill Handle icon to fill the other cells with the formula.
- Finally, you will observe that the range produced does not include blank cells.
🔎 How Does the Formula Work?
- ISBLANK($B$5:$B$12)
Here the ISBLANK function checks whether a cell is blank or not in the range B5:E12 and returns True or False.
- ROW($B$5:$B$12)
Now, the ROW function returns the row numbers in range B5:E12 and shows the following output:
{5;6;7;8;9;10;11;12}
- MIN(ROW($B$5:$B$12))
Then the MIN function finds the lowest row number in the range which is:
{5}
- IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1)
The above formula returns:
{1;2;””;4;5;6;””;8}
- SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))
Here, the SMALL function returns the k -th smallest value from the range and the formula returns the following output:
{1}
- INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1)))
The INDEX formula returns
{“Apple”}
- IFERROR(INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))),””)
The IFERROR function returns a blank if the INDEX formula returns an error.
Similar Readings
- How to Show Only One Page in Excel Page Layout View (4 Ways)
- What Is Normal View in Excel? (Detailed Explanation)
- How to Use Sheet View in Excel (with Simple Steps)
- How to Turn Off Sheet View in Excel (with Easy Steps)
5. Remove Unused Cells from a Horizontal Range
In contrast to the earlier technique, we will now eliminate empty cells from a horizontal range of data. We’ll once again combine several Excel functions (IF, COLUMN, SUM, INDEX, and SMALL) to complete the task at hand.
- First, write down the following formula in Cell C8.
=IF(COLUMN(B:B)<=SUM(--($B$5:$I$5<>""))+1,INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>"",COLUMN($B$5:$I$5)-1,""),COLUMN(B:B)-1)),"")- Next, press Enter.
- Therefore, you will get the below result.
- Now drag down the Fill Handle icon to the right to fill the other cells with the formula.
- Finally, you will observe that the range produced does not include blank cells.
🔎 How Does the Formula Work?
- COLUMN(B:B)<=SUM(–($B$5:$I$5<>””))+1
The above formula returns
{TRUE}
Where,
- COLUMN(B:B)
The COLUMN function returns the column number of B:B which is:
{2}
- $B$5:$I$5<>””
This will return:
{TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE}
- SUM(–($B$5:$I$5<>””)
The SUM function sums up the count of TRUE values and replies:
{6}
- INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1))
The above formula returns:
{“Apple”}
- IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””)
Here, the IF function checks whether $B$5:$I$5<>””, and replies accordingly:
{1,2,””,4,5,6,””,8}
- SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1)
Later, the SMALL function returns the k-th smallest value from our data range which is:
{1}
- IF(COLUMN(B:B)<=SUM(–($B$5:$I$5<>””))+1,INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1)),””)
The above formula returns:
{Apple}
6. Using FILTER Function to Delete Unused Cells
If you’re using Excel 365, you can eliminate empty cells by using the FILTER function. By pressing Ctrl + T, we will create an Excel table from the data range (B4:E12) and then apply the function. It’s important to keep in mind that if you use this method, every row that contains unused cells will be deleted.
- By pressing Ctrl + T, we will create an Excel table from the data range (B4:E12) in order to apply the function.
- Next, write down the following formula in Cell B15.
=FILTER(Table1,Table1[Items]<>"","")- Press Enter.
- The array (outlined in blue) created by the aforementioned formula will eliminate all blank cells from the first column (Items) of the aforementioned table.
- In the output image, we can see that rows with blank cells in the Items column have been removed as a result of using the Filter function based on the Items column and that rows with empty cells in other columns will also have their empty cells filled with a value of 0 as a result.
7. Using Find Option to Delete Unused Cells
Here, we will use the Find command. Follow the steps below to accomplish the task.
📌 Steps:
- First, select the range (B5:E12) of data.
- Then press Ctrl + F to bring the Find and Replace dialog.
- After the dialog appears, leave the Find what field blank, choose Values from the Look in the drop-down, put a checkmark on Match entire cell contents, and finally press Find All.
- Consequently, you will get a list containing the blank cells.
- Now select the whole output by holding the Ctrl key. Then go to Home > Cells >Delete > Delete Cells to bring the Delete dialog.
- After that, choose the delete option and press OK.
- Following our selection of the Shift cells up delete option, this is the output we have obtained.
8. Using Sort Command to Remove Unused Cells
In this method, we will show you how to remove empty cells using the Sort option in Excel. It’s important to keep in mind that if you use this method, every row that contains unused cells will be deleted.
📌 Steps:
- Select the range at first. Then go to Data > Sort & Filter > Sort A to Z icon.
- As a result, the data will be sorted as shown below. At the end of the range, a list of all the blank rows is presented.
- Now press Ctrl + – from the keyboard to bring the Delete dialog. Choose the Delete Row option and press OK.
- Here is the final outcome, to conclude. Our data range has all the blank rows removed.
9. Using Excel Power Query
Using Excel Power Query, we’ll demonstrate how to delete unused cells. Let’s look at how to do that. By pressing Ctrl + T, we changed my data range into a table so that we could operate more easily. It’s important to keep in mind that if you use this method, every row that contains unused cells will be deleted.
📌 Steps:
- First, click anywhere in the table, and go to Data > From Table/Range.
- As a result, the table below will show up in the Power Query Editor window. Here, by default, null is put in all blank cells.
- Now from the new window, follow the path: Home > Remove Rows > Remove Blank Rows.
- As a result, all the rows that were containing null are removed.
- Now to close the operation, go to Home > Close & Load > Close & Load.
- Finally, the final outcome will show up as the following in a new Excel sheet.
10. Applying VBA Code to Remove Unused Cells
We can also remove unused cells using Excel VBA code. Let’s walk through the following steps to do the task.
📌 Steps:
- VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.
- VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.
- As a result, a new module will be created.
- Now select the module if it isn’t already selected. Then write down the following code in it.
Sub RemoveBlankCells()
Dim rng As Range, cell As Range
Set rng = Range("B4:E12")
For Each cell In rng
If cell.Value = "" Then
cell.Delete Shift:=xlShiftUp ' or Shift:=xlShiftToLeft
End If
Next cell
End Sub- Next, save the code.
- Afterward, close the Visual Basic window. After that press Alt+F8.
- When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.
- Therefore, you will be able to remove the unused cells as shown below.
How to Remove Empty Rows in Excel
You can use the context menu to remove empty rows in Excel using the Delete command. You can remove one row or multiple rows at a time.
- First, select the empty row by left-clicking on the mouse in the row number.
- If you want to select multiple rows then you can use the CTRL key.
- Hold the CTRL key and select the row number to select multiple rows.
- Now, right-click on the mouse then from the context menu select Delete.
- Thus, it will delete the selected empty rows.
Read More: How to Grey Out Unused Cells in Excel (3 Quick Methods)
Frequently Asked Questions
1. How do you delete thousands of blank rows in Excel?
You can remove blank rows from an Excel document by first performing a “Find & Select” on those rows. Then you can eliminate them all at once by selecting “Delete” from the Home tab. In Excel, deleting rows or cells causes the data beneath them to move up.
2. How do I get rid of infinite columns to the right in Excel?
We can use the Excel context menu for deleting infinite columns. To delete infinite columns from the context menu, first,
- Select the first column from where you want to delete infinite columns by clicking on the column number (i.e. column G).
- Now, press CTRL+SHIFT+RIGHT Arrow to select all the columns right to your selected column.
- As a result, Excel will display the columns at the right end of your sheet and the area will be marked with gray color.
- At this point, right-click on any of the column header.
- As a result, a context menu will appear.
- Select Delete from this context menu.
- As a result, the display will automatically return at the beginning of the sheet. You will see now there is not an infinite number of columns. The last column number of your Excel datasheet is AA.
- You will find your existing dataset at the beginning of your Excel sheet.
3. How do I get rid of infinite rows in Excel?
Here, we’ll do the operation using the Delete tab from the Home ribbon. It’s quite easy.
- Select the rows that you want to remove.
- Click as follows: Home > Cells > Delete > Delete Sheet Rows.
- You will find that the rows are no more now.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to remove unused cells in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
