How to Remove Unused Cells in Excel (8 Easy Ways)

Excel users often find unused cells in their Excel sheets, and they face problems removing them. These unused or blank cells increase the chances of calculation errors. They also make the Excel sheet less presentable. So, the users should remove those unused cells. In this article, we will discuss how to remove unused cells in Excel. This will help the Excel users to deal with their data set properly.


Download the Practice Workbook

You can download the practice workbook from here.


8 Handy Approaches to Remove Unused Cells in Excel

Here, we will learn 8 efficient ways to remove unused cells in Excel. In the first method, we will get rid of the blank cells by manually selecting the cells and then removing them using the Delete command and the Delete Cells command. Next, we will learn to erase the cells using the Hide command. In the third method, we will use the Go To Special command to eliminate the cells. Then we will explain the use of the Find command in removing cells. In the fifth method, we will apply the Filter command to accomplish the task. After that, we will utilize the Advanced Filter command to terminate the cells. Then, we will apply the Sort command to complete the . Finally, we will use the FILTER function to eliminate the unused cells. We will use the sample dataset below in every method.

8 Handy Approaches to Remove Unused Cells in Excel


1. Selecting Manually to Remove Unused Cells in Excel

In this method, we will manually select the blank cells and then remove them. This is an easy way to get rid of the cells.

1.1 Using Delete Command

Here, we will select the cells manually and then remove them using the Delete command. Follow the steps below to carry out the task.

Step 1:

  • Firstly, select the rows containing the blank cells.
  • Here, we will choose row 6, row 8, row 10, and row 12.

Sample Data

Step 2:

  • Next, click on the right-click button.
  • Now, from the menus select the Delete command.

Sample Data

Step 3:

  • There is our dataset without a single unused cell.

Sample Data

1.2 Applying Delete Cells Command

In this additional method, we will remove the cells by applying the Delete Cells command after manual selection. Follow the outlined steps below to do so.

Step 1:

  • Firstly, select the unused cells from the dataset.

Sample Data

Step 2:

  • Secondly, go to the Home tab in the ribbon.
  • Thirdly, go to the Cells group and select the Delete tab.
  • Fourthly, from the drop-down list select the Delete Cells command.
  • Consequently, the Delete dialogue box will appear.

Sample Data

Step 3:

  • After that, from the Delete dialogue box select the Shift cells up option.
  • Finally, click OK.

Sample Data

Step 4:

  • Finally, we will get our unused cells free dataset.

Sample Data


2. Using Hide Command to Remove Unused Cells in Excel

In the following method, we will hide the entire blank cells to the right and to the bottom of our dataset by using the Hide command. Follow the upcoming steps to do the task.

Step 1:

  • Firstly, we will select an unused cell to the right of our dataset.
  • In this instance, we will select F4 cell.
  • Then, we will press Ctlr+Shift+Right Arrow all at the same time.
  • This command will select all the cells to the right of the dataset.

Sample Data

Step 2:

  • Next, go to the Home tab in the ribbon.
  • From the Home tab, hoover down to the Cells group.
  • Then, select the Format tab .
  • After that, select the Hide & Unhide command.
  • Finally, select the Hide Column command from the outgoing options.

Sample Data

Step 3:

  • Therefore, you will see that all the cells to the right of our dataset are hidden.

Sample Data

Step 4:

  • Next, select a cell at the bottom of your dataset
  • Here, we will select B11 cell.
  • After that, we will press Ctrl+Shift+Down Arrow.
  • Consequently, we will see that all the cells below our dataset are selected.

Sample Data

Step 5:

  • Afterward, go to the Home tab.
  • Go to the Cells group.
  • Then, click on the Format tab.
  • From the drop-down list select the Hide & Unhide option.
  • Finally, select the Hide Rows command.

Sample Data

Step 6:

  • Finally, we will see that all the unused cells are hidden around our dataset.

Sample Data


3. Applying Go To Special Command to Delete Unused Cells

In the following procedure, we will use the Go To Special command, and Excel will automatically select the blank cells from our dataset. Follow the ensuing steps to complete the task.

Step 1:

  • Firstly, select the entire dataset.

Sample Data

Step 2:

  • Next, go to the Home tab.
  • From the Home tab, go to the Editing group.
  • Now, click on the Find & Select option.
  • Finally, from the drop-down option select the Go To Special command.
  • Consequently, the Go To Special dialogue box will appear.

Sample Data

Step 3:

  • From the dialogue box, select the option, Blank.
  • Then, click OK.

Sample Data

Step 4:

  • As a result, we will see that Excel has selected the blank cells from our dataset.

Sample Data

Step 5:

  • Now, go to the Home tab in the ribbon.
  • Then, hover down to the Cells group and click on the Delete tab.
  • Finally, from the options in the drop-down list choose the Delete Cells command.
  • Consequently, the Delete dialogue box will be on the screen.

