How to Remove Value in Excel (9 Methods)

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.

Sample Dataset

Download Workbook to Practice

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

Using Keyboard Shortcut to remove Value in Excel

Then, press the DELETE key, it will remove the value from the selected cell.

Read More: How to Remove Formulas in Excel: 7 Easy Ways


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

Using Sort with Right Click to Remove Value in Excel

➤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.

Using Sort with Right Click to Remove Value in Excel

➤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.

Read More: How to Remove Sort in Excel (3 Easy Methods)


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

Delete Using Ribbon with Sort to Remove Value in Excel

➤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

Delete Using Ribbon with Sort to Remove Value in Excel

➤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.

Delete Using Ribbon with Sort to Remove Value in Excel

Read More: How to Remove Formulas in Excel: 7 Easy Ways


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

Using Find & Replace to Remove Value to Remove Value in Excel

➤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.

Using Find & Replace to Remove Value to Remove Value in Excel

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.

Using Find & Replace to Remove Value to Remove Value in Excel

Related Content: How to Find and Delete Rows in Excel (5 Ways)


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

Remove Value Using Go To Special to Remove Value in Excel

➤ 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.

Remove Value Using Go To Special to Remove Value in Excel

In the end, all the selected values are removed.

Remove Value Using Go To Special to Remove Value in Excel

Read More: How to Remove Data Validation in Excel (5 Ways)


Similar Readings


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

Using Filter to Remove Value to Remove Value in Excel

➤ 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.

Using Filter to Remove Value to Remove Value in Excel

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.

Using Filter to Remove Value to Remove Value in Excel

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 Clear Formula in Excel (7+ Methods)


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

Using Text to Columns to Remove Value in Excel

➤ 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.

Using Text to Columns to Remove Value in Excel

➤ 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.

Using Text to Columns to Remove Value in Excel

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.

Using Text to Columns to Remove Value in Excel

Read More: Data clean-up techniques in Excel: Replacing or removing text in cells


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

Using Sort and Hide to Remove Value

➤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.

Using Sort and Hide to Remove Value

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 )

Using VBA to Remove Value in Excel

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

Using VBA to Remove Value in Excel

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.

Using VBA to Remove Value in Excel

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.

Read More: Data clean-up techniques in Excel: Fixing trailing minus signs


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.

Practice sheet to Remove Value in Excel

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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo