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**