Sample Data

Step 6:

  • After that, choose the Shift cells up option in the Delete dialogue box.
  • Finally, click OK.

8 Handy Approaches to Remove Unused Cells in Excel

Step 7:

  • Finally, we will get a dataset without an unused cell.

8 Handy Approaches to Remove Unused Cells in Excel


4. Using Find Command to Delete Unused Cells

Here, we will use the Find command . Follow the steps below to accomplish the task.

Step 1:

  • Firstly, select the entire dataset.

8 Handy Approaches to Remove Unused Cells in Excel

Step 2:

  • Go to the Home tab.
  • From there, go to the Editing group.
  • Next, select the Find & Select tab.
  • Finally, select the Find command from the drop down options.
  • Consequently, Find and Replace dialogue box will appear.

8 Handy Approaches to Remove Unused Cells in Excel

Step 3:

  • In the dialogue box, keep the Find What command box blank.
  • Next, select Sheet from the Within option.
  • Then, select By Rows from the Search option.
  • Select Values from the Look in box.
  • After that, check the Match the entire cell contents box.
  • Then, click on Find All.
  • Finally, select all the results from the search.

8 Handy Approaches to Remove Unused Cells in Excel

Step 4:

  • Excel will select all the unused cells from the dataset.

8 Handy Approaches to Remove Unused Cells in Excel

Step 5:

  • Next, go to the Home tab in the ribbon.
  • From there, visit the Cells group and choose the Delete tab.
  • Finally, from the outstretched options select the Delete Cells command.
  • Consequently, we will encounter the Delete dialogue box.

8 Handy Approaches to Remove Unused Cells in Excel

Step 6:

  • Afterward, choose the Shift cells up option from the Delete dialogue box,
  • Finally, click OK.

8 Handy Approaches to Remove Unused Cells in Excel

Step 7:

  • Here is our desired result.

8 Handy Approaches to Remove Unused Cells in Excel


Similar Readings


5. Utilizing Filter Command to Remove Unused Cells in Excel

In this method, we will make use of the Filter command. Follow the steps written sequentially below to achieve the goal.

Step 1:

  • Firstly, select the entire dataset.

8 Handy Approaches to Remove Unused Cells in Excel

Step 2:

  • Secondly, go to the Home tab.
  • Then, look for the Editing group.
  • Upon finding the Editing group, click on the Sort & Filter tab.
  • Finally, click on the Filter command.

8 Handy Approaches to Remove Unused Cells in Excel

Step 3:

  • Therefore, all the row headers will have a filter option on the right side of the cell.

8 Handy Approaches to Remove Unused Cells in Excel

Step 4:

  • Click on any of the row header filter options.
  • In our case, we will select the row header named Percent Increase or Decrease”.

8 Handy Approaches to Remove Unused Cells in Excel

Step 5:

  • From the filter option, first check the SelectAll box.
  • Then, uncheck all other checkboxes except Blanks.
  • Click OK.

8 Handy Approaches to Remove Unused Cells in Excel

Step 6:

  • Excel will return only the blank rows.

8 Handy Approaches to Remove Unused Cells in Excel

Step 7:

  • Select the blank cells.
  • After that, visit the Home tab in the ribbon.
  • Next, move your cursor to the Cells group and select the Delete tab
  • Finally, from the impending options select the Delete Cells command.
  • Consequently, a dialogue box will appear asking if we want to delete the entire sheet row.

8 Handy Approaches to Remove Unused Cells in Excel

Step 8:

  • From the dialogue box select OK.

8 Handy Approaches to Remove Unused Cells in Excel

Step 9:

  • The blank cells are deleted.

8 Handy Approaches to Remove Unused Cells in Excel

Step 10:

  • Now, visit the Home tab.
  • Then, hoover down to the Editing group.
  • From that group, click on the Sort & Filter option.
  • Finally, click on the Clear command.

8 Handy Approaches to Remove Unused Cells in Excel

Step 11:

  • Finally, we will get an unused cell-free dataset.

8 Handy Approaches to Remove Unused Cells in Excel


6. Applying Advanced Filter Command to Delete Unused Cells

Here, we will use the Advanced Filter command . Follow the sequential steps to carry on the deed.

Step 1:

  • Firstly, create a filter criteria.
  • To do so, copy a row header to another cell.
  • Here, we have selected the row header named “Sales” and copied it into cell G4.
  • Next, formulate a condition based on which Excel will filter your dataset.
  • In this instance, we have written “<>” in cell G5. This means “not blank”.
  • This means the filtering will return only the cells with values.

8 Handy Approaches to Remove Unused Cells in Excel

