How to Remove Vlookup Formula in Excel (6 Methods)

VLOOKUP is one of the most significant functions of Microsoft Excel. We use this function to look up any object from a table or a range. An Excel file containing the VLOOKUP formula takes lots of time to load. Removing VLOOKUP  also reduces the file size. So, we may want to remove the Excel VLOOKUP  formula in such a way that the value remains unchanged. This article will light on 6 methods to remove the VLOOKUP formula in Excel.

How to Remove VLOOKUP Formula in Excel: 6 Handy Methods

We will discuss several methods that will help users withdraw the VLOOKUP function without hampering the values. We will use the below data set to explain the methods. This data set contains the VLOOKUP  formula to get the outcome.

1. Use Ribbon Options to Remove VLOOKUP Formula

We will show the ribbon shortcut to remove the VLOOKUP formula in Excel.

Step 1:

• Go to Cell F7, which contains the come. In the function bar, the VLOOKUP formula is shown.

Step 2:

• Now, click on Cell F7.
• Then select Copy from the Clipboard group.

Step 3:

• Now, go to Paste from the Clipboard group.
• Select Values(V) from the Paste Values.

Step 4:

• Again, click on Cell F7. Now, look at the formula bar.

No formula is showing now. That formula has been removed from that cell.

2. Keyboard Shortcut to Search and Terminate Vlookup in Excel

We will first search the VLOOKUP formula and then terminate that from Excel. We do not need to identify the formula manually.

Step 1:

• Press Ctrl+G.
• The Go To dialog box will appear. Select Special from that box.

Step 2:

• Choose Formulas from the Go To Special box.
• Then press OK.

This process identifies the cells holding VLOOKUP formulas.

Step 3:

• To view the cells with the VLOOKUP formula specifically, we change the color of the fonts from the Font group.

Step 4:

• Cell F7 is the cell that contains a VLOOKUP formula. Click on that cell. The formula bar shows the VLOOKUP function.

Step 5:

• Now, press Ctrl+C to copy that cell.

Step 6:

• Press Alt+E+S, and a new dialog box will appear named Paste Special.

Step 7:

• Select the Values feature from the Paste option.
• Then press OK.

Step 8:

• Again, click on Cell F7. Observe the formula bar again.

In the formula bar, no formula appears. Only a single value is showing.

3. Use Context Menu to Withdraw VLOOKUP Formula

In this section, we will show a simple mouse shortcut to withdraw the VLOOKUP formula.

Step 1:

• First, click on Cell F7 which contains a VLOOKUP formula.

Step 2:

• Now, press the right button of the mouse. Then press Copy from the list.

Step 3:

• Again, press the right button of the mouse.
• Choose Values(V) from the Paste Options.

Step 4:

• Again, click on Cell F7.

Now, notice the formula bar. The VLOOKUP formula is no longer there. But the value remains the same.

4. Alternate Way Using the Mouse Trick to Remove VLOOKUP

We will show another way to remove the VLOOKUP formula using the mouse shortcut.

Step 1:

• First, select Cell F7 that contains a VLOOKUP  The formula bar indicates the VLOOKUP formula.

Step 2:

• Now, set the cursor on the marked position. Then press the right button of the mouse. Keep pressing the right button.

Step 3:

• Move the cursor to the adjacent cell on the right side.
• Then, a list will show. Select the Copy Here as Values Only option.

Step 4:

• Now, click on Cell G7. Look at the formula bar.

No formula is showing in the formula bar, though we copy a cell holding a formula.

5. Remove VLOOKUP Formula from Multiple Sheet

We want to remove VLOOKUP from multiple sheets at a time. This is possible if the formula contains the same cell of the selected sheets.

Here, we have two sheets named Worksheet_1, and Worksheet_2. Both of the worksheets contain a VLOOKUP formula on cell F7.

Step 1:

• First, select both sheets using the Shift button.

Step 2:

• Now, click on Cell F7 of any of the sheets. The VLOOKUP formula is present in that cell.
• Press Ctrl+C to copy that cell.

Step 3:

• Now, press the Right button of the mouse to Paste the value.
• Select Values(V), from the Paste options.

Step 4:

• Now, click on Cell F7 of the Worksheet_1.

The formula has vanished from the cell.

Step 5:

• Now, go to the Sheet name bar.
• Press the right button of the mouse.
• Select the Ungroup Sheets option.

Step 6:

• Now, click on Cell F7 of the Worksheet_2.

No formula exists on this cell.

6. VBA Macro to Remove VLOOKUP Formula in Excel

By using VBA Macro code we can remove all the VLOOKUP formulas from an Excel file at a time. No matter how many formulas exist on a sheet.

We will consider the below sheet where two VLOOKUP formula exist on Cell G5 and G6.

Step 1:

• First, go to the Developer tab.
• Then click on the Record Macro option.
• Put the name “Remove_Vlookup_Formula” on the Record Macro box.

Step 2:

• Now, click on Macros.
• Select the Remove_Vlookup_Formula macro and click on the Step Into.

Step 3:

• Now, paste the following VBA code on the command window.

Sub Remove_Vlookup_Formula()
Dim Location As Range
Dim Sheet_property As String
Sheet_property = InputBox("Enter Desired Sheet Name: ")
Set Location = Sheets(Sheet_property).Cells
Location.Copy
Location.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Step 4:

• Click the marked box from the main tab to run the code. Or we can press F5 to run the code.

Step 5:

• An input box will appear. Input the desired sheet name Sheet_VBA.

Step 6:

• Now, click on Cell G5.

In the below, we can see the sheet name. And in the formula bar, no formula is showing.

Things to Remember

• When you want to remove all VLOOKUP formulas from a single sheet at a time then apply VBA operation.
• When need to remove the VLOOKUP formula from multiple sheets with the same position then, apply the 6th method.

Conclusion

In this article, we discussed how to remove the VLOOKUP formula in Excel I hope this will satisfy your needs. Please give your suggestions in the comment box.

Related Articles

• 10 Best Practices with VLOOKUP in Excel
• 7 Practical Examples of VLOOKUP Function in Excel
• VLOOKUP Example Between Two Sheets in Excel
• How to Use Dynamic VLOOKUP in Excel
• Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
• How to Make VLOOKUP Case Sensitive in Excel
• VLOOKUP from Another Sheet in Excel
• How to Use VLOOKUP Formula in Excel with Multiple Sheets
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• How to Hide VLOOKUP Source Data in Excel
• How to Copy VLOOKUP Formula in Excel

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel