While working with Microsoft Excel, it may be needed to remove the formulas when the data is filtered and maintain the data in Excel. We don’t want the filtered value to be connected to other cells and just show the values. Removing those formulas that are linked with other cells will assist a user in completing the workbook more quickly. In this article, we will demonstrate some easy ways to remove the formula when filtered in Excel.
How to Remove Formula When Filtered in Excel: 3 Easy Ways
It might be tough to access information fast if the worksheet has a lot of stuff. Filters may be used to limit the data in a spreadsheet so that we only see what we need. But if the filtered data contains formulas linked to any other cells might be problematic. To speed up the workbook sometimes we have to remove those formulas when the data is filtered.
To remove the formula when data is filtered, we will use the following dataset. The dataset contains some employees’ Names of a company in column B, the Start Date when they joined the office in column C, the End Date when they resigned from the office in column D, and the total number of total Year they worked for the company. We are using the DATEDIF function to find the working year.
Suppose we don’t get all the employee’s details. So, we will filter the data first. Then, we will remove the formula of the Year column from those filtered data. Let’s see the methods to remove formulas when the data is filtered.
1. Use Delete Button to Remove Filtered Formula with Values in Excel
There are no specific tools in Excel to remove formulas from filtered values. But still, we can remove formulas and we can use the Delete button on the keyboard for this. First, we need to filter those data as per the requirements. To do this we need to follow some procedures. Let’s look at the procedures.
STEPS:
- To begin with, select the whole data range B4:E12.
- Then, go to the Data tab from the ribbon and click on Filter under the Sort & Filter category.
- After that, click on the column header arrow, by which column you want to filter the values. In our case, we want to filter the data by the Name column, so we click on the Name column header arrow. And, select the names we want to filter.
- Further, click OK.
- Finally, we can see the filter data as we wanted.
Now, we need to remove the formula from the filtered values. We use the DATEDIF function in column E, which is displayed in the formula bar. Let’s remove those formulas along with the filtered values. For this, again we have to follow some steps.
STEPS:
- Firstly, select the filtered cells that contain the formula.
- Press the Delete button from the keyboard.
- Lastly, this will remove all the filtered formulas along with values.
- If you clear the filter by right-clicking on the filtered column header arrow and clicking on Clear Filter From “Name”.
- Finally, you will be able to see that the filtered cells that previously contained formulas are removed from the column along with the values.
Read More: How to Remove Formula in Excel and Keep Values
2. Apply Paste Value Feature to Remove Excel Formula When Filtered
We can remove formulas from the filtered data by copying and pasting the data into another cell. But normal pasting won’t work. And, with normal pasting, we can’t make sure that the formulas won’t copy. To keep cell formatting the same we can use the Paste Value option. For this, we need to follow the steps down.
STEPS:
- Select the filtered data >> Go to the Home tab from the ribbon.
- Click on Copy under the Clipboard category. Or, you can use the keyboard shortcut Ctrl + C to copy the cells.
- After that, choose the cells where you want to paste the data without the formula.
- Similarly again, go to the Home tab and click on the Paste drop-down menu.
- Now, select the second option on the Paste Value, which is Values & Number Formatting.
This will paste all the values without the formula.
Here, you can see, that the Year column contains the formula of the DATEDIF function in the upper section of the column. But after pasting it only contains the values without the formula. The formula won’t appear in the formula bar.
Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting
3. Delete Excel Filtered Formula with Paste Special Option
The Paste Special option allows us to choose what we wish to paste into cells. We can use the option to remove formulas from our filtered data. Let’s go along with the procedures to do that.
STEPS:
- Copy the filtered data >> Right-click on the selected cell where you want to paste the formulated cell >> Click on Paste Special.
- This will appear in the Paste Special dialog box.
- Now, select the Values under the Paste selection box >> click OK to finish the procedure.
- You can see the dates are also pasted as values. To fix this problem, select the cells that contain the date previously, then go to the Home tab > Short Date under the Number group.
- And, that’s it! If you choose the cells that contain the formula that previously had, those formulas will be removed from the data.
Alternative Way to Remove Formula in Excel
If we don’t use the filter, we can easily remove formulas from the data. Let’s see how we can do this.
STEPS:
- Likewise, in the previous methods, select the cells with the formula and the formula will display in the formula bar.
- Then, simply press Ctrl + C from your keyboard to copy the cell range.
- Right-click on the Mouse button and select the second option from the Paste Options.
- And, there you go! All the formulas will remove by just following the simple steps. As you can see the formula bar does not contain any formula now.
Read More: How to Remove Formulas from Entire Excel Workbook
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will teach you how to remove formula when filtered in Excel. I hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
Related Articles
<< Go Back to Remove Formulas in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