Step 2:

  • Next, go to the Data tab.
  • Then, select the Advanced command from the Sort & Filter tab.
  • As a result, the Advanced Filter dialogue box will appear.

8 Handy Approaches to Remove Unused Cells in Excel

Step 3:

  • In the Advanced Filter dialogue box first go to the List Range option.
  • From there, click on the upward arrow to the right of the box.

8 Handy Approaches to Remove Unused Cells in Excel

Step 4:

  • Next, select the range of data you want to filter .
  • In our case, it will be the entire dataset.
  • Then, click on the downward arrow to the right to confirm it.

8 Handy Approaches to Remove Unused Cells in Excel

Step 5:

  • We can see the range of data to be filtered is shown in the List range option box.
  • After that, in the Advanced Filter dialogue box, go to the Criteria Range to select the range of filter criteria.
  • Then, click on the upward arrow to the right.

8 Handy Approaches to Remove Unused Cells in Excel

Step 6:

  • Now, we will select the criteria cells that we have previously created.
  • In this case, the criteria range is G4:G5.
  • Next, click on the downward arrow to the right to confirm it.

8 Handy Approaches to Remove Unused Cells in Excel

Step 7:

  • We will find that the criteria range is added in the Criteria range option box.
  • Now, select OK.

8 Handy Approaches to Remove Unused Cells in Excel

Step 8:

  • Finally, we will get our dataset, which does not contain any unused cell.

8 Handy Approaches to Remove Unused Cells in Excel


7. Using Sort Command to Remove Unused Cells in Excel

In this method, we will resort to the Sort command to terminate the blank cells from our Excel dataset. Follow the steps below to do the job.

Step 1:

  • At first, select the entire dataset.

8 Handy Approaches to Remove Unused Cells in Excel

Step 2:

  • Next, put your cursor on the Home tab.
  • From there, visit the Editing group.
  • Select the Sort & Filter tab from the Editing group.
  • Finally, click on the Sort A to Z command.

8 Handy Approaches to Remove Unused Cells in Excel

Step 3:

  • Consequently, we will find that the data is sorted.
  • On the other hand, the unused cells are at the bottom.

8 Handy Approaches to Remove Unused Cells in Excel

Step 4:

  • After that, select the unused cells.
  • Then, go to the Home tab in the ribbon.
  • Visit the Cells group and choose the Delete tab.
  • Finally, from the drop-down menus select the Delete Cells command.
  • Consequently, we will see a Delete dialogue box on our screen.

8 Handy Approaches to Remove Unused Cells in Excel

Step 5:

  • Select Shift cells left option.
  • Finally, click OK .

8 Handy Approaches to Remove Unused Cells in Excel

Step 6:

  • There we will get our unused cell free dataset.

8 Handy Approaches to Remove Unused Cells in Excel


8. Applying FILTER Function to Remove Unused Cells in Excel

Applying the FILTER function is a quick way to get rid of the unused cells in Excel. Follow the ensuing steps to do that.

Step 1:

  • Firstly, select any blank cell.
  • Here, we will select the G4
  • Then, write an equal sign (“=”) followed by “Filter” in the cell.
  • Therefore, the FILTER function will appear.

8 Handy Approaches to Remove Unused Cells in Excel

Step 2:

  • The FILTER function has two required arguments, i.e., array, include and an optional argument if_empty.

8 Handy Approaches to Remove Unused Cells in Excel

Step 3:

  • For the array argument select the dataset range you want to filter through.
  • In our case, the selected range will be the entire dataset in the cells B4:E14.

8 Handy Approaches to Remove Unused Cells in Excel

Step 4:

  • Next, select a range for the include argument.
  • In this instance, it will be D4:D14 .
  • Excel will peer through these cells to filter out the blanks.

8 Handy Approaches to Remove Unused Cells in Excel

Step 5:

  • Finally, add a condition to filter the dataset.
  • In this case, we will write <>”” which means “not equal to blank”.
  • This means the filter function will return cells that are not blank.
  • Now, hit Enter.

8 Handy Approaches to Remove Unused Cells in Excel

Step 6:

  • Finally, we will have a copy of the filtered result .
  • The copy will not contain any unused cells.

8 Handy Approaches to Remove Unused Cells in Excel

Read More: How to Grey Out Unused Cells in Excel (3 Quick Methods)


Conclusion

Cells that do not contain values make life difficult for Excel users. They make the calculations erroneous and messy. The dataset also does not look attractive while these are present. After going through this article, the readers will have a complete understanding of how to remove unused cells in Excel. This will allow them to manage their data more accurately and efficiently. If you find it useful, please let us know in the comment section below and share any recommendations and thoughts regarding this or any other content of ours. Thank you for your time. Follow ExcelDemy.com for more articles like this.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo