A worksheet may contain various types of data from where you may not need all types of data then you can remove those unwanted data. Also, to replace any value without losing the format you can remove the value. In this article, I’ll explain how to remove value in Excel.
For the purpose of explanation, I’m going to use a dataset that represents products order information of any particular shop. The dataset has 5 columns are Product Name, Order ID, Price, Order Date, and Status.
9 Methods to Remove Value in Excel
1. Using Keyboard Shortcut
The easiest way to remove value from any cell or cell range is using the keyboard shortcut.
Now, let’s see how you can do that,
First, select any cell or cell range from where you want to remove the value.
➤I selected the cell F7
Then, press the DELETE key, it will remove the value from the selected cell.
Read More: How to Remove Numbers from a Cell in Excel
2. Using Sort with Right Click
In case you have some data or values that are not needed for you, or you don’t want them anymore then you can sort the values using the Sort command.
While you are done with Sorting then using the Right Click you can remove those values.
Let’s see the procedure,
First, select the cell range to apply Sort.
➤I selected the cell range B4:F12
Then, open the Data tab >> select Sort
➤A dialog box will pop up.
➤In Sort by I selected Status then click OK.
Here, Sort is applied, and all the values are sorted in A to Z Order according to the Status column value.
Suppose you want to remove the values of those products which are delivered.
To do that, select the delivered product values and Right Click on the mouse.
➤From the context menu select Delete.
➤A dialog box of the Delete option will appear.
➤I selected the Shift cells up option then click OK.
Therefore, the selected values are removed from the sheet.
3. Delete Using Ribbon with Sort
There is an alternate way to remove values while you don’t want them anymore then you can sort the values using the Sort command.
After completing Sorting, you can use the Delete command from the Ribbon to remove values.
Let’s begin the procedure,
For that, select the cell range to apply Sort.
➤I selected the cell range B4:F12
Now, open the Data tab >> select Sort
➤A dialog box will pop up.
➤In Sort by I selected Status then click OK.
Here, Sort is applied, and all the values are sorted in A to Z Order according to the Status column value.
To remove the value of your choice, first, select the cell or cell range.
➤I selected the cell range B4:F8.
Then, open the Home tab >> go to the Cells group >> from the Delete >> select Delete Cells
➤A dialog box of the Delete option will pop up.
➤I selected the Shift cells up option then click OK.
Hence, all the selected values are removed.
Read More: How to Remove Outliers in Excel
4. Using Find & Replace to Remove Value
Find & Replace is such a type of feature which not only helps us to remove values but also it gives us the opportunity to replace the value with a new value while removing it.
To use that, first, open the Home tab >> go to Editing group >> from Find & Select >> select Replace
➤A dialog box of Find and Replace will appear.
In Find what, provide the value you want to find to remove it.
➤I provided the value Delivered then click Find All.
Here, you will the cells which contain the Delivered value.
➤Now, click Replace All.
Here, a message of how many replacements occurred will appear.
➤It has 5 replacements.
Finally, click OK.
As a result, you will see the value you selected is removed.
5. Remove Value Using Go To Special
A worksheet contains various types of values such as text, number, formulas, etc. If you want to remove any particular types of values, you can do it by using Go To Special.
Let’s begin the procedure.
First, open the Home tab >> go to Editing group >> from Find & Select >> select Go To Special
➤ A dialog box of Go To Special will pop up.
From there select the type of your choice to remove.
➤ I selected Constants then Numbers
Finally, click OK.
Here, all the Constants Numbers values are selected. Now, press the DELETE key to remove the values.
In the end, all the selected values are removed.
Read More: How to Remove Dotted Lines in Excel
6. Using Filter to Remove Value
You also can remove values using the Filter command. The Filter option allows us to filter all types of values so that you can choose which types of values you want to remove from a large worksheet.
Let’s jump into the procedure,
Start with opening the Data tab >> From Sort & Filter >> select Filter
➤ The Filter will be applied here.
Now, select any column to use the Filter options then right click on the mouse.
First Deselect all then select the value of your choice.
➤I selected the Delivered value to apply Filters.
Here, all the values that contain Delivered will be Filtered.
➤First, select the cell range and press the DELETE key.
➤ All selected values are removed.
Finally, remove the Filter you will see all the values that contain Delivered value are removed and the rest is as it is.
Read More: How to Remove Panes in Excel
7. Using Text to Columns
Sometimes a column may contain two types of data together as we collect data from various sources. You can remove unwanted parts of data using Text to Columns.
For that, first, open the Data tab >> then select Text to Columns
➤ A dialog box will pop up.
From there choose the data type
➤ I’ve chosen Delimited then click Next
➤Another dialog box will pop up
Now select the Delimiters your data has.
➤ I selected Space (for the column header) and in Other given “_” as my data has underscore.
Then, click Next.
➤ Again a dialog box will pop up.
From there select the destination of your choice to place the separated values.
➤I selected the G3 cell.
Finally, click Finish.
Here, you will see the values of the Order ID column are separated into two columns.
As I only need the Order ID number so I will cut the values of the ID column.
Now, paste it into the Order ID column. Then, select the remaining Order column to remove and press the DELETE key.
Hence, all the unwanted values are removed from the sheet.
8. Using Sort and Hide to Remove Value
In case you may need the data again then the best option of removing data for a moment will be Hiding the values instead of removing them permanently.
To Hide the values first I will use Sort to sort the values.
Let’s see the procedure,
First, select the cell range to apply Sort.
➤I selected the cell range B4:F12
Then, open the Data tab >> select Sort
➤A dialog box will pop up.
➤In Sort by I selected Status then click OK.
Here, Sort is applied, and all the values are sorted in A to Z Order according to the Status column value.
To Hide the unwanted values, select the rows that contain unwanted values then keep the cursor in the selected row number.
Now, right click on the mouse then from the context menu select Hide.
As a result, unwanted values will be Hidden as well as removed for now.
9. Using VBA to Remove Value
To remove values in Excel you also can use the VBA.
To use the VBA editor,
First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )
Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module
A Module will open then type the following code in the opened Module.
Sub Remove_Values()
Range("B4:F7").Select
Selection.ClearContents
End Sub
Here, I declared the Sub procedure Remove_Values
I used the Select method to select the given range B4:F7 (you can use the range of your choice or need)
Then, used the ClearContents method to remove the values from the given range.
Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros
➤ A dialog box will pop up.
Now, from the Macro name select the Remove_Values also select the workbook within Macros in.
Then, Run the selected Macro.
Here, you will see the selected range values are removed.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.
Download Practice Workbook
Conclusion
In this article, I’ve explained 9 different ways to remove values in Excel. I expect all these different ways will help you to remove the values of your choice. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Related Articles
- How to Remove Compatibility Mode in Excel
- How to Remove Drop Down Arrow in Excel
- How to Remove HTML Tags from Text in Excel
- How to Remove 0 from Excel
<< Go Back To Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
